Still driving me nuts: selecting several timelines from several lines from multibale tables
-
So, I called in your help a week ago on this post: https://www.sqlservercentral.com/Forums/1869218/Driving-me-nuts-selecting-valid-timelines-from-several-lines-in-a-table
It helped me out for a first part, but then things got really complicated, and now I can't look past my own code. I thought I cracked it at one time, but it turned out to be anything but solved.
Please have a look at the enclosed document (Excel) where I laid out my problem. It has 3 parts. If you guys have any insights on how to solve this; that would be great.
/* Table for Part 1 */
CREATE TABLE #clients
(
clientid TINYINT,
assessment_nbr TINYINT,
begin_dt DATE,
end_dt DATE,
date_died DATE
)/* Insert your data into your table. */
INSERT #clients(clientid, assessment_nbr, begin_dt, end_dt, date_died)
VALUES
(1, 104, '2007-03-16', '2009-03-15', '2011-12-03'),
(1, 105, '2008-11-24', '2010-11-30', '2011-12-03'),
(1, 106, '2008-12-02', '2009-03-31', '2011-12-03'),
(1, 107, '2009-04-01', '2010-03-31', '2011-12-03'),
(1, 108, '2009-04-01', '2010-03-31', '2011-12-03'),
(1, 109, '2008-12-02', '2010-12-31', '2011-12-03'),
(1, 110, '2010-12-27', '2011-12-26', '2011-12-03'),
(1, 111, '2010-12-27', '2011-12-26', '2011-12-03'),
(1, 112, '2011-06-22', '2026-06-21', '2011-12-03')SELECT *
FROM #clients/* Tables for Part 2 */
CREATE TABLE #careprofiles
(
clientid TINYINT,
assessment_nbr TINYINT,
careprofiletype CHAR(1),
begin_dt DATE,
end_dt DATE
)INSERT #careprofiles(clientid, assessment_nbr, careprofiletype, begin_dt, end_dt)
VALUES
(1, 104, 'A', '2007-03-16', '2009-03-15'),
(1, 105, 'B', '2008-11-24', '2010-11-30'),
(1, 106, 'D', '2008-12-02', '2009-03-31'),
(1, 111, 'B', '2010-12-27', '2011-01-31'),
(1, 111, 'D', '2011-06-01', '2011-12-26'),
(1, 112, 'D', '2011-06-22', '2026-06-21')CREATE TABLE #functions
(
clientid TINYINT,
assessment_nbr TINYINT,
functioncode TINYINT,
begin_dt DATE,
end_dt DATE
)INSERT #functions(clientid, assessment_nbr, functioncode, begin_dt, end_dt)
VALUES
(1, 107, 88, '2009-04-01', '2010-03-31'),
(1, 108, 88, '2009-04-01', '2010-03-31'),
(1, 109, 87, '2008-12-06', '2008-12-20'),
(1, 110, 63, '2010-12-27', '2011-12-26'),
(1, 111, 71, '2011-02-01', '2011-02-28'),
(1, 111, 73, '2011-03-03', '2011-03-10'),
(1, 111, 73, '2011-03-01', '2011-03-31'),
(1, 111, 81, '2011-04-01', '2011-05-31')/* Cleanup */
DROP TABLE #clients
DROP TABLE #careprofiles
DROP TABLE #functionsThank you very much in advance for taking time to look into this. Again, any insight is welcome. I have tried to simplify this 3 times, but I am going in circles every time.
Cheers,
Jeroen -
Your table definitions are generating errors... Please correct, test and repost.
-
Made some changes on my phone to the code. I have no acces to a SQL Server DB right now. Hopefully it's better now. If not I will check tomorrow morning.
Thank you for taking the time to look at my problem.
-
Sorry, there was still 1 small error. That is corrected. The code above is correct now.
Here is the solution to the 1st part, making valid timelines for assesments. After that I'm lost.
SELECT a.clientid, a.assessment_nbr, a.begin_dt, a.end_dt, a.new_end_dt, e.effective_end_dt, a.date_died
FROM (
SELECT clientid, assessment_nbr, c.begin_dt, end_dt, date_died,
DATEADD(DAY, -1, LEAD(begin_dt, 1, end_dt) OVER(ORDER BY begin_dt)) AS new_end_dt
FROM #clients c
WHERE NOT EXISTS
(
SELECT *
FROM #clients c2
WHERE c.clientid = c2.clientid
AND c.assessment_nbr < c2.assessment_nbr
AND c.begin_dt >= c2.begin_dt
AND c.end_dt <= c2.end_dt
)
) a
CROSS APPLY
(
SELECT MIN(DATUM) effective_end_dt
FROM (
VALUES (new_end_dt), (a.date_died)
) AS D(DATUM)
) e
ORDER BY a.clientid, a.assessment_nbr -
Try this... It's a liitle cumbersome and would benefit greatly from some proper indexes but it does apear to produce the desired results.
WITH
cte_RowsToKeep AS (
SELECT
c.clientid,
c.assessment_nbr,
c.begin_dt,
c.end_dt,
c.date_died,
KeepNum = MAX(c.assessment_nbr) OVER (PARTITION BY c.clientid ORDER BY c.begin_dt, c.assessment_nbr DESC ROWS UNBOUNDED PRECEDING)
FROM
#clients c
),
cte_ClientSet AS (
SELECT
rtk.clientid,
rtk.assessment_nbr,
rtk.begin_dt,
end_dt = DATEADD(dd, -1, LEAD(rtk.begin_dt, 1, rtk.date_died) OVER (PARTITION BY rtk.clientid ORDER BY rtk.begin_dt) )
FROM
cte_RowsToKeep rtk
WHERE
rtk.assessment_nbr = rtk.KeepNum
),
cte_Functions AS (
SELECT
f.clientid,
f.assessment_nbr,
f.functioncode,
f.begin_dt,
f.end_dt,
MaxPrevEndDt = ISNULL(MAX(f.end_dt) OVER (PARTITION BY f.clientid, f.assessment_nbr, f.functioncode ORDER BY f.begin_dt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), '1900-01-01')
FROM
#functions f
),
cte_CareProfilesUnionFunction AS (
SELECT
cuf.clientid,
cuf.assessment_nbr,
cuf.careprofiletype,
cuf.FunctionCode,
cuf.begin_dt,
end_dt = DATEADD(dd, -1, LEAD(cuf.begin_dt) OVER (PARTITION BY cuf.clientid ORDER BY cuf.begin_dt) )
FROM (
SELECT c.clientid, c.assessment_nbr, c.careprofiletype, FunctionCode = NULL, c.begin_dt,
end_dt = DATEADD(dd, -1, LEAD(c.begin_dt, 1, c.end_dt) OVER (PARTITION BY c.clientid ORDER BY c.assessment_nbr, c.begin_dt) )
FROM #careprofiles c
UNION ALL
SELECT f.clientid, f.assessment_nbr, careprofiletype = NULL, f.functioncode, f.begin_dt, f.end_dt
FROM cte_Functions f
WHERE f.end_dt > f.MaxPrevEndDt
) cuf
),
cte_AddLawSplit AS (
SELECT
cuf.clientid,
cuf.assessment_nbr,
cuf.careprofiletype,
cuf.FunctionCode,
cuf.begin_dt,
end_dt = CASE WHEN lc.LawCode = 'A' THEN '2010-12-31' ELSE cuf.end_dt END,
LawCode = ISNULL(lc.LawCode, CASE WHEN cuf.begin_dt < '2010-12-31' THEN 'A' ELSE 'B' END )
FROM
cte_CareProfilesUnionFunction cuf
LEFT JOIN (
SELECT lc.LawCode FROM ( VALUES ('A'), ('B') ) lc (LawCode)
) lc
ON '2011-01-01' BETWEEN cuf.begin_dt AND cuf.end_dt
)SELECT
cs.clientid,
cs.assessment_nbr,
cs.begin_dt,
cs.end_dt,
als.careprofiletype,
als.FunctionCode,
als.begin_dt,
end_dt = ISNULL(als.end_dt, cs.end_dt),
als.LawCode
FROM
cte_ClientSet cs
LEFT JOIN cte_AddLawSplit als
ON cs.clientid = als.clientid
AND cs.assessment_nbr = als.assessment_nbr
ORDER BY
cs.clientid,
cs.assessment_nbr,
als.begin_dt;Results...
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply