Post reply

How to combine these 2 Select Statements

  • August 18, 2004 at 10:38 am

    #89117

    I have a situation in which I am trying to display some statistics on some data via a Datagrid (ASP.NET).  I am using a stored procedure and it works fine for the first query below which has all of its data in one table (tblAudit).  I am trying to add a feature to the Datagrid by adding the totals of all flagged items.  It becomes cumbersome since the parameters for the second query span a total of two tables with another table linked between them.  I am having difficulty adding linked data to the first query since it has a CASE statement.  Is this valid?

    Here is my first query:

     

    SELECT CASE

        WHEN MONTH(AuditDT) = 1 THEN 'JAN'

        WHEN MONTH(AuditDT) = 2 THEN 'FEB'

        WHEN MONTH(AuditDT) = 3 THEN 'MAR'

        WHEN MONTH(AuditDT) = 4 THEN 'APR'

        WHEN MONTH(AuditDT) = 5 THEN 'MAY'

        WHEN MONTH(AuditDT) = 6 THEN 'JUN'

        WHEN MONTH(AuditDT) = 7 THEN 'JUL'

        WHEN MONTH(AuditDT) = 8 THEN 'AUG'

        WHEN MONTH(AuditDT) = 9 THEN 'SEP'

        WHEN MONTH(AuditDT) = 10 THEN 'OCT'

        WHEN MONTH(AuditDT) = 11 THEN 'NOV'

        ELSE 'DEC'

        END AS Month,

        YEAR(AuditDT) [Year],

        SUM(CASE StatusID WHEN 1 THEN 1 ELSE 0 END) Outstanding,

        SUM(CASE StatusID WHEN 2 THEN 1 ELSE 0 END) Pending,

        SUM(CASE StatusID WHEN 3 THEN 1 ELSE 0 END) Finished,

        SUM(CASE FlagYN WHEN 1 THEN 1 ELSE 0 END) Flagged,

        COUNT(*) Total

         FROM tblAudit

        GROUP BY MONTH(AuditDT), YEAR(AuditDT)

        ORDER BY YEAR(AuditDT) DESC, MONTH(AuditDT) DESC

     

    Here is my second query:
    SELECT SUM(tblChecks.Amount), MONTH(tblAudit.AuditDT) , YEAR(tblAudit.AuditDT)

    FROM tblChecks INNER JOIN

    tblFlaggedChecks ON tblChecks.CheckID = tblFlaggedChecks.CheckID

    INNER JOIN tblAudit ON tblFlaggedChecks.FlaggedCheckID = tblAudit.FlaggedCheckID

    WHERE (tblAudit.FlagYN = 1)

    GROUP BY MONTH(tblAudit.AuditDT), YEAR(tblAudit.AuditDT)

    ORDER BY YEAR(tblAudit.AuditDT) DESC, MONTH(tblAudit.AuditDT) DESC

    So basically, I am trying to add the SUM(tblChecks.Amount) to the first query so I can just add this parameter (say Amount) to the Datagrid.  I have tried a few combinations, but I always seem to get errors when I try to save the stored procedure.

    Any assistance will be greatly appreciated.

  • August 18, 2004 at 4:53 pm

    #519661

    From what I can tell you want your first query to be the inner query of the second query.  If this is what you want, remove the ORDER BY clause on the inner query otherwise it wont work.

    Hope this helps.  If I'm out, explain a bit more and I'll help.

    Dave Hilditch.

  • August 19, 2004 at 4:43 am

    #519728

    Hi,

    To test a script I might need a table, Creating myself time consumes.

    Pls export couple of records from each tables you use in the query into EXCEL and zip it and fwd to me. So I can help you out

    Rgds- Ahmed

  • August 19, 2004 at 8:08 am

    #519800

    also, you may want to create a "Months" TABLE containing MonthId, MonthName and join to it.  You could replace your mutli-CASE statement w/ a simple SELECT MonthName.

    Corie Curcillo
    MCT, MCDBA, MCSD

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply