Counting based on row condition

[twifosp]

I have a problem I can't seem to get my head around.

I have a table that has a column that contains days between issues we log. So it's basically a datediff between the next row.

Example:

ID	Issue	Days_btwn_issue
1a	1	1
1a	2	0
1a	3	3
1a	4	4
1a	5	10
1a	6	0

I want to calculate a new issue number based on rules for days_between_issue. The rule would be, when days_between_issue is less than 2, then the issues are tied together in the same string.

Example:

ID	Issue	Days_btwn_issue	issue_num
1a	1	1		1
1a	2	0		2
1a	3	3		1
1a	4	1		2
1a	5	2		3
1a	6	10		1

As you can see from the example, everytime days between issue is greater than 2, then the count starts over. I've got some code to do counts, but it isn't working obviously.

select 	*, 
	'issue_num' = 
		(select	count(issue)
		from	#temp
		where	id= t.id
		and	issue <
			(select	min(issue)
			from	#temp
			where	issue > t.issue)
from	#temp t

This just counts it in numerical order. How can I structure it so it resets the count based on the days_btwn_issue column?

 

[twifosp]

any ideas?

 

[rac2]

An alternative to COUNT() is to SUM() 1's and 0's which are determined in a CASE expression.

SELECT 
       SUM(
            CASE
              WHEN issue <
                   ( select min(issue)
                     from   #temp 
                     where  issue > t.issue
                   )
                 THEN 1
              ELSE 0
            END
          )
FROM    #temp t

I doubt the query is what you need; I just plugged in some pieces from your post. The essential idea is to show how to structure the SUM(CASE ...) expression as an alternative to COUNT().