July 14, 2017 at 9:56 am
Hi ,
I am calculating the end date based on start date and using the help of LEAD function.However i am not able to retain the value for the duplicate records.
My Source
a b
sku1 20170101
sku1 20170101
sku1 20170101
sku1 20170101
sku1 20171020
Using LEAD Function
select a,b,lead (b) OVER (partition by a ORDER BY b asc) AS end from aq
a b end
sku1 20170101 20170101 ----- i want this to print 20170120 as the previous value is same(20170101)
sku1 20170101 20170101 ----- i want this to print 20170120 as the previous value is same(20170101)
sku1 20170101 20170101 ----- i want this to print 20170120 as the previous value is same(20170101)
sku1 20170101 20171020 ----- correct
sku1 20171020 (null) -----correct
so my output should look like
sku1 20170101 20170120
sku1 20170101 20170120
sku1 20170101 20170120
sku1 20170101 20171020
sku1 20171020 null
Create table statements
create table aq(a varchar(4),b varchar(8) );
insert into aq values('sku1','20171020');
insert into aq values('sku1','20170101');
insert into aq values('sku1','20170101');
insert into aq values('sku1','20170101');
insert into aq values('sku1','20170101');
July 14, 2017 at 10:14 am
Is there any reason not to get rid of the duplicates?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 14, 2017 at 10:21 am
Hi,
Yes,we can't remove the duplicates and need to calculate the end date for each row and if the end date for the previous start date is same then we need to continue with the same end date and should change where it found a new start date.
July 14, 2017 at 10:51 am
Here's a possibility. Although, I didn't test for performance.
WITH CTE AS(
SELECT *,
DENSE_RANK() OVER(PARTITION BY a ORDER BY b) dr,
ROW_NUMBER() OVER(PARTITION BY a,b ORDER BY b) rn
FROM aq
)
SELECT c1.a, c1.b, c2.b
FROM CTE c1
LEFT JOIN CTE c2 ON c1.a = c2.a
AND c1.dr = c2.dr - 1
AND c2.rn = 1;
July 14, 2017 at 11:16 am
Thanks Luis for this.Is there any other way where i can do it without the help of WITH clause.i have some other more columns in select and i need to do some transformation.
Any possibility with combination of any Analytic function
July 14, 2017 at 11:28 am
There might be other ways. However, I'm not sure what do you need as you're just giving a piecemeal of the information. I can't see a reason why the solution wouldn't work with additional columns.
July 14, 2017 at 12:00 pm
I'm with Luis. I don't know why using a CTE should cause you any issues here.
With that in mind, here's another way that only hits the base table once, but at the cost of some increased worktable usage:
WITH CTE AS
(
SELECT *,
rn=ROW_NUMBER() OVER (PARTITION BY a,b ORDER BY @@VERSION ASC)
FROM aq
)
SELECT a,
b,
[End]=LEAD (b,rn) OVER (PARTITION BY a ORDER BY rn DESC)
FROM cte;
Cheers!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply