How to combine these 2 Select Statements

Question

How to combine these 2 Select Statements

eb24

SSC Eights!

Points: 823
More actions
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.

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

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

dhilditch SSC-Addicted Points: 495 More actions · Answer 1

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.

aj-156165 SSC Veteran Points: 230 More actions · Answer 2

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

Corie Curcillo SSC-Addicted Points: 418 More actions · Answer 3

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