Select Min Date value from subquery to main query

Question

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

Please show the dataset your query currently produces. Tag your dbms. — Serg
– Serg, Commented Jul 28, 2019 at 10:48

Jim Jimson · Accepted Answer · 2019-07-28 11:20:56Z

1

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.

answered Jul 28, 2019 at 11:20

Jim Jimson

2,5784 gold badges22 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hugh Self Taught Over a year ago

You're too clever! Thank you so much. I see I have some new tricks to start learning in the unbounded preceding & following. I don't have enough points for my vote to count but maybe someone else reading this post can apply some votes on my behalf. I have marked as answer though

Collectives™ on Stack Overflow

Select Min Date value from subquery to main query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related