Finding where my date falls in date range

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

General SQL Server Forums

New to SQL Server Programming

Finding where my date falls in date range

Author

Topic

slihp
Yak Posting Veteran

61 Posts

Posted - 2007-10-25 : 07:19:43

Hi;

We received a Payment from a customer on '10/10/2007 10:30:00'. i am trying to calculate the commission we would receive from that payment. the commission rate can be edited. so i have to find what the commission rate was when that payment was received.

I have a CommisionAudit table that tracks changes in commission rate with the following values.

ID | Commission Change | UpdatedOn
----------------------------------------------
1 | Change from 20->25 | 03/07/2007 09:00:00
----------------------------------------------
2 | Change from 25->35 | 10/09/2007 17:00:00
----------------------------------------------
3 | Change from 35->20 | 01/10/2007 16:00:00
----------------------------------------------
4 | Change from 20->26 | 11/10/2007 10:00:00
----------------------------------------------

with this payment, as the commission rate had been changed on 01/10/2007 it would obviously be 20%(ID 3). But I need to write sql to cover all eventualities i.e. Before the first and after the last. any help would be most welcome.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 07:43:26

Care to post any sample data and expected output?

E 12°55'05.25"
N 56°04'39.16"

slihp
Yak Posting Veteran

61 Posts

Posted - 2007-10-25 : 08:23:33

Thanks for the reply:

SampleDate
Payment Date 1: '02/07/2007 10:00:00'
Payment Date 2: '04/10/2007 10:00:00'

Table – ContractAudit

ID| RateChangeDescription| ChangeDate
---------------------------------------------------------------------------------------------
711| Change Fee Rate: 22.00 -> 26.00| 2007-07-03 11:29:38.857
1037| Change Fee Rate: 26.00 -> 35.50| 2007-10-01 15:10:34.990
1043| Change Fee Rate: 35.50 -> 37.50| 2007-10-02 09:42:12.837
1057| Change Fee Rate: 37.50 -> 35.00| 2007-10-08 10:02:16.010

Expected Output : 22.00

so as can be seen if we use payment1 commission rate would be 22.00, but if we use payment2 the commission rate would be 37.50.

hope this makes it clearer

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-25 : 08:34:23

I had a similar problem, effective dates with no expiration dates, and in SQL 2005 I did this

with cte_Service

AS

(
select 'aRank' = RANK() OVER (PARTITION BY skill order by effectivedate)
,skill,effectivedate,servicethreshold
from
results..ctlskillthresholds
)

SELECT a.skill,a.effectivedate,ISNULL(b.effectivedate,'12/31/2999'),a.servicethreshold
FROM
cte_service a
LEFT join
cte_service b
on
a.aRank <= b.arank+1
and a.skill = b.skill
and a.effectivedate < b.effectivedate

WHERE a.skill = 841

Hope this helps

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 08:35:58

SELECT TOP 1 * FROM ContractAudit
WHERE ChangeDate >= @SomeDate
ORDER BY ChangeDate

E 12°55'05.25"
N 56°04'39.16"

slihp
Yak Posting Veteran

61 Posts

Posted - 2007-10-25 : 08:41:32

i am also using sql 2005, but i cant quite follow your post jim.

slihp
Yak Posting Veteran

61 Posts

Posted - 2007-10-25 : 09:00:12

thanks for the input peso.

i had similar sql, but with this sql if the payment date is after the very last date, it returns no rows, where i would then need it to go back to the last row in the table.

slihp
Yak Posting Veteran

61 Posts

Posted - 2007-10-25 : 12:05:12

Just wondering if this what i am after is possible, or would it be easier to created a separate table with a start & end date of when a com date was active.

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-10-25 : 14:00:24

How about


SELECT 
  Max(ChangeDate) 
FROM 
  ContractAudit
WHERE
  PaymentDate >= ChangeDate

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-25 : 14:37:32

slihp,

the cte_service in my query is a common table expression which you can look up in Books On Line, as well as the rank function. What my query does is make an expiration date for each effective date, no when I query my date can be between an effective date and an expiration date. The real trouble you'll have is getting the rate out of your ratedescription field. Are the rates actually in their own field?

Jim

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-10-25 : 15:01:51

You can use this to query aginst your table with the rates in it ,or better yet, use it to create a better tables, with effectivedate and expiration date

declare @table table (ID int, RateChangeDescription varchar(50),ChangeDate datetime)

INSERT INTO @table
SELECT 711 ,' Change Fee Rate: 22.00 -> 26.00','2007-07-03 11:29:38.857' union
SELECT 1037 ,' Change Fee Rate: 26.00 -> 35.50','2007-10-01 15:10:34.990' union
SELECT 1043 ,' Change Fee Rate: 35.50 -> 37.50','2007-10-02 09:42:12.837' union
SELECT 1057 ,' Change Fee Rate: 37.50 -> 35.00','2007-10-08 10:02:16.010'

;
with cte_rate
as

(
Select Changedate,[aRank] = RANK() OVER(ORDER BY changedate asc)
FROM @table
)

SELECT a.changedate,ISNULL(b.changedate,'12/31/2099')
FROM
cte_rate a
LEFT JOIN
cte_rate b
ON
a.arank = b.arank-1

slihp
Yak Posting Veteran

61 Posts

Posted - 2007-10-26 : 04:47:03

thanks allot Jim for the detailed reply. yes you are right, the real problem is getting the value. The values are not in another table, this table is the only one that has a historical view of the rate changes. I will persevere

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources