CODEWITHSUNDEEP
sqlsql-serverjoin
Suneth
Suneth

Reputation: 209

How to return records with no activity date in the last 90 days?

I have a query  to return all the records that have no activity in the last 90 days, but it looks like it is returning records that are active in the last 90 days too. How can I fix this?

My logic to check if they are active -  tm.timeslip_date <= DATEADD(day, -90, GETDATE()) I thought this would do anything if they are not active for the last 90 days. This might be a easy fixed, but I am stumped now.

WITH unit AS (
   SELECT
         tm.create_date 
       , tm.timeslip_date
       , cases.case_sk
       , cases.case_number
       , cases.closed_ind
       , cases.atty2_sk
       , vc.atty2_name    AS [Business Leader]
       , em.smtp_reply_to AS [Business Leader Email]
       , cases.atty1_sk
       , vc.atty1_name    AS [Assign Attorney]
       , tm.detail_notes
   FROM dbo.cases
   LEFT JOIN dbo.vcases vc ON cases.case_sk = vc.case_sk
   LEFT JOIN dbo.employee em ON cases.atty2_sk = em.employee_sk    
   LEFT JOIN dbo.timeslips tm 
         ON cases.case_sk = tm.case_sk
         AND tm.timeslip_date <= DATEADD(day, -90, GETDATE()) -- This where I check last 90 days no timeslip dates** 
   WHERE cases.closed_ind = 'O'

      
), agg AS (
   SELECT
         MIN(u.create_date)   AS [Created Date]
       , MAX(u.timeslip_date) AS [Last Bill Date]
       , u.case_sk
       , u.case_number
       , u.closed_ind
       , u.atty2_sk
       , u.[Business Leader]
       , u.[Business Leader Email]
       , u.atty1_sk
       , u.[Assign Attorney]
   FROM unit u
   GROUP BY 
         u.case_sk
       , u.case_number
       , u.closed_ind
       , u.atty2_sk
       , u.[Business Leader]
       , u.[Business Leader Email]
       , u.atty1_sk
       , u.[Assign Attorney]
)
SELECT agg.*, unit.detail_notes
FROM agg
INNER JOIN unit
   ON  agg.[Last Bill Date] = unit.[timeslip_date]
        AND agg.case_sk = unit.case_sk
        AND agg.case_number = unit.case_number
        AND agg.closed_ind = unit.closed_ind
        AND agg.atty2_sk = unit.atty2_sk
        AND agg.atty1_sk = unit.atty1_sk
WHERE agg.case_sk ='66505'
ORDER BY agg.[Created Date] ASC

So this below case should not pull, since it has activity within the last 90 days

enter image description here

Upvotes: 0

Views: 856

Answers (1)

lptr
lptr

Reputation: 6788

...............   
LEFT JOIN dbo.timeslips tm 
         ON cases.case_sk = tm.case_sk
         --AND tm.timeslip_date <= DATEADD(day, -90, GETDATE()) -- This where I check last 90 days no timeslip dates** 
   WHERE cases.closed_ind = 'O'
   AND NOT EXISTS(SELECT * FROM dbo.timeslips tsm WHERE tsm.case_sk = cases.case_sk AND tsm.timeslip_date > DATEADD(day, -90, GETDATE()) )
.........................

Upvotes: 1

Related Questions