March 23, 2014 at 4:12 am
Hi,
I am working on a rewards program and I have a table whenever customer completes a trip, his total fare,business points earned for that particular trip and respective Promotional points gets inserted.
Now I have a scenario whenever customer business points accumulates to 10 then need to award 3 promotional points.
If Business Points=14 for a single trip then for the first 10 points respective Promo points will be awarded and the remaining 4 points should get carry forward for the next trip and this 4 points should get accumulated with the next trip Business Points and so on.
Basically need to check for every 10 Business points accumulated award some Promo points and carry forward remaining points.
Please suggest some ideas or logic to solve this query.Let me know if this information is clear or not.
Here is the sample table structure and data :
CREATE TABLE [dbo].[tblRedeems]
(
[Mobileno] [varchar](50) NOT NULL,
[TripNo] [int] NOT NULL,
[CustomerName] [varchar](50) NULL,
[TripEndTime] DATETIME NOT NULL,
[Fare] [money] NULL,
[BusinessPoints] [int] NULL,
[AccumlatedBusinessPoints] [int] NULL,
[PromotionalPoints] [int] NULL
)
INSERT INTO tblRedeem(Mobileno,TripNo,CustomerName,TripEndTime,Fare,BusinessPoints,AccumlatedBusinessPoints,PromotionalPoints)
SELECT '123456789',1,'Michael','2014-02-28 11:02:12.155',125.0000,1,0,0
UNION ALL
SELECT '123456789',2,'michael','2014-03-13 15:28:51.390',1250.0000,12,3,3
UNION ALL
SELECT '123456789',3,'Michael','2014-03-17 19:45:35.234',825.0000,8,1,3
UNION ALL
SELECT '123456789',4,'michael','2014-03-23 23:56:09.140',475.0000,5,6,0
Thanks...
March 23, 2014 at 4:59 am
for clarification...are you asking for an update on business/promo points on the entire table or are you expecting that points will be automatically calculated on a new row being inserted.?
can you please explain, based on your data, how many points Michael has currently accrued...this may help explain your logic.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 23, 2014 at 6:29 am
ivar_SQL (3/23/2014)
Hi,I am working on a rewards program and I have a table whenever customer completes a trip, his total fare,business points earned for that particular trip and respective Promotional points gets inserted.
Now I have a scenario whenever customer business points accumulates to 10 then need to award 3 promotional points.
If Business Points=14 for a single trip then for the first 10 points respective Promo points will be awarded and the remaining 4 points should get carry forward for the next trip and this 4 points should get accumulated with the next trip Business Points and so on.
Basically need to check for every 10 Business points accumulated award some Promo points and carry forward remaining points.
Please suggest some ideas or logic to solve this query.Let me know if this information is clear or not.
Here is the sample table structure and data :
CREATE TABLE [dbo].[tblRedeems]
(
[Mobileno] [varchar](50) NOT NULL,
[TripNo] [int] NOT NULL,
[CustomerName] [varchar](50) NULL,
[TripEndTime] DATETIME NOT NULL,
[Fare] [money] NULL,
[BusinessPoints] [int] NULL,
[AccumlatedBusinessPoints] [int] NULL,
[PromotionalPoints] [int] NULL
)
INSERT INTO tblRedeem(Mobileno,TripNo,CustomerName,TripEndTime,Fare,BusinessPoints,AccumlatedBusinessPoints,PromotionalPoints)
SELECT '123456789',1,'Michael','2014-02-28 11:02:12.155',125.0000,1,0,0
UNION ALL
SELECT '123456789',2,'michael','2014-03-13 15:28:51.390',1250.0000,12,3,3
UNION ALL
SELECT '123456789',3,'Michael','2014-03-17 19:45:35.234',825.0000,8,1,3
UNION ALL
SELECT '123456789',4,'michael','2014-03-23 23:56:09.140',475.0000,5,6,0
Thanks...
is there an actual need to store these in the table?
can you calculate them when needed instead...something like this maybe?
SELECT Mobileno,
SUM(BusinessPoints) AS totalBusinessPoints,
SUM(BusinessPoints) % 10 AS AccumulatedBusinessPoints,
SUM(BusinessPoints) / 10 * 3 AS PromotionalPoints
FROM tblRedeems
GROUP BY Mobileno
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 23, 2014 at 7:11 am
a proof of concept on 1 million rows of test data
USE [tempdb]
GO
/*Drop and Create a test table */
IF OBJECT_ID('tempdb..tblRedeems_TST', 'U') IS NOT NULL
DROP TABLE tempdb..tblRedeems_TST ;
CREATE TABLE [dbo].[tblRedeems_TST](
[TranID] [int] IDENTITY(1,1) NOT NULL,
[Mobileno] [int] NOT NULL,
[BusinessPoints] [int] NULL
) ON [PRIMARY]
GO
/*the following will insert data for 50000 mobile numbers ( in this example they are INT)
and randomly insert business points in the range from 1 to 9 for a million rows*/
INSERT INTO [dbo].[tblRedeems_TST] (
[Mobileno],
[BusinessPoints]
)
SELECT TOP 1000000 ----note 1 million
Mobileno = 1 + CAST(Abs(Checksum(Newid()) % 50000) AS INT),
BusinessPoints = 1 + CAST(Rand(Checksum(Newid())) * 9 AS INT)
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
CROSS JOIN sys.all_columns ac3
GO
/*Create a couple of indexes*/
ALTER TABLE dbo.tblRedeems_TST ADD CONSTRAINT PK_tblRedeems_TST PRIMARY KEY CLUSTERED (TranID)
GO
CREATE NONCLUSTERED INDEX [tblRedeems_TST_2] ON [dbo].[tblRedeems_TST] ([Mobileno] ASC) INCLUDE ([BusinessPoints]) ON [PRIMARY]
GO
/*get results*/
SELECT Mobileno,
SUM(BusinessPoints) AS totalBusinessPoints,
SUM(BusinessPoints) % 10 AS AccumulatedBusinessPoints,
SUM(BusinessPoints) / 10 * 3 AS PromotionalPoints
FROM tblRedeems_TST
GROUP BY Mobileno
--HAVING (Mobileno = 14671)
--ORDER BY Mobileno
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 23, 2014 at 7:30 am
Thanks for the response...
Here Michael has total 26 Business points for his total trips and based on that he should get 6 promotional points for the first 20 business points ,
the remaining 6 points should get carry forwarded for his next trip.
For first 10 points---3 promotional points,
Next 10 points------3 Promotional points,
next 6 points-------should be stored in AccumulatedBusinessPoints column ..
For his next trip,if he gets 5 business points ,then the previous 6 points and current 5 business points should get summed up and if it is greater than 10 then respective Promo points should be addded.
so here it should be like this:
Previous 6 +Current 5 Business points =11 Points ,so here for that 10 points he will 3 promo points and reamining 1 point should be carry forwarded for his next trip.
So I need to update AccumulatedBusinessPoints column with carry forward value.
Basically I need check for each trip whether the business points accumulated to 10 and if it is then add promo points and expire previous rows and carry forward the remaining points .
Please let me know if I am clear in explaining the problem....
Thanks....
March 23, 2014 at 7:47 am
ivar_SQL (3/23/2014)
Thanks for the response...Here Michael has total 26 Business points for his total trips and based on that he should get 6 promotional points for the first 20 business points ,
the remaining 6 points should get carry forwarded for his next trip.
For first 10 points---3 promotional points,
Next 10 points------3 Promotional points,
next 6 points-------should be stored in AccumulatedBusinessPoints column ..
For his next trip,if he gets 5 business points ,then the previous 6 points and current 5 business points should get summed up and if it is greater than 10 then respective Promo points should be addded.
so here it should be like this:
Previous 6 +Current 5 Business points =11 Points ,so here for that 10 points he will 3 promo points and reamining 1 point should be carry forwarded for his next trip.
So I need to update AccumulatedBusinessPoints column with carry forward value.
Basically I need check for each trip whether the business points accumulated to 10 and if it is then add promo points and expire previous rows and carry forward the remaining points .
Please let me know if I am clear in explaining the problem....
Thanks....
please read my other posts on this thread
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply