# T SQL Query Find a value present in a particular time bucket

• ### Question

• Hai,

I Have a requirement needs to find a student have present once in a week of a program of a full week of a month how could we find.

table1 has the follwoing column studentid,dayofpresent.

1001,2010-03-08
1001,2010-03-10
1001,2010-03-15
1002,2010-03-10
1002,2010-03-15

logic: user who have present atleast once present in the full week period (Sunday to Saturday).

Full week period of March 2010 :
week1 : 2010-03-07  to 2010-03-13
week2 : 2010-03-14 to  2010-03-20
week3 : 2010-03-21 to 2010-03-27

Output: 1001 -  weekly presnet student,1002 - Not a weeky present student

Thanks,

Prabakaran

Thanks, Vasantha Prabakaran

Monday, April 9, 2012 6:20 AM

• ```DECLARE	@Sample TABLE
(
StudentID SMALLINT NOT NULL,
DayOfPresent DATE NOT NULL
)

INSERT	@Sample
VALUES	(1001, '20100308'),
(1001, '20100310'),
(1001, '20100415'),
(1002, '20100310'),
(1002, '20100315')

-- Solution by SwePeso
SELECT		d.StudentID,
DATEADD(DAY, 7 * v.Number, d.theMin) AS StartOfWeek,
DATEADD(DAY, 7 * v.Number + 6, d.theMin) AS EndOfWeek,
ISNULL(f.Data, 0) AS IsPresent
FROM		(
SELECT		StudentID,
DATEADD(DAY, DATEDIFF(DAY, '18991231', MIN(DayOfPresent)) / 7 * 7, '18991231') AS theMin,
DATEADD(DAY, DATEDIFF(DAY, '18991231', MAX(DayOfPresent)) / 7 * 7, '18991231') AS theMax
FROM		@Sample
GROUP BY	StudentID
) AS d
INNER JOIN	master.dbo.spt_values AS v ON v.Type = 'P'
AND v.Number BETWEEN 0 AND DATEDIFF(DAY, d.theMin, d.theMax) / 7
OUTER APPLY	(
SELECT TOP(1)	1
FROM		@Sample AS w
WHERE		w.StudentID = d.StudentID
AND w.DayOfPresent >= DATEADD(DAY, 7 * v.Number, d.theMin)
AND w.DayOfPresent <= DATEADD(DAY, 7 * v.Number + 6, d.theMin)
) AS f(Data)```

N 56°04'39.26"
E 12°55'05.63"

Monday, April 9, 2012 6:52 AM