May 18, 2021 at 1:23 am
Below is the schema and sample data.
At any given time (date range), we shouldn't have overlap of two salesman tied to a customer number. If you look at custono = 10, assignment of salesman do not overlap on validfrom and validto.
But if you look at custno 258615, salesrep overlaps. I need to be able to pick only one row from it where validfrom date is less than the other row date. In this case, I want the line with Salesep = A093557. The other record needs to be ignored.
create table #temp (Custno varchar(100), salesman varchar(100), SalesRep varchar (3), validfrom varchar(10), validto varchar(10))
insert into #temp
select '258615', 'I999999', 'ISR', '20210505', '20391231'
union all
select '258615', 'A093557', 'ISR', '20210501', '20391231'
union all
select '10', 'A092279', 'ISR', '20170101', '20190531'
union all
select '10', 'A093958', 'ISR', '20190601' ,'20190831'
union all
select '10', 'A095228', 'ISR', '20190901' ,'20191231'
union all
select '10', 'I00024Q', 'ISR', '20200101' ,'20391231'
Let me know if you need further clarif.
May 18, 2021 at 3:53 am
Not 100% sure this works... but it should at least give you some ideas... If you use LAG(), you can get the "previous" record (in time order) and see if it overlaps the current one - (if the start time or end time of one record falls within the start/end of the other.)
SELECT Prev.CustNo
, Prev.validfrom
, Prev.ValidTo
, TestOverlap = CASE WHEN PrevFrom <= validFrom AND PrevTo >= validTo THEN 1 ELSE 0 END
FROM
(SELECT CustNo
,validfrom
,validto
,PrevFrom = LAG(validfrom) OVER (PARTITION BY CustNo ORDER BY validFrom, validTo)
,PrevTo = LAG(validTo) OVER (PARTITION BY CustNo ORDER BY validFrom, validTo)
FROM #temp) Prev
WHERE Prev.PrevFrom IS NOT NULL
AND Prev.PrevTo IS NOT NULL
May 18, 2021 at 4:28 pm
You could just use row_number() - no need to compare the previous values.
row_number() over(partition by CustNo order by validFrom asc, validTo desc)
If you have ties on validFrom - the one with the latest validTo would be selected. If you have ties on both - then you would need something else included in the order to determine which one comes first.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply