CODEWITHSUNDEEP
sqldb2
brucezepplin
brucezepplin

Reputation: 9752

count occurrences for each week using db2

I am looking for some general advice rather than a solution. My problem is that I have a list of dates per person where due to administrative procedures, a person may have multiple records stored for this one instance, yet the date recorded is when the data was entered in as this person is passed through the paper trail. I understand this is quite difficult to explain so I'll give an example:

Person     Date            Audit
------     ----            -----
1          2000-01-01      A
1          2000-01-01      B
1          2000-01-02      C
1          2003-04-01      A
1          2003-04-03      A

where I want to know how many valid records a person has by removing annoying audits that have recorded the date as the day the data was entered, rather than the date the person first arrives in the dataset. So for the above person I am only interested in:

Person     Date            Audit
------     ----            -----
1          2000-01-01      A
1          2003-04-01      A

what makes this problem difficult is that I do not have the luxury of an audit column (the audit column here is just to present how to data is collected). I merely have dates. So one way where I could crudely count real events (and remove repeat audit data) is to look at individual weeks within a persons' history and if a record(s) exists for a given week, add 1 to my counter. This way even though there are multiple records split over a few days, I am only counting the succession of dates as one record (which after all I am counting by date).

So does anyone know of any db2 functions that could help me solve this problem?

Upvotes: 0

Views: 1191

Answers (1)

mustaccio
mustaccio

Reputation: 18945

If you can live with standard weeks it's pretty simple:

select 
  person, year(dt), week(dt), min(dt), min(audit) 
from 
  blah 
group by 
  person, year(dt), week(dt)

If you need seven-day ranges starting with the first date you'd need to generate your own week numbers, a calendar of sorts, e.g. like so:

with minmax(mindt, maxdt) as ( -- date range of the "calendar"
  select min(dt), max(dt) 
  from blah
), 
cal(dt,i) as ( -- fill the range with every date, count days
  select mindt, 0 
  from minmax 
 union all 
  select dt+1 day , i+1 
  from cal 
  where dt < (select maxdt from minmax) and i < 100000
) 
select 
  person, year(blah.dt), wk, min(blah.dt), min(audit)  
from 
  (select dt, int(i/7)+1 as wk from cal) t -- generate week numbers
inner join 
  blah 
    on t.dt = blah.dt 
group by person, year(blah.dt), wk

Upvotes: 1

Related Questions