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-28 : 22:08:11
|
| Anyone can help to give idea how to edit this query of current result format to desired result (group by different shop total)? My boss request to group by shop total in different time range of sales performance with compare current day and yesterday as well.current result.(Time range from 9:00am-23:00pm)Shop Time_slot cur Amt, yest Amt, Diff Amt, Sales DirectionAbc 10:59 $100 $50 +50 (+)Abc 11:59 $100 $50 +150 (+)Abc 12:59 $100 $50 +50 (+)BBB 11:59 $100 $50 +150 (+)BBB 12:59 $100 $50 +50 (+)------------------------------------------------------------------Desired Result .Shop Time_slot cur Amt, yest Amt, Diff Amt, Sales DirectionAbc 10:59 $100 $50 +50 (+)Abc 11:59 $100 $50 +150 (+)Abc 12:59 $100 $50 +50 (+) Total $300 $150 +$200 (+)BBB 11:59 $10 $50 -40 (-)BBB 12:59 $10 $50 -40 (-) Total $20 $100 -80 (-) ----------------------------------------------------------- select shop ,ltrim(str(datepart(hh,yourdatetimefield)))+':00 - '+ltrim(str(datepart(hh,yourdatetimefield)))+':59' as time_span ,sum(case when datediff(dd,yourdatetimefield,getdate())=0 then amt else 0 end ) as current_amount ,sum(case when datediff(dd,yourdatetimefield,getdate())=1 then amt else 0 end ) as yesterday_amount ,sum(amt*case when datediff(dd,yourdatetimefield,getdate())=1 then -1 else 1 end ) as diff_amount ,case sign(sum(amt*case when datediff(dd,yourdatetimefield,getdate())=1 then -1 else 1 end ) ) when 0 then 'Same' when 1 then 'Up' else 'Down' end as amount_direction from yourtable where yourdatetimefield>=cast(dateadd(dd,-1,getdate()) as date) and yourdatetimefield<cast(dateadd(dd,1,getdate()) as date) group by shop ,datepart(hh,yourdatetimefield) |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-08-29 : 02:54:46
|
Which version of SQL Server are you using?
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
|
usafelix
Posting Yak Master
165 Posts |
Posted - 2014-08-29 : 03:09:47
|
| This query is no problem in 00:00 - 12:00 am sales amount is right , after then 13:01 - 23:00 the sales amount is wrong . Thus I guess this query is missing handle 24 hour format? Sql server is 2008. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-08-29 : 03:32:50
|
Post a sample table with sample data so we have a chance to see what is going on.
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
|
|
|
|
Anyone can help edit this query ?