Retrieve activities "Followed By" certain activities in SQL

Transact-SQL
, ,
1

Hello

I have three SQL database tables as shown bellow:

1
1.JPG931×513 71 KB

In SQL , i want to retrieve the first three persons (marked with a tick) because they have activities that follow each other (FR, ISRO, VIS, REC) in sequence order dates. The last two persons (4 and 5) must not appear because 4 starts with DC and 5 has DE as the second last activity instead of VIS.

Please note that there can be other activities in between the history of these persons. But the idea is that if there is FR and after that it is followed by ISRO and after that by VIS and after that by REC, then this person must show up. NB (even if there might be other activities between them.

Please help with the code.

The pseudocode is:
SELECT Person.Name
FROM Person LeftJoin History On Person.person_id = History.person_id
Where ActivityName = FR followed-by ISRO followed-by VIS followed-by REC
even if there can be other activities between them.

2

Do you have any DDL and sample data to provide? we'll need that in order to help. We can't use screen shots

3

Only that data on excel and no DLL yet

4

We'll try to help, but we aren't going to go through the task of creating DDL and sample data for you.

5

you need to provide DDL in the following format for us to work with.

create table #bafanabafana(person_id int, Name varchar (50), 
ActivityName char(20), Seq varchar(10))

then DML

insert into #bafanabafana
select 1, 'jane' 'Fr', '01.01.2019' union

--etc

Aint nobody got time to do all this for you. :slight_smile:

6

Yeah it is ok , i just need the skeleton SQL code to select an item , and make sure it followed by something(FR), and followed by something(ISRO) , and followed by something(VIS), and followed by something(REC) using the sequence order date to compare them.

7

Please provide sample data if you expect others to answer your question.

8

This information is in our company server, it look exactly like what i put on the picture, all i need is skeleton code of selecting items satisfying the condition that they are preceded with other items.

9

and all we need is for you to provide sample data to try it out on our server. or give us access to your server :slight_smile: its very simple. provide sample data you have attached as a picture in the following format

Create table #bafanabafana(person_id int, Name varchar (50), 
ActivityName char(20), Seq varchar(10))

then DML

insert into #bafanabafana
select 1, 'jane' 'Fr', '01.01.2019' union

Why is that so hard for you to do that? In order for folks to answer you question they cant use the data presented in a picture. They will have to recreate that data from scratch.

10

You know that i cannot give a community access to private and corporate information, on my last post you answered me very well with a skeleton code, that i went on and use and it worked.

11

Please provide sample data as follows

Create table #bafanabafana(person_id int, Name varchar (50), 
ActivityName char(20), Seq varchar(10))

then DML

insert into #bafanabafana
select 1, 'jane' 'Fr', '01.01.2019' union
12

Rabelani:

You need to understand. We are volunteering to write code for you, to solve your problem, and we do it for many other people during the day too. I just don't have time to create sample data for everyone's issue.

You expect each of us to do it for you. But, if it's your issue, and you don't do it, then how important is it really?

13

I am just looking for skeleton code like:

SELECT ...
FROM db1.dbo.table1
WHERE ...
UNION ALL
SELECT ...
FROM db2.dbo.table1
WHERE ...
UNION ALL
SELECT ...
FROM db3.dbo.table1
WHERE ...

14

i understand the requirements, just that i didnt create those tables as well, i was just giving an overview of what is in our corporate databases

15

Rabelani

Please provide sample data as follows

Create table #bafanabafana(person_id int, Name varchar (50), 
ActivityName char(20), Seq varchar(10))

then DML

here please do the select of each of the data rows in your picture.

insert into #bafanabafana
select 1, 'jane' 'Fr', '01.01.2019' union

this way we will have a sample of your corporate database data on our database so we can provide you the answer.

1 Like
16

@yosiasz :+1: for persistency

17

Good luck with this.

18

:laughing:

I remember this was a hard concept for me when I got started in the SQL world.

19

Wild guessing on this one with no data: -

SELECT DISTINCT P.NAME

FROM   (SELECT Row_number()

                 OVER (

                   ORDER BY activity_id) AS rn,

               activityname,

               person_id

        FROM   activity) Z

       JOIN (SELECT Row_number()

                      OVER (

                        partition BY person_id

                        ORDER BY seq) AS rn,

                    activityname

             FROM   historyactivity) Y

         ON Y.rn = Z.rn

            AND Y.activityname = Z.activityname

       JOIN person P

         ON P.person_id = Z.person_id

       JOIN (SELECT Row_number()

                      OVER (

                        partition BY person_id

                        ORDER BY seq) AS rn,

                    activityname,

                    person_id

             FROM   historyactivity) AS blaa

         ON blaa.rn = 1

            AND blaa.activityname = 'FR'

            AND blaa.person_id = Z.person_id; 

20

hi Rabelani

I know this post is from Long Time ago !!!
I am just putting down my thoughts !! hope it helps :slight_smile: :slight_smile:

How about using Lead() function with

Lead(1) = FR ... current row
Lead(2) = ISRO .... 2nd row
Lead(3) = VIS ... 3rd row
Lead(4) = REC ... 4th row

if this condition satisfied .. select the person

image

image
image1004×125 16.2 KB

Lead(column,2) means 2nd row