Sub Query Counts Based on Conditions

[twifosp]

Having a hard time getting my head around how to write this query.

I have some data that looks like this:

inventory_id	incident_timestamp
abc123	4/26/2006 0:00
abc123	7/7/2006 0:00
abc123	7/8/2006 0:00
abc123	7/19/2006 0:00
abc456	4/24/2006 0:00
abc456	7/1/2006 0:00
abc456	7/9/2006 0:00
abc456	7/11/2006 0:00

Now I want to give it a rolling issue number for how many incidents are included with a 7 day time period. The data would look like this:

inventory_id	incident_timestamp	Incident_Num
abc123	4/26/2006 0:00	1
abc123	7/7/2006 0:00	1
abc123	7/8/2006 0:00	2
abc123	7/9/2006 0:00	3
abc123	7/10/2006 0:00	4
abc123	7/19/2006 0:00	1
abc123	7/20/2006 0:00	2
abc456	4/24/2006 0:00	1
abc456	7/1/2006 0:00	1
abc456	7/9/2006 0:00	1
abc456	7/11/2006 0:00	2

So I wrote a query that looks like this:

SELECT  a.*
        (SELECT	COUNT(1) + 1
        FROM	incident b
        WHERE	b.inventory_id = a.inventory_id
	AND 	a.incident_timestamp] > b.incident_timestamp]
		AND 	DATEDIFF(dd,b.incident_timestamp] , a.incident_timestamp]) <= 7)
	AS IncidentNumber
FROM      dbo.incident a
where	a.inventory_id = '03DLB'
order by incident_timestamp

And it works for inventorys that have 2 or less incident strings. In other words, my data comes out like this instead:

inventory_id	incident_timestamp	Incident_Num
abc123	4/26/2006 0:00	1
abc123	7/7/2006 0:00	1
abc123	7/8/2006 0:00	2
abc123	7/9/2006 0:00	3
abc123	7/10/2006 0:00	4
[b]abc123	7/19/2006 0:00	2
abc123	7/20/2006 0:00	3[/b]
abc456	4/24/2006 0:00	1
abc456	7/1/2006 0:00	1
abc456	7/9/2006 0:00	1
[b]abc456	7/11/2006 0:00	1[/b]

The bolded rows are incorrect as they are within 7 days of the last incident, but not resetting to 1. Anyone know why it works for some of the dataset, but not all of it? Anyone have a better way of doing this?

 

[vbkris]

a better and easier way would be the use of cursors. however this is a very challenging query. will try to come up with a pure SQL solution...

Known is handfull, Unknown is worldfull

 

[twifosp]

I don't know about better, but yes cursors would be easier. They would take a whole lot longer and be resource intensive though. I don't see a reason why this query doesn't work, especially since it works part of the time but not all of the time.

 

[twifosp]

Any experts have any input on this? I can't figure out why it works for some of the data set and not all of it... I can do this another way with while loops or cursors, but I really would like to keep it clean, not to mention find out why the code doesn't work.

 

[vbkris]

hi, rather than using count try sum:

(SELECT SUM(1)

Known is handfull, Unknown is worldfull

 

[twifosp]

Well I suppose I could, but I'd have to add a 1 int column to each record. How is that going to operate any different than count?

 

[gmmastros]

I think your sample output is flawed because it shows:
abc123 7/9/2006 0:00 3

But, in the sample data, abc123 doesn't have an incident on that day.

This is what I came up with:

Select 	A.Inventory_ID, 
        A.Incident_TimeStamp, 
        Count(B.Inventory_Id) As Incident_Num
From    dbo.incident  A
        Left Join dbo.incident  B 
            On A.Inventory_Id = B.Inventory_Id
            And A.Incident_TimeStamp >= B.Incident_TimeStamp
Where   A.Incident_TimeStamp - B.Incident_TimeStamp Between 0 and 7
Group By A.Inventory_ID, A.Incident_timeStamp
Order By A.Inventory_ID, A.Incident_TimeStamp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom