August 13, 2015 at 6:51 pm
Hello,
I am having trouble trying to design a query that helps me construct the relation between two tables. One table contains credit card information and the other contains purchase information. I am trying to create a CASE WHEN statement that is able to match on multiple conditions. I will try to simplify the example a bit. Imagine the tables have the following columns:
Credit_Card_Info
ID, First_Name, Status, Status_Date
Purchase_Info
ID, Credit_Card, Transaction_Date,
This is fairly oversimplified, but I'm trying to establish the relation between the two tables by replacing Credit Cards in the Purchase_Info table with actual ID numbers. So I want to create a query that matches credit cards (Credit_Card to First_Name) and also checks to see if the transaction date is less than or equal to the status date (some cards have been lost/stolen, closed, etc.). My query looks like:
SELECT CASE WHEN First_Name = Credit_Card THEN
CASE WHEN Transaction_Date <= Status_Date THEN Credit_Card_Info.ID
WHEN Status_Date IS NULL THEN Credit_Card_Info.ID
ELSE NULL
FROM Purchase_Info
INNER JOIN Credit_Card ON Credit_Card.First_Name = Purchase_Info.Credit_Card
Obviously, this doesn't work well. Essentially, most of the time there is a 1 to 1 match between credit cards, however some have been reissued because they have been lost or stolen so if I only match on that the program doesn't know which entry to take so I'm trying to layer in the fact that if the transaction was before the day the card was lost or stolen then it was that card that was used and afterwards the other card was being used.
Any help would be much appreciated. Thanks for the help and I apologize if this question is trivial.
Kevin
August 13, 2015 at 7:00 pm
Could you provide some sample dummy data that illustrates the problem and what query results should look like after the query is executed?
These key elements would help get a sample solution more quickly.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 13, 2015 at 7:47 pm
I hope this formats correctly. Here is sample data with the end result shown in the far right column:
IDCredit_Card Transaction_Date RESULT
13578 4539 4328 43812012-04-12 00:00:009368
23578 4539 4328 43812012-04-24 00:00:009368
33578 4539 4328 43812012-04-25 00:00:009368
43578 4539 4328 43812012-04-27 00:00:009368
53578 4539 4328 43812012-05-14 00:00:009368
63578 4539 4328 43812012-05-14 00:00:009368
73578 4539 4328 43812012-05-16 00:00:009368
83578 4539 4328 43812012-05-24 00:00:009368
93578 4539 4328 43812012-05-24 00:00:009368
103578 4539 4328 43812012-05-31 00:00:009368
113578 4539 4328 43812012-06-05 00:00:009368
123578 4539 4328 43812012-06-19 00:00:009368
133578 4539 4328 43812012-06-26 00:00:009368
143578 4539 4328 43812012-06-26 00:00:009368
153578 4539 4328 43812012-07-06 00:00:009368
163578 4539 4328 43812012-07-13 00:00:009368
173578 4539 4328 43812012-07-18 00:00:009368
183578 4539 4328 43812012-08-10 00:00:009369
193578 4539 4328 43812012-08-10 00:00:009369
203578 4539 4328 43812012-08-21 00:00:009369
213578 4539 4328 43812012-08-23 00:00:009369
223578 4539 4328 43812012-08-28 00:00:009369
233578 4539 4328 43812012-09-03 00:00:009369
243578 4539 4328 43812012-09-12 00:00:009369
253578 4539 4328 43812012-09-25 00:00:009369
263578 4539 4328 43812012-09-25 00:00:009369
273578 4539 4328 43812012-10-01 00:00:009369
283578 4539 4328 43812012-10-15 00:00:009369
293578 4539 4328 43812012-10-22 00:00:009369
303578 4539 4328 43812012-10-22 00:00:009369
313578 4539 4328 43812012-10-25 00:00:009369
323578 4539 4328 43812012-11-02 00:00:009369
333578 4539 4328 43812012-11-28 00:00:009369
343578 4539 4328 43812012-11-28 00:00:009369
353578 4539 4328 43812012-11-28 00:00:009369
363578 4539 4328 43812012-12-03 00:00:009369
373578 4539 4328 43812012-12-03 00:00:009369
383578 4539 4328 43812012-12-05 00:00:009369
393578 4539 4328 43812012-12-14 00:00:009369
403578 4539 4328 43812012-12-14 00:00:009369
Here is the Credit Card Table:
IDFirst_Name Status Status_Change_Date
93683578 4539 4328 4381LostOrStolen2012-07-19 00:00:00
93693578 4539 4328 4381Closed 2014-01-09 00:00:00
So any transaction before 2012-07-19 would have an ID of 9368 and anything after would have an ID of 9369. Again sorry if this doesn't format correctly. Thanks for the help.
Kevin
August 14, 2015 at 12:09 am
I think this is right... and it's missing the relationship between Card and Transaction...
CREATE TABLE CCTransaction (
TransactionID INT,
Credit_Card CHAR(19),
TransactionDate DATE,
Result INT
);
INSERT INTO CCTransaction
SELECT 1 AS ID,'3578 4539 4328 4381' AS Credit_Card,'2012-04-12 00:00:00' AS Transaction_Date,9368 AS Result
UNION ALL SELECT 2,'3578 4539 4328 4381','2012-04-24 00:00:00',9368
UNION ALL SELECT 3,'3578 4539 4328 4381','2012-04-25 00:00:00',9368
UNION ALL SELECT 4,'3578 4539 4328 4381','2012-04-27 00:00:00',9368
UNION ALL SELECT 5,'3578 4539 4328 4381','2012-05-14 00:00:00',9368
UNION ALL SELECT 6,'3578 4539 4328 4381','2012-05-14 00:00:00',9368
UNION ALL SELECT 7,'3578 4539 4328 4381','2012-05-16 00:00:00',9368
UNION ALL SELECT 8,'3578 4539 4328 4381','2012-05-24 00:00:00',9368
UNION ALL SELECT 9,'3578 4539 4328 4381','2012-05-24 00:00:00',9368
UNION ALL SELECT 10,'3578 4539 4328 4381','2012-05-31 00:00:00',9368
UNION ALL SELECT 11,'3578 4539 4328 4381','2012-06-05 00:00:00',9368
UNION ALL SELECT 12,'3578 4539 4328 4381','2012-06-19 00:00:00',9368
UNION ALL SELECT 13,'3578 4539 4328 4381','2012-06-26 00:00:00',9368
UNION ALL SELECT 14,'3578 4539 4328 4381','2012-06-26 00:00:00',9368
UNION ALL SELECT 15,'3578 4539 4328 4381','2012-07-06 00:00:00',9368
UNION ALL SELECT 16,'3578 4539 4328 4381','2012-07-13 00:00:00',9368
UNION ALL SELECT 17,'3578 4539 4328 4381','2012-07-18 00:00:00',9368
UNION ALL SELECT 18,'3578 4539 4328 4381','2012-08-10 00:00:00',9369
UNION ALL SELECT 19,'3578 4539 4328 4381','2012-08-10 00:00:00',9369
UNION ALL SELECT 20,'3578 4539 4328 4381','2012-08-21 00:00:00',9369
UNION ALL SELECT 21,'3578 4539 4328 4381','2012-08-23 00:00:00',9369
UNION ALL SELECT 22,'3578 4539 4328 4381','2012-08-28 00:00:00',9369
UNION ALL SELECT 23,'3578 4539 4328 4381','2012-09-03 00:00:00',9369
UNION ALL SELECT 24,'3578 4539 4328 4381','2012-09-12 00:00:00',9369
UNION ALL SELECT 25,'3578 4539 4328 4381','2012-09-25 00:00:00',9369
UNION ALL SELECT 26,'3578 4539 4328 4381','2012-09-25 00:00:00',9369
UNION ALL SELECT 27,'3578 4539 4328 4381','2012-10-01 00:00:00',9369
UNION ALL SELECT 28,'3578 4539 4328 4381','2012-10-15 00:00:00',9369
UNION ALL SELECT 29,'3578 4539 4328 4381','2012-10-22 00:00:00',9369
UNION ALL SELECT 30,'3578 4539 4328 4381','2012-10-22 00:00:00',9369
UNION ALL SELECT 31,'3578 4539 4328 4381','2012-10-25 00:00:00',9369
UNION ALL SELECT 32,'3578 4539 4328 4381','2012-11-02 00:00:00',9369
UNION ALL SELECT 33,'3578 4539 4328 4381','2012-11-28 00:00:00',9369
UNION ALL SELECT 34,'3578 4539 4328 4381','2012-11-28 00:00:00',9369
UNION ALL SELECT 35,'3578 4539 4328 4381','2012-11-28 00:00:00',9369
UNION ALL SELECT 36,'3578 4539 4328 4381','2012-12-03 00:00:00',9369
UNION ALL SELECT 37,'3578 4539 4328 4381','2012-12-03 00:00:00',9369
UNION ALL SELECT 38,'3578 4539 4328 4381','2012-12-05 00:00:00',9369
UNION ALL SELECT 39,'3578 4539 4328 4381','2012-12-14 00:00:00',9369
UNION ALL SELECT 40,'3578 4539 4328 4381','2012-12-14 00:00:00',9369;
CREATE TABLE CCard (
Result INT,
CCNo CHAR(20) PRIMARY KEY,
CCStatus VARCHAR(20),
StatusChangeDate DATETIME);
GO
INSERT INTO CCard VALUES ('9368', '3578 4539 4328 4381','LostOrStolen','2012-07-19 00:00:00');
INSERT INTO CCard VALUES ('9369', '3578 4539 4328 4381','Closed', '2014-01-09 00:00:00');
August 14, 2015 at 1:22 am
kevin.ellis86 (8/13/2015)
So any transaction before 2012-07-19 would have an ID of 9368 and anything after would have an ID of 9369. Again sorry if this doesn't format correctly. Thanks for the help.
Kevin
Quick suggestion
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.CCTransaction') IS NOT NULL DROP TABLE dbo.CCTransaction;
CREATE TABLE dbo.CCTransaction (
TransactionID INT,
Credit_Card CHAR(19),
TransactionDate DATE,
Result INT
);
INSERT INTO dbo.CCTransaction(TransactionID,Credit_Card,TransactionDate,Result)
SELECT 1 AS ID, '3578 4539 4328 4381' AS Credit_Card, '2012-04-12 00:00:00' AS Transaction_Date, 9368 AS Result
UNION ALL SELECT 2, '3578 4539 4328 4381', '2012-04-24 00:00:00', 9368
UNION ALL SELECT 3, '3578 4539 4328 4381', '2012-04-25 00:00:00', 9368
UNION ALL SELECT 4, '3578 4539 4328 4381', '2012-04-27 00:00:00', 9368
UNION ALL SELECT 5, '3578 4539 4328 4381', '2012-05-14 00:00:00', 9368
UNION ALL SELECT 6, '3578 4539 4328 4381', '2012-05-14 00:00:00', 9368
UNION ALL SELECT 7, '3578 4539 4328 4381', '2012-05-16 00:00:00', 9368
UNION ALL SELECT 8, '3578 4539 4328 4381', '2012-05-24 00:00:00', 9368
UNION ALL SELECT 9, '3578 4539 4328 4381', '2012-05-24 00:00:00', 9368
UNION ALL SELECT 10, '3578 4539 4328 4381', '2012-05-31 00:00:00', 9368
UNION ALL SELECT 11, '3578 4539 4328 4381', '2012-06-05 00:00:00', 9368
UNION ALL SELECT 12, '3578 4539 4328 4381', '2012-06-19 00:00:00', 9368
UNION ALL SELECT 13, '3578 4539 4328 4381', '2012-06-26 00:00:00', 9368
UNION ALL SELECT 14, '3578 4539 4328 4381', '2012-06-26 00:00:00', 9368
UNION ALL SELECT 15, '3578 4539 4328 4381', '2012-07-06 00:00:00', 9368
UNION ALL SELECT 16, '3578 4539 4328 4381', '2012-07-13 00:00:00', 9368
UNION ALL SELECT 17, '3578 4539 4328 4381', '2012-07-18 00:00:00', 9368
UNION ALL SELECT 18, '3578 4539 4328 4381', '2012-08-10 00:00:00', 9369
UNION ALL SELECT 19, '3578 4539 4328 4381', '2012-08-10 00:00:00', 9369
UNION ALL SELECT 20, '3578 4539 4328 4381', '2012-08-21 00:00:00', 9369
UNION ALL SELECT 21, '3578 4539 4328 4381', '2012-08-23 00:00:00', 9369
UNION ALL SELECT 22, '3578 4539 4328 4381', '2012-08-28 00:00:00', 9369
UNION ALL SELECT 23, '3578 4539 4328 4381', '2012-09-03 00:00:00', 9369
UNION ALL SELECT 24, '3578 4539 4328 4381', '2012-09-12 00:00:00', 9369
UNION ALL SELECT 25, '3578 4539 4328 4381', '2012-09-25 00:00:00', 9369
UNION ALL SELECT 26, '3578 4539 4328 4381', '2012-09-25 00:00:00', 9369
UNION ALL SELECT 27, '3578 4539 4328 4381', '2012-10-01 00:00:00', 9369
UNION ALL SELECT 28, '3578 4539 4328 4381', '2012-10-15 00:00:00', 9369
UNION ALL SELECT 29, '3578 4539 4328 4381', '2012-10-22 00:00:00', 9369
UNION ALL SELECT 30, '3578 4539 4328 4381', '2012-10-22 00:00:00', 9369
UNION ALL SELECT 31, '3578 4539 4328 4381', '2012-10-25 00:00:00', 9369
UNION ALL SELECT 32, '3578 4539 4328 4381', '2012-11-02 00:00:00', 9369
UNION ALL SELECT 33, '3578 4539 4328 4381', '2012-11-28 00:00:00', 9369
UNION ALL SELECT 34, '3578 4539 4328 4381', '2012-11-28 00:00:00', 9369
UNION ALL SELECT 35, '3578 4539 4328 4381', '2012-11-28 00:00:00', 9369
UNION ALL SELECT 36, '3578 4539 4328 4381', '2012-12-03 00:00:00', 9369
UNION ALL SELECT 37, '3578 4539 4328 4381', '2012-12-03 00:00:00', 9369
UNION ALL SELECT 38, '3578 4539 4328 4381', '2012-12-05 00:00:00', 9369
UNION ALL SELECT 39, '3578 4539 4328 4381', '2012-12-14 00:00:00', 9369
UNION ALL SELECT 40, '3578 4539 4328 4381', '2012-12-14 00:00:00', 9369;
GO
IF OBJECT_ID(N'dbo.CCard') IS NOT NULL DROP TABLE dbo.CCard;
GO
CREATE TABLE dbo.CCard (
Result INT PRIMARY KEY,
CCNo CHAR(20),
CCStatus VARCHAR(20),
StatusChangeDate DATETIME);
GO
INSERT INTO CCard (Result,CCNo,CCStatus,StatusChangeDate)
VALUES ('9368', '3578 4539 4328 4381', 'LostOrStolen', '2012-07-19 00:00:00')
,('9369', '3578 4539 4328 4381', 'Closed', '2014-01-09 00:00:00');
;WITH BASE_DATA AS
(
SELECT
CCT.TransactionID AS TransactionID
,ROW_NUMBER() OVER
(
PARTITION BY CCT.TransactionID
ORDER BY CCT.TransactionDate
,CC.StatusChangeDate
) AS CCT_RID
,CCT.Credit_Card AS CCT_Credit_Card
,CCT.TransactionDate AS CCT_TransactionDate
,CCT.Result AS CCT_Result
,CC.Result AS CC_Result
,CC.CCNo AS CC_CCNo
,CC.CCStatus AS CC_CCStatus
,CC.StatusChangeDate AS CC_StatusChangeDate
FROM dbo.CCTransaction CCT
CROSS APPLY dbo.CCard CC
WHERE CCT.Credit_Card = CC.CCNo
AND CCT.TransactionDate <= CC.StatusChangeDate
)
SELECT
BD.TransactionID
,BD.CCT_RID
,BD.CCT_Credit_Card
,BD.CCT_TransactionDate
,BD.CCT_Result
,BD.CC_Result
,BD.CC_CCNo
,BD.CC_CCStatus
,BD.CC_StatusChangeDate
FROM BASE_DATA BD
WHERE BD.CCT_RID = 1
;
Results
TransactionID CCT_RID CCT_Credit_Card CCT_TransactionDate CCT_Result CC_Result CC_CCNo CC_CCStatus CC_StatusChangeDate
------------- --------- ------------------- ------------------- ----------- ----------- -------------------- -------------- -----------------------
1 1 3578 4539 4328 4381 2012-04-12 9368 9368 3578 4539 4328 4381 LostOrStolen 2012-07-19 00:00:00.000
2 1 3578 4539 4328 4381 2012-04-24 9368 9368 3578 4539 4328 4381 LostOrStolen 2012-07-19 00:00:00.000
3 1 3578 4539 4328 4381 2012-04-25 9368 9368 3578 4539 4328 4381 LostOrStolen 2012-07-19 00:00:00.000
4 1 3578 4539 4328 4381 2012-04-27 9368 9368 3578 4539 4328 4381 LostOrStolen 2012-07-19 00:00:00.000
5 1 3578 4539 4328 4381 2012-05-14 9368 9368 3578 4539 4328 4381 LostOrStolen 2012-07-19 00:00:00.000
6 1 3578 4539 4328 4381 2012-05-14 9368 9368 3578 4539 4328 4381 LostOrStolen 2012-07-19 00:00:00.000
7 1 3578 4539 4328 4381 2012-05-16 9368 9368 3578 4539 4328 4381 LostOrStolen 2012-07-19 00:00:00.000
8 1 3578 4539 4328 4381 2012-05-24 9368 9368 3578 4539 4328 4381 LostOrStolen 2012-07-19 00:00:00.000
9 1 3578 4539 4328 4381 2012-05-24 9368 9368 3578 4539 4328 4381 LostOrStolen 2012-07-19 00:00:00.000
10 1 3578 4539 4328 4381 2012-05-31 9368 9368 3578 4539 4328 4381 LostOrStolen 2012-07-19 00:00:00.000
11 1 3578 4539 4328 4381 2012-06-05 9368 9368 3578 4539 4328 4381 LostOrStolen 2012-07-19 00:00:00.000
12 1 3578 4539 4328 4381 2012-06-19 9368 9368 3578 4539 4328 4381 LostOrStolen 2012-07-19 00:00:00.000
13 1 3578 4539 4328 4381 2012-06-26 9368 9368 3578 4539 4328 4381 LostOrStolen 2012-07-19 00:00:00.000
14 1 3578 4539 4328 4381 2012-06-26 9368 9368 3578 4539 4328 4381 LostOrStolen 2012-07-19 00:00:00.000
15 1 3578 4539 4328 4381 2012-07-06 9368 9368 3578 4539 4328 4381 LostOrStolen 2012-07-19 00:00:00.000
16 1 3578 4539 4328 4381 2012-07-13 9368 9368 3578 4539 4328 4381 LostOrStolen 2012-07-19 00:00:00.000
17 1 3578 4539 4328 4381 2012-07-18 9368 9368 3578 4539 4328 4381 LostOrStolen 2012-07-19 00:00:00.000
18 1 3578 4539 4328 4381 2012-08-10 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
19 1 3578 4539 4328 4381 2012-08-10 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
20 1 3578 4539 4328 4381 2012-08-21 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
21 1 3578 4539 4328 4381 2012-08-23 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
22 1 3578 4539 4328 4381 2012-08-28 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
23 1 3578 4539 4328 4381 2012-09-03 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
24 1 3578 4539 4328 4381 2012-09-12 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
25 1 3578 4539 4328 4381 2012-09-25 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
26 1 3578 4539 4328 4381 2012-09-25 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
27 1 3578 4539 4328 4381 2012-10-01 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
28 1 3578 4539 4328 4381 2012-10-15 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
29 1 3578 4539 4328 4381 2012-10-22 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
30 1 3578 4539 4328 4381 2012-10-22 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
31 1 3578 4539 4328 4381 2012-10-25 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
32 1 3578 4539 4328 4381 2012-11-02 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
33 1 3578 4539 4328 4381 2012-11-28 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
34 1 3578 4539 4328 4381 2012-11-28 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
35 1 3578 4539 4328 4381 2012-11-28 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
36 1 3578 4539 4328 4381 2012-12-03 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
37 1 3578 4539 4328 4381 2012-12-03 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
38 1 3578 4539 4328 4381 2012-12-05 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
39 1 3578 4539 4328 4381 2012-12-14 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
40 1 3578 4539 4328 4381 2012-12-14 9369 9369 3578 4539 4328 4381 Closed 2014-01-09 00:00:00.000
August 14, 2015 at 9:29 am
These results look like how to create the tables which I don't need help with. Suppose these two tables had no relationship between them built yet and I wanted to build it i.e. the RESULT column did not exist. How would I set up a CASE WHEN statement to get the credit card ID from the one table and apply it to each purchase in the Purchase_Info table based on the conditions I outlined before?
Am I missing something?
Kevin
August 14, 2015 at 9:39 am
kevin.ellis86 (8/14/2015)
These results look like how to create the tables which I don't need help with. Suppose these two tables had no relationship between them built yet and I wanted to build it i.e. the RESULT column did not exist. How would I set up a CASE WHEN statement to get the credit card ID from the one table and apply it to each purchase in the Purchase_Info table based on the conditions I outlined before?Am I missing something?
Kevin
Guess you are, the code does what your conditions outlined but it also contains the setup needed in order to run the code ( hint: the solution starts with a ;WITH cte clause.)
😎
August 14, 2015 at 10:28 pm
Eirikur Eiriksson,
Thanks for the help. Your solution was perfect for what I needed. I finally understood what you did. I was confused because I had never seen CROSS APPLY and I end up using the WITH clause rarely in my work. Anyways, thanks for the help.
Kevin
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply