Query to split difference (number of nights) between 2 dates into respective "Month" column

Question

Query to split difference (number of nights) between 2 dates into respective "Month" column

hoolash

Ten Centuries

Points: 1258
More actions
September 23, 2014 at 1:32 pm

#311758

Hi all,
I'm using SQL Server 2012 and I need to run a query against my database that will output the difference between 2 dates (namely, DateOfArrival and DateOfDeparture) into the correct month column in the output.
Both DateOfArrival and DateOfDeparture are in the same table (let's say GuestStay). I will also need some other fields from this table and do some joins on some other tables but I will simplify things so as to solve my main problem here. Let's say the fields needed from the GuestStay table looks like below:
I need my query to output in the following format:
Can anyone please help how to write this query?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I can't see your images because dropbox is blocked at work. That's why we usually ask for DDL, sample data and expected results as a runnable script. If you can post it, that would be great.

Meanwhile, what's wrong with using DATEDIFF()?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182918 More actions · Answer 2

Try this for a size

😎

USE tempdb;

GO

SET NOCOUNT ON;

;WITH SAMPLE_DATA ([ID number],DateOfArrival,DateOfDeparture)

AS (SELECT * FROM (VALUES

(2031,'2014-03-20','2014-03-26')

,(1056,'2014-04-29','2014-05-03')

,(3245,'2014-06-19','2014-06-20')

) AS X([ID number],DateOfArrival,DateOfDeparture)

)

,MONTH_NUMBERS AS (SELECT MNO FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) AS X(MNO))

,T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),

(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

,COUNTING_DAYS AS

(

SELECT

SD.[ID number]

,SD.DateOfArrival

,SD.DateOfDeparture

,MONTH(DATEADD(DAY,NM.N,SD.DateOfArrival)) AS MONTH_NO

,COUNT(SD.[ID number]) OVER

(

PARTITION BY SD.[ID number]

,MONTH(DATEADD(DAY,NM.N,SD.DateOfArrival))

) AS COUNT_IN_MONTH

FROM SAMPLE_DATA SD

OUTER APPLY (SELECT TOP(DATEDIFF(DAY,SD.DateOfArrival,SD.DateOfDeparture) ) ROW_NUMBER()

OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7) AS NM

)

SELECT

CD.[ID number]

,CD.DateOfArrival

,CD.DateOfDeparture

,MAX(CASE WHEN CD.MONTH_NO = 1 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS JAN14

,MAX(CASE WHEN CD.MONTH_NO = 2 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS FEB14

,MAX(CASE WHEN CD.MONTH_NO = 3 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS MAR14

,MAX(CASE WHEN CD.MONTH_NO = 4 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS APR14

,MAX(CASE WHEN CD.MONTH_NO = 5 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS MAY14

,MAX(CASE WHEN CD.MONTH_NO = 6 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS JUN14

,MAX(CASE WHEN CD.MONTH_NO = 7 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS JUL14

,MAX(CASE WHEN CD.MONTH_NO = 8 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS AUG14

,MAX(CASE WHEN CD.MONTH_NO = 9 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS SEP14

,MAX(CASE WHEN CD.MONTH_NO = 10 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS OCT14

,MAX(CASE WHEN CD.MONTH_NO = 11 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS NOV14

,MAX(CASE WHEN CD.MONTH_NO = 12 THEN CD.COUNT_IN_MONTH ELSE 0 END) AS DEC14

FROM COUNTING_DAYS CD

GROUP BY CD.[ID number]

,CD.DateOfArrival

,CD.DateOfDeparture;

Results

ID number DateOfArrival DateOfDeparture JAN14 FEB14 MAR14 APR14 MAY14 JUN14 JUL14 AUG14 SEP14 OCT14 NOV14 DEC14

----------- ------------- --------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------

1056 2014-04-29 2014-05-03 0 0 0 1 3 0 0 0 0 0 0 0

2031 2014-03-20 2014-03-26 0 0 6 0 0 0 0 0 0 0 0 0

3245 2014-06-19 2014-06-20 0 0 0 0 0 1 0 0 0 0 0 0

hoolash Ten Centuries Points: 1258 More actions · Answer 3

Thanks a lot, Eirikur Eiriksson! Wow! I wished I could write SQL queries that good!

I'm a bit confused with the first part of the query (blame that on my lack of knowledge of SQL queries). If I understand well, you have created only 2 tables to tackle the problem: Sample_data and Counting_days? Sample_data is the equivalent of my GuestStay table?

Can you please replace the terms used in the query with my table name GuestStay. I'll go from there! Thanks again!

Eirikur Eiriksson SSC Guru Points: 182918 More actions · Answer 4

Here is the same code with the changed name and some explanatory comments. I noticed I had left the month sequence in the other code without using it, no need for that so I removed it.

😎

USE tempdb;

GO

SET NOCOUNT ON;

/* The sample data for Guest Stay, you can replace this

with your table, the likely equivalent would be for the

year 2014

;WITH GuestStay AS

(

SELECT

[ID number]

,DateOfArrival

,DateOfDeparture

FROM [myTableName]

WHERE YEAR(DateOfArrival) = 2014

)

*/

;WITH GuestStay ([ID number],DateOfArrival,DateOfDeparture)

AS (SELECT * FROM (VALUES

(2031,'2014-03-20','2014-03-26')

,(1056,'2014-04-29','2014-05-03')

,(3245,'2014-06-19','2014-06-20')

) AS X([ID number],DateOfArrival,DateOfDeparture)

)

/* A tally table seed for the numbers table used to count

all days in the stay, that is to unpack the period. The

NULL values are meant to underline that the actual values

are not relevant, only that there are 10 empty/blank/meaningless

rows in this set.

*/

,T(N) AS (SELECT N FROM ( VALUES (NULL),(NULL),(NULL),

(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

/* The unpacking of the period (event interval, events being

start and end of stay

*/

,COUNTING_DAYS AS

(

SELECT

SD.[ID number]

,SD.DateOfArrival

,SD.DateOfDeparture

,MONTH(DATEADD(DAY,NM.N,SD.DateOfArrival)) AS MONTH_NO

/* Window function Count, think of the partition as

for every row in the set that belongs to the partition

combination, count the entries, or in other terms, devide

the set by those columns and count the entries

*/

,COUNT(SD.[ID number]) OVER

(

PARTITION BY SD.[ID number]

,MONTH(DATEADD(DAY,NM.N,SD.DateOfArrival))

) AS COUNT_IN_MONTH

FROM GuestStay SD

/* This is the driver for the unpacking, generates a sequence

of numbers matching the number of days between the two dates

for each record/row

*/

OUTER APPLY (SELECT TOP(DATEDIFF(DAY,SD.DateOfArrival,SD.DateOfDeparture) ) ROW_NUMBER()

OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7) AS NM

)

/* Crosstab to "columize" the output. The MAX simply puts any value greater than

zero in the column if it exists

*/

SELECT

CD.[ID number]

,CD.DateOfArrival

,CD.DateOfDeparture

/* Each line represents one column, when the MONTH_NO matches it returns

the count of nights (COUNT_IN_MONTH) for that month, otherwise zero