Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
usafelix
Posting Yak Master
165 Posts |
 Posted - 2014-08-25 : 23:32:13
|
| 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 ofeach shop of total amt, and qty ?----------------------------------------date shop products qty amount13:00 shopA mask01 2 $513:00 shopA mask02 3 $15Total shopA 5 $20=======================================14:00 shopA mask03 2 $20Total ShopA 2 $20========================================14:00 shopB mask01 3 $10 14:00 shopB mask02 2 $10Total shopB 5 $20Grand 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 SalesAmountFROM xsodetailinner join fl_one_piece_mask_master on fl_one_piece_mask_master.sku= xsodetail.skuWHERE xsodetail.txdate = Convert(varchar, GETDATE(),112) and xsodetail.itemamt>0GROUP BY grouping sets((xsodetail.createdatetime,xsodetail.shopcode,fl_one_piece_mask_master.description,xsodetail.salesqty,xsodetail.actualsalesamt),())order by xsodetail.createdatetime,xsodetail.shopcode |
|
|
|
|
|
|
|
group time + shop & insert blank row with dotline