Update Table without Cursors 1

[TLeaders]

First let me thank everyone for the help in that past I may not post many questions but I learned SQL by reading tek-tips for direction and the looking up the details in BOL. Without the hints I would have been lost.

I have a problem that I have been trying to work through for a while. I want to update a table with the earliest date where the sum of all previous records for that department is positive. The records must be calculated in priority (load_order) order with the daily positive records added in last. I have written the update using cursors but for speed reasons I would prefer not to use them.

This is that code that works using cursors.

UPDATE A 
SET REQ_DT = DBO.fn_TL_Avail_Day(DEPT,IDI,LOAD_ORDER,REQ_DT)
FROM TLTEST A
WHERE WORK_FLAG='J'

What I would like is something like this.

UPDATE A 
SET REQ_DT = (SELECT TOP 1 B.REQ_DT FROM TLTEST B WHERE A.DEPT=B.DEPT AND (SELECT SUM(C.QTY_TOTAL) FROM TLTEST C WHERE B.DEPT=C.DEPT AND (B.LOAD_ORDER > C.LOAD_ORDER OR C.LOAD_ORDER = 999999)  ) > 0 ) -- ORDER BY REQ_DT,LOAD_ORDER)
FROM TLTEST A
WHERE WORK_FLAG='J'

Is this possible?
Thanks
Tom

/* drop the old table and function */

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[TLTEST]') AND OBJECTPROPERTY(ID, N'IsUserTable') = 1)
  DROP TABLE TLTEST
GO
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME = 'fn_TL_Avail_Day' AND TYPE = 'FN')
drop function dbo.fn_TL_Avail_Day
go

/* create new table */
Create table TLTEST(
Dept char (3) Null,
Par_item_no char (12),
Work_Flag char (1) NULL,
req_dt INT,
Qty_total decimal (14,6),
SEQCOUNT int,
Load_order int,
idi numeric identity
)

go

/* create new function */
create function dbo.fn_TL_Avail_Day
   (
@Dept char (3),
@idi int,
@load_order int,
@req_dt int
   )
   returns INT
as
 begin

declare @returnday INT
declare @Qty_total decimal (14,6)
declare @Running_total decimal (14,6)
set @Running_total = 0

DECLARE Work_cursor CURSOR FOR
select req_dt,qty_total
from TLTEST f
    where  dept = @dept and IDI<>@idi
      and (f.load_order < @load_order OR F.LOAD_ORDER = 999999)
    order by f.req_dt,load_order

OPEN Work_cursor

FETCH NEXT FROM Work_cursor into @returnday,@Qty_total
WHILE @@FETCH_STATUS = 0
BEGIN
   set @Running_total = @Running_total + @qty_total
   if @Running_total > 0
     begin
      goto Process_done
     end
   FETCH NEXT FROM Work_cursor into @returnday,@Qty_total
END
Process_done:
CLOSE Work_cursor
DEALLOCATE Work_cursor
if @req_dt > @returnday
  begin
   set @returnday = @req_dt
  end

return @returnday
end
go



/* build test data */
declare @daycount INT
declare @todaysdatePlus INT
set @daycount = 1

while @daycount < 100
  begin
    SET @todaysdatePlus = (SELECT CONVERT(INT,CONVERT(varchar (8),dateadd(d,@daycount,GETDATE()),112)))
    insert into TLTEST (Dept, Par_item_no, Work_Flag, req_dt, Qty_total, Load_order) VALUES ('A',' ','E',@todaysdatePlus,100,999999)
    insert into TLTEST (Dept, Par_item_no, Work_Flag, req_dt, Qty_total, Load_order) VALUES ('B',' ','E',@todaysdatePlus,100,999999)
    insert into TLTEST (Dept, Par_item_no, Work_Flag, req_dt, Qty_total, Load_order) VALUES ('C',' ','E',@todaysdatePlus,100,999999)
    insert into TLTEST (Dept, Par_item_no, Work_Flag, req_dt, Qty_total, Load_order) VALUES ('D',' ','E',@todaysdatePlus,100,999999)
    SET @todaysdatePlus = (SELECT CONVERT(INT,CONVERT(varchar (8),dateadd(d,@daycount+200,GETDATE()),112)))
    insert into TLTEST (Dept, Par_item_no, Work_Flag, req_dt, Qty_total, Load_order) VALUES ('D',' ','E',@todaysdatePlus,100,999999)
    SET @todaysdatePlus = (SELECT CONVERT(INT,CONVERT(varchar (8),dateadd(d,3,GETDATE()),112)))
    insert into TLTEST (Dept, Par_item_no, Work_Flag, req_dt, Qty_total, Load_order) VALUES ('A','TEST'+STR(@DAYCOUNT,3),'J',@todaysdatePlus,-30,999-@daycount)
    insert into TLTEST (Dept, Par_item_no, Work_Flag, req_dt, Qty_total, Load_order) VALUES ('B','TEST'+STR(@DAYCOUNT,3),'J',@todaysdatePlus,-60,999-@daycount)
    insert into TLTEST (Dept, Par_item_no, Work_Flag, req_dt, Qty_total, Load_order) VALUES ('C','TEST'+STR(@DAYCOUNT,3),'J',@todaysdatePlus,-90,999-@daycount)
    insert into TLTEST (Dept, Par_item_no, Work_Flag, req_dt, Qty_total, Load_order) VALUES ('D','TEST'+STR(@DAYCOUNT,3),'J',@todaysdatePlus,-120,999-@daycount)
    set @daycount = @daycount + 1
  end

