complex MAX(date) query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

complex MAX(date) query

Author

Topic

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-07-16 : 19:52:52

I have a list of players with skill values as at various dates.
These records only exist when and where a skill changed.
In order to graph the historical changes of these player skills I wish to create a query that will list the latest skill value per player on specific dates (eg. every Sunday) - Meaning that if there is no value on the date for wanted Graph Date, then take the highest available date that is prior to the GraphDateWanted.
My tables are:
ArchivePlayers.PlayerIDCode (int)
ArchivePlayers.Firstname (nvarchar(50))
ArchivePlayers.LastName (nvarchar(50))

ArchivePlayerSkillsLong.PlayerIDCode (int)
ArchivePlayerSkillsLong.Batting(int)
ArchivePlayerSkillsLong.Bowling(int)
ArchivePlayerSkillsLong.Date (datetime)

GraphDateWanted.Date (datetime)

SELECT     ArchivePlayerSkillsLong.PlayersID, ArchivePlayers.FirstName, ArchivePlayers.LastName, MAX(ArchivePlayerSkillsLong.Date) AS MaxDate, 
                      GraphDateWanted.Date AS GraphDate
FROM         ArchivePlayerSkillsLong INNER JOIN
                      ArchivePlayers ON ArchivePlayerSkillsLong.ArchivePlayersID = ArchivePlayers.ArchivePlayersID LEFT OUTER JOIN
                      GraphDateWanted ON ArchivePlayerSkillsLong.Date <= GraphDateWanted.Date
GROUP BY ArchivePlayerSkillsLong.PlayersID, GraphDateWanted.Date, ArchivePlayers.FirstName, ArchivePlayers.LastName
HAVING      (GraphDateWanted.Date IS NOT NULL)

This code gives me the latest skills date < = graph date wanted. It seems to be correct on all the samples I have studied from it.
But, as soon as I select the batting or bowling .. the data is incorrect. I then get duplicate records for graph dates - not just the max available date prior to the Wanted Graph Date.
I do not know how to filter (or do the join) so that I do not get those duplicates. Can someone help, please?

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-07-16 : 22:30:43

Is it impossible to do this in one query ?

Mopani
Yak Posting Veteran

55 Posts

Posted - 2009-07-17 : 03:08:48

I seem to have code that works now..

SELECT     AP.FirstName, AP.LastName, MD.MaxDate, MD.GraphDate, APSL.Batting
FROM         ArchivePlayerSkillsLong AS APSL INNER JOIN
                      ArchivePlayers AS AP ON APSL.PlayersID = AP.PlayersID INNER JOIN
                          (SELECT     APSL.PlayersID, MAX(APSL.Date) AS MaxDate, GDW.Date AS GraphDate
                            FROM          ArchivePlayerSkillsLong AS APSL INNER JOIN
                                                   GraphDateWanted AS GDW ON APSL.Date <= GDW.Date
                            GROUP BY APSL.PlayersID, GDW.Date
                            HAVING      (GDW.Date IS NOT NULL)) AS MD ON MD.PlayersID = APSL.PlayersID AND APSL.Date = MD.MaxDate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-17 : 13:35:04

seems like this


SELECT gdw.GraphDateWanted
FROM GraphDateWanted gdw
CROSS APPLY (SELECT ROW_NUMBER() OVER(PARTITION BY ap.PlayerIDCode ORDER BY apsl.Date DESC) AS Seq, *
             FROM ArchivePlayers ap
             INNER JOIN ArchivePlayerSkillsLong apsl
             ON apsl.PlayerIDCode=ap.PlayerIDCode
             WHERE apsl.Date<=gdw.GraphDateWanted)tmp
WHERE tmp.Seq=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources