Select Min Date value from subquery to main query

I’m trying to get the min Date value as a column in the main query where the main query & the subquery have inner joins & the min Date value is dependent on grouping of 3 columns from the main query joins

I tried subqueries in the main query Select statement but they only execute once as my research showed. I’ve tried various ways of joins but can’t get either the logic or the syntax to work. I’m thinking a combination of cte may work but haven’t been able to fathom it out (still a bit too new to these advanced queries for an old dog learning new tricks)

DECLARE

@StartBallDate date = '09-05-2009',
@StartLatDate date = '09-05-2009',
@Male int = 1,
@Female int = 4

SELECT T.*, E.StylID, C.PtsFirstRec

FROM dbo.tblPtsPerCompHistory AS T 
        INNER JOIN 
        (
        SELECT Min(Comp_Date) PtsFirstRec, Competition_Idx
        FROM tblCompetitions 
        GROUP BY Comp_Date, Competition_Idx
        ) C ON C.Competition_Idx = T.PtsCompID

        INNER JOIN tblEvtStructure AS E ON E.EvtStruct_Idx = T.PtsStructID

        WHERE E.SectnID Not Between 9 And 10 And (T.PtsMale = @Male Or T.PtsFemale = @Female And E.StylID = 1 And PtsFirstRec >= @StartBallDate) Or (T.PtsMale = @Male Or T.PtsFemale = @Female And E.StylID = 2 And PtsFirstRec >= @StartLatDate)

This that I have so far gives me the Comp_Date of each row. What I need to achieve so I can use the data elsewhere later in my program is

T.PtsMale      T.PtsFemale      C.Comp_Date      E.StylID      PtsFirstRec
1               4                2009-05-05       1            2009-05-05
1               4                2009-05-05       2            2009-05-05
1               4                2010-03-16       1            2009-05-05
1               202              2015-03-25       1            2015-03-25
1               4                2015-03-25       2            2009-05-05
1               202              2016-07-17       1            2015-03-25

So I have all the rows where either Ptsmale or PtsFemale but the min Date Value PtsFirstRec is the earliest date of the pairing grouped also by E.StylID

Answer

This is another window function one:

DECLARE

@StartBallDate date = '09-05-2009',
@StartLatDate date = '09-05-2009',
@Male int = 1,
@Female int = 4

SELECT T.*, E.StylID, MIN(C.PtsFirstRec) OVER (PARTITION BY T.PtsMale      T.PtsFemale, E.StylID ORDER BY C.Comp_Date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) [NewValue]

FROM dbo.tblPtsPerCompHistory AS T 
        INNER JOIN 
        (
        SELECT Min(Comp_Date) PtsFirstRec, Competition_Idx
        FROM tblCompetitions 
        GROUP BY Comp_Date, Competition_Idx
        ) C ON C.Competition_Idx = T.PtsCompID

        INNER JOIN tblEvtStructure AS E ON E.EvtStruct_Idx = T.PtsStructID

        WHERE E.SectnID Not Between 9 And 10 And (T.PtsMale = @Male Or T.PtsFemale = @Female And E.StylID = 1 And PtsFirstRec >= @StartBallDate) Or (T.PtsMale = @Male Or T.PtsFemale = @Female And E.StylID = 2 And PtsFirstRec >= @StartLatDate)

If you included your test data we’d be able to make sure the code actually works obviously. But it should be something along these lines.