Find MAX value from multiple datetime columns

Question

Find MAX value from multiple datetime columns

dface_21

Mr or Mrs. 500

Points: 509
More actions
November 4, 2020 at 3:24 pm

#3804254

This may have an easy resolution but I'm having difficulty finding it. I'm creating a package that needs to take records that are new or updated since the last time the job run. I have 2 DateTime columns Insert_date and Last_Updated. I need records for newly inserted records and modified/last_updated records. How do I accomplish that with a SQL script and how do I construct that script or conditional split. I know I could use the MAX function but seem to only get back the latest Last_Updated records not the latest inserted records. Any help would be appreciated.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 1

WHERE (Insert_date > @LastJobRun OR Last_Updated > @LastJobRun)

dface_21 Mr or Mrs. 500 Points: 509 More actions · Answer 2

Thanks, Jonathon but I'm not getting any results. I'm new to SQL and SSIS learning as I go this is the query I'm using in the OL DB SOURCE for the package:

DECLARE @LastJobRun datetime;

SELECT [ID]

,[SETID]

,[ACCOUNT]

,[ACCT_DESCR]

,[ACCT_DESCRSHORT]

,[ACCOUNT_TYPE]

,[ACCT_TYPE_DESCR]

,[BALANCE_FWD_SW]

,[BUDGETARY_ONLY]

,[INSERT_DATE]

,[EFF_DATE]

,[ACCT_STATUS]

,[LAST_UPDATED]

FROM [DCRPT].[dbo].[AMOUNT]WHERE (INSERT_DATE > @LastJobRun OR LAST_UPDATED > @LastJobRun)

Phil Parkin SSC Guru Points: 246686 More actions · Answer 3

You need to set the value of the @LastJobRun parameter before executing that query.

Do you have this stored somewhere?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

dface_21 Mr or Mrs. 500 Points: 509 More actions · Answer 4

I do not. What would be the best way to go about that as a variable?

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 5

dface_21 wrote:

I do not. What would be the best way to go about that as a variable?

You might not need a variable and just use a subquery to get it.

Where is the value stored?

Phil Parkin SSC Guru Points: 246686 More actions · Answer 6

dface_21 wrote:

I do not. What would be the best way to go about that as a variable?

When the job runs, does it write to a log table? (Containing, for example, RunNumber, StartedAt, CompletedAt, IsSuccess)?

If you have a table like that, you can query it to get the most recent RunDate

SELECT TOP (1) StartedAt
FROM ETLLog
WHERE IsSuccess = 1
ORDER BY RunNumber DESC

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

dface_21 Mr or Mrs. 500 Points: 509 More actions · Answer 7

Currently, I don't have the value stored anywhere. How would I write it as a subquery?

dface_21 Mr or Mrs. 500 Points: 509 More actions · Answer 8

Phil I don't believe the job writes to a log table. This is a table that gets updated daily I want to get the updated records and the latest inserted records from this table extracted into a CVS file just newly inserted and changed records nothing else.

Jonathan AC Roberts SSCoach Points: 18257 More actions · Answer 9

dface_21 wrote:

Currently, I don't have the value stored anywhere.

How do you know when the job last ran?

dface_21 Mr or Mrs. 500 Points: 509 More actions · Answer 10

Ok, I got confused but once the package is created we will deploy it and schedule it to run in a SQL job.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 11

Jonathan AC Roberts wrote:

dface_21 wrote:
Currently, I don't have the value stored anywhere.
How do you know when the job last ran?

+1, if you don't know when it last ran, this is close to impossible.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 12

Could you

SELECT MAX(Last_Updated)

from your target table and use that instead?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

dface_21 Mr or Mrs. 500 Points: 509 More actions · Answer 13

If I use the query below I get the LAST_UPDATED fields but no newly INSERT_DATE records which I should get atleast one record.

SELECT t.[ID]

,t.[SETID]

,t.[ACCOUNT]

,t.[ACCT_DESCR]

,t.[ACCT_DESCRSHORT]

,t.[ACCOUNT_TYPE]

,t.[ACCT_TYPE_DESCR]

,t.[BALANCE_FWD_SW]

,t.[BUDGETARY_ONLY]

,t.[INSERT_DATE]

,t.[ACCT_STATUS]

,t.[EFF_DATE]

,t.[LAST_UPDATED]

FROM [DCRPT].[dbo].[AMOUNT] t

inner join (

select ID, MAX(INSERT_DATE) AS INSERT_DATE, MAX(LAST_UPDATED) AS LAST_UPDATED

FROM [dbo].[AMOUNT]

Group By [ID]

) tm on t.ID = tm.ID and t.INSERT_DATE = tm.INSERT_DATE and t.LAST_UPDATED = tm.LAST_UPDATED

Phil Parkin SSC Guru Points: 246686 More actions · Answer 14

Try something like this (I wasn't sure of the names of your source and target tables, so I'll leave that to you):

DECLARE @MaxInsertDate DATETIME
       ,@MaxLastUpdated DATETIME;

SELECT @MaxInsertDate = MAX(Insert_Date)
      ,@MaxLastUpdated = MAX(Last_Updated)
FROM TargetTable;

SELECT t.ID
      ,t.SETID
      ,t.ACCOUNT
      ,t.ACCT_DESCR
      ,t.ACCT_DESCRSHORT
      ,t.ACCOUNT_TYPE
      ,t.ACCT_TYPE_DESCR
      ,t.BALANCE_FWD_SW
      ,t.BUDGETARY_ONLY
      ,t.INSERT_DATE
      ,t.ACCT_STATUS
      ,t.EFF_DATE
      ,t.LAST_UPDATED
FROM SourceTable t
WHERE t.Insert_Date >= @MaxInsertDate
      OR t.Last_Updated >= @MaxLastUpdated;

This will bring in some rows which have already been loaded, so you'll need to handle that, but should not miss anything.

If rows can be deleted from the source table, you will need to handle that separately. This method finds only new and modified rows.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.