none
Calculating 30,60,90 Days Totals in sql

    Question

  • Hi All,

    Here I  have written a query to find the total for 30Days for a perticular member after the discharge date.

    These 30Days Amount will be populated into another table based on joins.

    Now my question is i am trying to get the total for 60 Days , 90 Days and 180 Days.

    I can write Different queries changing datediff to 60,90,180 but i want all those fields in single query .

    please suggest how to write in single query.... 

    Select Distinct  ip.member

    sum(isnull(NetAmt,0.00)) as 30DaysAmount

    from claims c

    join claimsDetail cd on c.claimsnbr=cd.claimsnbr

    join inpatient ip on cd.membernbr=ip.membernbr

    where c.formnbr='SNF' 

    and (datediff(day,cd.specificdateofservice ,dischargedate) between 0 and 30

    group by ip.member

    I really appreciate your help.

    Thanks,

    Kalyan.

    Sunday, June 08, 2014 2:34 AM

Answers

  • Hi Visakh16/Dan,

    I tried using case statement in select statement itself using 

    case when datediff(d,cd.specificdateofservice,dischargedate) between 0 and 30 then sum(isnull(NETAMT),0.00)

    then it throws an error i cannot use between in select, then i removed then it says all other columns not part of aggregate must be part of group by(similar to that)

    Then i included all  other fields in group by, but i am worried will the value be correct ?

    Thanks for your reply i will try using your query as above:using SUM(case.....)

    I dont have access to work network, so i am writing general wordings of errror and query...

    Thanks,

    Kalyan.

    The CASE..WHEN should be inside the SUM

    you can also use this

    Select ip.member,
    sum(case when datediff(day,cd.specificdateofservice ,dischargedate) >= 0 and datediff(day,cd.specificdateofservice ,dischargedate) <= 30 then isnull(NetAmt,0.00) else 0.00 end) as 30DaysAmount,
    sum(case when datediff(day,cd.specificdateofservice ,dischargedate) >= 0 and datediff(day,cd.specificdateofservice ,dischargedate) <= 60 then isnull(NetAmt,0.00) else 0.00 end) as 60DaysAmount,
    sum(case when datediff(day,cd.specificdateofservice ,dischargedate) >= 0 and datediff(day,cd.specificdateofservice ,dischargedate) <= 90 then isnull(NetAmt,0.00) else 0.00 end) as 90DaysAmount,
    sum(isnull(NetAmt,0.00)) as 180DaysAmount
    
    from claims c
    
    join claimsDetail cd on c.claimsnbr=cd.claimsnbr
    
    join inpatient ip on cd.membernbr=ip.membernbr
    
    where c.formnbr='SNF' 
    
    and datediff(day,cd.specificdateofservice ,dischargedate) between 0 and 180
    
    group by ip.member


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, June 08, 2014 5:22 AM
  • I tried using case statement in select statement itself using 

    case when datediff(d,cd.specificdateofservice,dischargedate) between 0 and 30 then sum(isnull(NETAMT),0.00)

    then it throws an error i cannot use between in select,

    So the code that Dan posted was:

    SELECT
         ip.member
       , SUM(CASE WHEN DATEDIFF(day, cd.specificdateofservice, dischargedate) BETWEEN  0 AND  30 THEN NetAmt END) AS [30DaysAmount]

    That is, you have SUM around it all, and inside the SUM you only use the value if the date difference is 30 days. That is how you usuall solve this type of problems.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, June 08, 2014 9:34 AM

All replies

  • just tweak the query as below

    Select ip.member,
    sum(case when datediff(day,cd.specificdateofservice ,dischargedate) between 0 and 30 then isnull(NetAmt,0.00) else 0.00 end) as 30DaysAmount,
    sum(case when datediff(day,cd.specificdateofservice ,dischargedate) between 0 and 60 then isnull(NetAmt,0.00) else 0.00 end) as 60DaysAmount,
    sum(case when datediff(day,cd.specificdateofservice ,dischargedate) between 0 and 90 then isnull(NetAmt,0.00) else 0.00 end) as 90DaysAmount,
    sum(isnull(NetAmt,0.00)) as 180DaysAmount
    
    from claims c
    
    join claimsDetail cd on c.claimsnbr=cd.claimsnbr
    
    join inpatient ip on cd.membernbr=ip.membernbr
    
    where c.formnbr='SNF' 
    
    and datediff(day,cd.specificdateofservice ,dischargedate) between 0 and 180
    
    group by ip.member

    Also the isnull() is redundant within SUM as it ignores the NULL values even otherwise while doing the aggregation


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, June 08, 2014 3:03 AM
  • One method is with a CASE expression:

    SELECT 
    	  ip.member
    	, SUM(CASE WHEN DATEDIFF(day, cd.specificdateofservice, dischargedate) BETWEEN  0 AND  30 THEN NetAmt END) AS [30DaysAmount]
    	, SUM(CASE WHEN DATEDIFF(day, cd.specificdateofservice, dischargedate) BETWEEN 31 AND  60 THEN NetAmt END) AS [60DaysAmount]
    	, SUM(CASE WHEN DATEDIFF(day, cd.specificdateofservice, dischargedate) BETWEEN 61 AND  90 THEN NetAmt END) AS [90DaysAmount]
    	, SUM(CASE WHEN DATEDIFF(day, cd.specificdateofservice, dischargedate) BETWEEN 91 AND 180 THEN NetAmt END) AS [18DaysAmount]
    	, SUM(CASE WHEN DATEDIFF(day, cd.specificdateofservice, dischargedate) > 180              THEN NetAmt END) AS [Over18DaysAmount]
    FROM dbo.claims c
    JOIN dbo.claimsDetail cd ON c.claimsnbr=cd.claimsnbr
    JOIN dbo.inpatient ip ON cd.membernbr=ip.membernbr
    WHERE c.formnbr='SNF' 
    GROUP BY ip.member;
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, June 08, 2014 3:05 AM
  • Hi Visakh16/Dan,

    I tried using case statement in select statement itself using 

    case when datediff(d,cd.specificdateofservice,dischargedate) between 0 and 30 then sum(isnull(NETAMT),0.00)

    then it throws an error i cannot use between in select, then i removed then it says all other columns not part of aggregate must be part of group by(similar to that)

    Then i included all  other fields in group by, but i am worried will the value be correct ?

    Thanks for your reply i will try using your query as above:using SUM(case.....)

    I dont have access to work network, so i am writing general wordings of errror and query...

    Thanks,

    Kalyan.

    Sunday, June 08, 2014 3:51 AM
  • Hi Visakh16/Dan,

    I tried using case statement in select statement itself using 

    case when datediff(d,cd.specificdateofservice,dischargedate) between 0 and 30 then sum(isnull(NETAMT),0.00)

    then it throws an error i cannot use between in select, then i removed then it says all other columns not part of aggregate must be part of group by(similar to that)

    Then i included all  other fields in group by, but i am worried will the value be correct ?

    Thanks for your reply i will try using your query as above:using SUM(case.....)

    I dont have access to work network, so i am writing general wordings of errror and query...

    Thanks,

    Kalyan.

    The CASE..WHEN should be inside the SUM

    you can also use this

    Select ip.member,
    sum(case when datediff(day,cd.specificdateofservice ,dischargedate) >= 0 and datediff(day,cd.specificdateofservice ,dischargedate) <= 30 then isnull(NetAmt,0.00) else 0.00 end) as 30DaysAmount,
    sum(case when datediff(day,cd.specificdateofservice ,dischargedate) >= 0 and datediff(day,cd.specificdateofservice ,dischargedate) <= 60 then isnull(NetAmt,0.00) else 0.00 end) as 60DaysAmount,
    sum(case when datediff(day,cd.specificdateofservice ,dischargedate) >= 0 and datediff(day,cd.specificdateofservice ,dischargedate) <= 90 then isnull(NetAmt,0.00) else 0.00 end) as 90DaysAmount,
    sum(isnull(NetAmt,0.00)) as 180DaysAmount
    
    from claims c
    
    join claimsDetail cd on c.claimsnbr=cd.claimsnbr
    
    join inpatient ip on cd.membernbr=ip.membernbr
    
    where c.formnbr='SNF' 
    
    and datediff(day,cd.specificdateofservice ,dischargedate) between 0 and 180
    
    group by ip.member


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, June 08, 2014 5:22 AM
  • I tried using case statement in select statement itself using 

    case when datediff(d,cd.specificdateofservice,dischargedate) between 0 and 30 then sum(isnull(NETAMT),0.00)

    then it throws an error i cannot use between in select,

    So the code that Dan posted was:

    SELECT
         ip.member
       , SUM(CASE WHEN DATEDIFF(day, cd.specificdateofservice, dischargedate) BETWEEN  0 AND  30 THEN NetAmt END) AS [30DaysAmount]

    That is, you have SUM around it all, and inside the SUM you only use the value if the date difference is 30 days. That is how you usuall solve this type of problems.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, June 08, 2014 9:34 AM
  • Then i included all  other fields in group by, but i am worried will the value be correct ?

    You don't need to other columns in the GROUP BY once you place the CASE expression in the SUM.  Adding other columns to the GROUP BY will change the query behavior so you have good reason to be worried if you do that.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, June 08, 2014 12:23 PM