July 13, 2012 at 2:36 am
Hi,
I have 2 tables witht he following structure.
MyTable1
----------
CustomerId INT
TransactionId INT
TransDate DATETIME
PointsEarned INT
MyTable2
----------
CustomerId INT
TransactionId INT
TransDate DATETIME
PointsSpent INT
Sample data:
MyTable1
-----------
101 1 20120101 50
101 2 20120115 100
101 3 20120201 150
MyTable2
-----------
101 1 20120220 125
Now, I want to split 125 points from MyTable2 as per the MyTable1
Example output:
CustomerId SpentTransId SpentDate EarnedDate SpentPoints FromEarnedPoints
-------------------------------------------------------------------------------------------------------
101 1 20120220 20120101 125 50
101 1 20120220 20120115 125 75 (its from 100 - balance from the previous row[125-50])
I am struggling to get this.
Can anyone suggest how do I achieve?
thanks
July 13, 2012 at 3:23 am
Hi
There are certainly other ways of doing this but due to time constraints here is one way if I have understood correctly:
DECLARE @MYTABLE1 AS TABLE
(
CustomerId INT
,TransactionId INT
,TransDate DATETIME
,PointsEarned INT
)
DECLARE @MYTABLE2 AS TABLE
(
CustomerId INT
,TransactionId INT
,TransDate DATETIME
,PointsSpent INT
)
INSERT INTO @MYTABLE1
SELECT 101, 1, '20120101', 50 UNION ALL
SELECT 101, 2, '20120115', 100 UNION ALL
SELECT 101, 3, '20120201', 150
INSERT INTO @MYTABLE2
SELECT 101, 1, '20120220', 125
SELECT
T2.CustomerId
,T2.PointsSpent
,T1.TransDate
,T1.PointsEarned
,PNTS.Points
FROM
@MYTABLE2 AS T2
INNER JOIN(SELECT
*
, ROW_NUMBER () OVER (PARTITION BY CustomerID ORDER BY TransDate ASC) AS RowNuM
FROM
@MYTABLE1
) AS T1
ON T1.CustomerId = T2.CustomerId
CROSS APPLY (SELECT
*
,(PointsSpent - PointsEarned ) AS Points
FROM
(
SELECT
*
, ROW_NUMBER () OVER (PARTITION BY CustomerID ORDER BY TransDate ASC) AS RowNum
FROM
@MYTABLE1
) AS T3
WHERE
T3.CustomerId = T2.CustomerId
AND T3.RowNum = T1.RowNuM
) AS PNTS
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
July 13, 2012 at 7:46 am
Sounds a bit like a running total query
July 13, 2012 at 8:55 am
CELKO (7/13/2012)
>> I have 2 tables with the following structure.<<Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
This is minimal polite behavior on SQL forums. What you did post is wrong. Why did you make the customer id an integer? What math do you do with it? Where is the key? Why is everything NULL-able? Where is the DRI? Ever hear of the design flaw known as attribute splitting?
CREATE TABLE Point_Transactions
(transaction_nbr INTEGER NOT NULL PRIMARY KEY,
customer_id CHAR(10) NOT NULL
REFERENCES Customers(customer_id),
trans_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
trans_points INTEGER NOT NULL
CHECK (trans_points <> 0));
Why do think that adding and deleting points to a customer account are so totally different, like squids and automobiles, that they get split into two tables? Use positive and negative numbers. You might want to add a transaction type code to this.
SELECT transaction_nbr, customer_id, trans_date, trans_points,
SUM (trans_points)
OVER(PARTITION BY customer_id
ORDER BY trans_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS current_point_balance
FROM Point_Transactions;
Mr. Celko, Once again, you post an answer that WON'T work on the version of SQL Server that the OP is apparently using. Please, in the future, try to provide tested answers that work on the version of SQL Server that is being used. Answers like yours do the OP absolutely no good.
July 13, 2012 at 9:07 am
Why did you make the customer id an integer?
Errm because it makes MUCH more sense than a CHAR(10) Mr C!!! :crazy:
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply