other than CURSOR??? - 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)

other than CURSOR???

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-03-27 : 22:02:19

--i have #temp:

--EMP1 SCH1 1 2006-01-15 00:00:00.000 Monthly 2.50 20.00
--EMP1 SCH3 2 2006-01-05 00:00:00.000 Monthly 5.00 120.00
--EMP2 SCH4 1 2006-01-10 00:00:00.000 Daily 2.00 25.00
--EMP2 SCH1 2 2006-01-18 00:00:00.000 Monthly 2.50 100.00
--EMP3 SCH3 1 2006-01-20 00:00:00.000 Daily 3.00 30.00
--EMP3 SCH4 2 2006-01-10 00:00:00.000 Daily 2.00 80.00

create table #result
( EmpNo varchar(10),
Type varchar(1),
CurrScheme varchar(10),
Period varchar(10),
PayDate datetime,
Amt decimal(10,2)
)

-------------------------------------FLOW-------------------------------------
--Given @startDate = '20060101' and @postingDate = '20060123' and @AmtPaid = 5
--I wanna have #result :
--If the latest WEFDate of EMP1 of Type 1 is Monthly then
----insert into #result ((select * from #temp), getdate() as PayDate)
--If the latest WEFDate of EMP1 of Type 2 is Daily then
----from @startDate to @postingDate
------if amt + @AmtPaid <= @maxAmt then
------( insert into #result ((select * from #temp), getdate() as PayDate);
------ @AmtPaid = @AmtPaid + amt
------)
-------------------------------------------------------------------------------

--i want to pump data into #result:
--|Empno, type, scheme, period, paydate, amt
--|------------------------------------------
--|EMP1, 1, sch1, monthly, @postingDate, 2.50
--|EMP1, 2, sch3, monthly, @postingDate, 5.00
--|EMP2, 1, sch4, daily, 2006-01-10, 2.00
--|EMP2, 1, sch4, daily, 2006-01-11, 2.00
--|EMP2, 1, sch4, daily, 2006-01-12, 2.00
--|EMP2, 1, sch4, daily, 2006-01-13, 2.00
--|EMP2, 1, sch4, daily, 2006-01-14, 2.00
--|EMP2, 1, sch4, daily, 2006-01-15, 2.00
--|EMP2, 1, sch4, daily, 2006-01-16, 2.00
--|EMP2, 1, sch4, daily, 2006-01-17, 2.00
--|EMP2, 1, sch4, daily, 2006-01-18, 2.00
--|EMP2, 1, sch4, daily, 2006-01-19, 2.00 --(it stops because @AmtPaid + (10*2.00) reaches @maxAmt(25))
--|EMP2, 2, sch1, monthly, @postingDate, 2,50
--|EMP3, 1, sch3, daily, 2006-01-20, 3.00
--|EMP3, 1, sch3, daily, 2006-01-21, 3.00
--|EMP3, 1, sch3, daily, 2006-01-22, 3.00
--|EMP3, 1, sch3, daily, 2006-01-23, 3.00
--|EMP3, 2, sch4, daily, 2006-01-10, 2.00
--|EMP3, 2, sch4, daily, 2006-01-11, 2.00
--|EMP3, 2, sch4, daily, 2006-01-12, 2.00
--|EMP3, 2, sch4, daily, 2006-01-13, 2.00
--|EMP3, 2, sch4, daily, 2006-01-14, 2.00
--|EMP3, 2, sch4, daily, 2006-01-15, 2.00
--|EMP3, 2, sch4, daily, 2006-01-16, 2.00
--|EMP3, 2, sch4, daily, 2006-01-17, 2.00
--|EMP3, 2, sch4, daily, 2006-01-18, 2.00
--|EMP3, 2, sch4, daily, 2006-01-19, 2.00
--|EMP3, 2, sch4, daily, 2006-01-20, 2.00
--|EMP3, 2, sch4, daily, 2006-01-21, 2.00
--|EMP3, 2, sch4, daily, 2006-01-22, 2.00
--|EMP3, 2, sch4, daily, 2006-01-23, 2.00

i thought of using cursor, and it can be done.
but since it's gonna be a huge table, does anybody has better ideas (instead of using cursor)?

... sql is fun...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources