urgent edit query for hourly sales and total

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.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

urgent edit query for hourly sales and total

Author

Topic

usafelix
Posting Yak Master

165 Posts

Posted - 2014-08-21 : 13:20:22

How to edit this query to output the time range by hourly from 8:00 to 23:00 pm.

I hope the result is
------------------------------
shopid date time range trx time item amount
A shop 20140821 08:00 - 09:00 08:15 am Apple $ 50
A shop 20140821 15:00 - 16:00 15:32 pm pie $ 20
A Shop total : 70

B shop 20140821 23:00 - 24:00 23:02 pm Apple $70

Grand total : $140
------------------------------------------------
SELECT CONVERT(varchar(13), xsodetail.txdate, 121) + ':00',xsodetail.dess as item, Sum(xsodetail.itemAmt) AS SalesAmount FROM xsodetail
WHERE xsodetail.txdate = Convert(varchar, GETDATE(),112)-3 and xsodetail.dess like '%[A-z]%'
GROUP BY CONVERT(varchar(13), xsodetail.txdate, 121), xsodetail.item

viggneshwar
Yak Posting Veteran

86 Posts

Posted - 2015-01-09 : 01:02:26

Use Grouping Sets that will give the subtotal

Regards
Viggneshwar A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources