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.
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]
)