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!!!

What version of SQL Server? And can we assume a unique constraint on `UserId, DateRequested`? — Martin Smith, May 23 '11 at 15:24
SQL Server 2005. UserId is a primary key, and yes there is (should!) be a unique constraint on UserId and DateRequested — Umair, May 23 '11 at 15:31
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. — JNK, May 23 '11 at 15:32
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. — Umair, May 23 '11 at 15:37
@Umair - 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. — JNK, May 23 '11 at 15:39
@Umair - 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. — JNK, May 23 '11 at 15:41
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! — Umair, May 23 '11 at 15:49

score 6 · Accepted Answer · answered May 23 '11 at 15:33

6

;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

answered May 23 '11 at 15:33

Martin Smith

438,706
87
741
845

1

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. – Ben Pearson May 23 '11 at 15:47
1

@hoombar - If there are no rows entered for Saturday or Sunday that will be treated as a gap yes. @Umair - 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. – Martin Smith May 23 '11 at 15:49

score 1 · Answer 2 · answered May 23 '11 at 15:59

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;

Need help finding the correct T-SQL Query

2 Answers2

Linked