Here is some sample data.

SET NOCOUNT ON;
DECLARE @t TABLE (SSN VARCHAR(15))
INSERT INTO @t (SSN) SELECT '123-45-6789'
INSERT INTO @t (SSN) SELECT '123-45-67.89'
INSERT INTO @t (SSN) SELECT 'ABC-12-3455'
INSERT INTO @t (SSN) SELECT '123-45-67890'
INSERT INTO @t (SSN) SELECT '123-456789'
INSERT INTO @t (SSN) SELECT ' 123-45-6789'
INSERT INTO @t (SSN) SELECT '12345-6789'
INSERT INTO @t (SSN) SELECT '123456789'
INSERT INTO @t (SSN) SELECT '123-12-1234'

/*
Expected Result:

SSN
---------------
123-45-6789
123-12-1234
*/

Notes:

1. Remember the key here is to write the shortest code
2. CLR Functions not allowed
3. Make sure that the code runs on the above table. (don’t create your own table. That will give me a tough time testing the code. You can add more rows to the above table to test your code with more data. In such cases, include those insert statements when you send me the code)
4. Send your entries to jacob@beyondrelational.com
5. Do not paste the code in the email body. Include it as an attachment (.sql file)
6. Mention ‘TSQL Challenge 4’ in the email subject
7. Last Date: 24 April 2009

The toughest part with this challenge was that almost all entries produced the correct result. However, several dozens of them used cursors and WHILE LOOPS to generate the result. Since the challenge is to generate the result with a single query, I discarded the entries that used cursors and loops.

After all the scanning and filtering, I ended up with 70+ shortlisted entries and identifying the top 3 from them was really difficult. I used the following logic to select the top 3 from the shortlisted entries.

1. I ordered the entries by the length and complexity of the query. Some of the entries used only one CTE, but many used two or three CTEs. I discarded the entries with more than one CTE and ended with 13 finalists.
2. I looked at the IO used by each query and ordered them based on that. After ordering them, I took the top 3 entries that used least IO to produce the results.

I would like to thank everyone who participated in this contest. I would like to congratulate those 70+ winners who sent me ‘correct’ entries that solves the challenge.

Here are the (TOP 3) winners from the list of 70+ SQL Server experts who can translate any business logic into a TSQL query.

Rui Carvalho

Rui Carvalho is a senior developper on Sql Server and .Net mainly experienced in web applications. He work as consultant for a Microsoft experts company called Winwise in france. Rui worked in the past as a full time Sql developper specialised in sql optimisations and reporting for CRM applications and now  mainly focus his time on .Net core technologies and Asp.net MVC. Architecture and software design are also part of his job.

He runs two blogs, one in French and one in English.

Rui was one of the winners in TSQL Challenge 2 as well.

Leonid Koyfman

Leonid Koyfman is a Senior Developer with Razorfish ( San Francisco). He is in software development over 10 years. His focus is data visualization and delivering reporting solutions from various data sources, but spending most of the time with SQL Server.

Leonid was one of the winners in TSQL Challenge 1 as well.

Friedrich Paul

Friedrich Paul live in Bangalore, India. He completed my MCA from Christ College , Bangalore on April 2008. Currently he is working as database developer at First Indian Corporation from past 8 months. He maintains a blog at http://datawarehousejourney.blogspot.com/

Solving the Problem

Two of the winners, Rui and Friedrich used a recursive CTE to generate the required output while Leonid used a combination of a number table and FOR XML path to produce the ‘reversed’ string.

Here is the solution of Rui.

DECLARE @t TABLE( ID INT IDENTITY, data NVARCHAR(20))
INSERT INTO @t(data) SELECT 'Jacob'
INSERT INTO @t(data) SELECT 'Sebastian'

;WITH REVERSE_CTE AS (
    SELECT
        ID,
        data,
        cast('' as NVARCHAR(20)) AS inverted,
        CASE WHEN LEN(ISNULL(data,'')) = 0 THEN 1 ELSE 0 END AS pos
    FROM @t 
    UNION ALL
    SELECT    
        ID,
        data, 
        CAST(inverted + SUBSTRING(data,LEN(data)-LEN(inverted),1) AS NVARCHAR(20)),
        pos = cast(LEN(data)-LEN(inverted) as int)
    FROM REVERSE_CTE
    WHERE LEN(data)-LEN(inverted) > 0 
)
SELECT ID,data=inverted 
FROM REVERSE_CTE WHERE pos = 1
/*
ID          data
----------- --------------------
2           naitsabeS
1           bocaJ
*/

Here is the solution of Leonid.

DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))
INSERT INTO @t(data) SELECT 'Jacob'
INSERT INTO @t(data) SELECT 'Sebastian'
 
;WITH Numbers(N) AS (
    SELECT    DISTINCT Number 
    FROM      master..spt_values
    WHERE     Number BETWEEN 1 AND (SELECT MAX(LEN(data))FROM @t)
)
SELECT 
    id,
    data_reversed
FROM @t T1
CROSS APPLY(
     SELECT 
        SUBSTRING(data,N,1)
     FROM @t T2 JOIN Numbers ON N<=LEN(data)
     WHERE T1.id=T2.id
     ORDER BY N DESC
     FOR XML PATH('')
)x(data_reversed)
ORDER BY id DESC
/*
id          data_reversed
----------- ---------------
2           naitsabeS
1           bocaJ
*/

Finally, here is the solution of Friedrich.

DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))
INSERT INTO @t(data) SELECT 'Jacob'
INSERT INTO @t(data) SELECT 'Sebastian'

;WITH revtab (id,data,position) AS
(
    select 
        id, 
        SUBSTRING(data,len(data),1),
        len(data)-1 as position 
    from @t
    UNION ALL
    select 
        t.id, 
        SUBSTRING(t.data,position,1),
        position - 1 as position
    from @t as t INNER JOIN revtab as r ON t.id = r.id 
    where position <> 0 
)
SELECT 
    t1.id,
    ( 
        SELECT 
            data + '' 
        FROM revtab t2
        WHERE t2.id= t1.id
        ORDER BY position desc
        FOR XML PATH('') 
    ) AS Names
FROM revtab t1
GROUP BY id
/*
id          Names
----------- ------------------
1           bocaJ
2           naitsabeS
*/

I hope you enjoyed the challenge. Thank you every one who participated in the challenge. I will post the next challenge later today.

So, the task is to reverse a string without using the REVERSE() function. What is wrong with the REVERSE() function? Nothing Really! As I mentioned earlier, this is to refresh your TSQL skills on set based operations. In real life, you should always use the REVERSE() function, if ever you need to reverse a string.

Again, we are not going to reverse a single string. We need to reverse all the values in the column of a table using a single query.

Here is the sample Data

ID          data
----------- --------------------
1           Jacob
2           Sebastian

Here is the expected result

id          data
----------- --------------------
2           naitsabeS
1           bocaJ

Use the script below to create the sample table.

DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(20))
INSERT INTO @t(data) SELECT 'Jacob'
INSERT INTO @t(data) SELECT 'Sebastian'

Notes:

1. Write a single query that produces the expected result. No User Defined Functions allowed.
2. Make sure that your code works with the sample script given above. Use the same column names, table variable name etc. This makes my life easier while testing the code.
3. No restriction on SQL Server version. You can write the query for SQL Server 2000, 2005 or 2008
4. Make sure that the subject of your email is ‘TSQL Challenge 3’
5. Last date to submit your entries: 31 March 2009

Here are the winners of TSQL Challenge 2.

Antoine Gémis

Antoine Gémis lives in Toulouse, France. He is involved in software development over 15 years, mostly in networking, accounting and kiosk development.
Since 2008,  he is an employee of Navocap  as full time SQL developer. Antoine is working on "real-time" geolocation databases and you can find his blog here.

Rui Carvalho

Rui Carvalho is a senior developper on Sql Server and .Net mainly experienced in web applications. He work as consultant for a Microsoft experts company called Winwise in france. Rui worked in the past as a full time Sql developper specialised in sql optimisations and reporting for CRM applications and now  mainly focus his time on .Net core technologies and Asp.net MVC. Architecture and software design are also part of his job.

He runs two blogs, one in French and one in English.

Tejas Shah

Tejas Shah, is very strong in .NET and MS SQL. He has 4+ years of Experience as a Web Developer. He is M.sc. (C.A. & I.T.) (Master Of Science in Computer Application and Information Technology). He is currently working as Team Leader at Ahmedabad, India.

You can find his blog here.

SOLVING THE CHALLENGE

Solving this challenge involves solving two logical hurdles. The first part of the problem is to generate a sequence of dates that falls within the given date values so that we can calculate the hours of each date. For example, to calculate the working hours between 6th March 2 PM and 9th March 11 AM, we need to look at each date that falls between this period and calculate the hours of each day. The following example illustrates this.

/*
From Date            Day        To Date              WorkHrs
-----------------    ---        -----------------    -------
2009-Mar-06 14:00    Fri        2009-Mar-06 17:00    03:00                
2009-Mar-07 08:00    Sat        2009-Mar-07 17:00    00:00
2009-Mar-08 08:00    Sun        2009-Mar-08 17:00    00:00
2009-Mar-09 08:00    Mon        2009-Mar-09 11:00    03:00
                                                     -----
                                                     06:00
                                                     -----
*/

So, the period between 2009-Mar-06 14:00 and 2009-03-09 11:00 gives us 6 working hours. We need to do such a calculation for each row we have in the table. So the key here is identifying the dates between given two values and then looking at the day (sunday, monday etc) and calculate the work hours accordingly.

The question now is, “how do we generate a sequence of dates that falls between a given date range. Well, there are a few options.

USING A CALENDAR TABLE

If you search on internet for “Calendar Table”, you will find many articles that explain the advantages of using a calendar table. Almost all databases that deal with business applications keep a calendar table to help perform various calculations. If your database has a calendar table, it can be used to solve the above problem.

Let us see, how we can make use of a calendar table to achieve this result. Let us build a calendar table for the purpose of this demonstration. I am inserting only 10 rows to the calendar table. But in a real life scenario, you might have a few years of data in your calendar table.

The following example builds a calendar table needed for this example

IF OBJECT_ID('Calendar','U') IS NOT NULL DROP TABLE Calendar
CREATE TABLE Calendar (dt DATETIME)
GO

DECLARE @FromDate DATETIME, @ToDate DATETIME
SELECT @FromDate = '2009-03-01', @ToDate = '2009-03-10'
WHILE @FromDate <= @ToDate BEGIN
    INSERT INTO Calendar (dt) SELECT @FromDate
    SELECT @FromDate = @FromDate + 1
END

SELECT * FROM Calendar
/*
dt
-----------------------
2009-03-01 00:00:00.000
2009-03-02 00:00:00.000
2009-03-03 00:00:00.000
2009-03-04 00:00:00.000
2009-03-05 00:00:00.000
2009-03-06 00:00:00.000
2009-03-07 00:00:00.000
2009-03-08 00:00:00.000
2009-03-09 00:00:00.000
2009-03-10 00:00:00.000
*/

Now, let us write a query that uses this calendar table to achieve the result that we needed.

DECLARE @t Table(FromDate DATETIME, ToDate DATETIME)
INSERT INTO @t (FromDate, ToDate) SELECT '2009-03-06 14:00', '2009-03-09 11:00'

SELECT 
    dt AS Date,
    DATENAME(weekday, dt) AS wkday
FROM @t t
CROSS JOIN Calendar c
WHERE 
    dt BETWEEN DATEADD(d,0,DATEDIFF(d,0,FromDate)) 
    AND 
    DATEADD(d,0,DATEDIFF(d,0,ToDate)) 
/*
Date                    wkday
----------------------- ------------------------------
2009-03-06 00:00:00.000 Friday
2009-03-07 00:00:00.000 Saturday
2009-03-08 00:00:00.000 Sunday
2009-03-09 00:00:00.000 Monday
*/

The above example demonstrated how to generate a sequence of dates between the given date values. Now let us apply a tiny logic and calculate the actual number of hours for each day. Let us generate the time period (business hours) on each day (between 8 am and 5 PM).

DECLARE @t Table(FromDate DATETIME, ToDate DATETIME)
INSERT INTO @t (FromDate, ToDate) SELECT '2009-03-06 14:00', '2009-03-09 11:00'

SELECT 
    dt AS Date,
    LEFT(DATENAME(weekday, dt),3) AS wkday,
    CASE 
        WHEN c.dt = DATEADD(d,0,DATEDIFF(d,0,FromDate)) THEN FromDate 
        ELSE DATEADD(hour,8,dt)
    END AS StartTime,
    CASE 
        WHEN c.dt = DATEADD(d,0,DATEDIFF(d,0,ToDate))  THEN ToDate 
        ELSE DATEADD(hour,17,dt) 
    END AS EndTime
FROM @t t
CROSS JOIN Calendar c
WHERE 
    dt BETWEEN DATEADD(d,0,DATEDIFF(d,0,FromDate)) 
    AND 
    DATEADD(d,0,DATEDIFF(d,0,ToDate))
/*
Date                    wkday StartTime               EndTime
----------------------- ----- ----------------------- -----------------------
2009-03-06 00:00:00.000 Fri   2009-03-06 14:00:00.000 2009-03-06 17:00:00.000
2009-03-07 00:00:00.000 Sat   2009-03-07 08:00:00.000 2009-03-07 17:00:00.000
2009-03-08 00:00:00.000 Sun   2009-03-08 08:00:00.000 2009-03-08 17:00:00.000
2009-03-09 00:00:00.000 Mon   2009-03-09 08:00:00.000 2009-03-09 11:00:00.000
*/ 

Look at the StartTime and EndTime. It shows the actual business hours on each day. Now, we can do DATEDIFF() function to get the differences between the two date values and ignore Sat and Sun. Then apply a SUM() over the DATEDIFF() function and you will get the total minutes. You can then convert this minutes to hours.

Note: The code given above is a simplified version of the final code, presented to explain one of the logical approaches to solve this problem. The above code is not tested against all the different combination of values.

USING A NUMBER TABLE

We saw, how to use a calendar table to solve the problem presented in this challenge. Now let us see how a number table can help solve this.

While a calendar table can help solve a number of date-time related problems, a number table can help solve date-time related problems as well as a wide range of other problems. I have used a number table to solve a number of string parsing problems.

Many of our databases have large number tables. If the range of numbers needed is small, I usually use the ‘undocumented’ table master..spt_values. You should use this only at your own risk :-). If you need a number table, it is always a good idea to create a number table instead of using spt_values table. However, I use master..spt_values for demonstrating code samples as it is pretty easy to use. (no setup needed).

The following code snippet shows a different version of the previous code that uses a number table.

DECLARE @t Table(FromDate DATETIME, ToDate DATETIME)
INSERT INTO @t (FromDate, ToDate) SELECT '2009-03-06 14:00', '2009-03-09 11:00'

SELECT 
    DATEADD(d,0,DATEDIFF(d,0,FromDate)) + Number AS Date,
    LEFT(DATENAME(weekday, DATEADD(d,0,DATEDIFF(d,0,FromDate)) + Number),3) AS wkday,
    CASE 
        WHEN DATEADD(d,0,DATEDIFF(d,0,FromDate)) + Number = DATEADD(d,0,DATEDIFF(d,0,FromDate)) 
            THEN FromDate 
        ELSE DATEADD(hour,8,DATEADD(d,0,DATEDIFF(d,0,FromDate)) + Number)
    END AS StartTime,
    CASE 
        WHEN DATEADD(d,0,DATEDIFF(d,0,FromDate)) + Number = DATEADD(d,0,DATEDIFF(d,0,ToDate))  
            THEN ToDate 
        ELSE DATEADD(hour,17,DATEADD(d,0,DATEDIFF(d,0,FromDate)) + Number) 
    END AS EndTime
FROM @t t
CROSS JOIN master..spt_values c
WHERE 
    [type] = 'P' 
    AND
    DATEADD(d,0,DATEDIFF(d,0,FromDate)) + Number BETWEEN DATEADD(d,0,DATEDIFF(d,0,FromDate)) 
    AND 
    DATEADD(d,0,DATEDIFF(d,0,ToDate))
/*
Date                    wkday StartTime               EndTime
----------------------- ----- ----------------------- -----------------------
2009-03-06 00:00:00.000 Fri   2009-03-06 14:00:00.000 2009-03-06 17:00:00.000
2009-03-07 00:00:00.000 Sat   2009-03-07 08:00:00.000 2009-03-07 17:00:00.000
2009-03-08 00:00:00.000 Sun   2009-03-08 08:00:00.000 2009-03-08 17:00:00.000
2009-03-09 00:00:00.000 Mon   2009-03-09 08:00:00.000 2009-03-09 11:00:00.000
*/ 

USING A RECURSIVE CTE

Another way of generating the sequence of dates is by using a recursive CTE. All the three winners of this challenge submitted their queries using a recursive CTE. This is a classic example that demonstrates the possibility of solving many more problems using recursive CTEs, against the common belief that recursive CTEs are only for generating hierarchical stuff.

Let us write a version of our previous queries using a recursive CTE.

DECLARE @t Table(FromDate DATETIME, ToDate DATETIME)
INSERT INTO @t (FromDate, ToDate) SELECT '2009-03-06 14:00', '2009-03-09 11:00'

;with cte as(
    SELECT  
        DATENAME(weekday,Fromdate) AS Wkday,
        FromDate,
        ToDate
    FROM @T
    UNION ALL
    SELECT 
        DATENAME(weekday,DATEADD(d, 1, DATEADD(dd,0, DATEDIFF(dd,0,c.Fromdate)))),
        DATEADD(d, 1, DATEADD(dd,0, DATEDIFF(dd,0,c.Fromdate))) AS Fromdate,
        c.ToDate
    FROM    @t t
    INNER JOIN cte c ON 
        DATEADD(d, 1, DATEADD(dd,0, DATEDIFF(dd,0,c.FromDate))) <= c.ToDate
)
SELECT * FROM cte
/*
Wkday                          FromDate                ToDate
------------------------------ ----------------------- -----------------------
Friday                         2009-03-06 14:00:00.000 2009-03-09 11:00:00.000
Saturday                       2009-03-07 00:00:00.000 2009-03-09 11:00:00.000
Sunday                         2009-03-08 00:00:00.000 2009-03-09 11:00:00.000
Monday                         2009-03-09 00:00:00.000 2009-03-09 11:00:00.000
*/

The above example demonstrates how to generate a range of dates between two given date values using a RECURSIVE CTE. Once the date values within the ranges are generated, we could apply a similar logic we discussed previously, to calculate the actual work hours between the values.

COMPLETE CODE LISTING

Now, let us see the listing of the complete code.  Here is the entry submitted by Tejas.

DECLARE @t TABLE (ID INT IDENTITY, StartDate DATETIME, EndDate DATETIME) 
INSERT INTO @t (StartDate, EndDate) SELECT '3/2/2009 8:00AM', '3/2/2009 3:00PM' --07:00 
INSERT INTO @t (StartDate, EndDate) SELECT '3/1/2009 4:00PM', '3/3/2009 2:00 PM' --15:00 
INSERT INTO @t (StartDate, EndDate) SELECT '2/26/2009 7:00AM', '2/26/2009 10:00PM' --09:00 
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 AM', '1/27/2009 5:15 PM' --07:45 
INSERT INTO @t (StartDate, EndDate) SELECT '1/17/2009 1:45 PM', '1/19/2009 7:45 AM' --00:00 
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 PM', '1/28/2009 9:15 AM' --01:15 

;with cte as(
SELECT    ID,
        StartDate AS OrgStartDate,
        EndDate AS OrgEndDate,
        StartDate,
        EndDate
FROM @T
UNION ALL
SELECT    t.ID,
        c.OrgStartDate AS OrgStartDate,
        c.OrgEndDate AS OrgEndDate,
        DATEADD(d, 1, DATEADD(dd,0, DATEDIFF(dd,0,c.StartDate))) AS StartDate,
        c.EndDate
FROM    @t t
INNER JOIN cte c ON t.ID = c.ID
AND DATEADD(d, 1, DATEADD(dd,0, DATEDIFF(dd,0,c.StartDate))) <= c.EndDate
)
, cte2 AS(
SELECT    ID,
        OrgStartDate AS OrgStartDate,
        OrgEndDate AS OrgEndDate,
        DATENAME(dw,StartDate) AS DayName,
        CASE 
            WHEN StartDate <= DATEADD(hh,8,DATEADD(dd,0, DATEDIFF(dd,0,c.StartDate)))
                THEN DATEADD(hh,8,DATEADD(dd,0, DATEDIFF(dd,0,c.StartDate)))
                ELSE StartDate
        END AS StartDate,
        CASE 
            WHEN EndDate <= DATEADD(hh,17,DATEADD(dd,0, DATEDIFF(dd,0,c.StartDate)))
                THEN EndDate
                ELSE DATEADD(hh,17,DATEADD(dd,0, DATEDIFF(dd,0,c.StartDate)))
        END AS EndDate
from cte c
)
SELECT    ID,
        OrgStartDate AS StartDate,
        OrgEndDate AS EndDate,
        CAST(SUM(CASE 
                    WHEN StartDate > EndDate THEN 0 
                    ELSE DATEDIFF(mi,StartDate,EndDate) 
                END) / 60 AS INT) AS Hours,
        CAST(SUM(CASE 
                    WHEN StartDate > EndDate THEN 0 
                    ELSE DATEDIFF(mi,StartDate,EndDate) 
                END) % 60 AS INT) AS Minutes
        
from cte2
WHERE DAYNAME NOT IN('Saturday', 'SUNDAY')
GROUP BY ID, OrgStartDate, OrgEndDate

Given below is the entry submitted by antoine

-- antoine.gemis@gmail.com --
DECLARE @t TABLE (ID INT IDENTITY, StartDate DATETIME, EndDate DATETIME) 
SET DATEFORMAT MDY
SET DATEFIRST 1
INSERT INTO @t (StartDate, EndDate) SELECT '3/5/2009 18:00PM', '3/10/2009 7:00AM' --18:00 
INSERT INTO @t (StartDate, EndDate) SELECT '3/2/2009 8:00AM', '3/2/2009 3:00PM' --07:00 
INSERT INTO @t (StartDate, EndDate) SELECT '3/1/2009 4:00PM', '3/3/2009 2:00 PM' --15:00 
INSERT INTO @t (StartDate, EndDate) SELECT '2/26/2009 7:00AM', '2/26/2009 10:00PM' --09:00 
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 AM', '1/27/2009 5:15 PM' --07:45 
INSERT INTO @t (StartDate, EndDate) SELECT '1/17/2009 1:45 PM', '1/19/2009 7:45 AM' --00:00 
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 PM', '1/28/2009 9:15 AM' --01:15 

-- CTE pour fabriquer la sequence de jours concernés
;WITH CTE_EXTRACTWORKDAY (ID, StartDate, EndDate) AS
(
    SELECT 
        ID, StartDate, EndDate 
    FROM @t 
    group by ID, startdate, enddate
    UNION ALL
    SELECT ID, DATEADD(day, 1, StartDate), EndDate
    FROM CTE_EXTRACTWORKDAY
    WHERE StartDate < EndDate
),
-- On extrait les jours de travail potentiels
CTE_WORKDAY (ID, WorkDay) AS
(
    SELECT ID, DATEADD(d, 0, DATEDIFF(DAY, 0, startdate)) AS WorkDay  
    FROM CTE_EXTRACTWORKDAY
    WHERE StartDate < EndDate
    UNION 
    SELECT ID, DATEADD(d, 0, DATEDIFF(DAY, 0, enddate)) AS WorkDay
    FROM CTE_EXTRACTWORKDAY
    WHERE StartDate < EndDate
),
-- Calcul de la différenc en minute entre les bornes de début de fin de travail au bureau.
CTE_MORECLEAR (WorkDay, ID, StartDate, EndDate,  WorkedMinutes) AS
(
    SELECT  CTE_WORKDAY.WorkDay, 
        A.ID,
        A.StartDate,
        A.EndDate, 
        CASE DATEPART(WEEKDAY, WORKDAY) -- On n'ouvre pas le week-end. 
            WHEN 6 THEN 0
            WHEN 7 THEN 0
        ELSE    
            CASE WHEN 
                DATEDIFF(MINUTE, 
                    CASE WHEN StartDate < DATEADD(HOUR, 8, CTE_WORKDAY.WorkDay) THEN 
                        DATEADD(HOUR, 8, CTE_WORKDAY.WorkDay)
                    ELSE
                        StartDate
                    END,
                    CASE WHEN EndDate > DATEADD(HOUR, 17, CTE_WORKDAY.WorkDay) THEN 
                        DATEADD(HOUR, 17, CTE_WORKDAY.WorkDay)
                    ELSE
                        EndDate
                    END)
                < 0 THEN 0
            ELSE
                DATEDIFF(MINUTE, 
                    CASE WHEN StartDate < DATEADD(HOUR, 8, CTE_WORKDAY.WorkDay) THEN 
                        DATEADD(HOUR, 8, CTE_WORKDAY.WorkDay)
                    ELSE
                        StartDate
                    END,
                    CASE WHEN EndDate > DATEADD(HOUR, 17, CTE_WORKDAY.WorkDay) THEN 
                        DATEADD(HOUR, 17, CTE_WORKDAY.WorkDay)
                    ELSE
                        EndDate
                    END)
            END
        END AS WorkedMinutes
        FROM CTE_WORKDAY
    LEFT JOIN @t A ON A.ID = CTE_WORKDAY.ID
)
SELECT     StartDate, EndDate, 
        SUM(WorkedMinutes) / 60 as Hours, 
        SUM(WorkedMinutes) -(SUM(WorkedMinutes) / 60 * 60) AS Minutes  
FROM CTE_MORECLEAR 
GROUP BY ID, StartDate, EndDate    
ORDER BY ID 

Finally, here is the code of Rui

DECLARE @t TABLE (ID INT IDENTITY, StartDate DATETIME, EndDate DATETIME)
SET DATEFORMAT MDY

INSERT INTO @t (StartDate, EndDate) SELECT '3/2/2009 8:00AM', '3/2/2009 3:00PM' --07:00
INSERT INTO @t (StartDate, EndDate) SELECT '3/1/2009 4:00PM', '3/3/2009 2:00 PM' --15:00
INSERT INTO @t (StartDate, EndDate) SELECT '2/26/2009 7:00AM', '2/26/2009 10:00PM' --09:00
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 AM', '1/27/2009 5:15 PM' --07:45
INSERT INTO @t (StartDate, EndDate) SELECT '1/17/2009 1:45 PM', '1/19/2009 7:45 AM' --00:00
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 PM', '1/28/2009 9:15 AM' --01:15

-- Build the range of days between start and end with additional helper calcultations
;WITH BUILD_RANGES AS
(
    -- select the exact start date based on business hours
    SELECT    
        T.ID
        ,StartDate = 
                CASE 
                WHEN DATEPART(HOUR,T.StartDate) < 8 
                    THEN DATEADD(HOUR,8,DATEADD(DAY,0,DATEDIFF(DAY,0,T.StartDate))) 
                WHEN DATEPART(HOUR,T.StartDate) > 17 
                    THEN DATEADD(HOUR,17,DATEADD(DAY,0,DATEDIFF(DAY,0,T.StartDate))) 
                ELSE T.StartDate 
                END
        ,T.EndDate 
        ,BusinessDay = CASE 
                        WHEN DATEPART(DW,T.StartDate) IN (6,7) OR T.EndDate < T.StartDate 
                        THEN 0 ELSE 1 
                       END 
    FROM @t T
    UNION ALL
    -- Select the other dates til the end
    SELECT    A.ID
            ,StartDate = DATEADD(HOUR,8,DateAdd(day,1,DATEADD(DAY,0,DATEDIFF(DAY,0,A.StartDate))))
            ,A.EndDate
            ,BusinessDay = CASE 
                            WHEN DATEPART(DW,DateAdd(day,1,A.StartDate)) IN (6,7) THEN 0 
                            ELSE 1 
                           END 
    FROM BUILD_RANGES A
        INNER JOIN @t T ON A.ID = T.id
    WHERE A.StartDate < T.EndDate
)
-- do final computation
SELECT    ID,StartDate,EndDate,
        HOURS = SUM(DATEDIFF(MINUTE,ComputedStart,ComputedEnd))/60,
        MINUTES = SUM(DATEDIFF(MINUTE,ComputedStart,ComputedEnd))%60
FROM    (
    -- select final values with enddate based on business hours
    SELECT  O.Id
            ,ComputedStart = C.StartDate
            ,ComputedEnd = 
                CASE WHEN DATEADD(DAY,0,DATEDIFF(DAY,0,C.StartDate)) 
                        = DATEADD(DAY,0,DATEDIFF(DAY,0,O.EndDate))
                THEN 
                    CASE 
                    WHEN DATEPART(HOUR,C.EndDate) < 8 
                        THEN DATEADD(HOUR,8,DATEADD(DAY,0,DATEDIFF(DAY,0,C.EndDate))) 
                    WHEN DATEPART(HOUR,C.EndDate) > 16 
                        THEN DATEADD(HOUR,17,DATEADD(DAY,0,DATEDIFF(DAY,0,C.EndDate))) 
                    ELSE CASE WHEN O.StartDate > O.EndDate 
                        THEN C.StartDate ELSE C.EndDate END
                    END
                ELSE
                    DATEADD(HOUR,17,DATEADD(DAY,0,DATEDIFF(DAY,0,C.StartDate)))
                END
            ,BusinessDay
            ,O.StartDate,O.EndDate
    FROM    BUILD_RANGES C
        FULL JOIN @t O ON C.ID = O.ID
) FINAL_DATA
WHERE 1=1
    AND    (BusinessDay = 1 OR StartDate > EndDate)
    AND DATEADD(DAY,0,DATEDIFF(DAY,0,ComputedStart)) 
        <= DATEADD(DAY,0,DATEDIFF(DAY,0,EndDate))
GROUP BY ID,StartDate,EndDate
ORDER BY ID

I would like to thank all of you who participated as well as those who attempted to solve this challenge. I will post the next challenge soon.

Here is the sample data for TSQL Challenge 2

ID          StartDate               EndDate
----------- ----------------------- -----------------------
1           2009-03-02 08:00:00.000 2009-03-02 15:00:00.000
2           2009-03-01 16:00:00.000 2009-03-03 14:00:00.000
3           2009-02-26 07:00:00.000 2009-02-26 22:00:00.000
4           2009-01-27 09:15:00.000 2009-01-27 17:15:00.000
5           2009-01-17 13:45:00.000 2009-01-19 07:45:00.000
6           2009-01-27 21:15:00.000 2009-01-28 09:15:00.000

The challenge is to calculate the Business Hours between StartDate and EndDate. Let us define Business Hours as the time between 8 AM and 5 PM, Monday to Friday.

If StartDate is Friday 12 Noon and EndDate is Monday 10 AM, you should count only the duration between 12 Noon and 5 PM on friday and 8AM to 10 AM on monday.

The query should return the following result.

StartDate               EndDate                 Hours       Minutes 
----------------------- ----------------------- ----------- ----------- 
2009-03-02 08:00:00.000 2009-03-02 15:00:00.000 7           0 
2009-03-01 16:00:00.000 2009-03-03 14:00:00.000 15          0 
2009-02-26 07:00:00.000 2009-02-26 22:00:00.000 9           0 
2009-01-27 09:15:00.000 2009-01-27 17:15:00.000 7           45 
2009-01-17 13:45:00.000 2009-01-19 07:45:00.000 0           0 
2009-01-27 21:15:00.000 2009-01-28 09:15:00.000 1           15

Here is the script to generate the sample data.

SET DATEFORMAT MDY

DECLARE @t TABLE (ID INT IDENTITY, StartDate DATETIME, EndDate DATETIME) 
INSERT INTO @t (StartDate, EndDate) SELECT '3/2/2009 8:00AM', '3/2/2009 3:00PM' --07:00 
INSERT INTO @t (StartDate, EndDate) SELECT '3/1/2009 4:00PM', '3/3/2009 2:00 PM' --15:00 
INSERT INTO @t (StartDate, EndDate) SELECT '2/26/2009 7:00AM', '2/26/2009 10:00PM' --09:00 
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 AM', '1/27/2009 5:15 PM' --07:45 
INSERT INTO @t (StartDate, EndDate) SELECT '1/17/2009 1:45 PM', '1/19/2009 7:45 AM' --00:00 
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 PM', '1/28/2009 9:15 AM' --01:15 

SELECT * FROM @t

Note: Please write your query using the sample data provided. This will make it easier for me to test your code.

1. Please make your submissions before 20th March 2009
2. I will announce the winners on 23rd March
3. Send your queries by email and write "TSQL Challenge 2" in the subject
4. Send your entries to jacob at beyondrelational dot com.

Happy Querying!

Adam Machanic

Adam Machanic is a Boston-based independent database consultant, writer, and speaker. He has been involved in dozens of SQL Server implementations for both high-availability OLTP and large-scale data warehouse applications, and has optimized data access layer performance for several data-intensive applications. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "Expert SQL Server 2005 Development" (Apress, 2007) and "Inside SQL Server 2005: Query Tuning and Optimization" (Microsoft Press, 2007). Adam regularly speaks at user groups, community events, and conferences on a variety of SQL Server and .NET-related topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, Microsoft Certified IT Professional (MCITP), and a member of the INETA North American Speakers

Ashish Gilhotra

Ashish Gilhotra a Web Developer from Jaipur, Rajasthan. He is an MCAD in .net with Sql Server.

Leonid Koyfman

Leonid Koyfman is a Senior Developer with Razorfish ( San Francisco). He is in software development over 10 years. His focus is data visualization and delivering reporting solutions from various data sources, but spending most of the time with SQL Server.

I would like to congratulate the winners. In the coming days, I will publish more details about these people so that all of you get to know more about them.

Solving the problem

Let us now have a look at how this challenge can be solved. The solution lies in the way we join @b and @c. This is the tricky part that we need to solve.

A join between @b and @c will produce duplicate rows. The "code" column in @b contains 2 records having value "1". Similarly the "code" column in @c contains 3 records having value "1". If you try to join them with a FULL JOIN you will receive 12 records. But our query should produce only 3 rows. Similarly, our query should produce only 2 records for rows having "2" in the column "code". The total number of rows we are looking for is 5 where as a FULL JOIN between @b and @c currently produces 16 records. See this example:

SELECT * FROM @b b
FULL JOIN @c c ON b.code = b.code
/*
code        bname      code        cname
----------- ---------- ----------- ----------
1           aaa        1           xxx
1           bbb        1           xxx
2           ccc        1           xxx
2           ddd        1           xxx
1           aaa        1           yyy
1           bbb        1           yyy
2           ccc        1           yyy
2           ddd        1           yyy
1           aaa        1           zzz
1           bbb        1           zzz
2           ccc        1           zzz
2           ddd        1           zzz
1           aaa        2           www
1           bbb        2           www
2           ccc        2           www
2           ddd        2           www
*/ 

How do we remove those duplicate rows? Well, the join that we apply should do the following. It should take the first row in @b with value "1" and join it with the first row in @c having "1". Then it should take the second row in @b having "1" and join it with the second row in @c having value "1". It should then take the third row in @c having value "1". This does not have a matching row in @b, so it will return the information from @c and will return NULL from @b. If we can apply such a join, we can receive back exactly 3 records.

To join the rows in the manner described above, we need to generate a sequence number for each row. The sequence number should reset for each distinct value in the "code" column. So the trick here is to generate this sequence number.

SQL Server 2005 introduced ROW_NUMBER() that can be used to generate a sequence number. However it is not available in SQL Server 2000. So, how do we generate a sequence number (and reset it for each group) in SQL Server 2000?

In SQL Server 2000, this can be achieved by doing a self join and counting the number of records smaller than equal to the current row. This trick is demonstrated in the below example.

SELECT
    b1.bname,
    b1.code,
    COUNT(*) AS row
FROM @b b1
INNER JOIN @b b2 ON 
    b1.code = b2.code
    AND b2.bname <= b1.bname
GROUP BY 
    b1.bname,
    b1.code
/*
bname      code        row
---------- ----------- -----------
aaa        1           1
bbb        1           2
ccc        2           1
ddd        2           2
*/  

This trick is going to help us solve the problem. Using this trick, we can join @b and @c on Code + row and it will make each row unique and will eliminate the duplicate values from the results. A join of @b and @c using this method will produce the following result.

SELECT * FROM (
    SELECT
        b1.bname,
        b1.code,
        COUNT(*) AS row
    FROM @b b1
    INNER JOIN @b b2 ON 
        b1.code = b2.code
        AND b2.bname <= b1.bname
    GROUP BY 
        b1.bname,
        b1.code
) b FULL JOIN (    
    SELECT
        c1.cname,
        c1.code,
        COUNT(*) AS row
    FROM @c c1
    INNER JOIN @c c2 ON 
        c1.code = c2.code
        AND c2.cname <= c1.cname
    GROUP BY 
        c1.cname,
        c1.code
) c ON b.Code = c.Code AND b.row = c.row
/*
bname      code        row         cname      code        row
---------- ----------- ----------- ---------- ----------- -----------
ccc        2           1           www        2           1
aaa        1           1           xxx        1           1
bbb        1           2           yyy        1           2
NULL       NULL        NULL        zzz        1           3
ddd        2           2           NULL       NULL        NULL
*/   

This leads us to the final solution. The final result can be achieved by simply joining this result with @a. Here is the complete code listing.

DECLARE @a TABLE (code INT, aname VARCHAR(10))
INSERT INTO @a(code, aname) SELECT 1,'Cat'
INSERT INTO @a(code, aname) SELECT 2,'Dog'
INSERT INTO @a(code, aname) SELECT 3,'Bird'

DECLARE @b TABLE (code INT, bname VARCHAR(10))
INSERT INTO @b(code, bname) SELECT 1,'aaa'
INSERT INTO @b(code, bname) SELECT 1,'bbb'
INSERT INTO @b(code, bname) SELECT 2,'ccc'
INSERT INTO @b(code, bname) SELECT 2,'ddd'

DECLARE @c TABLE (code INT, cname VARCHAR(10))
INSERT INTO @c(code, cname) SELECT 1,'xxx'
INSERT INTO @c(code, cname) SELECT 1,'yyy'
INSERT INTO @c(code, cname) SELECT 1,'zzz'
INSERT INTO @c(code, cname) SELECT 2,'www'

SELECT
    a.*,
    b.bname,
    c.cname
FROM @a a
LEFT JOIN (
    (
        SELECT
            b1.bname,
            b1.code,
            COUNT(*) AS row
        FROM @b b1
        JOIN @b b2 ON 
            b1.code = b2.code
            AND b2.bname <= b1.bname
        GROUP BY 
            b1.bname,
            b1.code
    ) b
    FULL JOIN
    (
        SELECT
            c1.cname,
            c1.code,
            COUNT(*) AS row
        FROM @c c1
        JOIN @c c2 ON 
            c1.code = c2.code
            AND c2.cname <= c1.cname
        GROUP BY 
            c1.cname,
            c1.code
    ) c ON 
        b.code = c.code
        AND b.row = c.row
) ON a.code IN (b.code, c.code)               
/*
code        aname      bname      cname
----------- ---------- ---------- ----------
1           Cat        aaa        xxx
1           Cat        bbb        yyy
1           Cat        NULL       zzz
2           Dog        ccc        www
2           Dog        ddd        NULL
3           Bird       NULL       NULL
*/            

I would like to thank everyone who participated in the challenge. Are you ready for the next challenge?