Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Urgent -- Access QRY to SQL Server QRY

Discussion in 'Information Technology' started by Joe, Oct 29, 2005.

  1. Joe

    Joe
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    URGENT PLZZ HELP: KAAM LUST BODY MASSAGE SPA ISSUE Sikh Youth Jul 9, 2015
    Sikh News Spate Of Attacks By Islamist Insurgents Rattle Sikh Community In Pakistan Breaking News Apr 19, 2015
    SciTech Urgent Need to Remove Space Debris Breaking News Apr 25, 2013
    Urgent! Need help with some gender equality questions! Questions and Answers Jan 7, 2013
    General URGENT! Video on reality of sexual grooming gangs in the UK - please share Hard Talk Nov 21, 2012

  3. Sylvain Lafontaine

    Sylvain Lafontaine
    Expand Collapse
    Guest

    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


    "Joe" <Nope@nopMainPlease.com> wrote in message
    news:MPG.1dcbfb191f0cfad2989680@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
     

Share This Page