Name Qty Location
-----------------------------------
Item A 2 Warehouse 1
Item A 5 Warehouse 2
Item B 3 Warehouse 1
Item C 1 Warehouse 1
Item B 6 Warehouse 2
How is the sql command/query/store procedure
to generate result like this?
Name Qty
-----------------
Item A 7
Item B 9
Item C 1
I want to get the list of items present in that order based on the confidentiality code of that product or Item and confidentiality code of the user.
I display the list of orders in first grid, by selecting the order in first grid I display the Items present in that order based on the confidentiality code of that item.
whenever order in 1st grid is selected i want to display the items that the item code should be less than or equal to the confidentiality code of the logged-in user other items should not display.
If the all the items present in the order having confidentiality code greater than Logged-in user at that time the order no# should not display in the first grid.
I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?
I am using the code below to get all the children of a particular product and it is working fine. How to get the particular product's id in the select statement. for example, i need to show 891 in a separate column for all the records returned by the query below.
DECLARE @Hierarchy TABLE (Product_Id INT, Parent_Product_Id INT) INSERT INTO @Hierarchy VALUES (123, 234) INSERT INTO @Hierarchy VALUES (234, 456) INSERT INTO @Hierarchy VALUES (456, 678) INSERT INTO @Hierarchy VALUES (678, 891) INSERT INTO @Hierarchy VALUES (891, NULL)
I've just downloaded SQL Server Express (with advanced services) from both the main SQL Server Express website and from MSDN. When I right click on the exe I downloaded and go into properties the Product Version says SQLEVALVER. To me this implies it is an evaluation.
Can I confirm this is just a mistake and if we start to redistribute the product it won't timeout in the future? I am sure it is not an evaluation but just want to make absolutely sure.
Hello everyone! I've got a problem with a real slow query, I would be very happy if somebody has any idea to improve the speed of it... The idea is to get the top 2 products, a customer hasn't bought wich are in his interest...
query (simplificated) ------------------------------------------------- SELECT TOP 2 prodID, Title, Price FROM bestSold7Days WHERE prodID NOT IN (SELECT prodID FROM orders INNER JOIN orderProducts ON orders.orderID = orderProducts.orderID WHERE (orders.custID=394)) AND (prodType = COALESCE((SELECT TOP 1 products.prodID FROM orders INNER JOIN orderProducts ON order.orderID = orderProducts.orderID INNER JOIN products ON orderProducts.prodID = products.prodID WHERE (orders.custID=394) GROUP BY products.prodType ORDER BY SUM(orderProducts.PCS) DESC), 2)) ------------------------------------------------- end query
(COALESCE is for replacing if the customer hasnt ordered anything, or hasnt ordered anything of this type)...
I have a transaction table (which contains details of all products on all orders) and is have a discrepancies table which includes details on products that couldnt been found (hence error in stock count)...
Now I want to write a query returning details of all products from a specfic orders.. pretty simple
SELECT product, description, qty from TRANSACTIONS where order_no = 'xxx'
but i also want to add an extra boolean column (true if product is in discrepancies table and false if product is not in discrepancies table...will become checkbox at front end)... im not sure how to write this bit....
I know it would a transaction table LEFT JOIN to discrepancies table, but I do not know how to write syntax to add extra column...
Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record. My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam ALTER PROCEDURE [dbo].[UpdateInvoiceSummary] @Invoice_ID intAS DECLARE @Amount intBEGIN SELECT Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX FROM InvoiceItems AS items GROUP BY Invoice_ID HAVING (Invoice_ID = @Invoice_ID) Update Invoices SET Amount = items.Amount WHERE Invoice_ID =@Invoice_IDEND
I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode 1A 1B 24 28 26 310 312 410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10 - 2 and 11 - 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
Lest Say with File-stream we have a table document where the file-stream column is located, then we have another table called product-document where the productID and the Document-node are both foreign key of the Product and the Document tables respectively. if i want to query the file-stream document for a specific product, i can just join all three tables, but with file-table there is something i probably didn't get as how to query the file table and get a document for a specific product.
I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).
I have a table of items (i.e. products). Â In some cases, items can be components of another item (called "Kits"). Â In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items". Â In our Items table, we have a field called "Parent_Item_Id". Â Records for Child Items contain the Item Id of the parent. Â So a sample of my database would be the following:
Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.
So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item. Â So an example of the report would be the following:
Name | Available Qty -------------------------- Kit A | 15 Kit B | 25 Item 4 | 100
Hi, I am a newbie, this is my first post (please go easy).
Iam at the moment trying to set up a query for someone looking for a property on an estate agents website.
From a drop down menu, the user can: select an area (where they may like to live) from a list of areas. select an amount of bedrooms from a list of bedrooms select a minimum price from a list of prices select a maximum price from a list of prices.
The query I worked out for this is as follows: $data = mysql_query("SELECT * FROM property WHERE area like '$area' and bedrooms like '$bedrooms' AND price BETWEEN '$min_price' AND '$max_price'") or die(mysql_error());
This seems to work fine and shows all the properties that meet the criteria onto my webpage.
However, I then thought, someone may not care which area they live in and want to see all properties in all the areas, so I decided to add the option 'All areas' to my 'areas' list, I then did the same for the other lists, eg 'all bedrooms' option to my bedrooms list and so on.
I am now trying to write a query that incorporates where the 'all..' option is selected and have become very stuck! Can someone set me off in the right direction for this. I hope that makes sense?!?!
Let's say I have the following table:entry product quality1 A 802 A 703 A 804 B 605 B 906 C 807 D 808 A 509 C 70I'm looking for a way to find the average "quality" value for aSEQUENTIAL GROUPING of the same Product. For exmple, I need anaverage of Entry 1+2+3 (because this is the first grouping of the sameproduct type), but NOT want that average to include row 8 (which isalso Product A, but in a different "group".)I'm sure it can be done (because I can describe it!), but I'll be amonkey's uncle if I can figure out how. I would imagine it wouldinvolve some sort of running tally that references the next record asit goes... to see if the product type has changed. Perhaps use of atemporary table?Muchas gracias!!Cy.
I've never been the most articulate person in the world, so i thoughthe best way to illustrate the script I am looking for help with, is toprovide you with two tables and the results I require.Table 1ProductName1ProductName2QtyAAAA-12BBBB-13CCCC-14Table2ProductNameQtyAA2BB-13DD6Desired ResultsProductNameQtyDD6Regards,Ciarán
I am currently working on a website that deals with sales of products. For one of my pages for the website I need it to be able to change the current sales information for a specific product.
The top part of the following code selects the specific product however I cannot get the update query to work.
Code: $describeQuery = "SELECT p.ID, p.NAME, dt.[Year], dt.[Month], dt.SalesVolume FROM Products p join (select ProductCode, sum(SalesVolume) as SalesVolume, [Year], [Month] from MonthlySales group by ProductCode, [Year], [Month])dt on dt.ProductCode = p.ID WHERE [NAME] = '$desiredProduct' AND [Year] = '$desiredYear' AND [Month] = '$desiredMonth'";
$editQuery = "UPDATE MonthlySales SET SalesVolume = '$NewSales' WHERE ID = '$desiredProduct' AND Year = '$desiredYear' AND Month = '$desiredMonth'";
I'm not sure if this is even possible but can i pull out rows from two tables (that have a one-to-many relationship) but only if they satisfy a few conditions.
tblWine tblSources ID Name ID WineID Source Price Status ----------------- ----------------------------------------- 1 Le Dome 1 1 Smith 100.00 IB 2 Teyssier 2 1 Jones 110.00 IB 3 Muscat 3 1 Hill 100.00 DP 4 2 Smith 135.00 DP 5 2 Hill 125.00 DP
I only want to pull out row that contain the cheapest wine for their status. So the result would look something like this.
tblWineSources WineID Name SourceID Source Price Status ------------------------------------------------------------- 1 Le Dome 1 Smith 110.00 IB 1 Le Dome 3 Hill 100.00 DP 2 Teyssier 5 Hill 125.00 DP
Hello everyone,I'm trying to solve this problem but can't seem to figure out how tostart. I would like to create a rating system where people can vote(1-5 stars) on randomly displayed items. The randomly displayed itemsshould either have very high ratings OR a very low number of ratings.For example, only return items in the top 20th percentile *OR* itemswith fewer than 5 votes.The question is, how would I write an SQL query to return such aresult? Is it even possible? Should this be handled by my applicationrather than the database?For simplicity, let's assume I have the following table:tbl_items-----------------item_iditem_nameavg_ratingnum_votes-----------------Any help or pointers in the right direction would be greatlyappreciated. My apologies in advance if the solution is obvious and Iam clearly missing the point ;-)
I have table1 with orderID and demographic info. Table2 with orderID and items. I would like to have a results display like this: OrderIDDemographicInfo Item1Item2Item3....ect One line per order. When I do a join I displaying all items in different rows.
select updatedb.callref, updatedb.updatetxt, updatedb.udsource, opencall.suppgroup from updatedb left join opencall on updatedb.callref=opencall.callref
where udindex = '0' and suppgroup = 'SUPPORT' and (updatetxt like '%' + @Word + '%')
And opencall.status <> '17'This means that when they search for items and they separate each word it is "and" between each one.
They would like it to be more fuzzy with "and" and "or". How can I adapt this?
I have a dropdown list with checkbox and when I select multiple options and search, its returning only the last selected value in the grid. Here is the code I use it for search. Designation is the column where I bind its values to the checkbox.
Use the Northwind database Products table as an example.Purchasing dept gets a report showing when inventory items on hand qty arebelow the reorder level.easy enough:Select ProductID, ProductName, SupplierID, UnitsInStock, ReorderLevelfrom Productswhere (UnitsInStock < ReorderLevel)Results:ProductID ProductName SupplierID UnitsInStock ReorderLevel2 Chang 1 17253 Aniseed Syrup 1 1325It would be nice to know what other products are purchased from this samevendor in case other items are close to their reorder level.All products for Supplier ID 1Select ProductID, ProductName, SupplierID, UnitsInStock, ReorderLevelfrom Productswhere SupplierID = 1Results:ProductID ProductName SupplierID UnitsInStock ReorderLevel1 Chai 1 39102 Chang 1 17253 Aniseed Syrup 1 1325This shows there is 1 more product (Chai) that also comes from Supplier 1.Is there a way to show all items from a vendor when some of the items arebelow the reorder level without needing a separate query for each vendor?Thanks
I have a query that returns material(items) that are used in an event on a certain day.
SELECT C.categoryName, count(I.itemID) AS InMission from items as I RIGHT JOIN Categories AS C on I.categoryID = C.categoryID INNER JOIN LinkMissionItem as LM on I.itemID = LM.itemID INNER JOIN Missions as M on LM.missionID = M.MissionID where '2015/12/19' BETWEEN M.freightLeave and M.freightReturn AND isReturned = 0 GROUP BY C.categoryName, C.categoryID ORDER BY C.categoryID
There are a total of 20 categories and I would like all the categories listed in the result even though there are no items booked in a mission. At the moment, I can only get the categories that have items in that category booked in a mission. I hoped that the RIGHT JOIN on the categories table would do the trick but it doesn't.
I am looking to filter the query using the free text predicate Contains for one of the column that have dimensions of the items. The query is something like
Select * from sampletable where contains(filtercolumn, 'Near("*cm , *cm , *cm , *Kg.",10,True)')
Data in the filtercolumn is something like 64cm x 25cm x 35cm = 10Kg.
The query do not return any data.I also tried running the below query to see if cm* can be searcher for, but with no result again
Select * from sampletable where contains(filtercolumn,, '"cm*"')
If I do something like below, it does return all columns that have 35cm within the text. Select * from sampletable where contains(filtercolumn,, '"35cm"')
I have a query that returns the data about test cases. Â Each test case can have multiple bugs associated to it. Â I would like a query that only returns the test cases that have all their associated bugs status = closed.For instance here is a sample of my data
TestCaseID TestCaseDescription  BugID BugStatus 1         TestCase1            1    Closed 2         TestCase1            2    Open 3         TestCase2            11   Closed 4         TestCase2            12   Closed 5         TestCase2            13   Closed
How can I limit this to only return TestCase2 data since all of that test case's bugs have a status of closed.
hello there... i need help with cross tab query with sum(case actually iam using ms access 2007 and vb.net 2005 i have problems with the sintaxis ill paste the code below:
TRANSFORM SUM(cantidad*(case tipo WHEN 'Entrada' else 1 then 1 end 0)-cantidad(case tipo WHEN 'Salida' else 1 then 0 end)) AS TOTAL SELECT S.nombre,S.grupo,SUM(cantidad) AS Sub FROM Movimiento AS M, Punto AS P, Sorteo AS S WHERE(M.id_sorteo = s.id_sorteo And M.id_punto = P.id_punto) GROUP BY P.nombre, S.nombre, S.grupo ORDER BY grupo PIVOT P.nombre"
i need the correct sintaxis for this sentece: SUM(cantidad*(case tipo WHEN 'Entrada' else 1 then 1 end 0)-
or another way to do this , the finally of this query is get the actual inventory.
Hi i'm trying to run a CASE, Database is TS_Positions Column is Position Type, we usually get data being -1 or 1, i would like use the Function CASE to change that in my query(easier to read) 1=being a BUY... -1=being a SELL. For Some reason my query will NOT Work, Every other part works just not the CASE part.. Any ideas?????? Query:
SELECT CASE PositionType WHEN PositionType '1' THEN 'BUY' WHEN PositionType '-1' THEN 'SELL' AS [BS}, CAST(TradePrice as float(20,8) )AS [Price], Quantity AS Volume, LEFT(Contracttype,1) as KIND, strike, expiringdate, comment, (SUBSTRING (contract+CONVERT(varchar,expiringdate),1,20)) AS [FEEDCODE] FROM TS_Positions WHERE (Contract LIKE 'LI%') OR (Contract LIKE 'LK%') OR (Contract LIKE 'LL%') OR (Contract LIKE 'LM%') ORDER BY Contract
The below query should only use the AND parts if the variables contain a value.
How do I fix the CASE clause??
Thanks.
Code Block SELECT SeniorMgr, Manager FROM Client f INNER JOIN Portfolio p ON p.ClientID = f.ClientID INNER JOIN Staff s ON (s.ClientID = p.ClientID) AND (s.FundID = p.PortfolioID) AND (s.ClientID = f.ClientID) WHERE s.ClientID = @ClientID AND s.FundID = @FundID CASE WHEN @SeniorManager != '' THEN AND SeniorMgr = @SeniorManager CASE WHEN @Manager != '' THEN AND Manager = @Manager END
I am trying to solve a data conversion problem using CASE and need some advice.
Column "tsstl9000details_vfeed.t.amountserviceaffected" should have numeric data entered, but since it is free-form via a web interface, alphanumeric data sometimes gets entered. I'm wanting to get the numeric portion of the column value with the following snippet, and ending up with an error. It works fine as long as the column value is numeric:
select f.assignedlinenum, t.amountserviceaffected, CAST(((CAST((CASE t.amountserviceaffected WHEN ISNUMERIC(t.amountserviceaffected) THEN t.amountserviceaffected ELSE LEFT(t.amountserviceaffected,PATINDEX(' ',t.amountserviceaffected)) END) AS REAL) / CAST(f.assignedlinenum AS REAL)) * 100) AS INTEGER) from fccdetails_vfeed f left outer join tsstl9000details_vfeed t on t.eventnum=f.eventnum where f.eventnum='wisw029147'
assignedline = 10910 actual column value amountserviceaffected = 151 lines actual column value
Server: Msg 245, Level 16, State 1, Line 1 Syntax error converting the varchar value '151 lines' to a column of data type int.
I want get get results in sql that are all written in UPPERCASE but I want to receive them in Initial Case format I know UPPERCASE is UPPER lowercase is lower but what is Initial Case(first letter Capital in a word)
In one SQL server table table1 it contains following data ID Date CA01-001 01/01/2000 CA01-002 02/01/2000 CA01-003 01/15/2000 CA01-004 02/11/2000 CB02-001 01/01/2001 CB02-002 02/01/2001
ID is grouped by first four characters CA01 or CB02, then each record increased by 1 to be 001, 002 .. untill 999 in sequence
How do I write SQL query just to get each group largerest record such as for our case: CA01-004 02/11/2000 CB02-002 02/01/2001
