CODEWITHSUNDEEP
sqldatetimemax
Chris
Chris

Reputation: 357

SQL to get the max date and time from two DateTime Columns

I have an SQL query I'm struggling with in Yellowfin (so I'm not sure of the SQL version).

I want to get the max revision from a table, however the table contains a date field and a time field (both in DateTime format).

What I've written so far is:

SELECT 
      a.MemberNo, 
      HealthScale, 
      status,
      a.EffDateTime = DATEADD(day, 0, DATEDIFF(day, 0, EffDate)) +
        DATEADD(day, 0 - DATEDIFF(day, 0, EffDate), EffTime)
FROM MembershipPlans AS a 
INNER JOIN
(
SELECT 
      MemberNo, 
      MAX(EffDateTime = DATEADD(day, 0, DATEDIFF(day, 0, EffDate)) +
        DATEADD(day, 0 - DATEDIFF(day, 0, EffDate), EffTime) 
FROM MembershipPlans GROUP BY MemberNo
) AS b
ON a.MemberNo = b.MemberNo AND a.EffDateTime = b.EffDateTime

and I get an error message:

SQL statement is invalid. The error message returned from the database was: Incorrect syntax near '='.

My desired result is to get one row per member number showing the max effdate and the matching healthscale and status. Any assistance would be greatly appreciated

Upvotes: 1

Views: 1737

Answers (1)

Rigel1121
Rigel1121

Reputation: 2016

Your query should be like this:

  SELECT a.MemberNo,HealthScale,status,a.EffDateTime, b.EffDateTime FROM
  (SELECT 
        MemberNo, 
        HealthScale, 
        status,
        DATEADD(day, 0, DATEDIFF(day, 0, EffDate)) +
        DATEADD(day, 0 - DATEDIFF(day, 0, EffDate), EffTime) AS EffDateTime
  FROM MembershipPlans) AS a 
  INNER JOIN
  (SELECT 
        MemberNo, 
        MAX(DATEADD(day, 0, DATEDIFF(day, 0, EffDate)) +
        DATEADD(day, 0 - DATEDIFF(day, 0, EffDate), EffTime)) AS EffDateTime 
   FROM MembershipPlans GROUP BY MemberNo) AS b
   ON a.MemberNo = b.MemberNo

Upvotes: 1

Related Questions