Hello,

I was wondering if some one can direct me to a link where code samples are
posted or syntax checkers.
I've  taken on this report project using stored procedures, the request is
base on inventory commission for sales reps.

The rep will be paid commission on a $ amt base on the invoice date. if the
invoice date is lets say prior to 4/10/04, the commission needs to be set to
2%, after that date commission is 3%. there's also a TargetAmt if they reach
the target amt  commission percent is 4%  and tally the AmtPaid to see when
they've reach the TargetAmt  . I would a link to a place with reports similar
to this one I have to do for ideas.

Thanks in advance.

There are a few ways you could do it...

If you want to have a date range indicating the commission, why not have a
table that you join to. Like this:

DateRangeCommission
(datefrom datetime,
dateto datetime,
commissionrate int)

Use dates that are sufficiently early/late to indicate "from the beginning
of time" or "until the end of time", such as '1-jan-1900' and '31-dec-2099'.

I've put commission as an int, but you call it whatever you want.

Then join like this:

select *
from orders o
join
daterangecommission c
on o.invoicedate between c.datefrom and c.dateto

As for checking the totals... that's a litle more complicated. My suggestion
would be to have a calculated field (persisted if possible) in the table
which gets populated with the running total. Then you can easily look at that
field to see if the bonus commission is due.

But I don't know of sites with sample code to do all this, sorry.

Rob


Show quoteHide quote

Surrogate or Natural Keys?
Execute stored procedure for each value returned in a query
Sql Injection
Insert multiple lines
Strange behavior with MS SQL