分配数量累计数据

[caijunling]

CREATE TABLE [dbo].[Stock](
	[StockID] [int] IDENTITY(1,1) NOT NULL,
	[PartName] [varchar](50) NULL,
	[Stock] [int] NULL,
	[DateValue] [datetime] NULL
) ON [PRIMARY]
GO


INSERT [dbo].[Stock] ([StockID], [PartName], [Stock], [DateValue]) VALUES (1, N'A', 100, CAST(0x00009DB700000000 AS DateTime))
INSERT [dbo].[Stock] ([StockID], [PartName], [Stock], [DateValue]) VALUES (2, N'B', -50, CAST(0x00009DB700000000 AS DateTime))
INSERT [dbo].[Stock] ([StockID], [PartName], [Stock], [DateValue]) VALUES (3, N'A', 60, CAST(0x00009DBE00000000 AS DateTime))
INSERT [dbo].[Stock] ([StockID], [PartName], [Stock], [DateValue]) VALUES (4, N'B', 53, CAST(0x00009DBE00000000 AS DateTime))
INSERT [dbo].[Stock] ([StockID], [PartName], [Stock], [DateValue]) VALUES (5, N'B', 41, CAST(0x00009DC500000000 AS DateTime))
INSERT [dbo].[Stock] ([StockID], [PartName], [Stock], [DateValue]) VALUES (6, N'A', 99, CAST(0x00009DC500000000 AS DateTime))

需要对A，B分配数值为100的数量，分配后A，B原来的数量加上分配后的数量的和的累计数值(按照天)相互最接近。如果不能平分的化，余数放在B上面。


