using datediff and then determining buckets for results

Question

Im trying to subtract one date from another and with the result group them into one of four buckets:

0-30 days
31-45 days
46-60 days
60+ days

this is what I have so far and I think I'm going down a couple different rabbit holes here and thusly in circles.

If anyone can assist that would be awesome:

DECLARE @Today DATETIME = GETDATE()
DECLARE @DATEBUCKET DATETIME = DATEDIFF(DAY,[appealDate],@Today )

SELECT
[ccoName]
,[Id]
,[memName]
,[DOB]
,[provider]
,[NPI]
,[callerId]
,[callTypeDesc]
,[appealDate]
,COUNT(DATEBUCKET) AS agebyAppD
,DTE=DATEADD(DAY, DATEDIFF(DAY,[dueDate], @Today )/4*4,0)
,[dueDate]

FROM mytable
WHERE [callTypeDesc] IN (
'##Claim Appeal'
,'Appeal - Claim MBA Retro Review'
,'Appeal - DME'
,'Appeal - System Coordinator Configuration Questions'
,'Appeal - UM-Medical (Quality Assurance)'
,'Claim Appeal Over 45 Days'
,'Escalated to Supervisor'
,'FAX Images'
,'General Remit/EOB Inquiries'

)
AND [appealDate] IS NOT NULL;

Answer 1

Hi @Mel Buckner ，

Welcome to the Microsoft TSQL Q&A Forum!

As ErlandSommarskog mentioned, because you did not provide relevant data and expected results, we are temporarily unable to provide you with advice. But according to your description, I wrote an example, which may be useful to you:

    create table #test(today date)  
    insert into #test values('2021-01-01'),('2020-12-21'),('2020-12-01'),('2020-10-01')  
                            ,('2021-01-02'),('2020-12-22'),('2020-12-02'),('2020-10-02')  
      
    ;with cte   
    as(select today,DATEBUCKET,case when DATEBUCKET between 0 and 30 then 1  
                            when DATEBUCKET between 31 and 45 then 2  
     when DATEBUCKET between 46 and 60 then 3  
     when DATEBUCKET >60 then 4 end rn  
    from (select today,DATEDIFF(DAY,today,GETDATE()) DATEBUCKET from #test) t)  
      
    select today,DATEBUCKET,case when rn=1 then DATEBUCKET end [0-30 days],  
    case when rn=2 then DATEBUCKET end [31-45 days],  
    case when rn=3 then DATEBUCKET end [46-60 days],  
    case when rn=4 then DATEBUCKET end [60+ days]  
    from cte  

Output:

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.