June 4, 2016 at 12:37 pm
I also see you have posted similar questions here
https://social.msdn.microsoft.com/Forums/en-US/user/threads?user=InnovativeArihant
but you have yet to reply......did any of the responses on that site help you?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 6, 2016 at 9:29 am
Ok....my thoughts on this...would appreciate feedback from all, but especially the OP.
Erland Sommarskog posted a possible solution here
however, I dont think it worked for all date cases....so based on Erlands post, I created a cte that built all the new date "ranges" and then applied Erlands code to that.
As for building the CTE "newperiods"....there may well be a better, shorter, concise way....and I would love to see that...but code below is my "longhand version".
Once the newperiods CTE is constructed then its straightforward (me thinks!)
I have included additional sample data, that I hope covers most scenarios...it would be good if the OP could indicate what "rules" there are for the "input" table generation. As I see this, there are potential date ranges that overlap, are contiguous and non contiguous and this requires clarification.
This neither delete or inserts into the "finalresult" table....but that is straightforward if required.
CREATE TABLE #finalresult
(Id INT,
A INT,
B INT,
C INT,
D INT,
E INT,
Validfrom DATE,
validto DATE
);
INSERT INTO #finalresult
(Id, A, B, C, D, E, Validfrom, validto)
VALUES
(1, 1, 2, 6, 4, 9, '1900-01-01', '1950-12-31'),
(1, 2, 3, 6, 8, 3, '1951-01-01', '2000-12-31'),
(1, 3, 6, 6, 1, 1, '2001-01-01', '9998-12-31'),
(2, 9, 9, 9, 9, 9, '2001-01-01', '9998-12-31'),
(4, 888, 777, 666, 555, 444, '2001-01-01', '9998-12-31'),
(6, 123, 456, 0, 0, 0, '2015-01-01', '2015-12-31');
CREATE TABLE #input(
Id int NULL,
AttributeID varchar(1) NOT NULL,
AttributeValue int NULL,
ValidFrom date NULL,
ValidTo date NULL
)
INSERT INTO #input
VALUES
(1 ,'B' ,10, '1900-01-01', '1930-12-31'),
(1 ,'B', 4, '1931-01-01', '2050-12-31'),
(1 ,'B', 7, '2051-01-01' ,'9998-12-31'),
(2 ,'C', 100, '2011-01-01' ,'2012-12-31'),
(3 ,'D', 999, '2011-01-01' ,'2012-12-31'),
(6, 'A', 444, '2015-11-01', '2015-12-31');
WITH cte as (
SELECT ID, validfrom pdate, 's' dtype FROM #finalresult
UNION
SELECT ID, validto pdate,'e' dtype FROM #finalresult
UNION
SELECT ID, validfrom pdate,'s' dtype FROM #input
UNION
SELECT ID, validto pdate, 'e' dtype FROM #input)
,c1 as (
SELECT id,pdate,dtype ,
CASE WHEN dtype = 's' then ROW_NUMBER() OVER(PARTITION BY ID,dtype ORDER BY pdate)
ELSE ROW_NUMBER() OVER(PARTITION BY ID,dtype ORDER BY pdate DESC) END rn
from cte
)
,c2 as (
SELECT id, pdate
FROM c1
UNION
SELECT id, DATEADD(day, -1, pdate)
FROM C1
WHERE rn > 1 AND dtype = 's'
UNION
SELECT id, DATEADD(day, 1, pdate)
FROM C1
WHERE rn > 1 AND dtype = 'e')
, c3 as (
SELECT id,pdate,
((ROW_NUMBER() OVER (PARTITION BY id ORDER BY pdate) - 1) / 2 + 1) AS grp
from c2
)
, newperiods as (
SELECT id
, min(pdate) AS validfrom
, max(pdate) AS validto
FROM C3
GROUP BY ID, grp
)
--select * from newperiods order by id,validfrom
SELECT np.id,
CASE WHEN I.AttributeID = 'A' THEN i.Attributevalue ELSE fr.A END as A,
CASE WHEN I.AttributeID = 'B' THEN i.Attributevalue ELSE fr.B END as B,
CASE WHEN I.AttributeID = 'C' THEN i.Attributevalue ELSE fr.C END as C,
CASE WHEN I.AttributeID = 'D' THEN i.Attributevalue ELSE fr.D END as D,
CASE WHEN I.AttributeID = 'E' THEN i.Attributevalue ELSE fr.E END as E,
np.validfrom,
np.validto
FROM newperiods AS np
LEFT JOIN #finalresult AS fr ON np.id = fr.Id
AND np.validto >= fr.Validfrom
AND np.validfrom <= fr.validto
LEFT JOIN #input AS i ON np.id = i.Id
AND np.validto >= i.Validfrom
AND np.validfrom <= i.validto
ORDER BY np.id, np.validfrom
DROP TABLE #finalresult
DROP TABLE #input
edit typo
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply