Points balance

Question

Points balance

a2zwd

SSCrazy

Points: 2356
More actions
July 6, 2012 at 2:54 am

#258850

Hi,
I need to write a query to show balance for the supermarket points(clubcard).
I have 2 tables with the structure
CardId, TransDate, PositivePoints
CardId, TransDate, NegativePoints
I need to write a query from these 2 tables to show the history of cardid.
How I earned, how I spent, and the balance at each point.
Can anyone suggest?
Pls note that these 2 tables are very big(each table contains around 10 million rows)
thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Please can you read through the second link in my signature below on posting code and data for the best help.

We would need the DDL of the tables along with any indexes and foreign keys between them, sample data for all tables and your expected outcome.

If you could also post what you have tried so far it would help as well.

a2zwd SSCrazy Points: 2356 More actions · Answer 2

Hi

Thanks for the reply.

DDL

AddedPoints: CardId INT, TransDate DATETIME, PositivePoints INT

SpentPoints: CardId INT, TransDate DATETIME, NegativePoints INT

Sample Data:

AddedPoints:

123 2012-01-01 12:24:59 200

123 2012-01-05 10:12:15 100

123 2012-02-15 12:24:59 250

Spent Points:

123 2012-01-04 08:39:59 -100

123 2012-01-10 18:13:15 -200

I want the query to produce resultset like

CardIdTransDate Points TotalPoints

-----------------------------------------------------------

123 2012-01-01 12:24:59 200200

123 2012-01-04 08:39:59 -100100

123 2012-01-05 10:12:15 100200

123 2012-01-10 18:13:15 -2000

123 2012-02-15 12:24:59 250250

Note: non clustered index on CardId on both tables

I tried Cumulative balance but I need to generate RowId and save them in another table.

Then I did cross join to get the cumulative. For a single cardid, its fine

My requirement is not to find balance for a single cardid.

I need to generate a resultset that is to be stored in fact table which shows the history of card points.

I am struck here

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 3

Deleted as misunderstood requirements

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

abdul.badru Mr or Mrs. 500 Points: 588 More actions · Answer 4

Find here my contribution:

Consider creating a temporary with the same structure as you two table table like this ( Just add a column Flag char(1)):

CREATE TABLE [dbo].[myPoints](

[CardId] [int] NULL,

[TransDate] [date] NULL,

[Points] [decimal](10, 2) NULL,

[Flag] [char](1) NULL

) ON [PRIMARY]

GO

The column Flag is used for control only. Merge the date of your two table into myPoints table.

Now execute the following code to see the result:

WITH pivotTable

AS

(

SELECT CardId, TransDate, [P] as Positive,[N] as Negative

FROM

(

SELECT * FROM myPoints) as s PIVOT (sum(Points) for Flag In ([P],[N])) as t

)

SELECT *, Positive+Negative as Balance

FROM pivotTable

ORDER BY CardId, TransDate

Note: run the code above as single transaction because pivotTable is a CTE e is needed in the last select statatment.

Best Regards

abdul.badru Mr or Mrs. 500 Points: 588 More actions · Answer 5

Just to add one more thing.

After the merge of the two table, Remember to update the column Flag wint P if the value is Positive and with N if the value is Negative.

update dbo.myPoints

set Flag='P' where Points>=0

update dbo.myPoints

set Flag='N' where Points<0

Regards

abdul.badru Mr or Mrs. 500 Points: 588 More actions · Answer 6

For my sample data, the result look like this:

CardIdTransDatePositiveNegativeBalance

11/1/2000914.36-745.95168.41

11/2/2000706.22-1030.15-323.93

11/3/2000699.53-1039.36-339.83

11/4/2000811.9-381.81430.09

11/5/2000888.44-891.95-3.51

11/6/2000539.8-556.76-16.96

11/7/2000600.8-527.1973.61

11/8/2000576.08-747.02-170.94

11/9/2000309.6-673.64-364.04

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

a2zwd (7/6/2012)
...
I am struck here

So are we. If you really want help with this, then take the time to read the forum etiquette article. It shows you how to post DDL and DML so that folks can copy and paste into SSMS and get cracking straight away on a solution. It's the least you can do when folks are offering their time to help you.

We'll need a lot more than 5 rows of sample data - consider maybe 30 or 40 to start with.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden