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:
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 !!! 
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
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
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 ...
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'Hi Scott
Thanks
I noticed it
But ignored it ..
Paid too little attention..
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
