making 10 queries 1 - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

making 10 queries 1

Author

Topic

nosenseofhumor1
Starting Member

7 Posts

Posted - 2009-02-06 : 13:40:30

i have a data set that looks a bit like this:

Article | +/- | Qty | Date
123 | + | 10 | 1/1/09
123 | + | 9 | 1/1/09
321 | + | 4 | 1/2/09
123 | - | 7 | 1/2/09
123 | - | 2 | 1/4/09
321 | + | 5 | 1/6/09
321 | - | 1 | 1/6/09
321 | - | 5 | 1/6/09
321 | + | 2 | 1/6/09
123 | - | 1 | 1/7/09

I want to write 1 query to view my results like this

ARTICLE | 1/7/09 | 1/6/09 | 1/5/09 | 1/4/09 | 1/3/09 | 1/2/09 | 1/1/09
123 | 9 | 10 | 10 | 10 | 12 | 12 | 19
321 | 5 | 5 | 4 | 4 | 4 | 4 | 0

how do i do it without writing a query for each column and joining them together?

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-02-06 : 13:42:11

Research the PIVOT function - it looks like is what you're looking for

Some days you're the dog, and some days you're the fire hydrant.

nosenseofhumor1
Starting Member

7 Posts

Posted - 2009-02-06 : 13:45:57

not exactly, because each column is the summation of the values for all days <= the column header date. the column list can be static

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-06 : 13:50:34

Can you elaborate ? What is +/- sign for ? Where did you 1/5/09?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-06 : 14:01:52

It's both a running total AND a pivot...

E 12°55'05.63"
N 56°04'39.26"

nosenseofhumor1
Starting Member

7 Posts

Posted - 2009-02-06 : 14:04:54

imagine it to be inventory transactions,
on 1/1/09 we added 10 and 9 123s to our inventory
on 1/2/09 we added 4 321s to our inventory and subtracted 7 123s
etc

i want to calculate the inventory level for each article (rows) on each day (columns) for a predetermined span of days

nosenseofhumor1
Starting Member

7 Posts

Posted - 2009-02-06 : 14:07:02

yes, a running total and a pivot... but some of the days that are going to be columns do not have any entries for them. and the running total is dependant on the +/- column to determine the behavior of that record...

a typical running sum would just take
123 | + | 10 | 1/1/09
123 | + | 9 | 1/1/09
321 | + | 4 | 1/2/09
123 | - | 7 | 1/2/09
123 | - | 2 | 1/4/09
321 | + | 5 | 1/6/09
321 | - | 1 | 1/6/09
321 | - | 5 | 1/6/09
321 | + | 2 | 1/6/09
123 | - | 1 | 1/7/09

and get 29 123s...

nosenseofhumor1
Starting Member

7 Posts

Posted - 2009-02-06 : 14:09:14

addendum:
in answer to the 1/5 question, there were no transactions on 1/5, but i want to calculate the inventory level for this day anyway.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-06 : 14:24:13

[code]Declare @T table
(Article int,Qty int,Date datetime)

Insert @T
Select 123 ,10,'1/1/09' union all
Select 123 ,9 ,'1/1/09' union all
Select 321 ,4 ,'1/2/09' union all
Select 123 ,-7 , '1/2/09' union all
Select 123 ,-2 ,'1/4/09' union all
Select 321 ,5 , '1/6/09' union all
Select 321 ,-1 , '1/6/09' union all
Select 321 ,-5 , '1/6/09' union all
Select 321 ,2 , '1/6/09' union all
Select 123 ,-1 , '1/7/09'

Select Article,a,a+b as b,a+b+c as c,a+b+c+d as d,a+b+c+d+e as e,a+b+c+d+e+f as f,a+b+c+d+e+f+g as g
from
(Select Article,
Sum(Case When Date ='1/1/09' then Qty else 0 end)as a,
Sum(Case When Date ='1/2/09' then Qty else 0 end)as b,
Sum(Case When Date ='1/3/09' then Qty else 0 end)as c,
Sum(Case When Date ='1/4/09' then Qty else 0 end)as d,
Sum(Case When Date ='1/5/09' then Qty else 0 end)as e,
Sum(Case When Date ='1/6/09' then Qty else 0 end)as f,
Sum(Case When Date ='1/7/09' then Qty else 0 end)as g
from @T
Group by Article)M[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 08:33:19

quote:
Originally posted by nosenseofhumor1

i have a data set that looks a bit like this:

Article | +/- | Qty | Date
123 | + | 10 | 1/1/09
123 | + | 9 | 1/1/09
321 | + | 4 | 1/2/09
123 | - | 7 | 1/2/09
123 | - | 2 | 1/4/09
321 | + | 5 | 1/6/09
321 | - | 1 | 1/6/09
321 | - | 5 | 1/6/09
321 | + | 2 | 1/6/09
123 | - | 1 | 1/7/09

I want to write 1 query to view my results like this

ARTICLE | 1/7/09 | 1/6/09 | 1/5/09 | 1/4/09 | 1/3/09 | 1/2/09 | 1/1/09
123 | 9 | 10 | 10 | 10 | 12 | 12 | 19
321 | 5 | 5 | 4 | 4 | 4 | 4 | 0

how do i do it without writing a query for each column and joining them together?


SELECT Article,[1/7/09],[1/6/09],[1/5/09],[1/4/09],[1/3/09],[1/2/09],[1/1/09]
FROM
(
SELECT t.Article,t.Date,tmp.Total
FROM Table t
OUTER APPLY 
(SELECT SUM(CASE WHEN [+/-] = '-' THEN -1 * Qty ELSE Qty END) AS Total
FROM Table
WHERE Article=t.Article
AND Date<=t.Date
)tmp
)m
PIVOT (SUM(Total) FOR Date IN ([1/7/09],[1/6/09],[1/5/09],[1/4/09],[1/3/09],[1/2/09],[1/1/09]))p

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 08:35:12

and if you dates are dynamic use like this

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources