Pivot

  • Thread starter Thread starter maximus
  • Start date Start date
M

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
Click to expand...
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
 
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
 
Back
Top