GO



/* updating a record count */
UPDATE TLTEST
SET SEQCOUNT = (SELECT COUNT(*) FROM TLTEST B WHERE TLTEST.IDI > B.IDI )
GO 


/* I keep thinking that because the record count update described above works 
that I could do a similar process using sum().
THIS DOES NOT WORK
*/
UPDATE A 
--SET REQ_DT = (SELECT MIN(B.REQ_DT) FROM TLTEST B WHERE A.DEPT=B.DEPT HAVING SUM(B.QTY_TOTAL) > 0 ) -- ORDER BY REQ_DT,LOAD_ORDER)
--SET REQ_DT = (SELECT TOP 1 B.REQ_DT FROM TLTEST B WHERE A.DEPT=B.DEPT AND (SELECT SUM(C.QTY_TOTAL) FROM TLTEST C WHERE B.DEPT=C.DEPT AND C.DEPT+STR(C.REQ_DT,6)+STR(C.LOAD_ORDER,6) > B.DEPT+STR(B.REQ_DT,6)+STR(B.LOAD_ORDER,6)) > 0 ) -- ORDER BY REQ_DT,LOAD_ORDER)
SET REQ_DT = (SELECT TOP 1 B.REQ_DT FROM TLTEST B WHERE A.DEPT=B.DEPT AND (SELECT SUM(C.QTY_TOTAL) FROM TLTEST C WHERE B.DEPT=C.DEPT AND (B.LOAD_ORDER > C.LOAD_ORDER OR C.LOAD_ORDER = 999999)  ) > 0 ) -- ORDER BY REQ_DT,LOAD_ORDER)
FROM TLTEST A
WHERE WORK_FLAG='J'

GO


GO
/* update the dates using cursors
THIS DOES WORK
*/
UPDATE A 
SET REQ_DT = DBO.fn_TL_Avail_Day(DEPT,IDI,LOAD_ORDER,REQ_DT)
FROM TLTEST A
WHERE WORK_FLAG='J'
GO


select top 10 * from tltest order by dept,req_dt,load_order 
/* expected result
A  	TEST 99     	J	20060914	-30.000000	887	900	888
A  	TEST 98     	J	20060914	-30.000000	878	901	879
A  	TEST 97     	J	20060914	-30.000000	869	902	870
A  	TEST 96     	J	20060914	-30.000000	860	903	861
A  	            	E	20060914	100.000000	0	999999	1
A  	TEST 95     	J	20060915	-30.000000	851	904	852
A  	TEST 94     	J	20060915	-30.000000	842	905	843
A  	TEST 93     	J	20060915	-30.000000	833	906	834
A  	            	E	20060915	100.000000	9	999999	10
A  	TEST 92     	J	20060916	-30.000000	824	907	825
*/

 

[maswien]

I just want make this simplier, hop this helps

first create a table populate it:

<code>

create table tltest ( req_dt smalldatetime, ord int, qty int, dept varchar(20) )


insert into tltest values('2000-01-01', 1, -2, 'IT')
insert into tltest values('2000-01-02', 2, -3, 'IT')
insert into tltest values('2000-01-03', 3, 1, 'IT')
insert into tltest values('2000-01-04', 4, 5, 'IT')
insert into tltest values('2000-01-05', 5, 4, 'IT')

insert into tltest values('2000-02-01', 6, -6, 'HR')
insert into tltest values('2000-02-02', 7, -4, 'HR')
insert into tltest values('2000-02-03', 8, 7, 'HR')
insert into tltest values('2000-02-04', 9, 2, 'HR')
insert into tltest values('2000-02-05', 10, 2, 'HR')
insert into tltest values('2000-02-06', 11, 5, 'HR')

</code>

Then run following SQL to get the results

<code>
select t1.dept, t1.req_dt
from tltest t0, tltest t1
where t0.req_dt < t1.req_dt and t0.dept = t1.dept
group by t1.dept, t1.req_dt
having sum(t0.qty) < 0 and sum(t0.qty) + max(t1.qty ) > 0
</code>

 

[maswien]

the results is:

dept                 req_dt               
-------------------- --------------------IT                   2000-01-04 00:00:00
HR                   2000-02-05 00:00:00

 

[TLeaders]

maswiew

That is a great idea.
Join the table to itself. group one, sum the other. I wish I had thought of it.

Tonight after work I'll put the logic back to the org project. The code I posted was a stripped down version to show the issue.

I'm sure that this concept will work.

Thanks
Tom