Allocation Query - 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

SQL Server Development (2000)

Allocation Query

Author

Topic

tschleiss
Starting Member

1 Post

Posted - 2005-04-01 : 01:34:02

I need an UPDATE query that will allocate an amount to only one row per ‘period’. Here is some example data (call it table A):

I StartD EndD Start Limit
1 1/1/58 1/1/59 0 500,000
2 1/1/59 1/1/60 0 100,000
3 1/1/59 4/1/59 100,000 200,000
4 4/1/59 1/1/60 100,000 300,000
5 1/1/60 1/1/61 0 500,000
6 1/1/60 2/1/60 500,000 100,000
7 1/1/60 2/1/60 600,000 200,000
8 2/1/60 1/1/61 500,000 100,000
9 2/1/60 1/1/61 600,000 200,000
…

Using records in table B, check to see if the Start Date – End Date’s fit into the range in table A, and if it does, subtract the Average from the Limit column. Here is an example:

(table B)

I StartD EndD Amount Average
1 1/1/58 1/1/80 500,000 22,727
2 1/1/49 1/1/67 100,000 5,555
3 1/1/60 1/1/2000 12,000 300
…

So record 1 in table B falls within the range of all records in table A. The average (22,727) should be subtracted from the limit in records: 1, 2, and 5, then go on to record 2 in table B. When index 1’s Limit = 0, that ‘period’ is closed. When index 2’s Limit = 0, then Index 3 and 4 should have the Average from tab B subtracted from their Limit (one third of the Average for Index 3, and two thirds of the average for Index 4). When Index 3 and 4 = 0, that ‘period’ is closed. When Index 5’s Limit = 0, then Index 6 and 8 will be allocated to. When 6 and 8’s Limit = 0, then Index 7 and 9 are allocated, and so on.

My program is already making the date comparisons, but it is allocating to every record in table A, instead of to only one period (or year) at a time.

UPDATE A
Set A.Limit = A.limit – B.Average
Where < links between A and B …> and
(Start = 0 and
Limit <> 0) or
…

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-04-04 : 00:43:56

Sorry t

I'm happy to help, but you'll have to explain some more. What is the reason for the code? What is the problem/situation you are trying to code?

Why for example, does Index 3 have one third of the average subtracted, but index four have 2 thirds? Is that because of the size of the date range? Also, am I correct in understanding that if the Avg is $22727 and Index 1 record limit is only $2727 then index 2 loses $20000?

Perhaps you could post some example result sets? And maybe some actual code for the query you have so far....

I suspect in the end you may need to use a cursor

to do exactly what you want...but that would be very slow

PS - In case anyone else can work out what is required before I get back to this, here's some DDL to set up the data


create table Limits(I Integer, StartD datetime, EndD datetime, Start money, Limit money)
insert into Limits(I, StartD, EndD, Start, Limit) values(1 ,  convert(datetime , '1/1/58') ,  convert(datetime , '1/1/59') , 0 , 500000)
insert into Limits(I, StartD, EndD, Start, Limit) values(2 ,  convert(datetime , '1/1/59') ,  convert(datetime , '1/1/60') , 0 , 100000)
insert into Limits(I, StartD, EndD, Start, Limit) values(3 ,  convert(datetime , '1/1/59') ,  convert(datetime , '4/1/59') , 100000 , 200000)
insert into Limits(I, StartD, EndD, Start, Limit) values(4 ,  convert(datetime , '4/1/59') ,  convert(datetime , '1/1/60') , 100000 , 300000)
insert into Limits(I, StartD, EndD, Start, Limit) values(5 ,  convert(datetime , '1/1/60') ,  convert(datetime , '1/1/61') , 0 , 500000)
insert into Limits(I, StartD, EndD, Start, Limit) values(6 ,  convert(datetime , '1/1/60') ,  convert(datetime , '2/1/60') , 500000 , 100000)
insert into Limits(I, StartD, EndD, Start, Limit) values(7 ,  convert(datetime , '1/1/60') ,  convert(datetime , '2/1/60') , 600000 , 200000)
insert into Limits(I, StartD, EndD, Start, Limit) values(8 ,  convert(datetime , '2/1/60') ,  convert(datetime , '1/1/61') , 500000 , 100000)
insert into Limits(I, StartD, EndD, Start, Limit) values(9 ,  convert(datetime , '2/1/60') ,  convert(datetime , '1/1/61') , 600000 , 200000)

create table Amounts(I Integer,StartD datetime, EndD datetime, Amount money, Average money)
insert into Amounts(I, StartD, EndD, Amount, Average) values(1 ,  convert(datetime , '1/1/58') ,  convert(datetime , '1/1/80') , 500000 , 22727)
insert into Amounts(I, StartD, EndD, Amount, Average) values(2 ,  convert(datetime , '1/1/1949') ,  convert(datetime , '1/1/67') , 100000 , 5555)
insert into Amounts(I, StartD, EndD, Amount, Average) values(3 ,  convert(datetime , '1/1/60') ,  convert(datetime , '1/1/2000') , 12000 , 300)

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources