gradually increasing value - SQL Server Forums

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)

gradually increasing value

Author

Topic

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-02-25 : 17:20:37

Has anyone done this before:
Example:
A=190 (varies)
parts = small, med, large(THE NUMBER of Parts will vary - there can be 1 part or 10 part, etc..)

Need to separate and assign A to each part but increasing the value for each part equally from the previous. SO that:

small= 40, med= 60 , large= 90
or something like that... the idea is that values need to increase as the parts value increases, but the total value has to be still 190 (or whatever it will be)

Hope this makes sense

Thanks,
Juls

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-02-25 : 17:51:48

I am not understanding this. Could you explain it further for us, possibly by giving a couple of examples?

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-02-25 : 20:47:53

Sounds like you want a set which is created by distributing a certain value A according to some rules
Can you provide ddl and like tduggan said we really need some examples on how you plan to distribute A in order to help you.

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-02-26 : 08:26:18

I need to distribute a value A across given number of parts. My "parts" are really dates and A is money.

In reality, I need this:

As the date increaese the value of money allocated to it from the total A will be greater. So that when $ distributed across all the available dates the total will be equal to A.

So if I have 3 dates: jan, feb, March and my total money is $190.

jan= $40, feb= $60 , march= $90 = $190.

I don't have any specific rules just so that the values increase and when summed will be equal the total.

Hope this makes more sense.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-26 : 09:23:31

Something like this might help:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10177

As far as adjusting the value, you'd have to modify the code to adjust that. Since you indicated there's no particular rule to this pattern I don't see why it's not just evenly divided over the number of parts.

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-02-26 : 10:01:51

Thanks.

However, this procedure asks for size of each order (part) to be specified. I, on the other hand, do not need this. I need each value to be increasing as parts increase. There is no rule by how much it will increase as long as the sum is equal the original total.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-26 : 10:37:33

How about something like this:

-- First, you need a table of months:

declare @Months table ([Month] int primary key)
insert into @Months
select 1 union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9 union
select 10 union
select 11 union
select 12

-- ..etc ... that's just an example. you will need
-- more if you ever spread over more than 12 months.

-- Now, for parameters:
--

declare @StartVal money;
declare @TotalVal money;
declare @StartMonth int;
declare @TotalMonths int;

set @StartVal = 40;
set @TotalVal = 190;
set @StartMonth = 1;
set @TotalMonths = 3;

-- And here's your SQL:

SELECT Month, @StartVal + (Month - @StartMonth) * (@totalVal - @StartVal * @TotalMonths)/@TotalMonths as Amount
FROM
@Months M
WHERE [Month] between @StartMonth and (-1 + @StartMonth + @TotalMonths)

Replace all references to @StartMonth with a hard-coded 1 and remove that parameter if the starting point is always 1; it makes it a little easier to understand and shorter:

SELECT Month, @StartVal + (Month - 1) * (@totalVal - @StartVal * @TotalMonths)/@TotalMonths as Amount
FROM
@Months M
WHERE [Month] between 1 and @TotalMonths

- Jeff

Edited by - jsmith8858 on 02/26/2003 10:42:40

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-02-26 : 12:04:52

Thanks Jeff,

But how will this be different if instead of Month I have full date (mm/dd/yy) and the more current the date the more of the amount it will be getting from the total.

thanks again

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-26 : 12:32:28

Just choose any starting date you want, or make it a parameter.

Then, just use the DateAdd() function to add as many months as you need to the starting date, based on the value in the @months table.

For example, if @StartDate is your starting date:

SELECT
DATEADD(m,Month-1, @StartDate) as Month,
@StartVal + (Month - 1) * (@totalVal - @StartVal * @TotalMonths)/@TotalMonths as Amount
FROM
@Months M
WHERE [Month] between 1 and @TotalMonths

- Jeff

Edited by - jsmith8858 on 02/26/2003 12:32:51

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-02-26 : 13:10:37

But my dates are all different, they are not sequential by month.

I may have 1/1/2003, 2/5/2003, 2/25/2003 and I need to spread the total value across these.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-26 : 13:48:56

Juls --

