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-29 : 01:11:34
|
| This query the output within from time at 00:00-12:00 the sales amount is correct. After 13:00-23:59 of time by sales amount is wrong. How to correct this query can handle 13:00 to 23:59?------------------------------------------------------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 : 14:45:44
|
| Please post:- sample data from your table- the result you get from the query- the result you had expected to get from the query |
 |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-08-29 : 14:48:03
|
| Never mind - this is duplicate of [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=195956[/url] |
|
|
|
|
|
|
|
|
bitsmed note - Time query problem