parsing data 1

[chaoma]

Hello,

I have the follow data:

ID Begin_date End_date
1 20050701 20050731
1 20051101 20051231
2 20050801 20051130

I would like the number of occurrences in the month from 7/1/2005 to 5/31/2006. From the above example, the result would be:

YYYYMM Count
200507 1
200508 1
200509 1
200510 1
200511 2
200512 2

Maybe I need to break the data into each individual month, if so how do I do that?

Thank you for any assistance

 

[r937]

which database is this?

also, what are the datatypes of the Begin_date and End_date columns?

the reason i ask is because the solution is easier with actual date columns and date functions, anbd of course date functions vary from one database to the next



r937.com | rudy.ca

 

[chaoma]

Thank you. This is an actual date datatype. Database is SQL 2000.

Chaoma

 

[r937]

if you don't already have one, start by creating an integers table

create table integers 
  (i integer not null primary key) 
insert into integers (i) values (0) 
insert into integers (i) values (1) 
insert into integers (i) values (2) 
insert into integers (i) values (3) 
insert into integers (i) values (4) 
insert into integers (i) values (5) 
insert into integers (i) values (6) 
insert into integers (i) values (7) 
insert into integers (i) values (8) 
insert into integers (i) values (9)

now we can generate your series of YYYYMM values like this --

select convert(char(6)
          ,dateadd(m,i,'2005-07-01'),112)
               as YYYYMM
  from integers

YYYYMM
200507
200508
200509
200510
200511
200512
200601
200602
200603
200604

then, we can use a LEFT OUTER JOIN to join with your data points

select convert(char(6)
          ,dateadd(m,i,'2005-07-01'),112)
               as YYYYMM
     , Begin_date  
     , End_date
  from integers
left outer
  join daTable
    on dateadd(m,i,'2005-07-01')
        between Begin_date
            and End_date 

YYYYMM  Begin_date End_date
200507  2005-07-01 2005-07-31 
200508  2005-08-01 2005-11-30 
200509  2005-08-01 2005-11-30 
200510  2005-08-01 2005-11-30 
200511  2005-11-01 2005-12-31 
200511  2005-08-01 2005-11-30 
200512  2005-11-01 2005-12-31 
200601   (null)     (null)    
200602   (null)     (null)    
200603   (null)     (null)    
200604   (null)     (null)

notice two things from this --

your test data was wrong (there's only one count for 200512)

there are only 10 integers, so we've generated only 200507 through 200604

to generate 12 YYYYMM values, we can either add values 10 and 11 to the integers table, or (and this is my preference), leave the integers as 0 through 9 and simply cross join the integers table to generate more numbers

finally, we need to summarize with a GROUP BY

select convert(char(6)
          ,dateadd(m,10*t.i+u.i,'2005-07-01'),112)
               as YYYYMM
     , count(Begin_date) as daCount         
  from integers as u
cross
  join integers as t  
left outer
  join daTable
    on dateadd(m,10*t.i+u.i,'2005-07-01')
        between Begin_date
            and End_date 
 where 10*t.i+u.i between 0 and 11
group
    by convert(char(6)
          ,dateadd(m,10*t.i+u.i,'2005-07-01'),112)


YYYYMM daCount
200507	1
200508	1
200509	1
200510	1
200511	2
200512	1
200601	0
200602	0
200603	0
200604	0
200605	0
200606	0

simple, eh?

r937.com | rudy.ca

 

[chaoma]

r937,
Thank you it worked wonderfully!!!!!!!!