How many entities of one type open in a given month?

[Collin James]

Hi all ,

My data looks like the below (id changed for privacy reasons. I can see the start and end date of investigations or cases. I want to make counts, for given months, of how many cases and investigations one staff person has open (my data has many staff people in it). For exmaple. In month 650, there were 2 investigations and a case opened. However, because the case and investigation before that don't end until month 658 and 650 respecitvely, I want it to count 3 invesitgaitons and 2 cases for month 650. How would I do this? In the end, all I need is by staff and month, how many total investigations and cases they were working on. TY!

----------------------- copy starting from the next line -----------------------


	
	
* Example generated by -dataex-. For more info, type help dataex
clear
input str7 staff_id float(start_date end_date) str8 asgn_cat
"K078" 648 658 "CAS"
"K078" 649 650 "INV"
"K078" 650 651 "INV"
"K078" 650 651 "INV"
"K078" 650 672 "CAS"
"K078" 677 680 "INV"
end
format %tm start_date
format %tm end_date
------------

[Clyde Schechter]

gen `c(obs_t)' obs_no = _n
expand end_date - start_date + 1
by obs_no, sort: gen month = start_date + _n - 1
format month %tm
collapse (count) open = obs_no, by(staff_id month asgn_cat)
reshape wide open, i(staff_id month) j(asgn_cat) string

Note: the final -reshape wide- command is optional, depending on what you will be doing with these results. For most purposes it will be better to skip the -reshape- and leave the data in long layout, but for some purposes wide is better.