what is the best way to do this

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 2005 Forums

Transact-SQL (2005)

what is the best way to do this

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2012-11-29 : 11:24:31

I have a table that has orders

orders
id
customerid
amount
orderdate
cancelleddate (default null)

and a table

orderrecharges

id
orderid
amount
rechargedate

when someone orders they automatically get recharges for life (every 3 months) until they cancel so now when they order I add 3 records to orderrecharges for that year.

Now I want to check the records and any records that has only 1 or 2 recharges left (based on date) - I want to add another year of records

What is the best way to do this?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-29 : 11:55:35

How are the two tables related? In other words, how can you tell which customerid a given row in the orderrecharges table belongs to?

What is the nature of id column in these tables? Are they autogenerated (IDENTITY), or is there some rule to generating them? Also, how are orderid to be generated?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2012-11-29 : 12:07:27

sorry orders.id=orderrecharges.orderid

That's how they are connected and that's what I have to go by
I have to somehow check all orders that are not cancelled that have less then 3 future recharges and add another 1 each time (each recharge is 3 months after the last) - I'm just wondering the best way to do this.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-29 : 12:31:57

This will list all the orderid's that need recharging

SELECT
	o.id AS orderid,
	o.customerid,
	o.amount,
	MAX(c.rechargedate) AS HighestRechargeDate,
	SUM(CASE WHEN c.rechargedate > GETDATE() THEN 1 ELSE 0 END ) AS RechargesRemaining,
	3-SUM(CASE WHEN c.rechargedate > GETDATE() THEN 1 ELSE 0 END ) AS RechargesToDo
FROM
	orders o
	INNER JOIN orderrecharges c ON
		c.orderid = o.id
GROUP BY
	o.id,
	o.customerid,
	o.amount
HAVING
	COUNT(*) < 3;

This will do one recharge; it could probably be simpler, but I am using the previous query exactly as it is to identify the orderid's that need a recharge:

INSERT INTO orderrecharges
	(orderid, amount, rechargedate)
SELECT
	orderid,
	amount,
	DATEADD(mm,3,HighestRechargeDate)
FROM
(	
	SELECT
		o.id AS orderid,
		o.customerid,
		o.amount,
		MAX(c.rechargedate) AS HighestRechargeDate,
		SUM(CASE WHEN c.rechargedate > GETDATE() THEN 1 ELSE 0 END ) AS RechargesRemaining,
		3-SUM(CASE WHEN c.rechargedate > GETDATE() THEN 1 ELSE 0 END ) AS RechargesToDo
	FROM
		orders o
		INNER JOIN orderrecharges c ON
			c.orderid = o.id
	GROUP BY
		o.id,
		o.customerid,
		o.amount
	HAVING
		COUNT(*) < 3
) s

If you need to do 2 recharges, run it twice

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2012-11-29 : 12:38:19

[code]If you need to do 2 recharges, run it twice[/code]

you mean the insert statement twice right?
as I could run this daily and it will only insert until there are 3.

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-29 : 13:11:12

Yes. If you run it any more than two, it should not insert anything at all. In other words, it will insert only one row for a customerid and it will do so only if there are fewer than 3 recharges left.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources