לבנק אוצר החייל קיימים 3 סניפים ברחבי העיר ירוחם, כאשר בכל סניף קיימים 2 כספומטים. נתונה הטבלה הבאה אשר מתארת את פרקי הזמן בהם הכספומטים השונים עבדו.
כתבו שאילתה אשר מחזירה את פרקי הזמן הרציפים בהם עבדו הכספומטים בבנקים השונים. לבנק מס’ 1 השאילתא אמורה להחזיר שורה אחת – 08:30-09:55, לבנק מס’ 2 שלוש שורות – 09:20-09:25, 09:30-09:55, 10:30-11:55 ולבנק מס’ 3 שתי שורות – 08:00-09:50 ו – 11:20-12:50.
CREATE TABLE time_table
(bank_id int,
atm_id int,
strt_date datetime,
end_date datetime)
INSERT INTO time_table VALUES (1, 1, '2015/04/12 08:30' , '2015/04/12 09:15')
INSERT INTO time_table VALUES (1, 2, '2015/04/12 09:00' , '2015/04/12 09:45')
INSERT INTO time_table VALUES (1, 1, '2015/04/12 09:20' , '2015/04/12 09:55')
INSERT INTO time_table VALUES (2, 1, '2015/04/12 09:20' , '2015/04/12 09:25')
INSERT INTO time_table VALUES (2, 2, '2015/04/12 09:30' , '2015/04/12 09:55')
INSERT INTO time_table VALUES (2, 1, '2015/04/12 10:30' , '2015/04/12 11:30')
INSERT INTO time_table VALUES (2, 2, '2015/04/12 11:15' , '2015/04/12 11:55')
INSERT INTO time_table VALUES (3, 1, '2015/04/12 08:00' , '2015/04/12 09:00')
INSERT INTO time_table VALUES (3, 2, '2015/04/12 08:55' , '2015/04/12 09:20')
INSERT INTO time_table VALUES (3, 1, '2015/04/12 09:15' , '2015/04/12 09:50')
INSERT INTO time_table VALUES (3, 1, '2015/04/12 11:20' , '2015/04/12 12:15')
-- part 1, create a view to identify the contiguousy
CREATE VIEW contiguous_vu
AS
SELECT bank_id, strt_date,
ISNULL(end_date, '9999-12-31') end_date,
CASE
WHEN strt_date > MAX(ISNULL(end_date, '9999-12-31'))
over(PARTITION BY bank_id ORDER BY strt_date
ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 preceding)
THEN 1
END contiguous_identifier
FROM time_table
-- part 2, group the different parts
SELECT bank_id, MIN(strt_date), MAX(end_date)
FROM
( SELECT bank_id, strt_date, end_date, contiguous_identifier ,
SUM(contiguous_identifier) over(PARTITION BY bank_id ORDER BY strt_date) contiguousy
FROM contiguous_vu
) TAB
GROUP BY bank_id, contiguousy
ORDER BY 1, 2
