Date Barriers 1

[HestonJames]

Hello Chaps,

I've got a requirement here whereby we charge customers for registering with our service however on certain dates we place in a new price increase, customers that have registered before this date will still be charged the old price however any new registrations will be charged the new price.

I've mocked up some data here for the example:

DECLARE @RegistrationCostBarrier TABLE (
	Cost char(17) Collate Database_Default,
	BarrierDate datetime
)

Insert Into	@RegistrationCostBarrier (Cost, BarrierDate)
Values	('£100', '01-01-2000')

Insert Into	@RegistrationCostBarrier (Cost, BarrierDate)
Values	('£300', '03-14-2008')

Insert Into	@RegistrationCostBarrier (Cost, BarrierDate)
Values	('£500', '09-16-2008')

Insert Into	@RegistrationCostBarrier (Cost, BarrierDate)
Values	('£1000', '02-22-2009')

DECLARE @Registration TABLE (
	Client_ID int,
	ClientName char(50) Collate Database_Default,
	RegistrationDate datetime
)

Insert Into @Registration (Client_ID, ClientName, RegistrationDate)
Values	(1, 'Some Client', '02-22-2006') /* This registratrion should return £100 */

Insert Into @Registration (Client_ID, ClientName, RegistrationDate)
Values	(2, 'Another Client', '09-01-2007') /* This registratrion should return £100 */

Insert Into @Registration (Client_ID, ClientName, RegistrationDate)
Values	(3, 'More Client', '03-15-2008') /* This registratrion should return £300 */

Insert Into @Registration (Client_ID, ClientName, RegistrationDate)
Values	(4, 'Final Example Client', '07-26-2011') /* This registratrion should return £1000 */

Now, what I need your help with is formulating a query which returns the clients registration details along with the cost which is relevant too them, you'll see that the first table defines all the price barriers and when new pricing comes into effect.

I've commented in the code to show what costs I expect the final query to return for each of the example clients based on their registration date.

I hope that explains what I'm after, please let me know if you need any more details.

Thanks,

Heston

 

[PDreyer]

Cost should not be stored as a char
What code have you tried? Where are you getting stuck?

 

[HestonJames]

Hey PDreyer,

This is only example data just to represent the basic structure, the datetime columns are the important ones for this case so just threw it in as a char for the sake of it. thanks for the concern though!

I'm stuck even getting started to be honest, thinking about how to select that is confusing me, if it was in two columns like BarrierStartDate and BarrierEndDate then that would be a simple enough query as you could just use a BETWEEN or <> clause however, as this is only a single barrier date and the BarrierEndDate is effectively the next record in that table I'm feeling a bit lost

Heston

 

[ZenRaven]

This should get you want you want

SELECT  client_id, clientname, registrationdate,
        (SELECT TOP 1
                cost
         FROM   @registrationcostbarrier rcb
         WHERE  barrierdate < r.registrationdate
         ORDER BY barrierdate DESC) as cost
FROM    @registration r

 

[HestonJames]

Ding Ding Ding!!! We have a winner! :-D

That works like a charm ZenRaven, thank you!

Heston