Query problem

[xloop]

Hi

I need to produce a report which will have the months from Oct to Sep (columns) and for the rows i need certain criteria, i.e will look something like this for instance

Oct Nov Dec Jan .... etc
Forecast 10 20 40 50
Cost 30 44 55 22
Difference .. .. .. .. .... etc


the forecast, cost and difference being queries for data based on Oct or nov or dec date etc..

Does this make sense??

I almost want to make a pivot query by using Forecast,Cost and difference as the columns and the dates as the rows, but i don't!!

Hope someone can shade a bit of light.
Thanks

 

[tlbroadbent]

You probably want a crosstab or pivot query. You didn't tell us the source of the data and its format so I can't be sure. If you need to convert row data to columns, a crosstab is in order. Search this forum there are lots of threads dealing with crosstabs. Click on the keyword search tab to get to the search page for the forum.

Some sample threads:

thread183-388437
Thread183-400202 Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[xloop]

If you can image a report setup like this:

.........OCT....NOV...DEC...JAN

FC.......200....300...40....50
JC.......400....30....44....33
AC.......333....23....33....22

So, FC,JC and AC are all seperate queries referencing different tables with completely different data.

To get a figure for FC for Oct i could do this:
SELECT SUM(Total) FROM TableA WHERE date between 01/10/2002 AND '30/10/2002'
..same for nov,dec..etc

Same principal for JC and AC.

I could create a temporary table:

QueryName
Oct
Nov
Dec
Jan

Then do inserts for each month and each query but this doesn't seem like the most efficient way of doing it.

I'd like to be able to do this in a loop of some sort.

Can you see where i'm coming from???

 

[MeanGreen]

xloop,
You can use the crosstab to create what you are looking for. I did one up against the NORTHWIND database with "TOTAL SALES" and "TOTAL QUANTITY" to give you an example. Notice how you can use the UNION to group your outputs together. You should be able to use this to understand how to get what you want:

select 'TOTAL SALES','OCT' = SUM(case when LEFT(datename(month,orderdate),3) = 'OCT' then unitprice * quantity else 0 end),
'NOV' = SUM(case when LEFT(datename(month,orderdate),3) = 'NOV' then unitprice * quantity else 0 end),
'DEC' = SUM(case when LEFT(datename(month,orderdate),3) = 'DEC' then unitprice * quantity else 0 end),
'JAN' = SUM(case when LEFT(datename(month,orderdate),3) = 'JAN' then unitprice * quantity else 0 end),
'FEB' = SUM(case when LEFT(datename(month,orderdate),3) = 'FEB' then unitprice * quantity else 0 end),
'MAR' = SUM(case when LEFT(datename(month,orderdate),3) = 'MAR' then unitprice * quantity else 0 end),
'APR' = SUM(case when LEFT(datename(month,orderdate),3) = 'APR' then unitprice * quantity else 0 end),
'MAY' = SUM(case when LEFT(datename(month,orderdate),3) = 'MAY' then unitprice * quantity else 0 end),
'JUN' = SUM(case when LEFT(datename(month,orderdate),3) = 'JUN' then unitprice * quantity else 0 end),
'JUL' = SUM(case when LEFT(datename(month,orderdate),3) = 'JUL' then unitprice * quantity else 0 end),
'AUG' = SUM(case when LEFT(datename(month,orderdate),3) = 'AUG' then unitprice * quantity else 0 end),
'SEP' = SUM(case when LEFT(datename(month,orderdate),3) = 'SEP' then unitprice * quantity else 0 end)
from orders o join [order details] od
on o.orderid = od.orderid
where orderdate between '10/1/1997' and '9/30/1998'
UNION
select 'TOTAL QUANTITY','OCT' = SUM(case when LEFT(datename(month,orderdate),3) = 'OCT' then quantity else 0 end),
'NOV' = SUM(case when LEFT(datename(month,orderdate),3) = 'NOV' then quantity else 0 end),
'DEC' = SUM(case when LEFT(datename(month,orderdate),3) = 'DEC' then quantity else 0 end),
'JAN' = SUM(case when LEFT(datename(month,orderdate),3) = 'JAN' then quantity else 0 end),
'FEB' = SUM(case when LEFT(datename(month,orderdate),3) = 'FEB' then quantity else 0 end),
'MAR' = SUM(case when LEFT(datename(month,orderdate),3) = 'MAR' then quantity else 0 end),
'APR' = SUM(case when LEFT(datename(month,orderdate),3) = 'APR' then quantity else 0 end),
'MAY' = SUM(case when LEFT(datename(month,orderdate),3) = 'MAY' then quantity else 0 end),
'JUN' = SUM(case when LEFT(datename(month,orderdate),3) = 'JUN' then quantity else 0 end),
'JUL' = SUM(case when LEFT(datename(month,orderdate),3) = 'JUL' then quantity else 0 end),
'AUG' = SUM(case when LEFT(datename(month,orderdate),3) = 'AUG' then quantity else 0 end),
'SEP' = SUM(case when LEFT(datename(month,orderdate),3) = 'SEP' then quantity else 0 end)
from orders o join [order details] od
on o.orderid = od.orderid
where orderdate between '10/1/1997' and '9/30/1998'

Hope this helps.