Latest Value Less than Todays Date

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)

Latest Value Less than Todays Date

Author

Topic

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-09 : 21:04:37

Hey guys and gals. I've got a table that stores salesreps commission rate history and the current rate defined by an effective date. This effective date can be a date in the future, ie: a sales rep's rate may increase from 10% to 15% on monday, but may have been entered today with monday as the effective date. What I'm trying to figure out is the current rate. The sales rep may have othe rates before but only the most recent up-to-date effective date is the current one, not including the one to occur in the future. I'm trying to write the code in a function, if possible, so keep in mind of those non-deterministic stuff, did I say that right?

Here's the ddl:

CREATE TABLE [dbo].[SalesRepCommissionRate] (
	[SalesRepCommissionRateID] [int] IDENTITY (1, 1) NOT NULL ,
	[SalesRepID] [int] NOT NULL ,
	[SalesRepCommissionRate] [real] NOT NULL , --i use real to store decimal values (10% = .1) is that good?
	[SalesRepCommissionEffectiveDate] [datetime] NOT NULL 
)

Not sure how to get the dml (is that what it's called) 'cept to write it out. So i'll do it this way, if there is an easy way to get the 'dml' without typing it out, please let me know, i been looking around the web, and here, but every post I see just asked people to post it and never see a clear answer to those who ask how. Anyhow:

SalesRepCommissionRateID	SalesRepID	SalesRepCommissionRate	SalesRepCommissionEffectiveDate
1				1		.1			5/24/2004
2				2		.1			6/21/2004
3				1		.12			7/30/2004
4				1		.15			8/16/2004

Desired output, when I send a param input of 1 for the sales rep:

SalesRepCommissionRate
.12

- RoLY roLLs

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-09 : 21:10:13

BTW: when I use the value of .15 in a 'real' column, I get the result of 0.15000001.

Why all those 0's and the 1 at the end?

- RoLY roLLs

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-09 : 21:17:36

Check this out:

after a few tries and getting all the results for salesrep 1 i finally came up with some code to, seemingly, give me what I want. But how good is it?

	select
		SalesRepCommissionRate
	from
		SalesRepCommissionRate
	where
		SalesRepCommissionRateID = 
			(
			select
				max(SalesRepCommissionRateID)
			from
				SalesRepCommissionRate
			where
				SalesRepID = 1
				and SalesRepCommissionEffectiveDate <= getdate()
			)

- RoLY roLLs

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-09 : 21:33:43

But of course...'getdate' cannot be used in a function, maybe try to pass it thru. But tell me if that statement is good or can be enhanced in other ways for performance.

- RoLY roLLs

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-09 : 21:41:35

ok, here's my modification. Let me know if you can make it perform better:

CREATE view CurrentDate
as
	select CurrentDate = getdate()


CREATE function GetSalesRepCommissionRate
	(
	@SalesRepID int
	)
returns
	real
as
begin
	declare @SalesRepCommissionRate real
	
	select
		@SalesRepCommissionRate = SalesRepCommissionRate
	from
		SalesRepCommissionRate
	where
		SalesRepCommissionRateID = 
			(
			select
				max(SalesRepCommissionRateID)
			from
				SalesRepCommissionRate
			where
				SalesRepID = @SalesRepID
				and SalesRepCommissionEffectiveDate <= 
				(
				select
					currentdate
				from
					currentdate
				)
			)

	return @SalesRepCommissionRate
end

- RoLY roLLs

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-10 : 04:47:06

It can be done a little easier and alot more dynamic:

CREATE function GetSalesRepCommissionRate
  (@SalesRepID int, EffectiveDate datetime)
returns
  real
as
begin
  declare @SalesRepCommissionRate real

  SET @SalesRepCommissionRate = (
    SELECT TOP 1 SalesRepCommissionRate
    FROM SalesRepCommissionRate
    WHERE SalesRepCommissionEffectiveDate <= @EffectiveDate
      AND SalesRepID = @SalesRepID
    ORDER BY SalesRepCommissionEffectiveDate DESC)

  return @SalesRepCommissionRate
end

--> Calling the function: SELECT dbo.GetSalesRepCommissionRate(1, GETDATE())

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-10 : 04:57:42

"float and real
Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented."

Float and real datatypes are most commonly used in scientific experiments and such and is not really good for regular math-functions. Use deciaml or numeric instead and specify both precision and scale. Precision would be the total number of digits and scale would be the number of digits to the right of the decimal-point. The number 245.16 would qualify as deciaml(5, 2).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources