SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 bitsmed note - Time query problem
 New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

usafelix
Yak Posting Veteran

Hong Kong
97 Posts

Posted - 08/29/2014 :  01:11:34  Show Profile
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
Constraint Violating Yak Guru

405 Posts

Posted - 08/29/2014 :  14:45:44  Show Profile
Please post:
- sample data from your table
- the result you get from the query
- the result you had expected to get from the query
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

405 Posts

Posted - 08/29/2014 :  14:48:03  Show Profile
Never mind - this is duplicate of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=195956
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000