dcsimg
Results 1 to 3 of 3

Thread: [RESOLVED] SQL – Conditional sum

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    336

    Resolved [RESOLVED] SQL – Conditional sum

    Hi
    I have this query that helps me to get the count of different stuff. My cases have system deadline and owner deadline. Owner deadlines are just a few days before system deadline. In cases when a case is past system deadline it is obviously also past owner deadline. However, I don’t want to count those overdues twice as it is doing now (one for system overdue and one for owner overdue) and only want to indicate a single system overdue.

    Code:
    SELECT Oshort,
    Sum (CASE WHEN [System Deadline] - GETDATE() < 0  then 1 else 0 end) as [System past due],
    Sum (CASE WHEN [Owner Deadline] - GETDATE() < 0 then 1 else 0 end) as [Owner past due],
    Sum (CASE WHEN [Owner Deadline] - GETDATE() >=0 And [Owner Deadline] - GETDATE() < 7  then 1 else 0 end) as [Due Within 7 days],
    Sum (CASE WHEN [Owner Deadline] - GETDATE() >=7 And [Owner Deadline] - GETDATE() < 14 then 1 else 0 end) as [Due Within 14 days],
    Sum (CASE WHEN [Owner Deadline] - GETDATE() >=14  then 1 else 0 end) as [Due More Than 14 days]
    FROM  DMS_Q_Activity 
    Where 
    Oshort in ('154','2556') 
    Status = 'Closed'
    Group by Oshort

    How can I do that?

    Thanks in advance.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,453

    Re: SQL – Conditional sum

    It's going to suck, but case in a case....

    Something like this:
    Code:
    Sum( CASE WHEN [System Deadline] - GETDATE() < 0 then 0 else CASE WHEN [Owner Deadline] - GETDATE() < 0 then 1 else 0 end end as [Owner past due],
    Sum( CASE WHEN [System Deadline] - GETDATE() < 0 then 0 else CASE WHEN [Owner Deadline] - GETDATE() >= 0 and [Owner Deadline] - GETDATE() < 7  then 1 else 0 end end as [Due within 7 days],
    ...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    336

    Re: SQL – Conditional sum

    I got the idea, thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width