sqlsql-serversql-server-2019

Find records using max date but exclude time


I need a query to pull records from a SQL Server table based on the max date in the InsertDTS column and the date should not be included in the output.

The InsertDTS column is defined as Datetime. I need it to pull everything with the max date, but ignore the time, since records can be loaded throughout the day. I have the query below but it is pulling only records with the most recent date and time.

SELECT
    [Payer],
    [File],
    [Data_Rows],
    [Amt_Billed],
    [Amt_Paid] 
FROM
    [Customer].[dbo].[Billing] 
WHERE
    InsertDTS = (SELECT MAX(InsertDTS) 
                 FROM [Customer].[dbo].[Billing])

I tried using a CAST in the WHERE clause, but could not get it to work.


Solution

  • From a performance perspective its best not to run functions against columns in your where clause because it makes the query unsargable i.e. unable to use indexes. Therefore its best to use a date window rather than a specific date e.g.

    select
      [Payer]
      , [File]
      , [Data_Rows]
      , [Amt_Billed]
      , [Amt_Paid] 
    from [Customer].[dbo].[Billing] 
    where InsertDTS >= (
      select convert(date, max(InsertDTS))
      from [Customer].[dbo].[Billing]
    )
    

    and it works equally well with cast e.g.

    select
      [Payer]
      , [File]
      , [Data_Rows]
      , [Amt_Billed]
      , [Amt_Paid] 
    from [Customer].[dbo].[Billing] 
    where InsertDTS >= (
      select cast(max(InsertDTS) as date)
      from [Customer].[dbo].[Billing]
    )