May 17, 2018 at 4:44 am
I have this issue. I have a table with booked items, simplified here:
declare @t table(dBookedFrom smalldatetime, dBookedTill smalldatetime)
insert into @t
values('20180101 12:00', '20180101 20:00')
, ('20180103 08:00', '20180105 06:00')
When I run a "select all" query I get 2 rows with the booked period:
row 1: 2018-01-01 12:00:00 - 2018-01-01 20:00:00
row 2: 2018-01-03 08:00:00 - 2018-01-05 06:00:00
But I need the "free" periods now, so I need this result:
row 1: null - 2018-01-01 12:00:00
row 2: 2018-01-01 20:00:00 - 2018-01-03 08:00:00
row 3: 2018-01-05 06:00:00 - null
Anyone knows how to do this?
Thanks!
May 17, 2018 at 4:59 am
SELECT
dBookedTill AS FreeFrom
, LEAD(dBookedFrom,1) OVER (ORDER BY dBookedFrom) AS FreeTo
FROM @t
UNION ALL
SELECT
NULL
, MIN(dBookedFrom)
FROM @t
ORDER BY FreeFrom
John
May 17, 2018 at 5:03 am
Cool...
But when there are overlaps, the result is cot correct.
Please try this:
declare @t table(dBookedFrom smalldatetime, dBookedTill smalldatetime)
insert into @t
values('20180101 12:00', '20180101 20:00')
, ('20180102 08:00', '20180104 06:00')
, ('20180103 08:00', '20180105 06:00')
May 17, 2018 at 6:51 am
Technically you should not book overlaps. The same book cannot be booked twice. You need to add logic to prevent double booking (unless you are an airline). For example, run the query to show available free periods prior t inserting a booking. If you have multiple books you would include that criteria in the PARTITION clause.
May 17, 2018 at 6:57 am
Good remark, but when there are adjacent bookings, where "till" date from booking 2 is same as "from" date of booking 3 then I also get an unwanted row (row 3), and these are common situations.
Try this:declare @t table(dBookedFrom smalldatetime, dBookedTill smalldatetime)
insert into @t
values('20180101 12:00', '20180101 20:00')
, ('20180102 08:00', '20180103 08:00')
, ('20180103 08:00', '20180105 06:00')
May 17, 2018 at 7:01 am
NULL 2018-01-01 12:00:00
2018-01-01 20:00:00 2018-01-02 08:00:00
2018-01-03 08:00:00 2018-01-03 08:00:00 This row should not be there (for my result)
2018-01-05 06:00:00 NULL
May 17, 2018 at 7:17 am
Try this. It involves a triangular join, so performance may not be good on large data sets.
WITH BeginningsandEndings AS (
SELECT
dBookedFrom
, dBookedTill
FROM @t
UNION ALL
SELECT NULL, '19000101' -- lower limit of smalldatetime
UNION ALL
SELECT '20790606', NULL -- upper limit of smalldatetime
)
, Joined AS (
SELECT
b1.dBookedTill AS FreeFrom
, b2.dBookedFrom AS FreeTo
, ROW_NUMBER() OVER (PARTITION BY b1.dBookedTill ORDER BY b2.dBookedFrom) AS RowNoFrom
, ROW_NUMBER() OVER (PARTITION BY b2.dBookedFrom ORDER BY b1.dBookedTill DESC) AS RowNoTo
FROM BeginningsandEndings b1
JOIN BeginningsandEndings b2
ON b1.dBookedTill < b2.dBookedFrom
)
SELECT
FreeFrom
, FreeTo
FROM Joined
WHERE RowNoFrom = 1
AND RowNoTo = 1;
John
May 17, 2018 at 7:22 am
Yes, this works like I want it!
Thank you so much.🙂
May 17, 2018 at 7:23 am
SELECT
RangeStart = MAX(CASE WHEN dir = 1 THEN dt ELSE NULL END),
RangeEnd = MAX(CASE WHEN dir = 0 THEN dt ELSE NULL END)
FROM (
SELECT d.dir, d.dt,
grp = SUM(dir) OVER(ORDER BY dt)
FROM #t
CROSS APPLY (VALUES (0, dBookedFrom), (1, dBookedTill)) d (dir, dt)
) d
GROUP BY grp
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 17, 2018 at 7:38 am
marc.corbeel - Thursday, May 17, 2018 6:57 AMGood remark, but when there are adjacent bookings, where "till" date from booking 2 is same as "from" date of booking 3 then I also get an unwanted row (row 3), and these are common situations.
Try this:declare @t table(dBookedFrom smalldatetime, dBookedTill smalldatetime)insert into @t
values('20180101 12:00', '20180101 20:00')
, ('20180102 08:00', '20180103 08:00')
, ('20180103 08:00', '20180105 06:00')
SELECT
RangeStart = MAX(CASE WHEN dir = 1 THEN dt ELSE NULL END),
RangeEnd = MAX(CASE WHEN dir = 0 THEN dt ELSE NULL END)
FROM (
SELECT d.dir, d.dt,
grp = SUM(dir) OVER(ORDER BY dt)
FROM #t
CROSS APPLY (VALUES (0, dBookedFrom), (1, dBookedTill)) d (dir, dt)
) d
GROUP BY grp
HAVING CASE WHEN MAX(CASE WHEN dir = 1 THEN dt ELSE NULL END) = MAX(CASE WHEN dir = 0 THEN dt ELSE NULL END) THEN 1 ELSE 0 END <> 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 17, 2018 at 7:38 am
Chris, this returns also the unwanted 3rd row
May 17, 2018 at 7:39 am
All right, with the HAVING clause it works like it should.
Thanks
May 18, 2018 at 8:47 am
My thought would be to pre-allocate periods, using the ISO half open interval model. Then each client would be given one or more periods. The the unallocated periods would be very easy to find. The trade-off is that you have to decide how you want to do your intervals. Psychiatrist have the 50 minute hour. Most places find that cutting the day into 15 minute chunks works..Here is a quick skeleton:
CREATE TABLE Periods
(period_name CHAR(??) NOT NULL,
start_timestamp DATETIME2(0) NOT NULL,
end_timestamp DATETIME2(0) NOT NULL,
PRIMARY KEY (period_name, start_timestamp),
CHECK (start_timestamp < end_timestamp_timestamp));
CREATE TABLE Appointments
(client_id CHAR(10) NOT NULL,
period_name CHAR(??) NOT NULL
REFERENCES Periods (period_name),
etc);
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply