Pivot

[maximus]

Hi,
I'm having hard time with transfering one query to SQL
Stored Procedure. In Access 2000 It looks like this:

PARAMETERS [Please Enter State Code] Text ( 255 );
TRANSFORM Count([Month]) AS Expr1
SELECT tGroup.GrpYear, tGroup.StPr,
tRegionCrossRefUS.Region, Count([Month]) AS StateTotal
FROM tGroup INNER JOIN tRegionCrossRefUS ON tGroup.StPr =
tRegionCrossRefUS.StateUSCode
WHERE (((tGroup.GrpYear)>1989) AND ((tGroup.StPr)=[Please
Enter State Code]) AND ((tGroup.GrpStatus)="C" Or
(tGroup.GrpStatus)="I" Or (tGroup.GrpStatus)="X" Or
(tGroup.GrpStatus)="InProg" Or (tGroup.GrpStatus)="Comp")
AND ((tGroup.GrpLanguage)="English" Or
(tGroup.GrpLanguage) Is Null))
GROUP BY tGroup.GrpYear, tGroup.StPr,
tRegionCrossRefUS.Region
PIVOT IIf([GrpDateSchedStart] Is Not Null,Month
([GrpDateSchedStart]),IIf([GrpDateSchedStart] Is Null And
[DateWk1Rpt] Is Not Null,Month([DateWk1Rpt]),IIf(Month
([DateGrpOrder]) Is Not Null,Month([DateGrpOrder]),IIf
((Month([DateCertSent])-3)<1,IIf(Year([DateCertSent])=
[GrpYear],1,10),Month([DateCertSent])-3)))) In
(1,2,3,4,5,6,7,8,9,10,11,12);

Instead of getting different amount for each month, for
specific year, I get the same amount for jan,feb,.... for
each year. My Stored Procedure looks like this:

Alter PROCEDURE [Qr-GroupStartsUSSt]
@Please_Enter_State_Code nvarchar(2)
AS
select distinct g.GrpYear, @Please_Enter_State_Code as
StPr,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') ) StateTotal,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=1) Jan,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=2) Feb,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=3) Mar,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=4) Apr,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=5) May,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=6) Jun,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=7) Jul,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=8) Aug,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=9) Sep,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=10) Oct,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=11) Nov,
(select COUNT(DateGrpOrder) from dbo.tGroup g where
g.GrpYear >1989 and g.StPr=@Please_Enter_State_Code and
g.GrpStatus in ('i','x','InProg','Comp') AND Month
(g.DateGrpOrder)=12) Dec
FROM
(select distinct g.GrpYear as GroupYear from dbo.tGroup g
where g.StPr=@Please_Enter_State_Code and g.GrpYear

1989 ) nn

INNER JOIN
dbo.tGroup g on g.GrpYear=nn.GroupYear
ORDER BY g.GrpYear,g.StPr

If you know, please tell me what do I do wrong?
Thank you in advance

 

[Rufus T. Firefly]

If your working on S2k you can check out the
RAC utility which is similar to Access crosstab in nature
but much more powerful (many more features/options).

RAC v2.2 and QALite @
www.rac4sql.net