Calculation that spans rows (probably non-aggregate)

[jararaca]

Hi,

I have a table with a column called AdmitDate and I need to know how many rows in that table have AdmitDates that are within 30 days of the AdmitDate of the previous row. I'm stumped about how to write such a query, or if it is even possible to do so. Can anyone please help?

thanks

 

[gmmastros]

I usually use a table variable with an identity column to do this. Ex:

[green]-- Dummy data[/green]
Declare @YourTable Table(Id integer, AdmitDate DateTime)

Insert into @YourTable Values(1, '2006-06-28')
Insert into @YourTable Values(2, '2004-06-28')
Insert into @YourTable Values(2, '2005-06-28')
Insert into @YourTable Values(2, '2006-06-28')
Insert into @YourTable Values(3, '2006-06-01')
Insert into @YourTable Values(3, '2006-06-10')
Insert into @YourTable Values(3, '2006-06-28')

[green]-- End of dummy data
-- Query starts here[/green]

Declare @Temp Table (RowId Integer Identity(1,1), Id Integer, AdmitDate DateTime)

Insert Into @Temp(Id, AdmitDate)
Select Id, AdmitDate
From   @YourTable
Order By Id, Admitdate

Select *
From   @Temp A
       Inner Join @Temp B
         On  A.RowId = B.RowId - 1
         And A.ID = B.Id
         And DateDiff(dd, A.AdmitDate, B.AdmitDate) <= 30

Of course, tablenames and field names would have to change to match your database structure.

-George

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

 

[jararaca]

Amazing! Thanks!