Sort of a reverse of a Running Total

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sort of a reverse of a Running Total

Author

Topic

axisperfect
Starting Member

21 Posts

Posted - 2004-05-10 : 03:44:48

Here's my "problem"/situation:

Users may have coupons that they can use when purchasing products. However, the coupon is redeemable only once-- that means, if the total value of the coupon(s) exceeds the value of the products, the difference is lost. (Users also get the product for "free" since coupon covered the entire price).

Now, I've these tables that roughly tracks the usage of the coupons.
Coupon - stores the details of coupons

CouponID
CouponValue

CouponCart - stores which coupons the userid wants to use (temporary area)

CouponID
UserID
CouponDateAdded

CouponUsage - records the actual redemption of a coupon (based on Coupon and CouponCart tables)

CouponID
CouponUsed

I want to track which coupons was used, and how much. In most situations, coupons are fully used except the "last" coupon that may exceed a purchase. E.g. A product may cost 50 points, but a user may have three 20-point coupons. The user may "add" all the coupons to the purchase, making the product "free", but the last coupon is actually just "half-used".

Here's my solution:
Note: @totalCouponUsed -- indicates the total value of all the coupons that was actually used against purchase. As above, the totalCouponUsed value would be 50 points.


-- create temporary table
CREATE TABLE #tmpCoupon
	(
		couponID int,
		couponValue money,
		couponUsed money,
		couponTotal money -- not necessary
	)


-- populate temporary value
INSERT INTO #tmpCoupon(
	couponID,
	couponValue)
	SELECT couponID, couponValue
		FROM CouponCart left outer join Coupon
			on couponcart.couponid = coupon.couponid
		WHERE userID = @customerID 
			ORDER BY CouponDateAdded ASC



-- Initialize @totalCouponValue to 0
SET @totalCouponValue = 0

-- figure out how much was used
update #tmpCoupon
set 
	--add the current coupon value to the accumulated total amount
	@totalCouponValue = couponTotal =  couponValue + @totalCouponValue, 

	--if the current accumulated value of coupon is less than the totalCouponUsed 
	couponUsed = CASE WHEN	@totalCouponValue <= @totalCouponUsed THEN
			
			--then this current coupon was completely used 
			couponValue 

		      ELSe
			-- else this coupon was either partially used or not used at all
			CASE WHEN couponValue -  (@totalCouponValue - @totalCouponUsed) >= 0 THEN
				--partially used
				 couponValue -  (@totalCouponValue - @totalCouponUsed)
			ELSE
				-- not used at all
				0
			END
		
		       END

From the temporary table, I transfer them into CouponUsage. I avoided cursors because I thought I read somewhere they were not as performance friendly as temporary tables. Then I read somewhere else, temporary tables should really be avoided at all cost.

Whatever it is, is here a more elegant solution?

This code has increased the performance of one of my stored procedures to about 5 seconds longer in Query Analyzer. Just executing it once; I can't imagine what'll it be like with a few simultaneous users ..

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-05-10 : 09:50:04

[code]create table coupon (CouponID int identity(0,1), CouponValue int)

insert into coupon
select 20 union all
select 20 union all
select 20

create table CouponCart (CouponID int, UserID int, CouponDateAdded datetime)

insert into couponcart
select 0,1,getdate()-3 union all
select 1,1,getdate()-2 union all
select 2,1,getdate()-1

create table CouponUsage (CouponID int, CouponUsed int)

declare
@totalCouponUsed int,
@customerid int

select
@totalCouponUsed = 50,
@customerid = 1

insert into couponusage
select
couponid,
case
when (ttlcouponused-ttlcouponvalue) >=0 then couponValue
else abs((ttlcouponused-ttlcouponvalue))
end
from
(
select *,@totalCouponUsed ttlcouponused,
( select sum(couponvalue)
from
(
select top 100 percent c.couponID, c.couponValue
from CouponCart cc
left outer join Coupon c on cc.couponid = c.couponid
where userID = @customerID
order by CouponDateAdded
) di
where di.couponid <= do.couponid
) ttlcouponvalue
from
(
select top 100 percent c.couponID, c.couponValue
from CouponCart cc
left outer join Coupon c on cc.couponid = c.couponid
where userID = @customerID
order by CouponDateAdded
) do
) d

select * from couponusage
--drop table coupon,couponcart,couponusage
[/code]

axisperfect
Starting Member

21 Posts

Posted - 2004-05-10 : 23:37:35

Wow, I would never have come up with that kind of solution. Just one read and insert, and it's done! Thanks!

I made a little correction to the actual calculation to the amount used. Originally, it was giving me what wasn't used for the last coupon added. Also, I added another else so that when the coupon isn't even redeemable, the value is 0. (Of course, interface wise, the user shouldn't even be able to add that coupon, lest he waste it.)


	case 
		when (ttlcouponused-ttlcouponvalue) >=0 then couponValue 
		else 
		case
			when (couponValue - abs((ttlCouponUsed-ttCouponValue)) >= 0 then
				(couponValue - abs((ttlCouponUsed-ttCouponValue))
			else
				0
		end
	end

I put it in my code and it take about 4 seconds to complete.

Oh well. I'm still sticking with the no-temp-tables version, though -- I'm hoping that in the long run with a lot of concurrent executions of this stored procedure, I'll be saving some overhead without the temp tables. Is that a reasonable guess?

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-05-11 : 08:48:23

Just curious. Do you have any indexes and/or constraints on the tables?

axisperfect
Starting Member

21 Posts

Posted - 2004-05-11 : 21:17:52

Let's see.. all of the tables have primary keys, and I believe the cart table has one constraint (to control a certain column's values).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources