CODEWITHSUNDEEP
sqlsql-server
Milacay
Milacay

Reputation: 1497

SQL Update Or Insert By Comparing Dates

I am trying to do the UPDATE or INSERT, but I am not sure if this is possible without using loop. Here is the example:

Says, I have this SQL below in which I joined two tables: tblCompany and tblOrders.

SELECT CompanyID, CompanyName, c.LastSaleDate, o.SalesOrderID, o.SalesPrice
, DATEADD(m, -6, GETDATE()) AS DateLast6MonthFromToday
FROM dbo.tblCompany c
CROSS APPLY (
    SELECT TOP 1 SalesOrderID, SalesPrice
    FROM dbo.tblOrders o
    WHERE c.CompanyID = o.CompanyID
    ORDER BY SalesOrderID DESC
    ) AS a
WHERE Type = 'End-User'

Sample Result:

CompanyID, SalesOrderID, SalesPrice, LastSalesDate, DateLast6MonthFromToday
101        10001         50          2/01/2016   10/20/2016
102        10002         80          12/01/2016  10/20/2016
103        10003         80          5/01/2016   10/20/2016

What I am trying to do is comparing the LastSalesDate and the DateLast6MonthFromToday. Condition is below:

As the above sample result, the query will only update SalesOrderID 10001 and 10003. And For Company 102, NO insert since the LastSaleDate is greater, then just do the UPDATE for the SalesOrderID.

I know it is probably can be done if I create a Cursor to loop through every record and do the comparison then Update or Insert, but I wonder if there is another way perform this without the loop since I have around 20K records.

Sorry for the confusion,

Upvotes: 1

Views: 172

Answers (1)

Evgeny
Evgeny

Reputation: 637

I don't know your tables structure and your data types. Also I know nothing about duplicates and join ralationships between this 2 tables. But I want only show how it works on next example:

use [your test db];
go

create table dbo.tblCompany
(
    companyid int,
    companyname varchar(max),
    lastsaledate datetime,
    [type] varchar(max)
);

create table dbo.tblOrders 
(
    CompanyID int, 
    SalesOrderID int, 
    SalesPrice float
);


insert into dbo.tblCompany 
values
    (1, 'Avito', '2016-01-01', 'End-User'),
    (2, 'BMW', '2016-05-01', 'End-User'),
    (3, 'PornHub', '2017-01-01', 'End-User')

insert into dbo.tblOrders
values
    (1, 1, 500),
    (1, 2, 700),
    (1, 3, 900),
    (2, 1, 500),
    (2, 2, 700),
    (2, 3, 900),
    (3, 1, 500),
    (3, 2, 700),
    (3, 3, 900)

declare @column_1_value int = 5;
declare @column_2_value int = 777;

with cte as (
    select
        CompanyID,
        SalesOrderID,
        SalesPrice 
    from (
        select 
            CompanyID,
            SalesOrderID, 
            SalesPrice, 
            row_number() over(partition by CompanyID order by SalesOrderId desc) as rn
        from 
            dbo.tblOrders
    ) t
    where rn = 1
)

merge cte as target 
using (select * from dbo.tblCompany where [type] = 'End-User')  as source
    on target.companyid = source.companyid
    and source.lastsaledate >= dateadd(month, -6, getdate())
when matched 
    then update set target.salesprice = 1111
when not matched
    then insert (
        CompanyID,
        SalesOrderID,
        SalesPrice
    )
    values (
        source.CompanyId,
        @column_1_value,
        @column_2_value
    );

select * from dbo.tblOrders

If you will give me an information, then I can prepare target and source tables properly.

Upvotes: 1

Related Questions