Record added for the first time

[lavadan]

I have a table of the below format:


ID ChildCode ParentCode Action ActionDate
1 1 1 Added 8/9/16 14:01
1 2 1 Added 8/9/16 14:01
1 1 1 Removed 2/27/17 9:21
1 1 1 Added 2/15/19 10:27
1 3 2 Added 2/15/19 10:27
1 4 2 Added 2/15/19 10:27
1 5 2 Added 2/15/19 10:27

I am looking for what parent code was added in Feb 2019 for the first time. In this case the result set will have data only for parentcode 2 and childcode 3,4,5.

Child 1 and ParentCode 1 was also added on 2/15 but it should not be in the result set because parentcode 1 with a child code of 2 was already added on 8/19 .

 

[Andrzejek]

Presenting your data, please use TGML tags:

[pre]
ID ChildCode ParentCode Action ActionDate
1 1 1 Added 8/9/16 14:01
1 2 1 Added 8/9/16 14:01
1 1 1 Removed 2/27/17 9:21
1 1 1 Added 2/15/19 10:27[blue]
1 3 2 Added 2/15/19 10:27
1 4 2 Added 2/15/19 10:27
1 5 2 Added 2/15/19 10:27[/blue]
[/pre]
" parentcode 1 with a child code of 2 was already added on 8/19 ." - no, it was on 8/9/16 14:01


---- Andy

There is a great need for a sarcasm font.

 

[Olaf Doschke]

Not sure what you want makes sense to me.
Do you want first time parents of a specific month and their children?

with firsttimers as
(Select ParentCode, FIRST_VALUE(ActionDate) Over (PARTITION BY ParentCode ORDER BY ActionDate) as FirstActionDate FROM yourtable)
Select * From yourtable inner join firsttimers 
on firsttimers.FirstActiondate>='20190201' 
and firsttimers.FirstActiondate<'20190301' 
and firsttimers.ParentCode = yourtable.parentcode

Bye, Olaf.












Olaf Doschke Software Engineering

https://www.doschke.name