May 3, 2013 at 6:06 am
Greetings everybody,
This is my first time posting.
I'm trying to think through a problem and can't figure it out.
There are two tables, Subscriptions and Payments, that share a number of fields:
[shared] CustomerId varchar(15)
[shared] Amount money
[shared] TransactionDate datetime
[not shared] UniqueId bigint
The records from Subscriptions need to be matched to those in Payments to ensure that all payments are recorded in the subscriptions.
Is there a SQL statement that would identify the payments that match for all customers at the same time?
For example:
One customer has 3 payments but only 2 were processed into subscriptions.
Sample data:
Payments
UniqueId CustomerId Amount Date
1 ABCD 25 1/1/13
7 ABCD 25 1/1/13
25 ABCD 25 1/1/13
Subscriptions
UniqueId CustomerId Amount Date
22 ABCD 25 1/1/13
29 ABCD 25 1/1/13
54 ABCD 25 1/1/13
The query should return the unique id of the record that match from each record, so the results should be:
Resultset:
PaymentId SubscriptionId
1 22
7 29
This could easily be done using T-SQL and a cursor, but there must be a better way to accomplish this without looping through the records?
May 3, 2013 at 7:16 am
Confused:
For example:
One customer has 3 payments but only 2 were processed into subscriptions.
Sample data:
Payments
UniqueId CustomerId Amount Date
1 ABCD 25 1/1/13
7 ABCD 25 1/1/13
25 ABCD 25 1/1/13
Subscriptions
UniqueId CustomerId Amount Date
22 ABCD 25 1/1/13
29 ABCD 25 1/1/13
54 ABCD 25 1/1/13
Looking at the above sample data I am trying to figure out how you have three payments and only two subscriptions as I see three of each. Could you perhaps elaborate a little more on this please?
May 3, 2013 at 7:38 am
Also: "share a number of fields" let the alarm bells go off. Also not sure what you want, but your database is not normalized. SQL is pretty easy if your design follow normalization principles.
May 3, 2013 at 9:33 am
Thank you for the reply!
In my haste to post the question I entered one row too many for subscriptions.
The scenario is that the two tables are populated with information coming from two different systems. One that manages subscriptions and the other that manages payments. The customer made three payments but something interfered with the third being received by the subscription system.
This query would allow controls to capture such problems.
May 3, 2013 at 10:07 am
mbigatti 28497 (5/3/2013)
Thank you for the reply!In my haste to post the question I entered one row too many for subscriptions.
The scenario is that the two tables are populated with information coming from two different systems. One that manages subscriptions and the other that manages payments. The customer made three payments but something interfered with the third being received by the subscription system.
This query would allow controls to capture such problems.
That helps clarify but from the examples you posted how do you know that PaymentID 1 is related to SubscriptionID 22 and 7 is related to 29? The dates are all the same so there is nothing that says which one belongs to which one.
It would be very helpful if you could post ddl (create table scripts) and sample data (insert statements).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 3, 2013 at 5:57 pm
In the scenario described it wouldn't matter which payment was meant for each subscription Id because the assumption is that the subscription doesn't capture any information besides the client id, amount, and date.
The situation could occur if customer meant to make two payments but while attempting one received a time out and assumes the payment failed. All three payments hit customer's account but one never resulted in a record to the subscription.
The tables for subscription would be very simple.
CREATE TABLE Subscriptions (
SubscriptionId bigint identity(1,1) NOT NULL,
CustomerId varchar(12) NOT NULL,
Amount money NOT NULL,
Date datetime NOT NULL)
Payments could have more information such as a receipt number, but this isn't passed to Subscriptions.
CREATE TABLE Payments (
PaymentId bigint identity(1,1) NOT NULL,
CustomerId varchar(12) NOT NULL,
Amount money NOT NULL,
Date datetime NOT NULL,
ReceiptNumber varchar(12) NOT NULL)
May 3, 2013 at 6:38 pm
mbigatti 28497 (5/3/2013)
In the scenario described it wouldn't matter which payment was meant for each subscription Id because the assumption is that the subscription doesn't capture any information besides the client id, amount, and date.The situation could occur if customer meant to make two payments but while attempting one received a time out and assumes the payment failed. All three payments hit customer's account but one never resulted in a record to the subscription.
The tables for subscription would be very simple.
CREATE TABLE Subscriptions (
SubscriptionId bigint identity(1,1) NOT NULL,
CustomerId varchar(12) NOT NULL,
Amount money NOT NULL,
Date datetime NOT NULL)
Payments could have more information such as a receipt number, but this isn't passed to Subscriptions.
CREATE TABLE Payments (
PaymentId bigint identity(1,1) NOT NULL,
CustomerId varchar(12) NOT NULL,
Amount money NOT NULL,
Date datetime NOT NULL,
ReceiptNumber varchar(12) NOT NULL)
OK this is feasible. Can you post some sample data as insert statements?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 3, 2013 at 9:20 pm
Here's some sample data
CREATE TABLE dbo.Subscriptions (
SubscriptionId bigint identity(1,1) NOT NULL,
CustomerId varchar(12) NOT NULL,
Amount money NOT NULL,s
Date datetime NOT NULL)
CREATE TABLE dbo.Payments (
PaymentId bigint identity(1,1) NOT NULL,
CustomerId varchar(12) NOT NULL,
Amount money NOT NULL,
Date datetime NOT NULL,
ReceiptNumber varchar(12) NOT NULL)
INSERT INTO dbo.Subscriptions (CustomerId, Amount, Date)
SELECT 'A0001', 12, getdate()
UNION ALL
SELECT 'A0002', 37, getdate()
UNION ALL
SELECT 'A0001', 12, getdate()
UNION ALL
SELECT 'A0012', 5, getdate()
UNION ALL
SELECT 'A0015', 8, getdate()
UNION ALL
SELECT 'A0022', 12, getdate()
INSERT INTO dbo.Payments (CustomerId, Amount, Date, ReceiptNumber)
SELECT 'A0022', 12, getdate(),'R012345'
UNION ALL
SELECT 'B0123', 21.22, getdate(),'R012346'
UNION ALL
SELECT 'A0001', 12, getdate(),'R012347'
UNION ALL
SELECT 'A0001', 12, getdate(),'R012348'
UNION ALL
SELECT 'A0015', 8, getdate(),'R012349'
UNION ALL
SELECT 'A0022', 12, getdate(),'R012350'
UNION ALL
SELECT 'A0001', 12, getdate(),'R012351'
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply