Geek Answers Handbook

SQL Server Query to Separate Nights Spent in the Corresponding Month Column

I am using SQL Server 2012, and I need to write a query that will divide the difference between the arrival date and departure date (i.e. the number of nights spent) and publish the result in the corresponding Monthcolumn.

I already know how to write a request that will receive my arrival date and departure date, but for me this is the hardest part. My final results should look something like this:

ID    Name       Date of Arrival    Date of Departure   Jan  Feb  Mar  Apr
203   Mr Smith   2014.02.24         2014.03.02                5    1    
455   Mr Jones   2014.04.10         2014.04.17                          7

ID, Name, Date of ArrivalAnd Date of Departureare drawn from a particular table. How to add a split part to my existing query so that the result looks like above?

After some research, I noticed that the table Calendarmay be useful, but I don't know how to implement this in my query.

Thanks for your details.

+4
source share
2 answers

I think a calendar table will be the way to go. I think the query looks something like this, I named your VisitDate table and assumed that there is a Date field and a Month field in the calendar table.

select ID, Name, [DATEADD of Arrival], [DATEADD of Departure], 
    sum(case when c.month = 1 then 1 else 0 end) as Jan, 
    sum(case when c.month = 2 then 1 else 0 end) as Feb, 
    sum(case when c.month = 3 then 1 else 0 end) as Mar, 
    sum(case when c.month = 4 then 1 else 0 end) as Apr 
from 
    VisitDate
    join Calendar c on c.Date >= [DATEADD of Arrival] and c.Date <= [DATEADD of Departure]
+2
source

This can only be done with a set of case statements: Let's do some test data:

DECLARE @Booking TABLE
(
    GuestID INT,
    Name VARCHAR(50),
    ArrivalDate DATETIME,
    DepartureDate DATETIME
)

INSERT INTO @Booking
( GuestID, Name, ArrivalDate, DepartureDate )
VALUES
( 203, 'Mr Smith', '02-24-2014', '03-02-2014' ),
( 455, 'Mr Jones', '04-10-2014', '04-17-2014' ),
( 531, 'Mrs James', '07-10-2014', '09-17-2014' );

Switch to this year's CTE calendar:

DECLARE @CheckYear INT = 2014

;WITH MonthInfo AS
(
    SELECT
    DATEADD(DAY, 0, DATEADD(MONTH, 0, DATEADD(YEAR, @CheckYear - 1900, 0))) JanStart,
    DATEADD(DAY, 0, DATEADD(MONTH, 1, DATEADD(YEAR, @CheckYear - 1900, 0))) FebStart,
    DATEADD(DAY, 0, DATEADD(MONTH, 2, DATEADD(YEAR, @CheckYear - 1900, 0))) MarStart,
    DATEADD(DAY, 0, DATEADD(MONTH, 3, DATEADD(YEAR, @CheckYear - 1900, 0))) AprStart,
    DATEADD(DAY, 0, DATEADD(MONTH, 4, DATEADD(YEAR, @CheckYear - 1900, 0))) MayStart,
    DATEADD(DAY, 0, DATEADD(MONTH, 5, DATEADD(YEAR, @CheckYear - 1900, 0))) JunStart,
    DATEADD(DAY, 0, DATEADD(MONTH, 6, DATEADD(YEAR, @CheckYear - 1900, 0))) JulStart,
    DATEADD(DAY, 0, DATEADD(MONTH, 7, DATEADD(YEAR, @CheckYear - 1900, 0))) AugStart,
    DATEADD(DAY, 0, DATEADD(MONTH, 8, DATEADD(YEAR, @CheckYear - 1900, 0))) SepStart,
    DATEADD(DAY, 0, DATEADD(MONTH, 9, DATEADD(YEAR, @CheckYear - 1900, 0))) OctStart,
    DATEADD(DAY, 0, DATEADD(MONTH, 10, DATEADD(YEAR, @CheckYear - 1900, 0))) NovStart,
    DATEADD(DAY, 0, DATEADD(MONTH, 11, DATEADD(YEAR, @CheckYear - 1900, 0))) DecStart,
    DATEADD(DAY, 0, DATEADD(MONTH, 12, DATEADD(YEAR, @CheckYear - 1899, 0))) EOY
)

Now we run our request and check it against the calendar and pull out all the overlapping dates and get the number of days

SELECT b.*,
    CASE WHEN b.ArrivalDate > m.JanStart AND b.ArrivalDate < m.FebStart THEN
        (CASE WHEN b.DepartureDate < m.FebStart THEN DATEDIFF(DAY, b.ArrivalDate, b.DepartureDate) 
            ELSE DATEDIFF(DAY, b.ArrivalDate, m.FebStart)
        END)
        WHEN b.ArrivalDate < m.JanStart AND b.DepartureDate > m.JanStart AND b.DepartureDate < m.FebStart THEN
            DATEDIFF(DAY, m.JanStart, b.DepartureDate) + 1
        WHEN b.ArrivalDate < m.JanStart AND b.DepartureDate > m.JanStart AND b.DepartureDate > m.FebStart THEN
            DATEDIFF(DAY, m.JanStart, m.FebStart)

    ELSE 0
    END JanDays,
    CASE WHEN b.ArrivalDate > m.FebStart AND b.ArrivalDate < m.MarStart THEN
        (CASE WHEN b.DepartureDate < m.MarStart THEN DATEDIFF(DAY, b.ArrivalDate, b.DepartureDate) 
            ELSE DATEDIFF(DAY, b.ArrivalDate, m.MarStart)
        END)
        WHEN b.ArrivalDate < m.FebStart AND b.DepartureDate > m.FebStart AND b.DepartureDate < m.MarStart THEN
            DATEDIFF(DAY, m.FebStart, b.DepartureDate) + 1
        WHEN b.ArrivalDate < m.FebStart AND b.DepartureDate > m.FebStart AND b.DepartureDate > m.MarStart THEN
            DATEDIFF(DAY, m.FebStart, m.MarStart)

    ELSE 0
    END FebDays,
    CASE WHEN b.ArrivalDate > m.MarStart AND b.ArrivalDate < m.AprStart THEN
        (CASE WHEN b.DepartureDate < m.AprStart THEN DATEDIFF(DAY, b.ArrivalDate, b.DepartureDate) 
            ELSE DATEDIFF(DAY, b.ArrivalDate, m.AprStart)
        END)
        WHEN b.ArrivalDate < m.MarStart AND b.DepartureDate > m.MarStart AND b.DepartureDate < m.AprStart THEN
            DATEDIFF(DAY, m.MarStart, b.DepartureDate) + 1
        WHEN b.ArrivalDate < m.MarStart AND b.DepartureDate > m.MarStart AND b.DepartureDate > m.AprStart THEN
            DATEDIFF(DAY, m.MarStart, m.AprStart)
    ELSE 0
    END MarDays,
    CASE WHEN b.ArrivalDate > m.AprStart AND b.ArrivalDate < m.MayStart THEN
        (CASE WHEN b.DepartureDate < m.MayStart THEN DATEDIFF(DAY, b.ArrivalDate, b.DepartureDate) 
            ELSE DATEDIFF(DAY, b.ArrivalDate, m.MarStart)
        END)
        WHEN b.ArrivalDate < m.AprStart AND b.DepartureDate > m.AprStart AND b.DepartureDate < m.MayStart THEN
            DATEDIFF(DAY, m.AprStart, b.DepartureDate) + 1
        WHEN b.ArrivalDate < m.AprStart AND b.DepartureDate > m.AprStart AND b.DepartureDate > m.MayStart THEN
            DATEDIFF(DAY, m.AprStart, m.MayStart)

    ELSE 0
    END AprDays,
    CASE WHEN b.ArrivalDate > m.MayStart AND b.ArrivalDate < m.JunStart THEN
        (CASE WHEN b.DepartureDate < m.JunStart THEN DATEDIFF(DAY, b.ArrivalDate, b.DepartureDate) 
            ELSE DATEDIFF(DAY, b.ArrivalDate, m.JunStart)
        END)
        WHEN b.ArrivalDate < m.MayStart AND b.DepartureDate > m.MayStart AND b.DepartureDate < m.JunStart THEN
            DATEDIFF(DAY, m.MayStart, b.DepartureDate) + 1
        WHEN b.ArrivalDate < m.MayStart AND b.DepartureDate > m.MayStart AND b.DepartureDate > m.JunStart THEN
            DATEDIFF(DAY, m.MayStart, m.JunStart)
    ELSE 0
    END MayDays,
    CASE WHEN b.ArrivalDate > m.JunStart AND b.ArrivalDate < m.JulStart THEN
        (CASE WHEN b.DepartureDate < m.JulStart THEN DATEDIFF(DAY, b.ArrivalDate, b.DepartureDate) 
            ELSE DATEDIFF(DAY, b.ArrivalDate, m.JulStart)
        END)
        WHEN b.ArrivalDate < m.JunStart AND b.DepartureDate > m.JunStart AND b.DepartureDate < m.JulStart THEN
            DATEDIFF(DAY, m.JunStart, b.DepartureDate) + 1
        WHEN b.ArrivalDate < m.JunStart AND b.DepartureDate > m.JunStart AND b.DepartureDate > m.JulStart THEN
            DATEDIFF(DAY, m.JunStart, m.JulStart)
    ELSE 0
    END JunDays,
    CASE WHEN b.ArrivalDate > m.JulStart AND b.ArrivalDate < m.AugStart THEN
        (CASE WHEN b.DepartureDate < m.AugStart THEN DATEDIFF(DAY, b.ArrivalDate, b.DepartureDate) 
            ELSE DATEDIFF(DAY, b.ArrivalDate, m.AugStart)
        END)
        WHEN b.ArrivalDate < m.JulStart AND b.DepartureDate > m.JulStart AND b.DepartureDate < m.AugStart THEN
            DATEDIFF(DAY, m.JulStart, b.DepartureDate) + 1
        WHEN b.ArrivalDate < m.JulStart AND b.DepartureDate > m.JulStart AND b.DepartureDate > m.AugStart THEN
            DATEDIFF(DAY, m.JulStart, m.AugStart)
    ELSE 0
    END JulDays,
    CASE WHEN b.ArrivalDate > m.AugStart AND b.ArrivalDate < m.SepStart THEN
        (CASE WHEN b.DepartureDate < m.SepStart THEN DATEDIFF(DAY, b.ArrivalDate, b.DepartureDate) 
            ELSE DATEDIFF(DAY, b.ArrivalDate, m.SepStart)
        END)
        WHEN b.ArrivalDate < m.AugStart AND b.DepartureDate > m.AugStart AND b.DepartureDate < m.SepStart THEN
            DATEDIFF(DAY, m.AugStart, b.DepartureDate) + 1
        WHEN b.ArrivalDate < m.AugStart AND b.DepartureDate > m.AugStart AND b.DepartureDate > m.SepStart THEN
            DATEDIFF(DAY, m.AugStart, m.SepStart)
    ELSE 0
    END AugDays,
    CASE WHEN b.ArrivalDate > m.SepStart AND b.ArrivalDate < m.OctStart THEN
        (CASE WHEN b.DepartureDate < m.OctStart THEN DATEDIFF(DAY, b.ArrivalDate, b.DepartureDate) 
            ELSE DATEDIFF(DAY, b.ArrivalDate, m.OctStart)
        END)
        WHEN b.ArrivalDate < m.SepStart AND b.DepartureDate > m.SepStart AND b.DepartureDate < m.OctStart THEN
            DATEDIFF(DAY, m.SepStart, b.DepartureDate) + 1
        WHEN b.ArrivalDate < m.SepStart AND b.DepartureDate > m.SepStart AND b.DepartureDate > m.OctStart THEN
            DATEDIFF(DAY, m.SepStart, m.OctStart)
    ELSE 0
    END SepDays,
    CASE WHEN b.ArrivalDate > m.OctStart AND b.ArrivalDate < m.NovStart THEN
        (CASE WHEN b.DepartureDate < m.NovStart THEN DATEDIFF(DAY, b.ArrivalDate, b.DepartureDate) 
            ELSE DATEDIFF(DAY, b.ArrivalDate, m.NovStart)
        END)
        WHEN b.ArrivalDate < m.OctStart AND b.DepartureDate > m.OctStart AND b.DepartureDate < m.NovStart THEN
            DATEDIFF(DAY, m.OctStart, b.DepartureDate) + 1
        WHEN b.ArrivalDate < m.OctStart AND b.DepartureDate > m.OctStart AND b.DepartureDate > m.NovStart THEN
            DATEDIFF(DAY, m.OctStart, m.NovStart)
    ELSE 0
    END OctDays,
    CASE WHEN b.ArrivalDate > m.NovStart AND b.ArrivalDate < m.DecStart THEN
        (CASE WHEN b.DepartureDate < m.DecStart THEN DATEDIFF(DAY, b.ArrivalDate, b.DepartureDate) 
            ELSE DATEDIFF(DAY, b.ArrivalDate, m.DecStart)
        END)
        WHEN b.ArrivalDate < m.NovStart AND b.DepartureDate > m.NovStart AND b.DepartureDate < m.DecStart THEN
            DATEDIFF(DAY, m.NovStart, b.DepartureDate) + 1
        WHEN b.ArrivalDate < m.NovStart AND b.DepartureDate > m.NovStart AND b.DepartureDate > m.DecStart THEN
            DATEDIFF(DAY, m.NovStart, m.DecStart)
    ELSE 0
    END NovDays,
    CASE WHEN b.ArrivalDate > m.DecStart AND b.ArrivalDate < m.EOY THEN
        (CASE WHEN b.DepartureDate < m.EOY THEN DATEDIFF(DAY, b.ArrivalDate, b.DepartureDate) 
            ELSE DATEDIFF(DAY, b.ArrivalDate, m.EOY)
        END)
        WHEN b.ArrivalDate < m.DecStart AND b.DepartureDate > m.DecStart AND b.DepartureDate < m.EOY THEN
            DATEDIFF(DAY, m.DecStart, b.DepartureDate) + 1
        WHEN b.ArrivalDate < m.DecStart AND b.DepartureDate > m.DecStart AND b.DepartureDate > m.EOY THEN
            DATEDIFF(DAY, m.DecStart, m.EOY)
    ELSE 0
    END DecDays
FROM @Booking b
INNER JOIN MonthInfo m
    ON b.ArrivalDate >= m.JanStart AND b.ArrivalDate < m.EOY

Here is the result:

GuestID Name    ArrivalDate DepartureDate   JanDays FebDays MarDays AprDays MayDays JunDays JulDays AugDays SepDays OctDays NovDays DecDays
203 Mr Smith    2014-02-24 00:00:00.000 2014-03-02 00:00:00.000 0   5   2   0   0   0   0   0   0   0   0   0
455 Mr Jones    2014-04-10 00:00:00.000 2014-04-17 00:00:00.000 0   0   0   7   0   0   0   0   0   0   0   0
531 Mrs James   2014-07-10 00:00:00.000 2014-09-17 00:00:00.000 0   0   0   0   0   0   22  31  17  0   0   0

, , , , , , . case , , , , .

, :

DECLARE @StartDate DATETIME
SELECT @StartDate = MIN(ArrivalDate) - 1 FROM @Booking

DECLARE @number_of_numbers INT = 100000

;WITH
a AS (SELECT 1 AS i UNION ALL SELECT 1),
b AS (SELECT 1 AS i FROM a AS x, a AS y),
c AS (SELECT 1 AS i FROM b AS x, b AS y),
d AS (SELECT 1 AS i FROM c AS x, c AS y),
e AS (SELECT 1 AS i FROM d AS x, d AS y),
f AS (SELECT 1 AS i FROM e AS x, e AS y),
numbers AS 
(
    SELECT TOP(@number_of_numbers)
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS number
    FROM f
), 

CalTable AS
(
    SELECT DATEADD(DAY, n.number, @StartDate) CheckDate
    FROM numbers n
)

:

SELECT b.GuestID,
    b.Name,
    SUM(CASE WHEN MONTH(c.CheckDate) = 1 THEN 1 ELSE 0 END) JanDays,
    SUM(CASE WHEN MONTH(c.CheckDate) = 2 THEN 1 ELSE 0 END) FebDays,
    SUM(CASE WHEN MONTH(c.CheckDate) = 3 THEN 1 ELSE 0 END) MarDays,
    SUM(CASE WHEN MONTH(c.CheckDate) = 4 THEN 1 ELSE 0 END) AprDays,
    SUM(CASE WHEN MONTH(c.CheckDate) = 5 THEN 1 ELSE 0 END) MayDays,
    SUM(CASE WHEN MONTH(c.CheckDate) = 6 THEN 1 ELSE 0 END) JunDays,
    SUM(CASE WHEN MONTH(c.CheckDate) = 7 THEN 1 ELSE 0 END) JulDays,
    SUM(CASE WHEN MONTH(c.CheckDate) = 8 THEN 1 ELSE 0 END) AugDays,
    SUM(CASE WHEN MONTH(c.CheckDate) = 9 THEN 1 ELSE 0 END) SepDays,
    SUM(CASE WHEN MONTH(c.CheckDate) = 10 THEN 1 ELSE 0 END) OctDays,
    SUM(CASE WHEN MONTH(c.CheckDate) = 11 THEN 1 ELSE 0 END) NovDays,
    SUM(CASE WHEN MONTH(c.CheckDate) = 12 THEN 1 ELSE 0 END) DecDays
FROM CalTable c
INNER JOIN @Booking b
    ON c.CheckDate BETWEEN b.ArrivalDate AND b.DepartureDate
GROUP BY b.GuestID, b.Name

:

GuestID Name    JanDays FebDays MarDays AprDays MayDays JunDays JulDays AugDays SepDays OctDays NovDays DecDays
455 Mr Jones    0   0   0   8   0   0   0   0   0   0   0   0
203 Mr Smith    0   5   2   0   0   0   0   0   0   0   0   0
531 Mrs James   0   0   0   0   0   0   22  31  17  0   0   0
0

More articles:


All Articles