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
 Sql query by time range + shop
 New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

usafelix
Posting Yak Master

Hong Kong
103 Posts

Posted - 08/28/2014 :  22:40:08  Show Profile
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 Direction
Abc 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 Direction
Abc 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
Constraint Violating Yak Guru

433 Posts

Posted - 08/29/2014 :  15:34:09  Show Profile
Well now - I think I might have see this question before ...
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.08 seconds. Powered By: Snitz Forums 2000