March 15, 2018 at 10:58 am
Hi
I would like to process a list of dates to produce a list of date ranges. I've searched extensively and despite finding lots of people who want to do the exact opposite (turn a date range into a list of dates), I've been unable to find a satisfactory solution to my problem.
The source table will have dates missing, and new ranges need to be created to navigate around these. There is also an additional column, 'CargoColmn', and a new range must also be created when this changes.
I've written code which works, in that it produces the desired result, but it doesn't strike me as being particularly efficient, doesn't appear to scale particularly well, and I was wondering if anyone has a cleverer way of approaching this.
Here's the data:
create table #MyDates
(
DateLogged date not null,
CargoColmn tinyint not null
)
insert #MyDates
(
DateLogged,
CargoColmn
)
select '01Jan2018', 5 union
select '02Jan2018', 5 union
select '03Jan2018', 5 union
select '04Jan2018', 5 union
select '05Jan2018', 5 union
select '06Jan2018', 5 union
select '07Jan2018', 5 union
-- There's a week missing here
select '15Jan2018', 5 union
select '16Jan2018', 5 union
select '17Jan2018', 5 union
select '18Jan2018', 5 union
select '19Jan2018', 5 union
select '20Jan2018', 5 union
select '21Jan2018', 5 union
select '22Jan2018', 4 union -- CargoColmn changes from 5 to 4
select '23Jan2018', 4 union
select '24Jan2018', 4 union
select '25Jan2018', 4 union
select '26Jan2018', 4 union
select '27Jan2018', 4 union
select '28Jan2018', 4 union
-- There's a week missing here
select '05Feb2018', 4 union
select '06Feb2018', 4 union
select '07Feb2018', 4 union
select '08Feb2018', 4 union
select '09Feb2018', 4 union
select '10Feb2018', 4 union
select '11Feb2018', 4 union
select '12Feb2018', 5 union -- CargoColmn changes from 4 to 5
select '13Feb2018', 5 union
select '14Feb2018', 5 union
select '15Feb2018', 5 union
select '16Feb2018', 5 union
select '17Feb2018', 5 union
select '18Feb2018', 5
And the expected output:
DateFrom DateTo CargoColmn
---------- ---------- ----------
2018-01-01 2018-01-07 5
2018-01-15 2018-01-21 5
2018-01-22 2018-01-28 4
2018-02-05 2018-02-11 4
2018-02-12 2018-02-18 5
And my attempt at solving the problem.
;with MyDates
as
(
select DateLogged = Src.DateLogged,
CargoColmn = Src.CargoColmn,
NextDate = Tgt.DateLogged
from #MyDates Src
left outer loop join #MyDates Tgt
on Tgt.CargoColmn = Src.CargoColmn
and Tgt.DateLogged = dateadd(day, 1, Src.DateLogged)
)
--select * from MyDates order by DateLogged
,
DateGroups
as
(
select DateLogged = MyDates.DateLogged,
CargoColmn = MyDates.CargoColmn,
GrpNumber = row_number() over ( order by MyDates.DateLogged )
from MyDates
where NextDate is null
)
--select * from DateGroups order by DateLogged
,
EachDayGrouped
as
(
select DateLogged = MyDates.DateLogged,
CargoColmn = MyDates.CargoColmn,
GrpNumber = LowestGrp.GrpNumber from MyDates cross apply
(
select top 1
DateGroups.GrpNumber
from DateGroups
where MyDates.DateLogged <= DateGroups.DateLogged
order by DateGroups.GrpNumber
) as LowestGrp
)
select DateFrom = min(EachDayGrouped.DateLogged),
DateTo = max(EachDayGrouped.DateLogged),
CargoColmn = min(EachDayGrouped.CargoColmn)
from EachDayGrouped
group by EachDayGrouped.GrpNumber
order by min(EachDayGrouped.DateLogged)
Many thanks in advance.
Dave
March 15, 2018 at 11:31 am
Perhaps something like this?
WITH CTE AS
(
SELECT *, grp=DATEADD(DAY,-ROW_NUMBER() OVER (PARTITION BY CargoColmn ORDER BY DateLogged ASC),DateLogged)
FROM #MyDates
)
SELECT DateFrom=MIN(DateLogged), DateTo=MAX(DateLogged),CargoColmn
FROM CTE
GROUP BY CargoColmn,grp
ORDER BY DateFrom ASC;
Cheers!
March 16, 2018 at 4:03 am
Jacob
I really like this solution, it's brilliant, works perfectly, and I can understand it.
This is my first ever post and I'm very grateful for your quick response.
Very best regards
Dave
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply