I'm trying to perform a count on some case data that has the following example columns, the LVLx are case type value (CTV) coding, that are not unique. This is all from the same table.
YearMonth Account Case_Creation_TS LVL1 LVL2 LVL3 LVL4
2012-07 1000000 2012-07-01 14:20:12 ABC ABB ACC BAA
What I need to do is get a count/total of each combination of CTV code, where the case timestamp is <=30 days from the previous case per account, grouped by YearMonth.
I've trying ranking cases, then getting the difference between dates, but I'm working with in excess of 13 million rows and my long-handed way is causing me to run out of spool.
Anyone have any suggestions on a simpler method of counting like this?