Urgent -- Access QRY to SQL Server QRY

28 Oct 2005 2:12 PM

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

28 Oct 2005 2:47 PM

Aaron Bertrand [SQL Server MVP]

IIF becomes CASE. e.g.

Item01 = SUM(CASE WHEN Literature1Quantity > 0 THEN 1 ELSE 0 END),

Also change your string delimiters from " to '

And you don't really need to define the table name in the column references
(e.g. in group by) since you are only referencing one table. It only serves
to make the query harder to read, IMHO.

Show quoteHide quote

"Joe" <N***@nopMainPlease.com> wrote in message
news:MPG.1dcbfbc7e17aed4f989684@news.microsoft.com...
>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

28 Oct 2005 2:49 PM

David Portas

Here's a guess:

SELECT extend, source,
COUNT(personalid) AS RecordCount,
COUNT(CASE WHEN literature1quantity>0
THEN literature1quantity END) AS item01,
COUNT(CASE WHEN literature2quantity>0
THEN literature2quantity END) AS item02,
COUNT(CASE WHEN literature3quantity>0
THEN literature3quantity END) AS item03,
COUNT(CASE WHEN literature4quantity>0
THEN literature4quantity END) AS item04
FROM dbo.Transaction_History
WHERE extend=538
AND extend_transtype='ib'
AND (literature1quantity>0
   OR literature2quantity>0
   OR literature3quantity>0
   OR literature4quantity>0)
GROUP BY extend, source
HAVING Source IN ('tm','web','brc') ;

If you need more help, please could you supply enough code to reproduce
the problem, i.e. DDL (CREATE TABLE) and sample data (INSERTs).

When porting an Access database, do NOT assume that your design in
Access will still make sense in SQL Server. The 4 quantity columns look
like a significantly flawed design if Transaction_History is a base
table rather than a view.

--
David Portas
SQL Server MVP
--

Thread options