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.
| Author |
Topic |
|
simflex
Constraint Violating Yak Guru
327 Posts |
 Posted - 2002-09-02 : 20:58:02
|
| I have a requirement for coming with consulting calculation.I have 4 fields called consulting_fee, consulting_duration, consulting_start_Date and Future.Consulting fee is the amount charged to a client for doing a consulting work for them.Consulting_duration is how long the consultation will last.Consulting_start_date is the date the consulting will start.Future is the leftover - I will explain this further.The deal is to divide consulting_fee by consulting_duration.Distribute the result among the consulting_duration.For instance, if the consuting_fee is $40, then divide consulting fee with consulting_duration (which we assume here is 4 months)let's say the result is 10 dollars for each month.we then distribute this 10 dollars among the 4 months.so january will be 10 dollars, february 10, mar 10 and april 10.So the client will be billed 10 dollars a month for 4 months.If for instance the 4 months starts oct, then we have 10 dollars for october, 10 for nov and 10 for dec. The remaining 10 dollars will go to the future field since it is a 12 month calendar that begins january.Here is the trip, you don't know before hand which month the consulting will start (consult_start_date) and you don't know the how long it is going to last (consult_duration).Have I confused you enough?If not, PLEASE!!!!!! I need your help!thanks in advance. |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-09-02 : 22:31:51
|
| For clarity I've renamed your "Future" input variable to "fiscal_overflow" below:feeduration (# months)startdatefiscal_overflowmonthly fee = fee / duration-- assumption: client billed in equal installmentsfiscal_overflow = { amount left over when calendar end reached } = fee - ((# of months from startdate to end of year) * {monthly fee})last day of year =dateadd( dd, -1, cast('01/01/' + cast(year(getdate())+1 as varchar) as datetime ))# of months from startdate to end of year =datediff( m, startdate, {last day of year})Jonathan{0} |
 |
|
|
shalini
Starting Member
3 Posts |
Posted - 2002-09-03 : 07:53:51
|
| monthly fee = fee / duration -- assumption: client billed in equal installments # of months from startdate to end of year = = 12 - DATEPART(mm,startDate)if # of months from startdate to end of year < duration thenfiscal_overflow = { amount left over when calendar end reached } = fee - ((# of months from startdate to end of year) * {monthly fee}) |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-09-03 : 09:11:46
|
| There seems to be an echo in here ...Jonathan{0} |
|
|
|
simflex
Constraint Violating Yak Guru
327 Posts |
Posted - 2002-09-03 : 16:57:39
|
| Thank you all very much!! |
|
|
|
|
|
|
|
|
complicated query