You gotta help me out here! Where are the dates stored? How do we know which dates to spread it out over? Are they in a table somewhere? do they need to be passed as parameters? should they be spread equally even with a pattern like 1/1/2000, 1/2/2000, 3/12/2000?

- Jeff

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-02-26 : 14:06:43

I have a table like this:
ID Date Totalmoney IndivMoney
1 1/1/03 190 need this
1 2/1/03 190 need this
1 2/15/03 190 need this
2 1/10/03 1000 need this
2 2/11/03 1000 need this
2 2/18/03 1000 need this
2 2/20/03 1000 need this

TotalMoney need to be distributed increasingly over each increasing date within each ID. As you can see, the number of dates per ID varies.

Thanks

Edited by - Juls on 02/26/2003 14:09:00

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-26 : 14:20:22

Juls -- hopefully you can see how we all had trouble providing a solution without the information you just gave.

Next question -- how is the starting amount for each id determined?

- Jeff

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-02-26 : 14:24:53

Yes, I do and I appologize.
I really appreciate everyone's time and help on this.

There is no starting amount for each id.. it can be any number. As far as I could get it can be Totalmoney / square(numer of dates)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-02-26 : 14:29:13

Juls-

On that same note, realize that you're asking for an extremely peculiar request, it's certainly NOT logical to us. If there's any background you can give on WHY this function needs to be the way it is, it would help tremendously. Also realize that "because it's a requirement" is not a valid explanation. If it is a requirement, and no one indicated what the logic might be (if any), it's reasonable to question whether it should be done this way at all. None of the data follows any pattern that can be utilized to fit your needs. When something is this difficult it's usually a good sign that the requirement should be changed or at least reconsidered.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-26 : 14:39:20

Ok, look at what I've listed below. It is tricky because I need to create a "running total" to keep track of which Part each date is for each ID; i also need to calculate the total number of parts which is the SUM of that running total. Instead of actually SUM'ing the running total, ~~i used for formula for N! which is (N*(N+1))/2 .~~ Don 't worry about that if it makes no sense.

SO, at that point, for each date, we know how many total parts there are and which part # we are on. That ratio is what we multiply the total by to get the amount for that date.

declare @D table (ID int, Date datetime, Total money)

insert into @d
select 1, '1/1/03',190 union
select 1,'2/1/03',190 union
select 1, '2/15/03',190 union
select 2, '1/10/03',1000 union
select 2, '2/11/03',1000 union
select 2, '2/18/03',1000 union
select 2, '2/20/03',1000

Select Parts.ID, Date, Totals.Total, 1.0 * Part/TotalParts * Totals.Total as Amount
FROM
-- this returns a running count of each date; I call the running
-- count "parts".
(SELECT A.*, (SELECT COUNT(*) FROM @D B WHERE A.ID = B.ID AND A.Date >= B.Date) as Part
From @D A) Parts
INNER JOIN
-- this returns the total number of parts for each ID, along with
-- the total dollar amount we are trying to get to.
(SELECT ID, Total, (COUNT(*) * (COUNT(*)+1))/2 as TotalParts FROM @d GROUP BY ID, Total) Totals
ON
Parts.ID = Totals.ID

- Jeff

EDIT: Oopps, it's not N!, but rather the sum from 1...N which is (N*(N+1))/2 ... that's what I meant.

Edited by - jsmith8858 on 02/26/2003 14:43:58

Edited by - jsmith8858 on 02/26/2003 14:47:36

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-02-26 : 14:40:02

No it's not "because it's a requirement"

Here is some logic:

Each Id(project) has an Amount(total allocated to it) and a number of records with dates(steps). As the steps (dates) move forward more money out of the total will be allocated to it.

I need to spread the amount increasingly as dates increase.

I see how this makes no sense that there is no defined start amount but this is all to show that more money will be needed for future steps.

Hope this is better explanation

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-02-26 : 15:03:57

Jeff, luv ya, you are a genious!!!!!

I know I am pushing my luck but....

can this be modified so that the result is given in whole numbers and still adds up to the total. Round up to the nearest whole?

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-26 : 18:00:14

I'm trying to come up with a solution for that one ... I'll post when I get a chance.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources