SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 group time + shop & insert blank row with dotline
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

Hong Kong
165 Posts

Posted - 08/25/2014 :  23:32:13  Show Profile  Reply with Quote
Anyone can help to edit this query to output this result, I would like in each time range between insert of blank row or dotline and time+total of
each shop of total amt, and qty ?

date shop products qty amount
13:00 shopA mask01 2 $5
13:00 shopA mask02 3 $15
Total shopA 5 $20

14:00 shopA mask03 2 $20
Total ShopA 2 $20

14:00 shopB mask01 3 $10
14:00 shopB mask02 2 $10
Total shopB 5 $20

Grand Total 19 $100
my query in below :
SELECT CONVERT(varchar(13), xsodetail.createdatetime, 121) + ':00' as reportdate,
isnull(xsodetail.shopcode,'') as shop,
isnull(fl_one_piece_mask_master.description,'One Piece Mask by hour Total =>') as Mask_Products,
sum(xsodetail.salesqty) as qty,
Sum(xsodetail.itemamt) AS SalesAmount

FROM xsodetail
inner join fl_one_piece_mask_master on fl_one_piece_mask_master.sku= xsodetail.sku

WHERE xsodetail.txdate = Convert(varchar, GETDATE(),112) and xsodetail.itemamt>0
GROUP BY grouping sets((xsodetail.createdatetime,xsodetail.shopcode,fl_one_piece_mask_master.description,xsodetail.salesqty,xsodetail.actualsalesamt),())
order by xsodetail.createdatetime,xsodetail.shopcode
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000