This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to generate the new EndBillDate if only one day difference between two bills then replace the previous date with new bill date.
Input Data:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Id BillCode EndBilllDate ----------- -------- ------------ 1 A001 2016-01-01 2 A002 2016-01-08 3 A003 2016-01-16 4 A004 2016-01-17 5 A005 2016-02-08 6 A006 2016-02-15 7 A007 2016-02-16 8 A008 2016-03-08 9 A009 2016-03-15 10 A010 2016-03-16 |
Expected output – Generate the New EndBillDate:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Id BillCode EndBilllDate NewEndBillDate ----------- -------- ------------ -------------- 1 A001 2016-01-01 2016-01-01 2 A002 2016-01-08 2016-01-08 3 A003 2016-01-16 2016-01-17 4 A004 2016-01-17 2016-01-17 5 A005 2016-02-08 2016-02-08 6 A006 2016-02-15 2016-02-16 7 A007 2016-02-16 2016-02-16 8 A008 2016-03-08 2016-03-08 9 A009 2016-03-15 2016-03-16 10 A010 2016-03-16 2016-03-16 |
Create a table with data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE TABLE BillDates ( Id INT ,BillCode VARCHAR(5) ,EndBilllDate DATE ) GO INSERT INTO BillDates VALUES (1,'A001','2016-01-01') ,(2,'A002','2016-01-08') ,(3,'A003','2016-01-16') ,(4,'A004','2016-01-17') ,(5,'A005','2016-02-08') ,(6,'A006','2016-02-15') ,(7,'A007','2016-02-16') ,(8,'A008','2016-03-08') ,(9,'A009','2016-03-15') ,(10,'A010','2016-03-16') GO |
Solution:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
;WITH CTE1 AS ( SELECT Id ,BillCode ,EndBilllDate ,DATEDIFF(DAY, (SELECT MIN(EndBilllDate) FROM BillDates),EndBilllDate) - ROW_NUMBER() OVER( ORDER BY EndBilllDate) rnk FROM BillDates ) ,CTE2 AS ( SELECT MIN(Id) Id , MIN(BillCode) BillCode , MIN(EndBilllDate) StartDate , MAX (EndBilllDate) EndBilllDate FROM CTE1 GROUP BY rnk ) SELECT b.Id , b.BillCode , b.EndBilllDate , a.EndBilllDate NewEndBillDate FROM CTE1 b LEFT JOIN CTE2 a ON b.EndBilllDate BETWEEN a.StartDate AND a.EndBilllDate |
Please try the different solution for this puzzle and share it via comment...
