Fred writes "How to retrieve rows for which sum of the qty field equals to a specified number - let's consider a basic table as with 3 fields : item (pk), qty, orderid (fk) :
item qty orderid
**** *** *******
itemA 1 ord-18/07/06
itemB 2 ord-01/01/05
itemC 2 ord-18/07/06
itemD 3 ord-18/07/06
itemE 1 ord-18/07/06
I want to know belong ord-18/07/06's items, which ones to pick up in order to reach a total qty of 5. Answer is itemC (2) + itemD (3). I tried with various combinations of SUM,MAX,HAVING.. Can't make it works. Help appreciated. Rgds."
i have one query: SELECT MAX(salesid) AS salesid, max(salestitle) as salestitle FROM saleshistory s where list = 'sales' GROUP BY DATEADD(dd, DATEDIFF(dd, 0, salesdate), 0) order by salestitle
i m getting more than 1000 rows.. in output some of them are :
when i m doing like: SELECT MAX(salesid) AS salesid, max(salestitle) as salestitle FROM saleshistory s where list = 'sales' GROUP BY substring([salestitle],1,charindex(' ',[salestitle])-1)
i m getting an error Msg 536, Level 16, State 3, Line 1 Invalid length parameter passed to the substring function.
Table Structrue: Table Name : Custom_Table_SPPR_Master Fields : Sno int pk SPPR_No int pk Revison_No int pk rev_Date date prop_amt decimal claim_amt decimal avail_amt decimal Status int
I want the record where max of the revision no is seen for each sppr that is the result should the rows of sno: 4,6,8,9
i tried the following but in vein
select max(revision_no) ,sppr_no from Custom_Table_SPPR_Master group by sppr_no -- in this i am getting records, but i want * datas
select distinct(sppr_no) from Custom_Table_SPPR_Master where revision_no in(select max(revision_no) from Custom_Table_SPPR_Master group by revision_no) group by sppr_no
select sppr_no,count(revision_no) from Custom_Table_SPPR_Master where revision_no in(select max(revision_no) from Custom_Table_SPPR_Master group by revision_no) group by sppr_no,revision_no
SELECT TOP 10 COUNT(*) AS ItemsSold, ProductName, order_details_product_id, SUM(order_details_price) AS order_details_price FROM tbl_Detail_Order_Product WHERE (OrderStatus NOT IN (14, 15)) GROUP BY order_details_product_id, ProductName, order_details_price ORDER BY ItemsSold DESC
which works fine and have these results: Purchased Returned Sold Part Number $ 598.29 37 16684 $ 200.29 33 16654 $ 300.29 25 16184 . . . my problem is I want to show the returned column wich is:
WHERE (OrderStatus IN (14, 15)) I dont know how can I do that. I tried to write another query but it didnt worked correctly. plz help me. thanks
alter PROCEDURE [dbo].[spsite] @fromdate datetime, @todate datetime
AS BEGIN
select site as sitecode, count(id) as t from an where mydate >=@fromdate and mydate<=@todate, count(id) as p from an where p=1 and mydatestage1 >=@fromdate and mydatestage1<=@todate
Hi, I have a problem regarding this i have this statement where in i can count the number of data and display what category is that.... my problem is how can i sum itHere's the Statement
SELECT Count([PullOut].[SOType]) as Count, [SODescription].[SODescription] FROM PullOut INNER JOIN SODescription ON [PullOut].[SOType]=[SODescription].[SOType] WHERE PullOut.Area in ('Rizal','Cavite') GROUP BY [SODescription].[SODescription];
m.material_subtotal, m.cec_job_id,m.location FROM material m left outer join Other_Project op on m.project_id=op.other_proj_id left outer join Daily_Time_Entry dl on dl.project_id =op.other_proj_id inner join Employee_Mgmt emp on emp.emp_mgmt_id = dl.emp_mgmt_id inner join CEC_Job cec ON dl.cec_job_id = cec.cec_job_id where (dl.dt_date='3/14/2008'and m.material_date='3/14/2008') and (dl.project_type='Other Project' and m.project_type = 'Other Project') and (dl.cec_job_id=m.cec_job_id) and (dl.daily_type='Electrical') and (dl.project_id='18')
I'm trying to return an integer from the following table that returnsthe number of unique cities:tblEmployeesName CityJohn BostonFrank New YorkJim OmahaBetty New YorkThe answer should be 3.DECLARE @EmployeeCities intSELECT @EmployeeCities = SELECT ... ???How go I return one row/column into @EmployeeCities from a Count and aGroupBy?Headache already... Maybe it's too early...
I have a table (tblA) that records the RecordID, UserID andLastViewedDate (DateTime) of each record opened in tblB where RecordIDis the PK in tblB. I want to construct a query that groups all recordsin tblA by RecordID, filters by UserID and keeps only the most recent25 RecordIDs and deletes the rest.This gets me a recordset of all RecordIDs filtered by UserID in tblAbut I can't figure out how to sort it by LastViewedDate DESC and toeliminate those not in the Top25:SELECT RecordIDFROM dbo.tblAWHERE (UserID = 1234)GROUP BY RecordIDAny help is appreciated!lq
I have a data (txt) file which has the following structure
M_ID1: CID11,Date11 CID12,Date12
M_ID2: CID,Date21 CID,Date22
My problem now is it, to get that into a table (MID,CID,DATE) as the text file "groups" CID and DATE by MID and I have no idea how to read the file and transform it.
I have a query to get data group by High, Medium and Low so only three rows will be returned.
select sum(case .... ) then 1 else 0 end) as [Column1], sum(case .... ) then 1 else 0 end) as [Column2], sum(case .... ) then 1 else 0 end) as [Column3] from sometable Group by High, Medium, Low
I want to manually add High, Medium and Low to each row's first column. the final result should look like:
Column1 Column2 Column3 High 123 123 123 Medium 123 123 123 Low 123 123 123
