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:40:08
|
| Anyone can help to edit this query to show the total by shop on time range ?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) |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-08-29 : 15:34:09
|
| Well now - I think I might have see this question before ... |
 |
|
|
|
|
|
|
|
Sql query by time range + shop