Came across this First_Value function that gets me pretty close to what I need, however, I need it to look at only A and B ElementID's. Didn't know if there was a way to look at subgroups within the partition? Thank you
1; A
1; A
1; B
1; B
2; B
2; B
2; B
2; A
2; A
IF OBJECT_ID('TempDB..#pat','U') IS NOT NULL
DROP TABLE #pat
create table #pat
(PatID varchar(10)
,ElementID varchar(50)
,ElementIDDate date)
Insert into #Pat
values
(1, 'A', '2020-08-03')
,(1, 'C', '2020-08-04')
,(1, 'B', '2020-08-04')
,(1, 'C', '2020-08-06')
,(2, 'B', '2020-08-26')
,(2, 'B', '2020-08-21')
,(2, 'B', '2020-08-26')
,(2, 'A', '2020-08-21')
,(2, 'C', '2020-08-26')
Select
p.patid
,p.elementid
,p.ElementIDDate
,FIRST_VALUE(Elementid) over(partition by patid order by elementiddate ) as 'NewValue'
from #pat p
December 24, 2021 at 11:49 pm
You would have to filter those out in the WHERE clause in the inner query.
SELECT FIRSTVALUE(…)
FROM (Select…WHERE elementID !=‘C’)
December 25, 2021 at 3:05 pm
It doesn't seem possible without additional information. In your FIRST_VALUE function the PARTITION BY patid and ORDER BY elementiddate doesn't seem to deterministically define the order of the rows where patid=1 and elementiddate='2020-08-04'. Does 'C' always come before 'B' or vice versa? Or neither because it's based on order of data entry but which column contains that information? With an ordinal column it seems likely the output could be derived using a "gaps and islands" approach. More could be said about assuming the ordinality of non-partitioned tables but it's better not to go there imo.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 26, 2021 at 8:13 am
I pretty much skipped a few previous steps in the query above for simplicity. Assuming I've already ordered the above data set with a previous cte, I need to look for the 'root' ElementID (A or B), then categorize any subsequent ElementID's within each 'Root' until another 'Root' ElementID (A or B) is hit.
Another example (A, A, F, B, C, D, B, A, C) will need to be categorized as (A, A, A, B, B, B, B, A, A) in another column.
Thank you for your response.
Suppose there's an ordinal column called 'Ord'. The WHERE clause makes sure the "first" row is a "root" ElementID. The OUTER APPLY selects the ElementID to substitute if the row value is not a root ElementID
drop table if exists #pat;
go
create table #pat(
Ord int
,PatID varchar(10)
,ElementID varchar(50)
,ElementIDDate date);
Insert into #Pat(Ord, PatID, ElementID, ElementIDDate) values
(1, 1, 'A', '2020-08-03')
,(2, 1, 'A', '2020-08-04')
,(3, 1, 'F', '2020-08-04')
,(4, 1, 'B', '2020-08-06')
,(5, 2, 'C', '2020-08-26')
,(6, 2, 'D', '2020-08-21')
,(7, 2, 'B', '2020-08-26')
,(8, 2, 'A', '2020-08-21')
,(9, 2, 'C', '2020-08-26');
select p.*, case when p.ElementID in ('A', 'B')
then p.ElementID
else te.ElementID end root_ElementID
from #pat p
outer apply (select top(1) pp.ElementID
from #pat pp
where pp.Ord<p.Ord
and pp.ElementID in('A', 'B')
order by pp.Ord desc) te(ElementID)
where not exists (select 1
from (select top(1) Ord
from #pat
where ElementID in('A', 'B')
order by Ord) min_root(Ord)
where p.Ord<min_root.Ord)
order by p.Ord;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
December 26, 2021 at 5:41 pm
This was removed by the editor as SPAM
December 29, 2021 at 11:46 am
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply