Databases
StackOverflow2013
Postsdbo
POhow to store and query based on daterange/age?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POhow to store and query based on daterange/age?
    primarykey
    data
    text
    <p>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. </p> <p>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. </p> <p>So, how do I query for and store this? My commission table is currently as follows, do I need to change it?</p> <pre><code> 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] </code></pre> <p>Here's how I built it when I was told all commissions would be the same:</p> <pre><code> 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 &gt; 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 </code></pre> <p>Sample Data (part of question is looking for suggestions on how to store)</p> <p>Customer 123 for salesrep A gets 1% for the first 2 years, than .5%<br> Customer 456 for salesrep B gets 2% for the first 1 year, than .75%</p> <p>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%.</p> <p>SOLUTION Based on Gordon Linoff's answer:</p> <pre><code>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 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMAW74656
    1. USMAW74656
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POhow to store and query based on daterange/age?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POhow to store and query based on daterange/age?
      1. USMAW74656
      1. VTFavorite
    3. VO
      singulars
      1. POhow to store and query based on daterange/age?
      1. USvaquito
      1. VTFavorite
    1. COPlease provide some sample data and expected results. Also, show what you've tried, such as calculating the customer age.
      singulars
      1. POhow to store and query based on daterange/age?
      1. USHolger Brandt
    2. CO@HolgerBrandt -See edits.
      singulars
      1. POhow to store and query based on daterange/age?
      1. USMAW74656
    3. COwhy dose Customer 123 get 1% ? base on what? you said they get 2% the first 2 years
      singulars
      1. POhow to store and query based on daterange/age?
      1. USvaquito
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload