Developer Forum

I have this access query that I am trying to move into SQL as Cross Tab query. Every time I move this one into Query Analyzer, it fails. Any help would be greatly appreciated€
I have the visual design if anyone would like to take a stab at it

Or maybe its just a complex Summary Query


SELECT DutySquadrons.Group, Sum(IIf([ReceivedMPF<<[CloseoutDate]+30,1,0)) AS OnTime, Count(Active.Counter) AS Tot, IIf([tot]=0,0,100*([OnTime]/[Tot])) AS Rate, Sum(IIf(([index<<13),1,0)) AS J_Tot, Sum(IIf(([ReceivedMPF<<[CloseoutDate]+30) And ([index<<13),1,0)) AS J_OnTime, IIf([j_tot]=0,0,([J_OnTime]/[J_Tot])*100) AS J_Rate, Sum(IIf(([ReceivedMPF<<[CloseoutDate]+30) And ([index>>12 And [index<<19),1,0)) AS S_OnTime, Sum(IIf(([index>>12 And [index<<19),1,0)) AS S_Tot, IIf([s_tot]=0,0,([S_OnTime]/[S_Tot])*100) AS S_Rate, Sum(IIf(([ReceivedMPF<<[CloseoutDate]+30) And ([index>>18),1,0)) AS O_OnTime, Sum(IIf(([index>>18),1,0)) AS O_Tot, IIf([o_tot]=0,0,([O_OnTime]/[O_Tot])*100) AS O_Rate, [S_OnTime]+[O_OnTime] AS B_OnTime, [S_Tot]+[O_Tot] AS B_Tot, IIf([B_Tot]=0,0,([B_OnTime]/[B_Tot])*100) AS B_Rate

FROM ((Active INNER JOIN DutySquadrons ON Active.Squadron=DutySquadrons.Squadron) INNER JOIN personnel ON Active.SSN=personnel.SSN) INNER JOIN Rank ON personnel.Rank=Rank.Rank

WHERE (((Active.[completion date]) Between [varStart] And [varStop]))

GROUP BY DutySquadrons.Group

HAVING (((DutySquadrons.Group)>>"tenant"));

Derek
You cannot move CROSSTAB queries to SQL Server.
You have to re-built it.





query. Every time I move this one into Query Analyzer, it fails. Any help
would be greatly appreciated
AS OnTime, Count(Active.Counter) AS Tot, IIf([tot]=0,0,100*([OnTime]/[Tot]))
AS Rate, Sum(IIf(([index]<13),1,0)) AS J_Tot,
Sum(IIf(([ReceivedMPF]<[CloseoutDate]+30) And ([index]<13),1,0)) AS
J_OnTime, IIf([j_tot]=0,0,([J_OnTime]/[J_Tot])*100) AS J_Rate,
Sum(IIf(([ReceivedMPF]<[CloseoutDate]+30) And ([index]>12 And
[index]<19),1,0)) AS S_OnTime, Sum(IIf(([index]>12 And [index]<19),1,0)) AS
S_Tot, IIf([s_tot]=0,0,([S_OnTime]/[S_Tot])*100) AS S_Rate,
Sum(IIf(([ReceivedMPF]<[CloseoutDate]+30) And ([index]>18),1,0)) AS
O_OnTime, Sum(IIf(([index]>18),1,0)) AS O_Tot,
IIf([o_tot]=0,0,([O_OnTime]/[O_Tot])*100) AS O_Rate, [S_OnTime]+[O_OnTime]
AS B_OnTime, [S_Tot]+[O_Tot] AS B_Tot,
IIf([B_Tot]=0,0,([B_OnTime]/[B_Tot])*100) AS B_Rate
Active.Squadron=DutySquadrons.Squadron) INNER JOIN personnel ON
Active.SSN=personnel.SSN) INNER JOIN Rank ON personnel.Rank=Rank.Rank

In addition, SQL Server doesn't have IIF. Use the ANSI SQL compliant (and more powerful) CASE
instead.

--
Tibor Karaszi, SQL Server MVP
Archive at: http://www.yqcomputer.com/

Check out the RAC utility for server crosstabs.

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

I replaced the IIf with the case, and bracketed some of the keywords
that were being used in as a field name. The only eror i recieve now is
on line one "Incorrect syntax near '<'" Stuck again, here is the
query...

SELECT DutySquadrons.[Group],
Sum(Case([ReceivedMPF]<[CloseoutDate]+30),1,0)

AS OnTime, Count(Active.Counter)
AS Tot, Case([tot]=0,0,100*([OnTime]/[Tot]))
AS Rate, Sum(Case(([index]<13),1,0))
AS J_Tot, Sum(Case(([ReceivedMPF]<[CloseoutDate]+30)

And ([index]<13),1,0))

AS J_OnTime, Case([j_tot]=0,0,([J_OnTime]/[J_Tot])*100)
AS J_Rate, Sum(Case(([ReceivedMPF]<[CloseoutDate]+30)

And ([index]>12 And [index]<19),1,0))
AS S_OnTime, Sum(Case(([index]>12

And [index]<19),1,0))
AS S_Tot, Case([s_tot]=0,0,([S_OnTime]/[S_Tot])*100)
AS S_Rate, Sum(Case(([ReceivedMPF]<[CloseoutDate]+30)

And ([index]>18),1,0))

AS O_OnTime, Sum(Case(([index]>18),1,0))
AS O_Tot, Case([o_tot]=0,0,([O_OnTime]/[O_Tot])*100)
AS O_Rate, [S_OnTime]+[O_OnTime]
AS B_OnTime, [S_Tot]+[O_Tot]
AS B_Tot, Case([B_Tot]=0,0,([B_OnTime]/[B_Tot])*100)
AS B_Rate

FROM ((Active INNER JOIN DutySquadrons ON
Active.Squadron=DutySquadrons.Squadron) INNER JOIN personnel ON
Active.SSN=personnel.SSN) INNER JOIN Rank ON personnel.Rank=Rank.Rank

WHERE Active.[completion date] Between [varStart] And [varStop]

GROUP BY DutySquadrons.[Group]

HAVING DutySquadrons.Group<>"tenant"



*** Sent via Developersdex http://www.yqcomputer.com/ ***
Don't just participate in USENET...get rewarded for it!

Hi

You are missing the WHEN keyword see:

http://www.yqcomputer.com/

John

Do I have to use either a then or else??
Here is what i have so far, with the same syntax error..Thanks for all
the help

SELECT DutySquadrons.[Group],Sum(Case when
[ReceivedMPF]<[CloseoutDate]+30,1,0)
AS OnTime, Count(Active.Counter)
AS Tot, when([tot]=0,0,100*([OnTime]/[Tot]))
AS Rate, Sum(when(([index]<13),1,0))
AS J_Tot, Sum(when(([ReceivedMPF]<[CloseoutDate]+30)
And ([index]<13),1,0))
AS J_OnTime, when([j_tot]=0,0,([J_OnTime]/[J_Tot])*100)
AS J_Rate, Sum(when(([ReceivedMPF]<[CloseoutDate]+30)
And ([index]>12 And [index]<19),1,0))
AS S_OnTime, Sum(when(([index]>12
And [index]<19),1,0))
AS S_Tot, when([s_tot]=0,0,([S_OnTime]/[S_Tot])*100)
AS S_Rate, Sum(when(([ReceivedMPF]<[CloseoutDate]+30)
And ([index]>18),1,0))
AS O_OnTime, Sum(when(([index]>18),1,0))
AS O_Tot, when([o_tot]=0,0,([O_OnTime]/[O_Tot])*100)
AS O_Rate, [S_OnTime]+[O_OnTime]
AS B_OnTime, [S_Tot]+[O_Tot]
AS B_Tot, when([B_Tot]=0,0,([B_OnTime]/[B_Tot])*100)
AS B_Rate

FROM ((Active INNER JOIN DutySquadrons ON
Active.Squadron=DutySquadrons.Squadron) INNER JOIN personnel ON
Active.SSN=personnel.SSN) INNER JOIN Rank ON personnel.Rank=Rank.Rank

WHERE Active.[completion date] Between [varStart] And [varStop]

GROUP BY DutySquadrons.[Group]

HAVING DutySquadrons.[Group]<>"tenant"



*** Sent via Developersdex http://www.yqcomputer.com/ ***
Don't just participate in USENET...get rewarded for it!

Do I have to use either a then or else??
Here is what i have so far, with the same syntax error..Thanks for all
the help

SELECT DutySquadrons.[Group],Sum(Case when
[ReceivedMPF]<[CloseoutDate]+30,1,0)
AS OnTime, Count(Active.Counter)
AS Tot, when([tot]=0,0,100*([OnTime]/[Tot]))
AS Rate, Sum(when(([index]<13),1,0))
AS J_Tot, Sum(when(([ReceivedMPF]<[CloseoutDate]+30)
And ([index]<13),1,0))
AS J_OnTime, when([j_tot]=0,0,([J_OnTime]/[J_Tot])*100)
AS J_Rate, Sum(when(([ReceivedMPF]<[CloseoutDate]+30)
And ([index]>12 And [index]<19),1,0))
AS S_OnTime, Sum(when(([index]>12
And [index]<19),1,0))
AS S_Tot, when([s_tot]=0,0,([S_OnTime]/[S_Tot])*100)
AS S_Rate, Sum(when(([ReceivedMPF]<[CloseoutDate]+30)
And ([index]>18),1,0))
AS O_OnTime, Sum(when(([index]>18),1,0))
AS O_Tot, when([o_tot]=0,0,([O_OnTime]/[O_Tot])*100)
AS O_Rate, [S_OnTime]+[O_OnTime]
AS B_OnTime, [S_Tot]+[O_Tot]
AS B_Tot, when([B_Tot]=0,0,([B_OnTime]/[B_Tot])*100)
AS B_Rate

FROM ((Active INNER JOIN DutySquadrons ON
Active.Squadron=DutySquadrons.Squadron) INNER JOIN personnel ON
Active.SSN=personnel.SSN) INNER JOIN Rank ON personnel.Rank=Rank.Rank

WHERE Active.[completion date] Between [varStart] And [varStop]

GROUP BY DutySquadrons.[Group]

HAVING DutySquadrons.[Group]<>"tenant"



*** Sent via Developersdex http://www.yqcomputer.com/ ***
Don't just participate in USENET...get rewarded for it!

Hi

It seems that you didn't read the details on the link I posted

The syntax of CASE is:
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END



Therefore

Sum(when(([index]<13),1,0)) AS J_Tot

should be

SUM(CASE WHEN [index]<13 THEN 1 ELSE 0 END) AS J_Tot

etc...

John

I know. I did read through, I had multiple query windows open in Query
Analyzer, must have pasted the wrong one. This is what I have so far,
cept my aliases do not seem to be getting declared: Keep getting invalid
column names. Thanks in advance...

declare @Start as datetime
,@Stop as datetime


set @Start = '1 sep 03'
set @Stop = '1 oct 03'


SELECT DutySquadrons.[Group]
,Sum(Case when [ReceivedMPF]<[CloseoutDate]+30 then 1 else 0 end)AS
OnTime
,Count(Active.Counter)AS Tot
,sum (Case when [tot]=0 then 0 else 100* [OnTime]/[Tot] end)AS Rate
,Sum(Case when [index]<13 then 1 else 0 end)AS J_Tot
,Sum(Case when [ReceivedMPF]<[CloseoutDate]+30 And [index]<13 then 1
else 0 end)AS J_OnTime
,Sum (Case when [j_tot]=0 then 0 else [J_OnTime]/[J_Tot] *100 end)AS
J_Rate
,Sum(Case when [ReceivedMPF]<[CloseoutDate]+30 And [index]>12 And
[index]<19 then 1 else 0 end)AS S_OnTime
,Sum(Case when [index]>12 And [index]<19 then 1 else 0 end)AS S_Tot
,Sum(Case when [s_tot]=0 then 0 else [S_OnTime]/[S_Tot]*100 end)AS
S_Rate
,Sum(Case when [ReceivedMPF]<[CloseoutDate]+30 And [index]>18 then 1
else 0 end)AS O_OnTime
,Sum(Case when [index]>18 then 1 else 0 end)AS O_Tot
,Sum(Case when [o_tot]=0 then 0 else [O_OnTime]/[O_Tot]*100 end)AS
O_Rate
,[S_OnTime]+[O_OnTime]AS B_OnTime, [S_Tot]+[O_Tot]AS B_Tot
,Sum(Case when [B_Tot]=0 then 0 else [B_OnTime]/[B_Tot]*100 end)AS
B_Rate

FROM ((Active INNER JOIN DutySquadrons ON
Active.Squadron=DutySquadrons.Squadron) INNER JOIN personnel ON
Active.SSN=personnel.SSN)
INNER JOIN Rank ON personnel.Rank=Rank.Rank

WHERE Active.[completion date] Between @Start And @Stop

GROUP BY DutySquadrons.[Group]

HAVING DutySquadrons.[Group]<>"tenant"







*** Sent via Developersdex http://www.yqcomputer.com/ ***
Don't just participate in USENET...get rewarded for it!

Seems you are reusing an expression given a column name in another expression. No can do in SQL
Server or in the ANSI SQL standard. Look at the usage of the column named "tot" here:



You have to repeat the expression or use a derived table for this.
(There is a theoretical explanation for this, but I won't bore you with it unless you ask for the
explanation... :-).)

--
Tibor Karaszi, SQL Server MVP
Archive at: http://www.yqcomputer.com/