Right join multiple key where mutiple key is null

Question

I need help from captain obvious I suppose. I'm trying to Insert data from a table into a temptable. Ok this is easy

I need to insert the data we got today and the data we got 10 days ago. The where clause may aford it, th's okay

What for me is hard is to insert the data of today only if it does not appear in the data 10 days ago

An exemple of the table I use ([datatable]) :

Date    Purchase    Line_Purchase
---------------------------------------------------------------------------
2017-04-29    0000002    01
2017-04-29    0000002    02
2017-04-29    0000003    01
2017-04-29    0000003    02
2017-04-29    0000003    03
2017-04-29    0000004    01
2017-04-29    0000005    01
2017-04-19    0000001    01
2017-04-19    0000001    02
2017-04-19    0000001    03
2017-04-19    0000002    01
2017-04-19    0000002    02

My desired table temptable:

Input_date    Purchase    Line_Purchase
-------------------------------------------------------------------------
2017-04-19    0000001    01
2017-04-19    0000001    02
2017-04-19    0000001    03
2017-04-19    0000002    01
2017-04-19    0000002    02
2017-04-29    0000003    01
2017-04-29    0000003    02
2017-04-29    0000003    03
2017-04-29    0000004    01
2017-04-29    0000005    01

Is there any request possible in SQL that can change that ?

I tried this way

INSERT INTO #TEMPTABLE
    (Input_date ,Purchase ,Line_Purchase)
SELECT 
    table.Date
    ,table.Purchase
    ,table.Line_Purchase
FROM
    datatable table
WHERE
    convert(date, table.Date) = convert(date, GETDATE() - 10)


INSERT INTO #TEMPTABLE
    (Input_date ,Purchase ,Line_Purchase)
SELECT 
    table.Date
    ,table.Purchase
    ,table.Line_Purchase
FROM
    datatable table
    RIGHT JOIN #TEMPTABLE temp
        on table.Purchase = temp.Purchase and table.Line_Purchase = temp.Line_Purchase
WHERE
    convert(date, table.Date) = convert(date, GETDATE())
    AND (temp.Purchase is null AND temp.Line_Purchase is null)

Thanks in advance

SqlZim · Accepted Answer · 2017-09-26T13:07:54.083

You can do this with not exists():

select date as Input_date, Purchase, Line_Purchase
into #temptable
from t
where date = '2017-04-19' --convert(date, getdate() - 10);

insert into #temptable (Input_date, Purchase, Line_Purchase)
select *
from t
where date = '2017-04-29'
  and not exists (
    select 1
    from t as i
    where i.purchase=t.purchase
      and i.line_purchase=t.line_purchase
      and i.date = '2017-04-19' --convert(date, getdate() - 10)
    );

select * 
from #temptable;

rextester demo: http://rextester.com/SAQSG21367

returns:

+------------+----------+---------------+
| Input_Date | Purchase | Line_Purchase |
+------------+----------+---------------+
| 2017-04-19 |  0000001 |            01 |
| 2017-04-19 |  0000001 |            02 |
| 2017-04-19 |  0000001 |            03 |
| 2017-04-19 |  0000002 |            01 |
| 2017-04-19 |  0000002 |            02 |
| 2017-04-29 |  0000003 |            01 |
| 2017-04-29 |  0000003 |            02 |
| 2017-04-29 |  0000003 |            03 |
| 2017-04-29 |  0000004 |            01 |
| 2017-04-29 |  0000005 |            01 |
+------------+----------+---------------+

Optionally, if you are doing both of these operations at the same time you can do it in the same query using a derived table/subquery or common table expression with row_number() ;

;with cte as (
select date, Purchase, Line_Purchase
  , rn = row_number() over (partition by Purchase,Line_Purchase order by date)
 from t
--where date in ('2017-09-26','2017-09-16') 
where date in (convert(date, getdate()), convert(date, getdate()-10))
)
select date as Input_date, Purchase, Line_Purchase
into #temptable
from cte
where rn = 1

select *
from #temptable;

rextester demo: http://rextester.com/QMF5992

returns:

+------------+----------+---------------+
| Input_date | Purchase | Line_Purchase |
+------------+----------+---------------+
| 2017-09-16 |  0000001 |            01 |
| 2017-09-16 |  0000001 |            02 |
| 2017-09-16 |  0000001 |            03 |
| 2017-09-16 |  0000002 |            01 |
| 2017-09-16 |  0000002 |            02 |
| 2017-09-26 |  0000003 |            01 |
| 2017-09-26 |  0000003 |            02 |
| 2017-09-26 |  0000003 |            03 |
| 2017-09-26 |  0000004 |            01 |
| 2017-09-26 |  0000005 |            01 |
+------------+----------+---------------+

select 1 from t as i I don't understand why select 1 and not select * — Valentin C, Sep 26 '17 at 13:03
I use `select 1` out of habit. Both `exists()` and `not exists()` do not return rows, so you could use `select null`,`select 1`, `select *`, or even `select 1/0`. Based on this article [EXISTS Subqueries: SELECT 1 vs. SELECT * - Conor Cunningham](http://www.sqlskills.com/blogs/conor/exists-subqueries-select-1-vs-select/) using `select 1` will avoid having to examine any unneeded metadata for that table during query compilation. [EXISTS Subqueries: SELECT 1 vs. SELECT * - Martin Smith](https://stackoverflow.com/a/6140367/2333499) ran tests that show no difference in actual performance. — SqlZim, Sep 26 '17 at 13:09

Right join multiple key where mutiple key is null

1 Answers1