CODEWITHSUNDEEP
sqlsql-serverdatabasedatabase-design
MAW74656
MAW74656

Reputation: 3549

how to store and query based on daterange/age?

I'm building a commission calculator, and I'm not sure how to proceed. I have situations like: saleRep A for customer 123 gets 2% for the first 2 years, then .5% thereafter. I already know how to get the age of the customer.

And the date ranges are variable. Meaning that one salesRep/Customer combination could split at 1 year, 2 years, anytime, but probably on the year.

So, how do I query for and store this? My commission table is currently as follows, do I need to change it?

    CREATE TABLE [dbo].[NCL_Commissions](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ProductType] [varchar](255) NULL,
    [LowCost] [int] NULL,
    [HighCost] [int] NULL,
    [CustCode] [varchar](30) NULL,
    [SalesRep] [varchar](10) NULL,
    [Commission] [float] NULL,
    [MinAge] [smallint] NULL,
    [MaxAge] [smallint] NULL,
 CONSTRAINT [PK_NCL_Commissions] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Here's how I built it when I was told all commissions would be the same:

  DECLARE @custCreationDate datetime

    SET @custCreationDate = (   SELECT TOP 1 cast(INVDate as datetime)
                                FROM InvoiceHeader
                                WHERE companycode = @custCode
                                order by recid asc      )

--  PRINT 'Line 54: @custCreationDate: ' + cast(@custCreationDate as varchar)

    --If the customer has existed for less than a year
    IF @custCreationDate > DateAdd(yy, -1, @now)
        BEGIN
            SET @result = 2.00  --Customers in existance for less than a year yeild 2% commission
--          PRINT 'Line 60 - @result: ' + cast(@result as varchar)
            GOTO Exit_Function
        END
    ELSE
        BEGIN
            SET @result = 0.50  --Customers in existance longer yeild 0.5 % commission.
--          PRINT 'Line 66 - @result: ' + cast(@result as varchar)
            GOTO Exit_Function
        END

Sample Data (part of question is looking for suggestions on how to store)

Customer 123 for salesrep A gets 1% for the first 2 years, than .5%
Customer 456 for salesrep B gets 2% for the first 1 year, than .75%

At any given time, I have to be able get the correct percentage based on the current age of the customer. So if customer A was created June 1 2012, commision is 2%. If customer was created Sept 5 2008, then commission is .5%.

SOLUTION Based on Gordon Linoff's answer:

SET @custAgeInMonths = datediff(month, @custCreationDate, @invDate)

    --First look for a customer specific record
    SELECT @result = C.Commission
    FROM NCL_Commissions C
    WHERE C.CustCode = @custCode
    AND C.SalesRep = @salesRep
    AND ProductType in ('L')
    AND @custAgeInMonths BETWEEN C.MinAgeMonths AND C.MaxAgeMonths

Upvotes: 1

Views: 181

Answers (2)

vaquito
vaquito

Reputation: 180

1) you need a table that has the commissions brackets (i recommend by month) 

CREATE TABLE [dbo].[CommissionsBrakets](
[ID] [int] NOT NULL,
[Commission] [Decimal](4, 4) NULL,
Month [smaillint])

fill it with the brakets

1    2%  1 /month 1 2%
1    2%  2 /month 2 2%
....
1  0.5% 24 /month 24 (2 years) 0.5%

2) you need a table relating costumer+salesrep+comisionBracket

CREATE TABLE [dbo].[NCL_Commissions](
[ID] [int] IDENTITY(1,1) NOT NULL,   
[CustCode] [varchar](30) NULL,
[SalesRep] [varchar](10) NULL,
[CommissionID] [int] NULL)

then you can do a join 

declare @mothDiff int = DATEDIFF(m, @custCreationDate, GETDATE())


Select b.Commission from NCL_Commissions C inner join CommissionsBrakets B 
on c.CommissionID  =   B.ID 
where 
B.month = case 
    when @mothDiff > ((select max(Month) from CommissionsBrakets CB where CB.id = B.ID   ))
    then (select max(Month) from CommissionsBrakets CB where CB.id = B.ID   )
    else @mothDiff
    end

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270371

This seems like a join.

Assume you have a customer table, with the start date of the customer and the commission table above. You can calculate the current age of the customer and join into the commission table. This assumes that the customer record has the sales rep:

select <whatever>
from (select c.*, datediff(day, c.joindate, getdate())/365.25 as AgeYears
      from customer c
     ) c left outer join
     NCL_Commissions com
     on c.CustCode = com.CustCode and
        c.ProductType= com.ProductType and
        c.SalesRep = com.SalesRep and
        c.AgeYears between MinAge and c.MaxAge

Given all the join criteria, I would watch out for non-matches (hence the use of the left outer join).

Upvotes: 1

Related Questions