Post reply

Multiple criteria (COUNT(DISTINCT(*) in select statement

  • July 28, 2008 at 9:19 am

    #196243

    Below is criteria for a report that I am creating. My question is ..... is it possible to combine all of this criteria in one select statement? What would be the most efficient way to acheive this result set?

    OperatorID

    #ofReceivedCases = distinct count of cases (case_id) opened for week based on open date or reopen date and status=O

    #ofResolvedCasesFraud = distinct count of cases (case_id) closed for week based on closed date and status=C and gross fraud >0 where firstrow=1

    #ofResolvedCasesNonFraud = distinct count of cases (case_id) closed for week based on closed date and status=C and gross fraud = 0 where firstrow=1

    #ofPendingCases = distinct count of cases that remain open at end of week, status=O at end of week.

  • July 28, 2008 at 9:41 am

    #849248

    Hi Brandon

    It's a little sketchy, but there's at least one solution for this. The simplest to follow is along the lines of...

    SELECT ReceivedCases = SUM(CASE WHEN status = 'C' AND [gross fraud] > 0 AND firstrow = 1 THEN 1 ELSE 0 END)

    .

    .

    .

    FROM YourTable

    GROUP BY Something

    Can you provide some more detail, say table structure, and any code you already have?

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • July 28, 2008 at 9:49 am

    #849253

    I have not created a table structure yet as I wanted to get the query constructed. The criteria that I have listed is all of the information that I have. Thank you for your assistance.

  • July 28, 2008 at 9:53 am

    #849262

    Not sure about everyone else.

    But tend to find it easier to design a db then write queries on it.

    You need to decide what data you want to store and weather the information is mainly transactional or reportable.

    you can as mentioned earlier use case statements to defind the criteria for sounting/suming your data so as to return your desired results in one select statement.

    hope that helps

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • July 28, 2008 at 3:48 pm

    #849501

    The requirements of this is that I do not need to create a table, just a stored procedure or query. I just wanted to know of an efficient method to select the criteria, broken down by operator id, using just one select statement.

    Thank you to everybody for their assistance in this.......:)

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

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