SQLTeam.com | Weblogs | Forums

Select Activities "Not Followed By" specific activities

Dear Friends

I have a two tables in SQL server, one of PERSONS and one of FULL HISTORY that contains activities that happened to those persons.

The two tables are related with a PersonID. One Person can have more than one activity in the Full History as shown bellow:
1

How can i write a T-SQL query to retrieve a person who have activity "Reg" that is not followed by any of the the other activities. So the answer must be PersonID = 4 and Name = Kimel.

sample data is on private SQL instance for work , the dates are there as shown in the Pic.

I wanna have an SQL like the one below:

SELECT PersonID, Name FROM PERSON Left Join FULL HISTORY WHERE Activity = 'Reg' And Activity NOT IN ('Dereg','Pass','Grad')

But the results must be only where we have "Reg" only, in this case only PersonID=4.

Note that the date is also available to work on this, please see the last column on the pic.

hi rabelani

what about my answer !!! :slight_smile:

it can work on that data, but the data keep increasing because it is entered dynamically into SQL server via CRM Dynamics, it might not give the same results as it grows , but for this specific case yes, your answer id a work around

it is an SQL table of people and their histories, as long as there is "Reg" then the query must look again in the Full History, to make sure that for that specific person there is no Dereg, Pass, or Grad and then retreive that person.
Cant we use COALESCE or LEAD functions ?

can you call me on video call on Facebook so i can show you ? my name on facebook is Rabelani Netshifhire

it is not allowed to to use those apps with an external person , but whatsap video will be good
+27764114480 is my whatsap number

You need a date to be able to tell when the 'Reg' occurred vs what came after it. I've assumed an activity_date is present in the data in the code below, although a $IDENTITY could work too.

SELECT FH.PersonId, P.name
FROM #FullHistory FH
INNER JOIN #person P ON P.personid = FH.PersonId
OUTER APPLY (
    SELECT TOP (1) Activity AS first_activity_after_Reg
    FROM #FullHistory FH2
    WHERE FH2.PersonId = FH.PersonId AND 
        FH2.ActivityDate > FH.ActivityDate AND
        FH2.Activity <> 'Reg'
) AS CA1
WHERE FH.Activity = 'Reg' AND 
    (CA1.first_activity_after_Reg IS NULL OR CA1.first_activity_after_Reg = 'Reg')

Hi Rabelani

I tried to create some sample data based on what i understood
I tried to cover all scenario's

please click arrow to the left for drop create data ...
drop table #person 
go 

create table #person 
(
personid int ,
name varchar(100)
)
go 

insert into #person select 1,'Manu'
insert into #person select 2,'Obed'
insert into #person select 3,'Jimmy'
insert into #person select 4,'Kimel'
insert into #person select 5,'Marry'
go 

select ' person data',* from #person
go 

drop table #FullHistory 
go 

create table #FullHistory
(
PersonId int ,
Activity Varchar(10),
Date_Activity Date 
)
go 

insert into #FullHistory select 1,'Reg','2019-01-01'
insert into #FullHistory select 1,'DeReg','2019-01-02'
insert into #FullHistory select 1,'Reg','2019-01-03'
insert into #FullHistory select 1,'Grad','2019-01-04'

insert into #FullHistory select 2,'DeReg','2019-01-01'
insert into #FullHistory select 2,'Pass','2019-01-02'
insert into #FullHistory select 2,'Reg','2019-01-03'
insert into #FullHistory select 2,'Grad','2019-01-04'

insert into #FullHistory select 3,'DeReg','2019-01-01'
insert into #FullHistory select 3,'Pass','2019-01-02'
insert into #FullHistory select 3,'Grad','2019-01-03'
insert into #FullHistory select 3,'Reg','2019-01-04'


insert into #FullHistory select 4,'DeReg','2019-01-01'
insert into #FullHistory select 4,'Pass','2019-01-02'
insert into #FullHistory select 4,'Grad','2019-01-03'
insert into #FullHistory select 4,'Reg','2019-01-04'
insert into #FullHistory select 4,'abc','2019-01-01'
insert into #FullHistory select 4,'def','2019-01-02'
insert into #FullHistory select 4,'xyz','2019-01-03'
insert into #FullHistory select 4,'zzz','2019-01-04'

insert into #FullHistory select 5,'DeReg','2019-01-01'
insert into #FullHistory select 5,'Reg','2019-01-02'
insert into #FullHistory select 5,'zz','2019-01-03'
insert into #FullHistory select 5,'ok','2019-01-04'

go 

select 'FullHistory Data',* from #FullHistory
go

image

hi Rabelani

After creating sample data thinking about all possible data scenarios ..
Please let me know if i am missing anything !!!!

I have tried to come up with SQL Solution..
Please see if ok .. Its different than Scott's ...

please click arrow to the left for SQL Script
select 'SQL Output',b.PersonId,a.name 
from 
(
	select 
		 MAX(b.Date_Activity) as max_date_activity,
		 b.personid,
		 a.name 
	from #person a join #FullHistory b 
			on a.personid = b.PersonId
	where Activity = 'REG' 
		 and Activity not in  ('Dereg','Pass','Grad')
	group by b.personid,a.name
) a join 
(
	select 
		 max(b.Date_Activity) as max_after_reg,b.personid 
	from #person a join #FullHistory b 
		 on a.personid = b.PersonId
	where Activity <> 'REG' 
		  and  Activity  in  ('Dereg','Pass','Grad') 
	group by b.PersonId
 ) b 
   on a.PersonId = b.PersonId 
   and a.max_date_activity > b.max_after_reg

That is meaningless. If Activity = 'REG' of course it cannot be anything else ('Dereg' or whatever).

There is a simpler solution.

Create tables with sample data:

drop table if exists #person 
go 

create table #person (
	personid int ,
	name varchar(100)
)
go 

insert into #person (personid, name) VALUES
(1,'Manu'), (2,'Obed'), (3,'Jimmy'), (4,'Kimel'), (5,'Marry')
go 
select * from #person ORDER BY personid
go 

drop table if exists #FullHistory 
go 
create table #FullHistory(
	PersonId int ,
	Activity Varchar(10),
	Date_Activity Date 
)
go 

insert into #FullHistory(PersonId, Activity, Date_Activity) VALUES
(1,'Reg','2019-01-01'), (1,'DeReg','2019-01-02'), (1,'Reg','2019-01-03'), (1,'Grad','2019-01-04'), 
(2,'Reg','2019-01-01'), (2,'Pass','2019-01-02'), (2,'Grad','2019-01-03'), 
(3,'Reg','2019-01-01'), (3,'Pass','2019-01-02'), 
(4,'Reg','2019-01-01'), 
(5,'Reg','2019-01-01'), (5,'DeReg','2019-01-02')
go 

select * from #FullHistory
go

Get the last activity per person from the history table. If that last activity = 'Reg' you have found what you were looking for.

;WITH CTE_fullHistory AS(
SELECT *
	, ROW_NUMBER() OVER (PARTITION BY PersonId ORDER BY Date_Activity DESC) as RowNum
FROM #FullHistory
)
SELECT *
FROM CTE_fullHistory FH
	INNER JOIN #person as P 
		ON FH.PersonId = P.personid
WHERE FH.RowNum = 1
	AND FH.Activity = 'Reg'
1 Like

Hi Scott

Thanks

I noticed it

But ignored it ..

Paid too little attention..

:+1::+1:

Hi Scott

Please see the picture bellow that shows the data in a CRM application.
The section indicated by P shows what will be in the Person table and FL is Full history (in the reporting database)

What i want the query to do is to retrieve the ticked item (Enfant enregiste) provided it is not followed by the crossed items(familee reunifee, reunification familee accepte, reunification famile demande).

But the ticked item that is followed by any other thing except the crossed one must be retreived.

I hope you get me.

What is on the picture is in two tables in the reporting database, A table of persons and of full history linked by personID. The data of the activites is available and the first activity is the one at the bottom .

[quote="Rabelani, post:18, topic:16957"]
Please see the picture bellow that shows the data in a CRM application.
The section indicated by P shows what will be in the Person table and FL is Full history (in the reporting database)

What i want the query to do is to retrieve the ticked item (Enfant enregiste) provided it is not followed by the crossed items(familee reunifee, reunification familee accepte, reunification famile demande).

But the ticked item that is followed by any other thing except the crossed one must be retreived.

I hope you get me.

What is on the picture is in two tables in the reporting database, A table of persons and of full history linked by personID. The data of the activites is available and the first activity is the one at the bottom .

You need to post directly usable sample data, that is, CREATE TABLE and INSERT statements, similar to what others have done here. A picture of data is worthless to us.

hi

what does this have to do with SQL ...

the earlier solutions given by me , Scott , Wim
are more than enough

its really really easy ... even if its not enough

:slight_smile: :slight_smile: