CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Jason
Jason

Reputation: 305

Get max date and max time

I'd like to get the t.Notes associated with the s.SR_Service_RecID that has the most recent t.Date_Start and t.Time_Start - As there can be multiple time entries on a single date against a single ticket, I need to include the time as well to get the most recent single result.

DECLARE @SD DATETIME,
        @ED DATETIME    

SET     @SD = DATEADD(dd, -14, GETDATE())
SET     @ED = GETDATE()

SELECT s.SR_Service_RecID
     , t.Notes

  FROM SR_Service s
       LEFT JOIN Time_Entry t 
              ON t.SR_Service_RecID = s.SR_Service_RecID

 WHERE s.Date_Closed BETWEEN @SD AND @ED

I have tried 2 sub-queries in the WHERE clause, one for the t.Date_Start and one for the t.Time_Start to only select MAX results, but it would limit the results for some reason and there would be missing entries... probably not the right way to go?

    AND t.Date_Start IN
        (
            SELECT  MAX(t.Date_Start)
              FROM  Time_Entry t
             WHERE  t.SR_Service_RecID = s.SR_Service_RecID
        )
    AND t.Time_Start IN
        (
            SELECT  MAX(t.Time_Start)
              FROM  Time_Entry t
             WHERE  t.SR_Service_RecID = s.SR_Service_RecID
        )

Upvotes: 1

Views: 270

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16894

Use subquery with keyword EXISTS.The WHERE clause of the outer query tests whether the rows that are returned by the subquery exist.

SELECT s.SR_Service_RecID, t.Notes
FROM SR_Service s LEFT JOIN Time_Entry t ON t.SR_Service_RecID = s.SR_Service_RecID
WHERE s.Date_Closed BETWEEN @SD AND @ED
 AND EXISTS (
             SELECT 1
             FROM Time_Entry t2
             WHERE s.SR_Service_RecID = t2.SR_Service_RecID
             HAVING MAX(CAST(t2.Date_Start AS datetime) + t2.Time_Start) 
               = CAST(t.Date_Start AS datetime) + t.Time_Start
             )

Upvotes: 0

HABO
HABO

Reputation: 15816

I think this should be close. The mystery is how to combine whatever data types you have for your separate dates and times into a single DateTime value. That's left as an exercise for the OP.

select *
  from (
    select s.SR_Service_RecID, t.Notes, t.Date_Start, t.Time_Start,
      Row_Number() over ( partition by s.SR_Service_RecID order by <combined date/time> desc) as [RN]
      from SR_Service as s left outer join
        Time_Entry as t on t.SR_Service_RecID = s.SR_Service_RecID
      where @SD <= s.Date_Closed and s.Date_Closed <= @ED
    ) as Bob
  where RN = 1

Upvotes: 1

Related Questions