Need help finding the correct T-SQL Query

Question

I am not quite sure how to go about doing this. Basically I have have a table like this

UserId       DateRequested           Approved ApprovedBy  Notes
------------ ----------------------- -------- ----------- -----
1            2011-05-26               0        NULL        NULL
1            2011-05-27               0        NULL        NULL
1            2011-05-28               0        NULL        NULL
1            2011-06-05               0        NULL        NULL
1            2011-06-06               0        NULL        NULL
1            2011-06-25               0        NULL        NULL

Which basically contains the days an employee requests a holiday. Now, when a day or days is granted, this data needs to be copied over to a table of the form

UserId DateFrom DateTo

So basically for the above data i want:

UserId DateFrom DateTo 
-------------------------------
1      2011-05-26 2011-05-28 
1      2011-06-05 2011-06-06 
1      2011-06-25 2011-06-25 

I.e I want consecutive days in the DateFrom and DateTo. Now I am not sure how to do this without using a while loop. This is SQL, So i would prefer a non-iterative solution.

Please advise!!!

Gasparo

What version of SQL Server? And can we assume a unique constraint on UserId, DateRequested? – Rhythmandblues

SQL Server 2005. UserId is a primary key, and yes there is (should!) be a unique constraint on UserId and DateRequested – Gasparo

Is there a reason you are structuring the second table that way? It seems like it would make a lot more sense to just have a date and a UserID, and a row per day requested off. – Ophicleide

Because rows from other tables also need to go in the second table. And one of those tables has a date range (expecting large date ranges in there). A range was chosen to keep the number of rows minimal. Hope that makes senseth. – Gasparo

@Gasparo - Unfortunately it doesn't. I'm guessing you are actually wasting space with this method. Do you have a lot of employees asking off for 20-30 days at a time? In my experience most requests are for single days or for weeks. For a single day you take up MORE space since you need 2 columns with identical data in it. For a week you only 4 more rows and they are narrower, and I will guess less frequent than single day requests. – Ophicleide

@Gasparo - Also your current method will lead to all kinds of headaches when you are checking for the number of employees that already have a certain day off, or how many workdays a request entails since presumably your ranges include weekends and non-working days. – Ophicleide

Hmmm. I will have a re-think on this. Now that I have a query for this problem (thanks martin), I can think on the current design. Thanks JNK i will do more research. This is the first time i will be designing a database from scratch, need to do it right! – Gasparo

Answer 1

;WITH cte AS
(
SELECT *,
        DATEDIFF(DAY,0,DateRequested)-
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY DateRequested) AS Grp
FROM YourTable  
WHERE Approved = 1 /*Presumably - but your example data doesn't show this.*/
)
SELECT UserId, 
       MIN(DateRequested) AS DateFrom, 
       MAX(DateRequested) AS DateTo  
FROM cte 
GROUP BY UserId,Grp

Rhythmandblues

This largely depends on what Umair is after. Would this still work if they book days off from Thursday, returning on Tuesday? I think the above would result in two records (Thurs-Fri and Mon-Tues) rather than a single record going from Thursday to Tuesday. – Postdate

@hoombar - If there are no rows entered for Saturday or Sunday that will be treated as a gap yes. @Gasparo - You would also need to make sure that each day has a maximum of one row per UserId, DateRequested. You say that it is the PK but as you are using SQL Server 2005 and hence datetime datatype a check constraint ensuring the timepart is midnight would ensure this. – Rhythmandblues

Answer 2

In Oracle PL/SQL it would be written as follows:

WITH cte
        AS (SELECT a.*,
                   daterequested - TRUNC (SYSDATE)
                   - ROW_NUMBER ()
                        OVER (PARTITION BY UserId ORDER BY DateRequested)
                      AS Grp
              FROM yourtable a
             WHERE Approved = 0)
  SELECT UserId, MIN (DateRequested) AS DateFrom, MAX (DateRequested) AS DateTo
    FROM cte
GROUP BY UserId, Grp;

Wales