CODEWITHSUNDEEP
sql-server
happysmile
happysmile

Reputation: 7777

SQL query to get with day range issue

ID                      DateTime
634004024169995257  2010-05-10 12:29:00.000
634004024169995257  2010-05-30 12:43:00.000
634004024169995257  2010-06-19 12:24:00.000
634004024169995257  2010-06-22 13:08:00.000
634004024169995257  2010-07-19 12:41:00.000
634004024169995257  2010-07-16 12:22:00.000
634004024169995257  2010-08-25 12:19:00.000
634004024169995257  2010-08-30 11:41:00.000
634004024169995257  2010-09-28 12:37:00.000
634004024169995257  2010-09-29 12:41:00.000
634004024169995257  2010-11-01 13:14:00.000

Here I have table like this. I need to get the records with this condition (where in between each transaction if there is an difference of 19 days, I need to get those records only).

How to write this condition in SQL Server?

Upvotes: 0

Views: 151

Answers (2)

Callie J
Callie J

Reputation: 31336

You can do in with CTEs something like this:

WITH datarownum AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY [datetime]) AS RowCount FROM myDataTable
)
SELECT main.*, DATEDIFF (d, chk.dt, main.dt) FROM datarownum AS main
    INNER JOIN datarownum chk ON chk.RowCount = main.RowCount - 1
    WHERE DATEDIFF(d, chk.dt, main.dt) = 19

...replacing myDataTable with whatever your tablename actually is.

Upvotes: 0

Oleg Dok
Oleg Dok

Reputation: 21776

The question is not clear, but I hope thats what you want:

Select * 
from YourTable yt
WHERE EXISTS 
  (
    SELECT * 
    FROM YourTable 
    WHERE DATEDIFF(d, [DateTime], yt.[DateTime]) = 19
  )

Or

Select * 
from YourTable yt
WHERE EXISTS 
  (
    SELECT * 
    FROM YourTable 
    WHERE 
        Id = yt.Id 
    AND DATEDIFF(d, [DateTime], yt.[DateTime]) = 19
  )

Upvotes: 2

Related Questions