分配 结果(要求解的列allocate_quantity,让allocate_quantity的数量分配到A，B后的数值,加上A,b原来的数量的累计数值(按照天) ---Allocated_Accumuated相互最接近。如果不能平分的化，余数放在B上面




ID PartNunber Qty datevalue allocate_quantity Allocated Allocated_Accumuated
1 A 100 2010-7-18 0 100 100
2 B -50 2010-7-18 100 50 50
3 A 60 2010-7-25 21 81 181 原来有60个，分配到了21个后＝81个，上一天还剩下100个，总共181个。
4 B 53 2010-7-25 79 132 182 原来有53个，分配到了79个后＝132个，上一天还剩下50个，总共182个。
5 B 41 2010-8-1 78 119 301
6 A 99 2010-8-1 22 121 302

[intellectual123456]

up...

[caijunling]

有没有什么思路可以提供

[永生天地]

为何 id 1,2的结果A=100,B=50

[永生天地]

[Quote=引用 1 楼 xys_777 的回复:]
为何 id 1,2的结果A=100,B=50
[/Quote]
知道了,不够分

[caijunling]

id 2 b的原来数量为-50,所以每天100的数量全部给 id 2 ,结果等于=-50+100=50
id 1 A的原来数量为100,为了使得AB 的分配后的数量接近,只分配0 ,结果等于=0+100=100

[xman_78tom]

if OBJECT_ID('dbo.stock') is not null
 drop table dbo.stock
go
CREATE TABLE [dbo].[Stock](
    [StockID] [int] NOT NULL,
    [PartName] [varchar](50) NULL,
    [Stock] [int] NULL,
    [DateValue] [datetime] NULL
) ON [PRIMARY];
GO
INSERT [dbo].[Stock] VALUES (1, N'A', 100, '2010-7-18');
INSERT [dbo].[Stock] VALUES (2, N'B', -50, '2010-7-18');
INSERT [dbo].[Stock] VALUES (3, N'A', 60, '2010-7-25');
INSERT [dbo].[Stock] VALUES (4, N'B', 53, '2010-7-25');
INSERT [dbo].[Stock] VALUES (5, N'A', 99, '2010-8-1');
INSERT [dbo].[Stock] VALUES (6, N'B', 41, '2010-8-1');
go

--select * from Stock;

declare @tab table
(StockID int,PartName varchar(50),Stock int,DateValue datetime,
 Allocate_Quantity int,Allocated int,Allocated_Accumulated int);
declare @StockID_A int, @PartName_A varchar(50), @Stock_A int,@DateValue datetime;
declare @StockID_B int, @PartName_B varchar(50), @Stock_B int;

-- 存储上次剩余的库存
declare @Accumulated_A int, @Accumulated_B int;
-- 存储当天的库存
declare @Allocated_Accumulated_A int, @Allocated_Accumulated_B int;
select @Allocated_Accumulated_A=0, @Allocated_Accumulated_B=0;

declare @Allocated_Accumulated int;
declare @Allocated int, @Allocate_Quantity int;

declare c cursor local 
for select * from Stock order by DateValue,PartName;
open c;

fetch next from c into @StockID_A ,@PartName_A,@Stock_A,@DateValue;
fetch next from c into @StockID_B ,@PartName_B,@Stock_B,@DateValue;
while @@FETCH_STATUS=0
begin
	select @Accumulated_A=@Allocated_Accumulated_A, @Accumulated_B=@Allocated_Accumulated_B;
	
	set @Allocated_Accumulated=@Stock_A+@Stock_B+@Accumulated_A+@Accumulated_B+100;
	set @Allocated_Accumulated_A=@Allocated_Accumulated/2;
	set @Allocated=@Allocated_Accumulated_A-@Accumulated_A;
	set @Allocate_Quantity=@Allocated-@Stock_A;
	if @Allocate_Quantity<0
		insert into @tab values(@StockID_A,@PartName_A,@Stock_A,@DateValue,
			0,@Stock_A,@Stock_A+@Accumulated_A);
	else if @Allocate_Quantity>100
		insert into @tab values(@StockID_A,@PartName_A,@Stock_A,@DateValue,
			100,@Stock_A+100,@Stock_A+@Accumulated_A+100);
	else 
		insert into @tab values(@StockID_A,@PartName_A,@Stock_A,@DateValue,
			@Allocate_Quantity,@Allocated,@Allocated_Accumulated_A);
	
	set @Allocated_Accumulated_B=@Allocated_Accumulated-@Allocated_Accumulated_A;
	set @Allocated=@Allocated_Accumulated_B-@Accumulated_B;
	set @Allocate_Quantity=@Allocated-@Stock_B;
	if @Allocate_Quantity<0
		insert into @tab values(@StockID_B,@PartName_B,@Stock_B,@DateValue,
			0,@Stock_B,@Stock_B+@Accumulated_B);
	else if @Allocate_Quantity>100
		insert into @tab values(@StockID_B,@PartName_B,@Stock_B,@DateValue,
			100,@Stock_B+100,@Stock_B+@Accumulated_B+100);
	else
		insert into @tab values(@StockID_B,@PartName_B,@Stock_B,@DateValue,
			@Allocate_Quantity,@Allocated,@Allocated_Accumulated_B);

	fetch next from c into @StockID_A ,@PartName_A,@Stock_A,@DateValue;
	fetch next from c into @StockID_B ,@PartName_B,@Stock_B,@DateValue;
end

close c;
deallocate c;

select * from @tab;

/*
1	A	100	2010-07-18 00:00:00.000	0	100	100
2	B	-50	2010-07-18 00:00:00.000	100	50	50
3	A	60	2010-07-25 00:00:00.000	46	106	181
4	B	53	2010-07-25 00:00:00.000	54	107	182
5	A	99	2010-08-01 00:00:00.000	21	120	301
6	B	41	2010-08-01 00:00:00.000	79	120	302
*/

[xman_78tom]

-- 少了一点东东
declare @tab table
(StockID int,PartName varchar(50),Stock int,DateValue datetime,
 Allocate_Quantity int,Allocated int,Allocated_Accumulated int);
declare @StockID_A int, @PartName_A varchar(50), @Stock_A int,@DateValue datetime;
declare @StockID_B int, @PartName_B varchar(50), @Stock_B int;

-- 存储上次剩余的库存
declare @Accumulated_A int, @Accumulated_B int;
-- 存储现在的库存
declare @Allocated_Accumulated_A int, @Allocated_Accumulated_B int;
select @Allocated_Accumulated_A=0, @Allocated_Accumulated_B=0;

declare @Allocated_Accumulated int;
declare @Allocated int, @Allocate_Quantity int;

declare c cursor local 
for select * from Stock order by DateValue,PartName;
open c;

fetch next from c into @StockID_A ,@PartName_A,@Stock_A,@DateValue;
fetch next from c into @StockID_B ,@PartName_B,@Stock_B,@DateValue;
while @@FETCH_STATUS=0
begin
	select @Accumulated_A=@Allocated_Accumulated_A, @Accumulated_B=@Allocated_Accumulated_B;
	
	set @Allocated_Accumulated=@Stock_A+@Stock_B+@Accumulated_A+@Accumulated_B+100;
	set @Allocated_Accumulated_A=@Allocated_Accumulated/2;
	set @Allocated=@Allocated_Accumulated_A-@Accumulated_A;
	set @Allocate_Quantity=@Allocated-@Stock_A;
	if @Allocate_Quantity<0
	begin
		insert into @tab values(@StockID_A,@PartName_A,@Stock_A,@DateValue,
			0,@Stock_A,@Stock_A+@Accumulated_A);
		set @Allocated_Accumulated_A=@Stock_A+@Accumulated_A;
	end
	else if @Allocate_Quantity>100
	begin
		insert into @tab values(@StockID_A,@PartName_A,@Stock_A,@DateValue,
			100,@Stock_A+100,@Stock_A+@Accumulated_A+100);
		set @Allocated_Accumulated_A=@Stock_A+@Accumulated_A+100;
	end
	else 
		insert into @tab values(@StockID_A,@PartName_A,@Stock_A,@DateValue,
			@Allocate_Quantity,@Allocated,@Allocated_Accumulated_A);
	
	set @Allocated_Accumulated_B=@Allocated_Accumulated-@Allocated_Accumulated_A;
	set @Allocated=@Allocated_Accumulated_B-@Accumulated_B;
	set @Allocate_Quantity=@Allocated-@Stock_B;
	if @Allocate_Quantity<0
	begin
		insert into @tab values(@StockID_B,@PartName_B,@Stock_B,@DateValue,
			0,@Stock_B,@Stock_B+@Accumulated_B);
		set @Allocated_Accumulated_B=@Stock_B+@Accumulated_B;
	end
	else if @Allocate_Quantity>100
	begin
		insert into @tab values(@StockID_B,@PartName_B,@Stock_B,@DateValue,
			100,@Stock_B+100,@Stock_B+@Accumulated_B+100);
		set @Allocated_Accumulated_B=@Stock_B+@Accumulated_B+100;
	end
	else
		insert into @tab values(@StockID_B,@PartName_B,@Stock_B,@DateValue,
			@Allocate_Quantity,@Allocated,@Allocated_Accumulated_B);

	fetch next from c into @StockID_A ,@PartName_A,@Stock_A,@DateValue;
	fetch next from c into @StockID_B ,@PartName_B,@Stock_B,@DateValue;
end

close c;
deallocate c;

select * from @tab;
/*
1	A	100	2010-07-18 00:00:00.000	0	100	100
2	B	-50	2010-07-18 00:00:00.000	100	50	50
3	A	60	2010-07-25 00:00:00.000	21	81	181
4	B	53	2010-07-25 00:00:00.000	79	132	182
5	A	99	2010-08-01 00:00:00.000	21	120	301
6	B	41	2010-08-01 00:00:00.000	79	120	302
*/

[永生天地]

猫，费了不少功夫啊

[claro]

windows的大虾

[永生天地]

[Quote=引用 3 楼 caijunling 的回复:]
id 2 b的原来数量为-50,所以每天100的数量全部给 id 2 ,结果等于=-50+100=50
id 1 A的原来数量为100,为了使得AB 的分配后的数量接近,只分配0 ,结果等于=0+100=100
[/Quote]
试了一下，好像超级麻烦，帮你顶

[caijunling]

可以参考一下这个贴:
http://topic.csdn.net/u/20100715/14/f3dc9ad4-2791-449f-9053-da04e39d5fea.html
但是后面计算累计数的时候,我感觉好像你中有我,我中有你,无法计算出来,请哪位数学好点的,想想办法.

[zhongxiutao123]

If you open the list of Scheduled Changes, you might be surprised that there is only the list of all existing schedules and you are able to edit them, but there is no option to create a new schedule. The reason for this is, that creating a new schedule does only make sense if you have a new task to be executed in a schedule. Therefore the button New Schedule shown in the screenshot above is only visible if you've clicked Save Scheduled in one of the CTI resources' configurations - in this case, you can pick one of the existing schedules from the list to append the current change to it, or you can create a new schedule through the New Schedule button. In both cases, the schedule's configuration is opened in the panel on the bottom (as described below in "Editing a schedule") and the current task (changes in the resources configuration) is appended to this schedule.

Editing a schedule
To edit e schedule, open the schedule's list and click on of the list's entries. The schedules configuration opens in the details panel. The same content is shown if you've just created a new schedule as described above.

The editing panel comprises two tabs and global buttons as follows:

Save: Saves all changes without closing the form
Save & Close: Saves all changes and closes the form
Cancel: Ignores all changes and closes the form without saving
Delete: Deletes the current schedule (shown only if saved at least once)

[zhongxiutao123]

The Schedule tab mainly comprises the time management of your schedule. The options in this form change depending on the options already chosen, e.g. if a schedule is not set active, the time planning fields are invisible.

Usually you create a schedule to use is, therefore the check box "Active" needs to be checked - if so, you can do the time planning and the server will execute the changes at the time configured. But you can also deactivate the schedule by unchecking the box, for example if you prepare a schedule for later use or you like to disable a schedule that is executed every week for some time without finally deleting it.

Schedules can be executed once at one date and time or periodically on a weekly basis within a given period of time. To change between both modes, use the Schedule Type radio buttons.

If you are configuring a schedule for single execution, only a execution date and time needs to be selected. All other fields shown on the screenshot above do not exist in this case.

For periodic schedules, you need to define a time frame in which the schedule is running by selecting a date and time for "valid from" and "valid to". Within this time frame, the task can be executed every day or on certain weekdays - configured in the "Days" row.

Last, you need to pick a time in the "Scheduled Time" row - this represents the time of the day when the task is executed.

Example: The screenshot above shows a task that is executed every Monday at 8 a.m. within a period running from Feb. 1st 2009 until Dec. 31st. 2010.

[zhongxiutao123]

On the "Changes" tab all tasks assigned to the current schedule are listed. One schedule can hold one or more independent tasks. For each task, a short summary is shown representing the configuration changes of the CTI resource objects affected.

All tasks are numbered in its headline; by clicking the gray arrow next to the number you can fold the tasks details and just show the headline's row - vice versa click it again to unfold it. To remove a task from your schedule, click the X-icon located on the right end of each task's headline row.

On the left half of each tasks' details all changes of the CTI resource's configuration is listed; using the Edit Change Set button allows you to reconfigure these changes.

The right part shows all objects that are affected by executing the task; the Change Resources button shows you a list of all objects of the appropriate type and allows you to select and unselect them for the scheduled task.

[caijunling]

楼上贴的什么东西啊