8 Replies Latest reply on Jul 29, 2009 7:34 AM by Dan Bracuk

    Max Date from Sub Query

    mycfquest

      I am running CF 8 and my backend is MS SQL 2005. I am attempting to create a recordset that will list all applicants along with their most recent training date. I have two tables, AppInfo and TrainInfo that are tied together with a primary/foreign key. For some reason in my list I do get all applicants listed but not their most recent training date. I have 3 training dates that belong to one TrainInfo.AppIDFK and 2 training dates that belong to another TrainInfo.AppIDFK but not getting either let alone the most recent.

       

      Here is the code I am using. Can you tell me where I am going wrong? I've been at this for a week now. Any help will be greatly appreciated.

       

      <!--- Query to get App Listing --->
      <cfquery name="qApp" datasource="#dsn#">
      SELECT AppIDPK, FName, LName, WkPhone, HmPhone,
                  SubmitDate, Status
      FROM AppInfo
      ORDER BY LName, FName
      </cfquery>


      <!--- Query to get training detail --->
      <cfquery name="qMaxDate" datasource="#dsn#">
          SELECT TrainID, AppIDFK, TrainingDate
          FROM TrainInfo
          WHERE TrainingDate =
                                    (SELECT MAX(TrainingDate)
                                    FROM TrainInfo
                                    WHERE AppIDFK = #qApp.AppIDPK#)
          GROUP BY TrainID, AppIDFK, TrainingDate
      </cfquery>

        • 1. Re: Max Date from Sub Query
          ilssac Level 5

          I have heard good things about the books "Teach yourself SQL in 10 minutes" and "Database Design for Mere Mortals".

           

           

          What you are trying to do is a basic inner join.

           

          I would have expected to see a query that could look something like.

           

          SELECT apppIDPK, FName, LName, WkPhone, HmPhone, SubmitDate, Status, Train ID, AppIDFK, Training Date
          FROM AppInfo INNER JOIN TrainInfo ON (AppInfo.AppIDPK = TrainInfo.APPFK)
          ORDER By LName, FName
          

          This query should return all the combined records.

           

          Filterering this down to the most recent training record can be added once the combination has been properly returned.

          • 2. Re: Max Date from Sub Query
            mycfquest Level 1

            I'm getting closer I think. I've applied your suggested code and without the WHERE clause it returned all TrainingDates for everyone and then I added the following and it returned one record which is the Max Date for all regardless their group. That reminds me, shouldn't I have GROUP BY somewhere in this query?

             

            SELECT AppIDPK, FName, LName, WkPhone, HmPhone, SubmitDate, Status,

                           NewDate, TrainID, AppIDFK, TrainingDate
            FROM AppInfo
            INNER JOIN TrainInfo ON (AppInfo.AppIDPK = TrainInfo.AppIDFK)

            WHERE TrainingDate = (SELECT Max(TrainingDate)
                                                    FROM TrainInfo)
            ORDER BY LName, FName

            • 3. Re: Max Date from Sub Query
              ilssac Level 5

              Not if you use a sub-query like that.

               

              You could forgo the sub-query and use the MAX operator in the SELECT clause of the main query, you would then need a group-by clause listing all fields not used in the MAX() opeator.

               

              I.E.

              SELECT aField, bField, cField, MAX(dField)

              FROM ....

              WHERE ...

              GROUP BY aField, bField, cField

               

              OR

               

              You can continue to use the sub-query, it might look something like:

               

              SELECT ....

              FROM aTable aaa INNER JOIN bTable bbb (aTable.key = bTable.key)

              WHERE aField = (SELECT MAX(aField) FROM aTable WHERE aTable.aKey = aaa.aKey)

              • 4. Re: Max Date from Sub Query
                Dan Bracuk Level 5

                You either don't need the subquery or you have it in the wrong place.

                 

                If all those other fields are not in the same table as training date, you don't need the subquery.  Put max(trainingdate) in your select clause and add a group by clause.

                 

                Otherwise, your subquery should be in the from clause, not the where clause.  Something like this

                 

                select TrainingDate, etc

                from appinfo ap join traininfo tr on (a.appidpk = tr.appidpk)

                join (select appidpk id, max(trainingdate) maxdate

                from traininfo

                where whatever

                group by appidpk) sq on tr.appikdpk = id and trainingdate = maxdate

                etc

                • 5. Re: Max Date from Sub Query
                  mycfquest Level 1

                  Thanks Dan for your response. I'm trying to implement it but need some clarification. On the Where clause, is that where I would check if it matches the AppIDPK field and if so, how would I write that? So far this is what I have but I'm stuck.

                   

                  select AppIDPK, FName, LName, TrainID, AppIDFK, TrainingDate

                  from AppInfo JOIN TrainInfo on (AppInfo.AppIDPK = TrainInfo.AppIDFK)

                  join (select AppIDFK, max(TrainingDate) maxdate, TrainID

                            from TrainInfo

                            where ?

                            group by TrainID) on AppIDFK = AppIDPK and TrainingDate = maxdate

                   

                  Here's my first table layout.

                  AppIDPKFNameLName
                  1JaneDoe
                  2JaniceDoe

                   

                  Here's my 2nd table layout.

                  TrainIDAppIDFKTrainingDate
                  1105/12/2006
                  2205/11/2008
                  3107/13/2008

                   

                  I'm trying to only retrieve the most recent training date for each name.

                  • 6. Re: Max Date from Sub Query
                    Dan Bracuk Level 5

                    You have already done it with "on AppIDFK = AppIDPK".

                     

                    Any where clause would deal with date ranges and other things to get just the results you want.  For performance reasons, whatever you contstrain on inside your subquery should be repeated outside your subquery.  And example would be

                    where trainingdate between date1 and date2

                    • 7. Re: Max Date from Sub Query
                      mycfquest Level 1

                      I've used the syntax below and I'm getting a SQL Server error "Incorrect syntax near the keyword 'on' ". As for the where clause, I'm not checking for a TrainingDate within a date range. I'm just wanting the most recent (Max) training date for each name so do I still have to include a where clause?

                       

                      BTW, thanks for all the suggestions thus far.

                       

                      SELECT AppIDPK, FName, LName, TrainID, AppIDFK, TrainingDate
                      FROM AppInfo JOIN TrainInfo on (AppInfo.AppIDPK = TrainInfo.AppIDFK) JOIN
                               (SELECT AppIDFK, Max(TrainingDate) MaxDate, TrainID
                                              FROM TrainInfo
                                              GROUP by TrainID) on AppIDFK = AppIDPK
                      ORDER BY LName, FName

                       

                      Unfortunately, the CF 8 debugging output isn't telling me much either and doesn't show the sql being ran.

                      • 8. Re: Max Date from Sub Query
                        Dan Bracuk Level 5

                        When you have a subquery in your from clause, it needs an alias.