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]

Count() doesn't count the values which are Null, so in the statement:

Count(IIf([Literature1Quantity] 0,([Literature1Quantity]),Null))

we are counting only the number of values which are greater than 0. In your
first attempt, the statement « COUNT(ISNULL(Literature1Quantity,0)) » is
similar to Count (*) or more simply to count *all* the values. Instead,
this should be replaced with:

Count (case when Literature1Quantity > 0 then Literature1Quantity else
Null end)

For the Where clause, the function « Nz([Literature1Quantity]) » should be
replace with « IsNull([Literature1Quantity], 0) »

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

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