CODEWITHSUNDEEP
sqlcountfilteringgroupingnetezza
tlswpsl
tlswpsl

Reputation: 97

How to group, count consecutive dates and use it as filter in Netezza

I'm trying to group consecutive dates, count the consecutive dates, and use that count as filter.

I have a table that currently looks like: 

pat_id   admin_dates   admin_grp    daily_admin    
-------------------------------------------------
1        08/20/2018        1          2 doses
1        08/21/2018        1          3 doses
1        08/22/2018        1          1 doses
1        10/05/2018        2          3 doses
1        12/10/2018        3          4 doses
2        01/05/2019        1          1 doses
2        02/10/2019        2          2 doses
2        02/11/2019        2          2 doses

where admin_grp is grouping consecutive dates per pat_id.

I want to exclude all rows that have less than 3 consecutive dates for same pat_id. In this example, only pat_id = 1 and admin_grp = 1 condition has 3 consecutive dates, which I would like to see in result. My desired output would be: 

pat_id   admin_dates   admin_grp    daily_admin    
-------------------------------------------------
1        08/20/2018        1          2 doses
1        08/21/2018        1          3 doses
1        08/22/2018        1          1 doses

I honestly have no idea how to perform this.. my attempt failed to count how many admin_grp has same value within same pat_id, let alone using that count as filter. If anyone could help out / suggest ideas how to tackle this, it will be greatly appreciated.

Upvotes: 0

Views: 201

Answers (2)

George Joseph
George Joseph

Reputation: 5922

Assuming that any admin_grp would only have consecutive days, you would just need to count those records by (patid,admin_grp) that have 3 or greater records.

Eg:

select x.* 
  from (select t.*
               ,count(*) over(partition by patid,admin_grp) as cnt
          from table t
       )x
 where x.cnt>=3

Upvotes: 1

Lars G Olsen
Lars G Olsen

Reputation: 1118

Short answer: join the table with itself on ‘pat_id’ and filter appropriately:

Select a.* from TABLE a 
join (Select * from TABLE where daily_admin=‘3 doses’) b
using (pat_id) 
Where a.daily_admin in (‘1 doses’, ‘2 doses’, ‘3 doses’)

Btw: too bad the ‘daily_admin’ column is not an integer... better data model would have made the Where statement slightly simpler :)

Upvotes: 0

Related Questions