none
T SQL Query Find a value present in a particular time bucket RRS feed

  • 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

Answers

  • 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