Select all date between which an object has certain properties

Question

I use dynamic properties to manage the characteristics of individuals. I have a table IndividusDynPropValues with 1 line for each individual and each property For example:

ID  IndividualID    ValueString  ValueFloat      ValueDate StartDate   Prop
108 2               Outside      NULL            NULL      2018-06-17  Out Status
107 2               Male         NULL            NULL      2018-05-17  Sex
106 1               Dead         NULL            NULL      2018-08-17  Out Status
105 2               Unk          NULL            NULL      2018-04-03  Sex
104 2               Adult        NULL            NULL      2018-04-03  Status
103 1               Femal        NULL            NULL      2018-03-27  Sex
102 1               Adult        NULL            NULL      2018-03-27  Status
101 2               Egg          NULL            NULL      2018-03-25  Status
100 1               Egg          NULL            NULL      2018-03-17  Status

I want to view a table with all living male individuals (I. e. Sex=’Male’ and Out Status=’Adult’ and Out status <> ’Dead’) between 2 dates using only one SELECT. So in the example I want to SELECT:

IndividualID    BeginDate          End Date      Group
2               2018-05-17         2019-05-16    Living male individuals

Is that possible? How to do this?

I've tried that for the Group "All Adult":

SELECT IDPV1.[IndividualID]
      ,IDPV1.[StartDate] AS BeginDate
     ,GETDATE() AS EndDate
     ,IDPV1.[ValueString]
     ,'All Adult' AS [Group]
   FROM [dbo].[IndividusDynPropValues] IDPV1
   WHERE IDPV1.ValueString='Adulte'

But it did not work with several properties...

Answer 1

Another possible approach is to count the matches and use appropriate HAVING clause:

Table:

CREATE TABLE #IDVP1 (
    ID int,
    IndividualID int,
    ValueString varchar(50),
    StartDate date,   
    Prop varchar(50)
)
INSERT INTO #IDVP1 
    (ID, IndividualID, ValueString, StartDate, Prop)
VALUES
    (108, 2, 'Outside', '20180617', 'Out Status'),
    (107, 2, 'Male',    '20180517', 'Sex'),
    (106, 1, 'Dead',    '20180817', 'Out Status'),
    (105, 2, 'Unk',     '20180403', 'Sex'),
    (104, 2, 'Adult',   '20180403', 'Status'),
    (103, 1, 'Femal',   '20180327', 'Sex'),
    (102, 1, 'Adult',   '20180327', 'Status'),
    (101, 2, 'Egg',     '20180325', 'Status'),
    (100, 1, 'Egg',     '20180317', 'Status')

Statement:

SELECT 
    IndividualID, 
    MIN(CASE WHEN (Prop = 'Sex') AND (ValueString = 'Male') THEN StartDate END) AS BeginDate,
    MIN(CASE WHEN (Prop = 'Out Status') AND (ValueString = 'Dead') THEN StartDate ELSE GETDATE() END) AS EndDate,
    'Living male individuals' AS [Group]
FROM #IDVP1
GROUP BY IndividualID
HAVING 
    SUM(
        CASE WHEN (Prop = 'Sex') AND (ValueString = 'Male') THEN 1 ELSE 0 END +
        CASE WHEN (Prop = 'Status') AND (ValueString = 'Adult') THEN 1 ELSE 0 END +
        CASE WHEN (Prop = 'Out Status') AND (ValueString <> 'Dead') THEN 1 ELSE 0 END
    ) = 3

Output (based on your example data only one row matches):

IndividualID    BeginDate           EndDate             Group
2               17/05/2018 00:00:00 17/05/2019 11:39:42 Living male individuals