sql - Retrieving Most Recent Status Value before A given transaction date - Stack Overflow

admin管理员组

文章数量:1291349

I have two tables, one that contains a customer's status as of a given date and another table that contains a customer's transaction history. For a given transaction date, I want to pull back what their status was on that transaction date (based on the most recent insert date (prior to the tran date) in the status table).

I've tried a few times with self-joins and things and I'm running into a wall. I appreciate any help in advance.

Working with Snowflake SQL here.

Status Table

ACCT	INSERT_DATE	STATUS
123	2023-05-31	GOLD
123	2023-03-01	SILVER

I have two tables, one that contains a customer's status as of a given date and another table that contains a customer's transaction history. For a given transaction date, I want to pull back what their status was on that transaction date (based on the most recent insert date (prior to the tran date) in the status table).

I've tried a few times with self-joins and things and I'm running into a wall. I appreciate any help in advance.

Working with Snowflake SQL here.

Status Table

ACCT	INSERT_DATE	STATUS
123	2023-05-31	GOLD
123	2023-03-01	SILVER

Tran Table

ACCT	TRAN_DATE	AMT
123	2023-06-30	400
123	2023-04-01	222

Desired Table

ACCT	TRAN_DATE	STATUS
123	2023-06-30	GOLD
123	2023-04-01	SILVER

• sql
• snowflake-cloud-data-platform

Share Improve this question Follow edited Feb 13 at 17:01 samhita 3,61522 gold badges1111 silver badges1818 bronze badges asked Feb 13 at 16:09 user15260186user15260186 5355 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

Conceptually same as above answer, here is the snowflake version since you mentioned it is for Snowflake.

1.We join the Tran table with the Status table to get all possible matches where the account numbers are the same, and the status INSERT_DATE is on or before the TRAN_DATE.

2.Then a rank is assigned for the same account and TRAN_DATE, based on the status INSERT_DATE, ordering them from the most recent to the oldest, so recent INSERT_DATE gets a rank of 1.

3.This is then filtered using QUALIFY to fetch the most recent status for each TRAN_DATE.

SELECT T.ACCT, T.TRAN_DATE, S.STATUS
FROM
TRAN T
LEFT JOIN 
STATUS S
ON T.ACCT = S.ACCT
AND S.INSERT_DATE <= T.TRAN_DATE
QUALIFY ROW_NUMBER() OVER (PARTITION BY T.ACCT, T.TRAN_DATE ORDER BY S.INSERT_DATE DESC) = 1
ORDER BY T.TRAN_DATE DESC;

Generates

As you haven't specified DB you use, here is an example for SQL Server

SELECT T.ACCT,T.TRAN_DATE,X.STATUS
FROM TRANSACTIONS_TABLE AS T
CROSS APPLY
(
  SELECT TOP 1 S.STATUS
  FROM STATUS_TABLE AS S
  WHERE T.ACCT=S.ACCT
  AND S.INSERT_DATE<=T.TRAN_DATE
  ORDER BY S.INSERT_DATE DESC
)AS X

本文标签： sqlRetrieving Most Recent Status Value before A given transaction dateStack Overflow