minus results - multiple select statment (i think)

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

minus results - multiple select statment (i think)

Author

Topic

pavlos
Yak Posting Veteran

59 Posts

Posted - 2012-02-19 : 23:51:05

Hey guys,
I have a data set that returns the store, date, sales

This is a query that works

SELECT store, sales FROM StoreSales
WHERE date >= 13-02-2012
AND date <= 19-02-2012

This works.. but I want to find out how much more I have sold this week than last week

For example
2 weeks ago store 1 sold 500 and store 2 sold 400
last week store 1 sold 550 and store 2 sold 300

so my results would look like
store 1: 50
store 2: -100

This is what i wanted to do

Select
Store,
(Select sales - (sales where WHERE date >= 05-02-2012
AND date <= 12-02-2012)) AS 'Difference'
FROM StoreSales
WHERE date >= 13-02-2012
AND date <= 19-02-2012

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-02-19 : 23:55:53

[code]
SELECT store,
sales_1 = sum(case when date between '2012-02-06' and '2012-02-12' then sales else 0 end),
sales_2 = sum(case when date between '2012-02-13' and '2012-02-19' then sales else 0 end),
diff = sum(case when date between '2012-02-06' and '2012-02-12' then sales else 0 end)
- sum(case when date between '2012-02-13' and '2012-02-19' then sales else 0 end)
FROM StoreSales
WHERE date >= '2012-02-06'
AND date <= '2012-02-19'
group by store
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 11:54:30

[code]
SELECT store,currentsales,prevsales
FROM
(
SELECT store,DATEADD(wk,DATEDIFF(wk,0,GETDATE())-1,0) AS weekdate,
SUM(sales) AS currentsales
FROM Table
WHERE t.date >=DATEADD(wk,DATEDIFF(wk,0,GETDATE())-1,0)
AND t.date <DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
GROUP BY store
)t
CROSS APPLY (SELECT SUM(sales) AS prevsales
FROM table
WHERE Store = t.Store
AND date >=DATEADD(wk,-1,t.date)
AND date < t.date
)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources