Complicated..Help - 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)

Complicated..Help

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-03-26 : 20:53:17

create table #SchemeHistory
( EmpNo varchar(10),
Scheme varchar(10),
Type varchar(1),
WEFDate datetime
)
create table #SchemeTypeOne
( Scheme varchar(10),
Period varchar(10),
Amt decimal(10,2),
MaxAmt decimal(10,2)
)
create table #SchemeTypeTwo
( Scheme varchar(10),
Period varchar(10),
Amt decimal(10,2),
MaxAmt decimal(10,2)
)

insert into #SchemeHistory
select 'EMP1', 'SCH2','1','2006/01/01' union all
select 'EMP1', 'SCH3','2','2006/01/05' union all
select 'EMP1', 'SCH1','1','2006/01/15' union all
select 'EMP1', 'SCH3','2','2006/01/25' union all
select 'EMP2', 'SCH1','1','2006/01/01' union all
select 'EMP2', 'SCH4','1','2006/01/10' union all
select 'EMP2', 'SCH1','2','2006/01/18' union all
select 'EMP3', 'SCH1','1','2006/01/02' union all
select 'EMP3', 'SCH4','2','2006/01/10' union all
select 'EMP3', 'SCH3','1','2006/01/20' union all
select 'EMP3', 'SCH2','1','2006/01/25'

insert into #SchemeTypeOne
select 'SCH1', 'Monthly',2.5, 20 union all
select 'SCH2', 'Monthly',1.5, 15 union all
select 'SCH3', 'Daily' ,3.0, 30 union all
select 'SCH4', 'Daily' ,2.0, 40

insert into #SchemeTypeTwo
select 'SCH1', 'Monthly',2.5, 100 union all
select 'SCH2', 'Daily' ,1.5, 100 union all
select 'SCH3', 'Monthly',5.0, 120 union all
select 'SCH4', 'Daily' ,2.0, 80
-------------------------------------------------

--Given parameters :
--@EndDate = '20060123',
--@StartDate = '20060101',
--@AmtPaid = 10.0

----------------- phase1----------------------------------
--I would like to have a new table #temp :
--EmpNo, Scheme, Max(WEFDate) where it's <= '20060125', Type, Period, Amt, MaxAmt
--with condition :
--if #SchemeHistory.Type = '1' then
--________The values of fields Period, Amt, and maxAmt are taken from #SchemeTypeOne
--else if #SchemeHistory.Type = '2' then
--________The values of fields Period, Amt, and maxAmt are taken from #SchemeTypeTwo

--the table #temp will be filled with these records :
--'EMP1', 'SCH1' ,'2006/01/15', 1 , 'Monthly' , 2.5 , 20
--'EMP1', 'SCH3' ,'2006/01/05', 2 , 'Monthly' , 5.0 , 120
--'EMP2', 'SCH4' ,'2006/01/10', 1 , 'Daily' , 2.0 , 40
--'EMP2', 'SCH1' ,'2006/01/18', 2 , 'Monthly' , 2.5 , 100
--'EMP3', 'SCH3' ,'2006/01/20', 1 , 'Daily' , 3.0 , 30
--'EMP3', 'SCH4' ,'2006/01/10', 2 , 'Daily' , 2.0 , 80

--I already have solution for phase1 (below), though i dont think
--it's an efficient solution.
--Any ideas or solutions will be much appreciated, because im
--dealing with a huge number of records.
------------------------------------------------------------------------------
create table #temp1
( EmpNo varchar(10),
CurrScheme varchar(10),
Type varchar(1),
WEFDate datetime
)
insert into #temp1 (EmpNo,CurrScheme,WEFDate,Type)
SELECT t1.empno, t1.scheme, t1.wefdate, t1.type
FROM #SchemeHistory t1
WHERE t1.WEFDate = (
SELECT MAX(WEFDAte)
FROM #SchemeHistory t3
WHERE t3.wefdate <= '20060123' AND
t3.empno = t1.empno and t3.type = t1.type
)
ORDER BY t1.type, t1.empno

create table #temp2
( EmpNo varchar(10),
CurrScheme varchar(10),
Type varchar(1),
WEFDate datetime,
Period varchar(10),
Amt decimal(10,2),
MaxAmt decimal(10,2)
)
insert into #temp2 (EmpNo,CurrScheme,Type,WEFDate,Period,Amt,MaxAmt)
(select T1.*, T2.Period,T2.Amt,T2.MaxAmt from #temp1 T1, #SchemeTypeOne T2
where T1.CurrScheme = T2.Scheme and T1.Type= '1'
union
select T1.*, T2.Period,T2.Amt,T2.MaxAmt from #temp1 T1, #SchemeTypeTwo T2
where T1.CurrScheme = T2.Scheme and T1.Type = '2'
)
select * from #temp2
------------------------------------------------------------------------------

--And now, im struggling with phase2, i dun really keen on using temporary
--tables again and again. is there any other ways? THx..thx..
------------------ phase2---------------------------------
--For each employee in #temp
----For each type(1 or 2)
------if period='Monthly' then
--------if Amt + AmtPaid <= MaxAmt then insert into #result all the information from #temp
------elseif period='Daily' then
--------From i = @StartDate To @EndDate
----------if Amt + AmtPaid <= MaxAmt then
------------insert into #result with all information from #temp including i
------------AmtPaid += AmtPaid

----------------------------------------------------------
SOrry if it's kinda messy.
Thanks btw for ur kind attention and ideas.
Regards'
Erwine

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-26 : 21:17:50

Phase 1 :

declare
	@StartDate	datetime,
	@EndDate	datetime,
	@AmtPaid	decimal(10,2)
	
select 	@StartDate 	= '20060101',
	@EndDate 	= '20060123',
	@AmtPaid 	= 10.0

select 	sh.EmpNo, sh.Scheme, sh.Type, sh.WEFDate, 
	(case when sh.Type = 1 then s1.Period else s2.Period end) 	as Period,
	(case when sh.Type = 1 then s1.Amt    else s2.Amt end) 	as Amt,
	(case when sh.Type = 1 then s1.MaxAmt else s2.MaxAmt end) 	as MaxAmt
from	(
		select	EmpNo, Type, max(WEFDate) as Max_WEFDate
		from	#SchemeHistory
		where	WEFDate		>= @StartDate
		and	WEFDate		<= @EndDate
		group by EmpNo,Type
	) as sm
	inner join #SchemeHistory sh
	on	sh.EmpNo	= sm.EmpNo
	and	sh.Type		= sm.Type
	and	sh.WEFDate	= sm.Max_WEFDate
	left join #SchemeTypeOne s1
	on	sh.Scheme	= s1.Scheme
	left join #SchemeTypeTwo s2
	on	sh.Scheme	= s2.Scheme
order by sh.EmpNo, sh.Type

Phase 2 :
Can you post what is the #result table structure ? and the expected result ?

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-03-27 : 00:44:51

--So, if 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
------)
-------------------------------------------------------------------------------

--the result will be like this :
--|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

thx KHTan

... sql is fun...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources