SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 what is the best way to do this
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

esthera
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 11/29/2012 :  11:24:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/29/2012 :  11:55:35  Show Profile  Reply with Quote
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?
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 11/29/2012 :  12:07:27  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/29/2012 :  12:31:57  Show Profile  Reply with Quote
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
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1393 Posts

Posted - 11/29/2012 :  12:38:19  Show Profile  Reply with Quote
If you need to do 2 recharges, run it twice


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

Thanks
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/29/2012 :  13:11:12  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000