CODEWITHSUNDEEP
sqlsql-server
Jota Pardo
Jota Pardo

Reputation: 878

How to compare date fields between two tables and get the less or equal date from the second table

I have two tables. Table A and table B. Both of them have date fields. I need compare those fields and get a table C with the less or equal date between Table A and table B, taking into account that the table A is the main.

CONTEXT: I have in Table A Expiration of products, and in table B on business days. The user can update table B when it is determined that a date is not to be considered as a "business day". Then delete the date from table B and then go to table A to update all product expirations that were registered with that date and assign them the previous business day. So in my case I am creating table C, which contains the Id of table A and the working date less or equal to the date mentioned. Then I will make the respective update.

IF OBJECT_ID('tempdb..#tmpA') IS NOT NULL DROP TABLE #tmpA
IF OBJECT_ID('tempdb..#tmpB') IS NOT NULL DROP TABLE #tmpB


CREATE TABLE #tmpA(Id INT IDENTITY(100,1),Fecha date)

INSERT INTO #tmpA(Fecha)
VALUES
('20170101'),('20171003'),('20170504'),('2017-09-01')

SELECT * FROM #tmpA

Id          Fecha
----------- ----------
100         2017-01-01
101         2017-10-03
102         2017-05-04
103         2017-09-01

CREATE TABLE #tmpB(Id INT IDENTITY(1,4),Fecha date)

INSERT INTO #tmpB(Fecha)
VALUES
('20170101'),('20171001'),('20170504')

SELECT * FROM #tmpB

Id          Fecha
----------- ----------
1           2017-01-01
5           2017-10-01
9           2017-05-04

I want to get this result (The same number of records in table A):

Id          Fecha
----------- ----------
100         2017-01-01
101         2017-10-01 --> **this row is less than 2017-10-03**
102         2017-05-04
103         2017-05-04 --> **this row is less than 2017-09-01**

I tried to built some queries without results,

IF OBJECT_ID('tempdb..#tmpC') IS NOT NULL DROP TABLE #tmpC
SELECT A.* INTO #tmpC FROM #tmpA A LEFT JOIN #tmpB B ON A.Fecha = B.Fecha WHERE  B.Fecha IS NULL

SELECT * FROM #tmpC 

SELECT *
FROM #tmpA A INNER JOIN 
(
    SELECT * 
    FROM #tmpC
    GROUP BY id, Fecha

) AS Q ON MAX(Q.Fecha) <= A.Fecha

UPDATE:

NOTE. The Id column is simply an identity, but it does not mean that it should be related. The important thing is the dates.

Regards

Upvotes: 2

Views: 8095

Answers (4)

Snova
Snova

Reputation: 1

@JotaPardo WHERE 1=1 is used to basically make sure the query runs if the WHERE conditions don't hold up. 1=1 will equate to true so saying WHERE 1=1 or WHERE TRUE, and TRUE is always TRUE, ensures the query will have at least one WHERE clause condition that will always hold up.

Upvotes: 0

Eli
Eli

Reputation: 2608

While I'm not sure if this will scale well (if you have more than 100k rows) this will bring back the results which you want.
Theoretically, the correct way for you to do this, in a fashion which will scale well, would be to have a view where you utilize RANK() and join both of these tables together, though this was the quick and easy way. Please try this and let me know if it meets your requirements.
For your edification, I have left both of the dates in there for you to be able to compare them.

SELECT
    A.ID
    ,A.FECHA OLDDATE
    ,B.FECHA CORRECTDATE
FROM #TMPA A
LEFT OUTER JOIN #TMPB B ON 1=1
WHERE 1=1 
AND B.FECHA = (
    SELECT MAX(FECHA)
    FROM #TMPB
    WHERE FECHA <= A.FECHA)

Upvotes: 1

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can get minmum by union all

select id, min(fecha) from (
   select * from #tmpA
     union all
   select * from #tmpB
  ) a
 group by a.id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270061

Is this what you want?

select a.id,
       (case when b.fecha < a.fecha then b.fecha else a.fecha end) as fecha
from #tmpA a left join
     #tmpB b
     on a.id = b.id;

Upvotes: 0

Related Questions