none
Select Activities "Not Followed By" specific activities RRS feed

  • Question

  • 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.


    Rabelani Netshifhire

    Wednesday, December 11, 2019 12:46 PM

All replies

  • Hi,

    Do you also have any date column like ActivityDate? Based on that a query can be written. 

    E.g.

    Id - Activity - ActivityDate
    1 - Reg  - 2019-12-01
    2 - Pass - 2019-12-05  


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Wednesday, December 11, 2019 1:01 PM
  • Please post sample data + desired result. Always state what version you are using.
    Please share us more detailed information about your requirement like your table structure  and your expected result and 
    some sample data. So that it will help us understand your issue more clearly. 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, December 11, 2019 1:02 PM
    Answerer
  • Yes the date of activity is there , please show mw the query

    Rabelani Netshifhire

    Wednesday, December 11, 2019 1:03 PM
  • 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.


    Rabelani Netshifhire

    Wednesday, December 11, 2019 1:17 PM
  • Yes the date of activity is there , please show mw the query

    Rabelani Netshifhire

    Please test below query and see if you get desired result.

    CREATE TABLE Person ([PersonID] INT, [Name] VARCHAR(5));
    
    INSERT INTO Person ([PersonID], [Name])
    VALUES (1, 'Manu'), (2, 'Obed'), (4, 'Kimel');
    
    CREATE TABLE FullHistory ([PersonID] INT, [Activity] VARCHAR(5), [ActivityDate] INT);
    
    INSERT INTO FullHistory ([PersonID], [Activity], [ActivityDate])
    VALUES (1, 'Reg', 20191201), (1, 'Dereg', 20191205), (1, 'Pass', 20191207), (1, 'Grad', 20191210), (2, 'Reg', 20191101), (2, 'Pass', 20191103), (2, 'Grad', 20191105), (4, 'Reg', 20191215)
    
    ;WITH RegCTE
    AS (
      SELECT *, ROW_NUMBER() OVER (PARTITION BY PersonId ORDER BY ActivityDate DESC) 'RowNum'
      FROM FullHistory
       )
    SELECT p.Name, p.PersonID, h.Activity, h.ActivityDate
    FROM Person AS p
    JOIN RegCTE AS h ON p.PersonID = h.PersonID
    WHERE h.RowNum = 1
    	AND h.Activity = 'Reg'


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Wednesday, December 11, 2019 1:22 PM
  • Lets assume that you dint have do create the sample data , but data is dynamically entered into SQL server via CRM. 

    How would you have wrote that query? I am asking because these data is in SQL server, so it must be a dynamic query.


    Rabelani Netshifhire

    Wednesday, December 11, 2019 1:55 PM
  • I cannot test it, but perhaps you can use LEAD function in T-SQL

    WITH cte

    AS

    (

    SELECT PersonID,Activity,LEAD(Activity) OVER (ORDER BY personID) followed_Activity

    FROM History 

    ) SELECT name,cte.personid FROM cte JOIN Person  ON cte.personid=Person.personid WHERE Activity=followed_Activity


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Wednesday, December 11, 2019 2:01 PM
    Answerer
  • DECLARE @activity varchar(20) = 'Reg';
    
    DECLARE @Persons TABLE (
    	[PersonID] int,
    	[Name] varchar(20)
    );
    DECLARE @FullHistory TABLE (
    	[PersonID] int,
    	[Activity] varchar(20),
    	[Date] date
    );
    
    INSERT INTO @Persons VALUES (1, 'Manu'), (2, 'Obed'), (3, 'Jimmy'), (4, 'Kimel'), (5, 'Marry');
    INSERT INTO @FullHistory VALUES 
    (1, 'Reg', '2019-01-01'), (1, 'Dereg', '2019-01-02'), (1, 'Pass', '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');
    
    WITH CTE AS (
    	SELECT *, 
    		LEAD([PersonID], 1, 0) OVER (PARTITION BY [PersonID] ORDER BY [Date]) AS LeadID, 
    		LAG([PersonID], 1, 0) OVER (PARTITION BY [PersonID] ORDER BY [Date]) AS LagID
    	FROM @FullHistory
    )
    
    SELECT p.* 
    FROM @Persons AS p
    INNER JOIN CTE AS c ON c.[PersonID] = p.[PersonID]
    WHERE c.[LeadID] = 0 AND c.[LagID] = 0 AND c.[Activity] = @activity;


    A Fan of SSIS, SSRS and SSAS

    Wednesday, December 11, 2019 2:36 PM
  • how about on Dynamic data ?

    Rabelani Netshifhire

    Wednesday, December 11, 2019 2:54 PM
  • how about on Dynamic data ?
    What does it mean? Give an example. 

    A Fan of SSIS, SSRS and SSAS

    Wednesday, December 11, 2019 3:13 PM
  • ;with mycte as (
    
     SELECT p.[PersonID],  
     p.Activity, p.ActivityDate
     ,d.maxActivityDate
    FROM FullHistory p
    Cross apply 
     (select max(ActivityDate) maxActivityDate 
     from FullHistory f
     where p.[PersonID]=f.[PersonID]
     group by [PersonID]) d
     
    WHERE  Activity = 'Reg' 
    and p.ActivityDate=d.maxActivityDate
    )
    
    Select p.name,p.PersonID,m.activity,m.ActivityDate
    from mycte m 
    join person p on m.personid=p.PersonID

    Wednesday, December 11, 2019 5:34 PM
    Moderator
  • how about on Dynamic data ?

    Rabelani Netshifhire

    What do you mean by the words "Dynamic data "? Have you tested the solutions written by others? Why don't they work?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 12, 2019 7:12 AM
    Moderator
  • No need to create sample data, because creating sample data make the data in the tables static, that data is just an abstraction of a lot of data and cannot be hard coded.

    Rabelani Netshifhire

    Thursday, December 12, 2019 2:05 PM
  • No need to create sample data, because creating sample data make the data in the tables static, that data is just an abstraction of a lot of data and cannot be hard coded.

    Rabelani Netshifhire

    Thursday, December 12, 2019 2:06 PM
  • we need to talk about the other three activities and how to exclude them with the query because these queries are still giving me those activities that i don't need.

    Rabelani Netshifhire

    Thursday, December 12, 2019 2:07 PM
  • wee need to state on the query the info about the those three other activities and how to exclude them , the current queries do give me "Reg" but it is still followed by the other three activities that i do not want.

    Rabelani Netshifhire

    Thursday, December 12, 2019 2:09 PM
  • i do not see where this query remove those three other activities (Dereg, Pass,Grad), so i know it will still give the "Reg" that are followed by those activities, please adjust it for me to show how it remove those three activities based on the date because we know that the other three activities happened after "Reg".


    Rabelani Netshifhire

    Thursday, December 12, 2019 2:12 PM
  • Did you try the query I posted? Please post more data to show the ouput does not match your requirements. 

    A Fan of SSIS, SSRS and SSAS

    Thursday, December 12, 2019 7:34 PM
  • i do not see where this query remove those three other activities (Dereg, Pass,Grad), so i know it will still give the "Reg" that are followed by those activities, please adjust it for me to show how it remove those three activities based on the date because we know that the other three activities happened after "Reg".


    Rabelani Netshifhire

    Not clear about what you're saying. If you would like to make us understand you better, please describe it by using sample data, screenshot, images. These mediums could support your thought.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 18, 2019 6:14 AM
    Moderator