By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE HotelAccommodation (
Id INT IDENTITY(1,1),
HotelCode VARCHAR(50),
AccomodationPeriodBegin DATE,
AccomodationPeriodEnd DATE,
RoomType VARCHAR(10),
AccmdMenTypeCode VARCHAR(10),
Price INT,
OverlapDaysCount INT
);
Declare
@accomodationDateFrom DATETIME = '2025-01-05',
@accomodationDateTo DATETIME = '2025-04-01'
declare
@NeededDaysCount as int = DATEDIFF(DAY, @accomodationDateFrom, @accomodationDateTo) + 1
INSERT INTO HotelAccommodation (HotelCode, AccomodationPeriodBegin, AccomodationPeriodEnd, RoomType, AccmdMenTypeCode, Price, OverlapDaysCount)
VALUES
('MONTE', '2025-01-10', '2025-03-10', 'DGV', '01010003', 99, 60),
('MONTE', '2025-01-10', '2025-03-10', 'DGV', '01010003', 99, 60),
('MONTE', '2025-03-06', '2025-05-14', 'DGV', '01010003', 106, 27),
('MONTE', '2025-03-06', '2025-05-14', 'DGV', '01010003', 106, 27),
('MONTE', '2024-07-01', '2025-07-15', 'DGV', '01010003', 109, 87),
('MONTE', '2024-07-01', '2025-07-15', 'DGV', '01010003', 109, 87),
('MONTE', '2025-01-10', '2025-03-10', 'DGV', '01010312', 99, 60),
('MONTE', '2025-01-10', '2025-03-10', 'DGV', '01010312', 99, 60),
('MONTE', '2025-03-06', '2025-05-14', 'DGV', '01010312', 106, 27),
('MONTE', '2025-03-06', '2025-05-14', 'DGV', '01010312', 106, 27),
('MONTE', '2024-07-01', '2025-07-15', 'DGV', '01010312', 109, 87),
('MONTE', '2024-07-01', '2025-07-15', 'DGV', '01010312', 109, 87);
;WITH BaseRows AS (
-- All the rows
SELECT
| GroupIds | HotelCode | AccmdMenTypeCode | OverlapDaysCount |
|---|---|---|---|
| 5 | MONTE | 01010003 | 87 |
| 11 | MONTE | 01010312 | 87 |