Urgent -- Access QRY to SQL Server QRY

[Joe]

I have to following Access2003 Code that needs to run in SQL 2000

SELECT
extend,
Source,
Count(PersonalID) AS RecordCount,
Count(IIf([Literature1Quantity]>0,([Literature1Quantity]),Null)) AS
Item01,
Count(IIf([Literature2Quantity]>0,([Literature2Quantity]),Null)) AS
Item02,
Count(IIf([Literature3Quantity]>0,([Literature3Quantity]),Null)) AS
Item03,
Count(IIf([Literature4Quantity]>0,([Literature4Quantity]),Null)) AS
Item04
FROM dbo_Transaction_History
WHERE
(((dbo_Transaction_History.extend)=538) And
((dbo_Transaction_History.extend_TransType)="ib")
And ((nz([Literature1Quantity])+nz([Literature2Quantity])+nz
([Literature3Quantity])+nz([Literature4Quantity]))>0))
GROUP BY dbo_Transaction_History.extend, dbo_Transaction_History.Source
HAVING (((dbo_Transaction_History.Source) In ("tm","web","brc")));

This will Give me the required result of

extend Source RecordCount Item01 Item02 Item03 Item04
538 BRC 919 0 0 919 0
538 TM 1178 41 115 1081 53
538 Web 4 0 0 4 0

The ItemXX is simply a COUNT if not null ELSE NULL (0) based on the
grouping. Also I have no idea how NZ() is fitting into the WHERE clause.

Now here's where I'm at with the SQL Server code

SELECT
extend,
Source,
Count(PersonalID) AS 'RecordCount',
CASE
COUNT(ISNULL(Literature1Quantity,0))
WHEN 0 THEN 0
ELSE COUNT(Literature1Quantity)
END AS Item01,

CASE
COUNT(ISNULL(Literature2Quantity,0))
WHEN 0 THEN 0
ELSE COUNT(Literature2Quantity)
END AS Item02,

CASE
COUNT(ISNULL(Literature3Quantity,0))
WHEN 0 THEN 0
ELSE COUNT(Literature3Quantity)
END AS Item03,

CASE
COUNT(ISNULL(Literature4Quantity,0))
WHEN 0 THEN 0
ELSE COUNT(Literature4Quantity)
END AS Item04

FROM Transaction_History
WHERE
extend=538
AND extend_TransType='ib'
GROUP BY extend,Source
HAVING Source In ('tm','web','brc')

extend Source RecordCount Item01 Item02 Item03 Item04
538 BRC 1812 0 0 1812 0
538 TM 20103 41 115 1081 53
538 Web 39 39 34 9 39

As you can see these results are nothing like the required.
I'm kind of new and really lost, can someone help?

Thank you

 

[Sylvain Lafontaine]

See my answer in m.p.access

 

[Michel Walsh]

Hi,



Change:

COUNT( iif(a, b, c) )

to
COUNT( CASE WHEN a THEN b ELSE c END )


and change

Nz(a, b)

to
COALESCE(a, b)


In your tentative, you placed the CASE outside the COUNT, it should stay
inside. The original is NOT: iif(COUNT), so the translated solution is
NOT: CASE( COUNT) -- well, neither in syntax, neither in "spirit".



Hoping it may help,
Vanderghast, Access MVP