CODEWITHSUNDEEP
sql-server-2008
Patrick
Patrick

Reputation: 2577

Can I replace this loop in a sql server query

I have a list of items, sorted by date descending, and it checks them like this: 

counted = 0
DateToCheck = now  

foreach(item)
{
   if( abs(item.date - DateToCheck) > 14 days )
   {
      counted++
   }
   DateToCheck = item.date
}

The goal is to get a count of items on the list that did not occur within 14 days of the previous item.

The table is just a list of dates, like this: 

 index      ItemDate
  307000    2017-08-17
  307001    2017-04-25 
  307002    2016-09-23
  307003    2016-08-26
  307004    2016-04-30
  307005    2016-03-01
  307006    2016-03-01

The result here should be a count of 6, the last one is ignored since it is within 14 days of the one before.

Upvotes: 1

Views: 33

Answers (2)

Amit Kumar Singh
Amit Kumar Singh

Reputation: 4475

You can use this query if you do not have a ID column. Use ID column directly if you have one.

;WITH TBL AS (
 SELECT ROW_NUMBER() OVER(ORDER BY ItemDate ASC) Id, ItemDate FROM TABLE_NAME 
)
SELECT COUNT(a.ItemDate) FROM TBL a INNER JOIN TBL b ON b.ID = a.ID + 1 WHERE DATEDIFF(d, a.CreatedOn, b.CreatedOn) > 14;

With ID column, query changes to 

SELECT COUNT(a.ItemDate) FROM TABLE_NAME a INNER JOIN TABLE_NAME b ON b.ID = a.ID + 1 WHERE DATEDIFF(d, a.CreatedOn, b.CreatedOn) > 14;

Upvotes: 1

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use this query.

DECLARE @item TABLE([index] int, [date] DATETIME)

INSERT INTO @item 
VALUES( 307006    ,'2017-08-17'),
  (307005    ,'2017-04-25'),
  (307004    ,'2016-09-23'),
  (307003    ,'2016-08-26'),
  (307002    ,'2016-04-30'),
  (307001    ,'2016-03-01'),
  (307000    ,'2016-03-01')

SELECT 
    count(*)
FROM @item T1
    OUTER APPLY ( 
        SELECT TOP 1 * 
        FROM @item T2 
        WHERE T2.[index] < T1.[index]  
        ORDER BY T2.[index] DESC) T
WHERE DATEDIFF(DAY, T.[date], T1.[date])  > 14

Upvotes: 1

Related Questions