November 30, 2018 at 7:39 am
Need some advice on improving/rewriting the below query. In summary, I have got a table which I am trying to recursively loop through to generate a parent-child relationship.
For example, the table has:
PAT_ID 1
+-------+------------------+------------------+
| EP_ID | START_DTTM | END_DTTM |
+-------+------------------+------------------+
| 1 | 01/12/2018 10:00 | 02/12/2018 15:00 |
| 2 | 03/12/2018 10:00 | 10/12/2018 15:00 |
| 3 | 04/12/2018 10:00 | 06/12/2018 15:00 |
| 4 | 07/12/2018 10:00 | 09/12/2018 15:00 |
| 5 | 11/12/2018 10:00 | 13/12/2018 15:00 |
| 6 | 12/12/2018 10:00 | 12/12/2018 15:00 |
| 7 | 01/12/2019 10:00 | 02/12/2019 15:00 |
+-------+------------------+------------------+
Desired Output:
+--------+-------+-----------+-----------------------------------------------------------------------------------------+
| PAT_ID | EP_ID | PARENT_ID | LINK_TYPE |
+--------+-------+-----------+-----------------------------------------------------------------------------------------+
| 1 | 1 | 0 | 'Parent' |
| 1 | 2 | 1 | 'Child' (Rule for child is that START_DTTM is less than 24 hours of parent EP_ID) |
| 1 | 3 | 2 | 'Inner' (Rule for inner is that START_DTTM is between START_DTTM and END_DTTM of Child) |
| 1 | 4 | 2 | 'Inner' |
| 1 | 5 | 0 | 'Parent' (doesnt qualify as child or inner for any row) |
| 1 | 6 | 5 | 'Child' |
| 1 | 7 | 0 | 'Parent |
+--------+-------+-----------+-----------------------------------------------------------------------------------------+
~~~
I have attempted to write the logic using a cursor which seems to be returning rows fine but the base table has over 10m rows so its unlikely to complete before my retirement which unfortunately has 30 more years to go :). Need the community's expert advice on how can I approach this query (i have tried while loop which was slower than cursor).
Thanks in advance!
IF (OBJECT_ID('tempdb..#PARENT') IS NOT NULL)
BEGIN
DROP TABLE #PARENT
END
IF (OBJECT_ID('tempdb..#CHILD') IS NOT NULL)
BEGIN
DROP TABLE #CHILD
END
CREATE TABLE #Parent (
EP_ID INT
,ID VARCHAR(20)
,PAT_ID VARCHAR(50)
,START_DTTM DATETIME
,END_DTTM DATETIME
,CT_DESC VARCHAR(100)
,CT_CODE VARCHAR(10)
,PARENT_EP_ID INT
,PARENT_ID VARCHAR(20)
,LINK VARCHAR(20)
,PROCESSED INT
,PARENT_EP_SEQ INT
)
CREATE TABLE #CHILD (
EP_ID INT
,ID VARCHAR(20)
,PAT_ID VARCHAR(50)
,START_DTTM DATETIME
,END_DTTM DATETIME
,CT_DESC VARCHAR(100)
,CT_CODE VARCHAR(10)
,PARENT_EP_ID INT
,PARENT_ID VARCHAR(20)
,LINK VARCHAR(20)
,PROCESSED INT
,CHILD_EP_SEQ INT
)
INSERT INTO #PARENT
SELECT deip.EP_ID
,deip.ID
,deip.PAT_ID
,START_DTTM
,END_DTTM
,CT_DESC
,CT_CODE
,0
,''
,'Parent' AS LINK
,0 AS PROCESSED
,row_number() OVER (
PARTITION BY deip.PAT_ID ORDER BY START_DTTM
) AS PARENT_EP_SEQ
FROM dbo.deip
INNER JOIN dbo.dEP ep ON deip.EP_ID = ep.EP_ID
dbo.RE ep.STATUS IN (
'A'
,'D'
)
AND ep.RECORD_STATUS = 'A'
AND
event_type = 'Active'
AND CT_CODE <> '10'
PRINT 'Parent Done'
DECLARE @PARENT_EP_SEQ INT
DECLARE @PAT_ID INT
DECLARE @EP_ID INT
DECLARE @COUNT BIGINT
DECLARE ChildCursor CURSOR LOCAL FAST_FORWARD
FOR
SELECT PARENT_EP_SEQ
,PAT_ID
,EP_ID
FROM #PARENT
where PROCESSED = 0
OPEN ChildCursor
while 1 = 1
BEGIN
-- And then fetch
FETCH NEXT
FROM ChildCursor
INTO @PARENT_EP_SEQ
,@PAT_ID
,@EP_ID
-- And then, if no row is fetched, exit the loop
IF @@fetch_status <> 0
BEGIN
BREAK
END
INSERT INTO #CHILD
SELECT C.EP_ID
,C.ID
,P.PAT_ID
,C.START_DTTM
,C.END_DTTM
,C.CT_DESC
,C.CT_CODE
,P.EP_ID AS PARENT_EP_ID
,P.ID
,'Child' AS LINK
,0 AS PROCESSED
,row_number() OVER (
PARTITION BY C.PAT_ID ORDER BY c.START_DTTM
) AS CHILD_EP_SEQ
FROM #PARENT p
INNER JOIN #PARENT C ON p.PAT_ID = c.PAT_ID
dbo.RE P.PAT_ID = @PAT_ID
AND P.EP_ID = @EP_ID
AND P.PARENT_EP_SEQ = @PARENT_EP_SEQ
AND P.EP_ID <> C.EP_ID
AND P.PARENT_EP_SEQ <> C.PARENT_EP_SEQ
AND datediff(hh, isnull(p.END_DTTM, getdate()), C.START_DTTM) BETWEEN 0
AND 24
AND p.PROCESSED = 0
AND c.CT_CODE <> '10'
ORDER BY p.PARENT_EP_SEQ
DELETE P
FROM #PARENT P
INNER JOIN #CHILD c ON p.PAT_ID = c.PAT_ID
AND p.EP_ID = c.EP_ID
UPDATE #PARENT
SET Processed = 1
dbo.RE PAT_ID = @PAT_ID
AND EP_ID = @EP_ID
AND PARENT_EP_SEQ = @PARENT_EP_SEQ
END
CLOSE ChildCursor
DEALLOCATE ChildCursor
PRINT 'Child Done'
Afterthought: I thought of using recursive/hierarchical CTE but i don't have a key that determines the relations. The parent->child association is what i am trying to generate.
December 3, 2018 at 8:23 am
Your setup query doesn't work. I don't know if people are going to be all that interested if before even trying any solutions, they can't do the setup.
Try to treat it as a service call, and make sure that the premise that describes your issue is itself not malfunctioning as the query you posted is. Folks need to be able to straight up post your SQL into a window and have it work.
December 5, 2018 at 3:10 pm
I'm not sure how you would ever get inner and your data is really bad. here's an example of what I think you want, but again only guessing here
drop table if exists #Parent
create table #parent
(ep_id int,
START_DTTM smalldatetime,
END_DTTM smalldatetime)
insert into #Parent values
( 1,'12/01/2018 10:00:00','12/01/2018 15:00:00'),
( 2,'12/03/2018 10:00:00','12/10/2018 15:00:00'),
( 3,'12/4/2018 10:00:00','12/6/2018 15:00:00'),
( 4,'12/7/2018 10:00:00','12/09/2018 15:00:00'),
( 5,'12/11/2018 10:00:00','12/13/2018 15:00:00'),
( 6,'12/12/2018 10:00:00','12/12/2018 15:00:00'),
( 7,'12/1/2019 10:00:00','12/2/2019 15:00:00')
--select *
-- from #parent
-- order by start_dttm
select ep_id, DateDiff(hour, PreviousStart_sttm, start_dttm) ,
Case when DateDiff(hour, PreviousStart_sttm, start_dttm) <= 24 then 'Child'
when PreviousStart_sttm between start_dttm and end_dttm then 'Inner'
else
'Parent'
end LinkType, *
from (
select ep_id, start_dttm, end_dttm,
LAG(start_dttm, 1) over (order by START_DTTM) PreviousStart_sttm,
LAG(end_dttm, 1) over (order by START_DTTM) Previousend_dttm
from #parent) vinsert into #Parent values ( 1,'12/01/2018 10:00:00','12/01/2018 15:00:00'),( 2,'12/03/2018 10:00:00','12/10/2018 15:00:00'),( 3,'12/4/2018 10:00:00','12/6/2018 15:00:00'),( 4,'12/7/2018 10:00:00','12/09/2018 15:00:00'),( 5,'12/11/2018 10:00:00','12/13/2018 15:00:00'),( 6,'12/12/2018 10:00:00','12/12/2018 15:00:00'),( 7,'12/1/2019 10:00:00','12/2/2019 15:00:00')--select *-- from #parent-- order by start_dttm select ep_id, DateDiff(hour, PreviousStart_sttm, start_dttm) , Case when DateDiff(hour, PreviousStart_sttm, start_dttm) <= 24 then 'Child' when PreviousStart_sttm between start_dttm and end_dttm then 'Inner' else 'Parent' end LinkType, *from (select ep_id, start_dttm, end_dttm, LAG(start_dttm, 1) over (order by START_DTTM) PreviousStart_sttm, LAG(end_dttm, 1) over (order by START_DTTM) Previousend_dttm from #parent) v
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 5, 2018 at 9:22 pm
Mike01 - Wednesday, December 5, 2018 3:10 PMI'm not sure how you would ever get inner and your data is really bad. here's an example of what I think you want, but again only guessing here
drop table if exists #Parent
create table #parent
(ep_id int,
START_DTTM smalldatetime,
END_DTTM smalldatetime)insert into #Parent values
( 1,'12/01/2018 10:00:00','12/01/2018 15:00:00'),
( 2,'12/03/2018 10:00:00','12/10/2018 15:00:00'),
( 3,'12/4/2018 10:00:00','12/6/2018 15:00:00'),
( 4,'12/7/2018 10:00:00','12/09/2018 15:00:00'),
( 5,'12/11/2018 10:00:00','12/13/2018 15:00:00'),
( 6,'12/12/2018 10:00:00','12/12/2018 15:00:00'),
( 7,'12/1/2019 10:00:00','12/2/2019 15:00:00')
--select *
-- from #parent
-- order by start_dttmselect ep_id, DateDiff(hour, PreviousStart_sttm, start_dttm) ,
Case when DateDiff(hour, PreviousStart_sttm, start_dttm) <= 24 then 'Child'
when PreviousStart_sttm between start_dttm and end_dttm then 'Inner'
else
'Parent'
end LinkType, *
from (select ep_id, start_dttm, end_dttm,
LAG(start_dttm, 1) over (order by START_DTTM) PreviousStart_sttm,
LAG(end_dttm, 1) over (order by START_DTTM) Previousend_dttm
from #parent) vinsert into #Parent values ( 1,'12/01/2018 10:00:00','12/01/2018 15:00:00'),( 2,'12/03/2018 10:00:00','12/10/2018 15:00:00'),( 3,'12/4/2018 10:00:00','12/6/2018 15:00:00'),( 4,'12/7/2018 10:00:00','12/09/2018 15:00:00'),( 5,'12/11/2018 10:00:00','12/13/2018 15:00:00'),( 6,'12/12/2018 10:00:00','12/12/2018 15:00:00'),( 7,'12/1/2019 10:00:00','12/2/2019 15:00:00')--select *-- from #parent-- order by start_dttm select ep_id, DateDiff(hour, PreviousStart_sttm, start_dttm) , Case when DateDiff(hour, PreviousStart_sttm, start_dttm) <= 24 then 'Child' when PreviousStart_sttm between start_dttm and end_dttm then 'Inner' else 'Parent' end LinkType, *from (select ep_id, start_dttm, end_dttm, LAG(start_dttm, 1) over (order by START_DTTM) PreviousStart_sttm, LAG(end_dttm, 1) over (order by START_DTTM) Previousend_dttm from #parent) v
Thanks for your reply. It helped!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply