SQL Merge using date range on source

Question

I'm trying to create a query that will merge only Trips occurring in the next 3 days. The Query will be ran on Microsoft flows nightly to populate company app. Db is running on Azure SQL and [Trip Date] data type is datetime2.

I have tried between getdate() and getdate() + 3 with no luck, all records merged. I appreciate any help.

merge TblAppTrips as target
using TblTrips as source on (target.TripID = source.TripID)

When matched then
    update 
        set TripId = source.TripID, 
            cost = source.cost, 
            [Trip Date] = source.[Trip Date]  

when not matched then
    insert ([TripID], [cost], [Trip Date])
    values (source.[TripID], source.[cost], source.[Trip Date]);

score 0 · Answer 1 · answered Jun 20 '19 at 13:38

Try this - you may need to play around with the numbers a bit, but it should point you in the right direction. You basically need to limit the target window to the next three days when trying to update. The addition to the insert is only going to add new trips that occur in the next 3 days.

merge TblAppTrips as target
using TblTrips as source on (target.TripID = source.TripID)

When matched and datediff(day, getdate(), target.[Trip Date]) between 0 and 3
then
  update 
    set TripId = source.TripID, 
        cost = source.cost, 
        [Trip Date] = source.[Trip Date]  

when not matched and datediff(day, getdate(), source.[Trip Date]) between 0 and 3 
then
  insert ([TripID], [cost], [Trip Date])
  values (source.[TripID], source.[cost], source.[Trip Date]);

SQL Merge using date range on source

1 Answers1