I’m working on a occupancy stats query with average length of stay. Everything was going great until I had a booking that spanned multiple months, then the wheels fell off. So now I’m trying to understand how I can split any entry like this into individual months with day count for each month.
I think I may have found something here http://social.technet.microsoft.com/wiki/contents/articles/23798.split-date-range-into-months.aspx but MSSQL is completely alien to me and possibly quite advanced.
Anyone out there have experience in this sort of thing?
I’m sure there is a more elegant way to approach this, but I would basically make an outer join for each month in the period.
SELECT t.UserID
, date1
, date2
, M1.DayCount
, M2.DayCount
, M3.DayCount
FROM datetable t
LEFT OUTER JOIN (SELECT userID
, datediff(CASE WHEN date2 > '2016-03-31' THEN '2016-03-31' ELSE date2 END
, CASE WHEN date1 < '2016-03-15' THEN '2016-03-15' ELSE date1 END) AS DayCount
FROM datetable
WHERE (date1 BETWEEN CAST('2016-03-15' AS DATE) AND CAST('2016-03-31' AS DATE)) OR
(date2 BETWEEN CAST('2016-03-15' AS DATE) AND CAST('2016-03-31' AS DATE)) OR
(date1 <= CAST('2016-03-15' AS DATE) AND date2 >= CAST('2016-03-31' AS DATE))) M1 ON M1.UserID = t.UserID
LEFT OUTER JOIN (SELECT userID
, datediff(CASE WHEN date2 > '2016-04-30' THEN '2016-04-30' ELSE date2 END
, CASE WHEN date1 < '2016-04-01' THEN '2016-04-01' ELSE date1 END) AS DayCount
FROM datetable
WHERE (date1 BETWEEN CAST('2016-04-01' AS DATE) AND CAST('2016-04-30' AS DATE)) OR
(date2 BETWEEN CAST('2016-04-01' AS DATE) AND CAST('2016-04-30' AS DATE)) OR
(date1 <= CAST('2016-04-01' AS DATE) AND date2 >= CAST('2016-04-30' AS DATE))) M2 ON M2.UserID = t.UserID
LEFT OUTER JOIN (SELECT userID
, datediff(CASE WHEN date2 > '2016-05-31' THEN '2016-05-31' ELSE date2 END
, CASE WHEN date1 < '2016-05-01' THEN '2016-05-01' ELSE date1 END) AS DayCount
FROM datetable
WHERE (date1 BETWEEN CAST('2016-05-01' AS DATE) AND CAST('2016-05-30' AS DATE)) OR
(date2 BETWEEN CAST('2016-05-01' AS DATE) AND CAST('2016-05-30' AS DATE)) OR
(date1 <= CAST('2016-05-01' AS DATE) AND date2 >= CAST('2016-05-31' AS DATE))) M3 ON M3.UserID = t.UserID
WHERE (date1 BETWEEN CAST('2016-03-15' AS DATE) AND CAST('2016-05-31' AS DATE)) OR
(date2 BETWEEN CAST('2016-03-15' AS DATE) AND CAST('2016-05-31' AS DATE)) OR
(date1 <= CAST('2016-03-15' AS DATE) AND date2 >= CAST('2016-05-31' AS DATE));
Hey @DaveMaxwell , it’ll take me a while to get my head round this but thanks. I appreciate your time and thoughts on this… I’ve been working away on it too but you seem to be very quick. I was just trying
INNER JOIN datehelper d ON d.dt BETWEEN b.startDate and b.endDate
@DaveMaxwell Unfortunately, after much deliberation, I don’t think your solution would work for me. But thanks to your reference to date table I found out about calendar tables (
), something I knew before. I’ve made progress on my original query using this method but as always, with progress comes more questions.
The query I have thus far is:
SELECT YEAR(d.dt) , MONTH(d.dt) , COUNT(*) AS bookingDaysInMonth FROM booking b INNER JOIN datehelper d ON d.dt BETWEEN b.bookingS AND b.bookingE WHERE d.dt < CURDATE() GROUP BY EXTRACT(YEAR_MONTH FROM d.dt) ORDER BY EXTRACT(YEAR_MONTH FROM d.dt)
This ids great and gives me number of booking days in a month on all bookings with a start to end date 
. However, I’m aslo trying to capture booking without an end date or bookings with a startDate and a transferDate… I’m thinking CASE in with the INNER JOIN or am I barking up the wrong tree?
INNER JOIN datehelper d ON d.dt CASE WHEN depature is NULL THEN BETWEEN b.bookingS AND curDate() ELSE CASE WHEN trasfer IS NULL THEN BETWEEN b.bookingS AND b.transfer ELSE BETWEEN b.bookingS AND b.bookingE END END
Through my lack of experience, MySQL syntax always seems to get the better of me and there are times like Alice down the rabbit hole, I just keep going deeper and deeper into the syntax abyss.
One day I’ll get my head around it all but in the meantime I appreciate your support
yeah, your syntax is b0rk3d
try this –
INNER
JOIN datehelper d
ON d.dt BETWEEN b.bookingS
AND CASE WHEN depature IS NULL
THEN CURRENT_DATE
WHEN transfer IS NULL
THEN b.transfer
ELSE b.bookingE
ENDp.s. CASE is standard sql, not just MySQL
@r937 that looks so much neater than what I was trying to do… I always manage to somehow b0rk the syntax. However, there are times when a booking departure date and transfer date will both be null so I’m still plugging away at that.
Also having a challenge with DATES - when a booking start and end or start and transfer are on the same day they don’t get counted and thus don’t appear because ‘same day’ in SQL world is 0. Is there a way to do this
Jun12 2016 - Jun 12 2016 would be 1 day
Jun 12 2016 - Jun 13 2016 would be 2 days
Here’s my query so far, appreciate that it doesn’t look great and is not efficient but:
`SELECT
YEAR(d.dt)
, MONTH(d.dt)
, COUNT(*)+1 AS bkDiM
FROM booking b
INNER JOIN datehelper d ON
CASE
WHEN bookingE IS NULL
THEN
CASE
WHEN transfer IS NULL
THEN d.dt BETWEEN DATE_SUB(b.bookingS,INTERVAL 1 DAY) AND DATE_ADD(CURDATE(), INTERVAL 1 DAY)
ELSE
CASE
WHEN DATEDIFF(b.transfer, b.bookingS) = 0
THEN d.dt BETWEEN b.bookingS AND DATE_ADD(b.transfer, INTERVAL 1 DAY)
ELSE d.dt BETWEEN b.bookingS AND b.transfer
END
END
ELSE
CASE
WHEN DATEDIFF(b.bookingS, b.bookingE) = 0
THEN d.dt BETWEEN b.bookingS AND DATE_ADD(b.bookingE, INTERVAL 1 DAY)
ELSE d.dt BETWEEN b.bookingS AND b.bookingE
END
END
WHERE d.dt <= CURDATE()
GROUP BY EXTRACT(YEAR_MONTH FROM bookingS)
ORDER BY EXTRACT(YEAR_MONTH FROM d.dt)`
you cannot toss BETWEEN into the middle of the CASE syntax
CASE is all about choosing a value to use, not choosing syntax
@r937 I appreciate that now and having looked at you earlier post I’ve had chance to update my query
`SELECT
YEAR(d.dt)
, MONTH(d.dt)
, COUNT(*)+1 AS bkDiM
FROM
booking b
INNER JOIN datehelper d ON d.dt BETWEEN bookingS
AND CASE
WHEN bookingE IS NULL
THEN
CASE
WHEN transfer IS NULL
THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY)
ELSE
CASE
WHEN DATEDIFF(b.transfer, b.bookingS) = 0
THEN DATE_ADD(b.transfer, INTERVAL 1 DAY)
ELSE b.transfer
END
END
ELSE
CASE
WHEN DATEDIFF(b.bookingS, b.bookingE) = 0
THEN DATE_ADD(b.bookingE, INTERVAL 1 DAY)
ELSE b.bookingE
END
END
WHERE d.dt <= CURDATE()
GROUP BY EXTRACT(YEAR_MONTH FROM bookingS)
ORDER BY EXTRACT(YEAR_MONTH FROM d.dt)`
GROUP BY EXTRACT(YEAR_MONTH FROM bookingS)
ORDER BY EXTRACT(YEAR_MONTH FROM d.dt)
this makes me feel queasy
first of all, if you’re going to ORDER BY something, it should be in the SELECT list
but what’s really unnerving is the thought that those year/months might be different, and how that would affect the results
@r937 Apologies if my query makes you feel queasy, it has the same effect on me. Seek solace in the fact that what takes you 20 minutes to work out usually take me around 4 days so I fully understand queasy…
bookingS is in the select of my original query and I’ve also used it to order by.
The year/months are not different. Perhaps I should have sanitised my query before posting. Haing spent 16 hours on this today I need a break
Thanks for your help. I learn a little more each day
1 Like
@DaveMaxwell @r937 Thanks for all your help and guidance. Finally cracked it, I think…
For others who are interested he’s my final query
` SELECT
bookingID
, bookingS
, COUNT(*) AS bkDiM
FROM booking b
INNER JOIN datehelper d
ON d.dt BETWEEN b.bookingS AND
CASE
WHEN b.bookingE IS NULL
THEN
CASE
WHEN b.transfer IS NULL
THEN DATE_ADD(CURDATE(), INTERVAL 1 DAY)
ELSE DATE_ADD(b.transfer, INTERVAL 1 DAY)
END
ELSE DATE_ADD(b.bookingE, INTERVAL 1 DAY)
END
WHERE d.dt <= CURDATE()
GROUP BY EXTRACT(YEAR_MONTH FROM d.dt)`
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.