2 coloumns from the same table with different date

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

2 coloumns from the same table with different date

Author

Topic

pavlos
Yak Posting Veteran

59 Posts

Posted - 2011-12-15 : 19:11:28

hey all,

i am having trouble coding something at work and sql isn't really my forte.. don't even know what to search for to find a correct solution.

to make it simple for you.

I need to present a table which consists of
Store name | Monthly Target | Target to the current date

So:

lets say I have a database called 'massive' with the store name = store, date = 2011-01-01, targetday = $xxxx

I have gone like this

select store, sum(targetday) from massive
where date between '2011-01-01' and '2011-01-31'
group by store

this will return the store name and target for the whole month but I cant add the total target to a current day as I am already using a particular sum.

I also want to return the sum(targetday) on for the days '2011-01-01' to '2011-01-10' - say that the 10th was the present day

So do I need to combine SQL statements?

I was thinking something like this (obviously this doesnt run)

Select store, (select sum(targetdate) from massive where date between '2011-01-01' and '2011-01-05') AS 'Month to day target', sum(targetday) from massive
where date between '2011-01-01' and '2011-01-31'
group by store

i hope this makes sense

even if you guys could point me in the direction of a good example that would be fantastic..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-15 : 19:35:18

[code]
select store,
sum(targetday) as whole_month,
sum(case when date between '2011-01-01' and '2011-01-10' then targetday else 0 end)
from massive
where date between '2011-01-01' and '2011-01-31'
group by store
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-15 : 19:35:52

Would this work for you?

SELECT store,
       SUM(targetday),
       SUM(CASE WHEN date BETWEEN '2011-01-01' AND '2011-01-10' THEN targetday END) AS TargetToJan10th
FROM   massive
WHERE  date BETWEEN '2011-01-01' AND '2011-01-31'
GROUP BY
       store

pavlos
Yak Posting Veteran

59 Posts

Posted - 2011-12-15 : 19:37:38

SELECT
StoreName,
(SELECT SUM(Amount) FROM BUDGET_LINES WHERE Date between '2011-01-01' and '2011-01-31') AS 'Mothly Target',
(SELECT SUM(Amount) FROM BUDGET_LINES WHERE Date between '2011-01-01' and '2011-01-10') AS 'Target to day'
FROM BUDGET_LINES

GROUP BY StoreName

pavlos
Yak Posting Veteran

59 Posts

Posted - 2011-12-15 : 19:38:11

hopefully the post above this one makes more sense on what i am trying to achieve.. cheers

pavlos
Yak Posting Veteran

59 Posts

Posted - 2011-12-15 : 19:42:35

sorry guys, didn't see the replies... khtan... your response worked.. thanks again!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources