Running Crosstab by Month - 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

Other Forums

MS Access

Running Crosstab by Month

Author

Topic

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-08-15 : 14:01:52

Hello,

How are you today?

I am trying to generate a report which looks like this:

March
Country 2002 2003 Change % Difference
USA 20 30 10 50%
Australia 20 5 10 -75%
Total 40 35 20

April
Country 2002 2003 Change % Difference
USA 20 30 10 50%
Australia 20 10 10 -50%
Total 40 41 20

% Difference is calculated using: (2003figure-2002figure)/2002figure * 100

I am not sure if I can calculate change and %Difference using SQL, worse case I can use a report writer to calculate these.

I ran this query

SELECT Book1.COUNTRY, Count(Book1.ED_CARD) AS Total, DatePart('yyyy',[ARRIVAL_DATE],1,0) AS [Year], DatePart('m',[ARRIVAL_DATE],1,0) As Month
FROM Book1
WHERE (((DatePart('yyyy',[ARRIVAL_DATE],1,0))=[PreviousYear] Or (DatePart('yyyy',[ARRIVAL_DATE],1,0))=[CurrentYear]))
GROUP BY Book1.COUNTRY, DatePart('yyyy',[ARRIVAL_DATE],1,0), DatePart('m',[ARRIVAL_DATE],1,0);

where I can enter 2002 for PreviousYear and 2003 for CurrentYear at run time

which gives me a list:

Country Total Year Month
USA 20 2002 5
USA 30 2003 5
Australia 10 2003 6

1. Is it possible to generate my report in the format I specified?
2. Can I run the calculations which I have described using SQL?

Feel free to respond with any questions

GreySky
Starting Member

20 Posts

Posted - 2003-08-15 : 14:52:48

Crosstab queries work with one "Value."

This means you can have:
Country 2002 2003
-USA 10 20
-Aus 20 30

But you can't have:
Country 2002 2003 % Diff
-USA 10 20 100
-Aus 20 30 50

However, if you only have two columns with values (i.e., 2002 and 2003 data), it is fairly easy to take the result of this crosstab as a base query, then add your other columns.

Something like:

Select Country, [2002], [2003], [2003]-[2002] As TotalChange, ([2003]/[2002])-1 As PercentChange From qxtblMyCrossTab

I hope this is what you were looking for.

David Atkins, MCP

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-15 : 15:31:04

SELECT DatePart('m',[ARRIVAL_DATE],1,0) as m, Book1.COUNTRY,

Sum(iif(DatePart('yyyy',[ARRIVAL_DATE],1,0)=2002,Book1.ED_CARD,0)) as y2002,

Sum(iif(DatePart('yyyy',[ARRIVAL_DATE],1,0)=2003,Book1.ED_CARD,0)) as y2003

FROM Book1

WHERE (((DatePart('yyyy',[ARRIVAL_DATE],1,0))=[PreviousYear] Or (DatePart('yyyy',[ARRIVAL_DATE],1,0))=[CurrentYear]))

GROUP BY DatePart('m',[ARRIVAL_DATE],1,0), Book1.COUNTRY;

This query produces something like:


m       Country       y2002       y2003
------------------------------------------
1       USA              5           6
1       Zambia          80          90
2       USA              7          20
2       Zambia          92          96

In your report you just add the Grouping Level on the field 'm' (i.e. month)
and put into the Grouping header a textbox with Data Source: m
(seems it should be formatted for displaying names of months instead of
their ordinal numbers).

Then in Details section you put 3 textboxes for fields Country, y2002, y2003
and 2 'new' textboxes with corresponding Data Sources:
1) =y2003-y2002
2) =100*(y2003-y2002)/y2002

Then put into the Grouping footer 3 textboxes for totals with Data Sources:
1) =Sum(y2002)
2) =Sum(y2003)
3) =Sum(y2003-y2002)

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-08-15 : 16:19:50

Hello,

Ran the query and got the following error:

The expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplyfying the expression by assigning parts of the expression to variables.

Can't see any syntax errorts, any idea of how to resolve this?

blinton25
Yak Posting Veteran

90 Posts

Posted - 2003-08-15 : 19:21:37

Hi,

Got it fixed:

SELECT format([ARRIVAL_DATE], "mmm") as [Month], Book1.COUNTRY,

count(iif(DatePart('yyyy',[ARRIVAL_DATE],1,0)=[PreviousYear],Book1.ED_CARD)) as y2002,
count(iif(DatePart('yyyy',[ARRIVAL_DATE],1,0)=[CurrentYear],Book1.ED_CARD)) as y2003

FROM Book1

WHERE (((DatePart('yyyy',[ARRIVAL_DATE],1,0))=[PreviousYear] Or (DatePart('yyyy',[ARRIVAL_DATE],1,0))=[CurrentYear]))

GROUP BY format([ARRIVAL_DATE], "mmm"), Book1.COUNTRY

Thanks a lot for the help everyone, and thanks for the pointers on building the report.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-16 : 11:11:35

Still better it would be:

PARAMETERS [PreviousYear] Integer, [CurrentYear] Inreger;
SELECT format([ARRIVAL_DATE], "mmm") as [Month], Book1.COUNTRY,
count(iif(DatePart('yyyy',[ARRIVAL_DATE],1,0)=[PreviousYear],Book1.ED_CARD)) as y1,
count(iif(DatePart('yyyy',[ARRIVAL_DATE],1,0)=[CurrentYear],Book1.ED_CARD)) as y2
FROM Book1
WHERE (((DatePart('yyyy',[ARRIVAL_DATE],1,0))=[PreviousYear] Or (DatePart('yyyy',[ARRIVAL_DATE],1,0))=[CurrentYear]))
GROUP BY format([ARRIVAL_DATE], "mmm"), Book1.COUNTRY

And more plain it would be:

PARAMETERS [PreviousYear] Integer, [CurrentYear] Inreger;
SELECT format([ARRIVAL_DATE], "mmm") as [Month], Book1.COUNTRY,
Sum(iif(DatePart('yyyy',[ARRIVAL_DATE],1,0)=[PreviousYear], 1, 0)) as y1,
Sum(iif(DatePart('yyyy',[ARRIVAL_DATE],1,0)=[CurrentYear], 1, 0)) as y2
FROM Book1
WHERE (((DatePart('yyyy',[ARRIVAL_DATE],1,0))=[PreviousYear] Or (DatePart('yyyy',[ARRIVAL_DATE],1,0))=[CurrentYear]))
GROUP BY format([ARRIVAL_DATE], "mmm"), Book1.COUNTRY

1) Why should we name the 3rd and the 4th output fields as 'y2002' and 'y2003'
if our input parameters may be any years?

2) I added here PARAMETERS description because without that it may occur
unpleasant and unnoticed surprises.

3) I didn't use here TRANSFORM ... PIVOT ... IN ... construction
because I don't know how to get in this case the same fields names
(I mean the 3rd and the 4th output fields) for different input years.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources