CODEWITHSUNDEEP
sqlsql-servert-sql
ltsai
ltsai

Reputation: 757

SQL Cartesian Join

I am trying a table of transitions logged and a table of dates.

TABLE A:

date
2017-01-01
2017-01-02
2017-01-03
2017-01-04

TABLE B:

state | date_logged | requestno_i
  A      2017-01-01      req01
  B      2017-01-03      req01
  C      2017-01-04      req01

Expected Result:

   date      | state | date_logged | requestno_i
2017-01-01       A      2017-01-01      req01
2017-01-02       A      2017-01-01      req01
2017-01-03       B      2017-01-03      req01
2017-01-04       C      2017-01-04      req01

Here is my attempt:

SELECT 
A.date,
B.state,
B.date_logged,
B.requestno_i
FROM   TABLE_A A CROSS JOIN TABLE_B B
WHERE requestno_i='req01' and A.date>=B.date_logged

Upvotes: 0

Views: 104

Answers (1)

ZLK
ZLK

Reputation: 2884

One way of doing this would be with a CROSS APPLY instead of a CROSS JOIN. For example:

DECLARE @TableA TABLE ([Date] DATE);
INSERT @TableA ([Date]) VALUES ('2017-01-01'), ('2017-01-02'), ('2017-01-03'), ('2017-01-04');

DECLARE @TableB TABLE ([State] CHAR(1), Date_Logged DATE, RequestNo_i VARCHAR(10));
INSERT @TableB ([State], Date_Logged, RequestNo_i) VALUES ('A', '2017-01-01', 'req01'), ('B', '2017-01-03', 'req01'), ('C', '2017-01-04', 'req01');

SELECT A.[Date], B.[State], B.Date_Logged, B.RequestNo_i
FROM @TableA AS A
CROSS APPLY
(
    SELECT TOP 1 *
    FROM @TableB
    WHERE Date_Logged <= A.[Date]
    AND RequestNo_i = 'req01'
    ORDER BY Date_Logged DESC
) AS B;

Upvotes: 4

Related Questions