solution needed - 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)

solution needed

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-04-17 : 01:37:51

create table T1
(
empID int,
leave int
)
create table T2
(
empid int,
workdate datetime,
leave char,
paid char
)

insert into T1
select 1, 3 union all
select 2, 4

insert into T2 (empid, workdate, leave)
select 1, '20060101','Y' union all
select 1, '20060102','Y' union all
select 1, '20060103','Y' union all
select 1, '20060104','N' union all
select 1, '20060105','N' union all
select 1, '20060106','N' union all
select 1, '20060107','N' union all
select 1, '20060108','N' union all
select 1, '20060109','Y' union all
select 1, '20060110','Y' union all
select 1, '20060111','Y' union all
select 1, '20060112','N' union all
select 1, '20060113','N' union all
--------------------------------------
select 2, '20060101','Y' union all
select 2, '20060102','Y' union all
select 2, '20060103','N' union all
select 2, '20060104','Y' union all
select 2, '20060105','Y' union all
select 2, '20060106','N' union all
select 2, '20060107','Y' union all
select 2, '20060108','N'

Hi all, i would like to know about the solution for my problem.
Using the tables defined above, i actually want to put information whether it is paid leave or unpaid leave.
required output :
1, '20060101','Y', 'paid' -- T1.leave -= 1 -> 2
1, '20060102','Y', 'paid' -- T1.leave -= 1 -> 1
1, '20060103','Y', 'paid' -- T1.leave -= 1 -> 0
1, '20060104','N',
1, '20060105','N',
1, '20060106','N',
1, '20060107','N',
1, '20060108','N',
1, '20060109','Y', 'unpaid'
1, '20060110','Y', 'unpaid'
1, '20060111','Y', 'unpaid'
1, '20060112','N',
1, '20060113','N',
--------------------------------------
2, '20060101','Y', 'paid' -- T1.leave -= 1 -> 3
2, '20060102','Y', 'paid' -- T1.leave -= 1 -> 2
2, '20060103','N',
2, '20060104','Y', 'paid' -- T1.leave -= 1 -> 1
2, '20060105','Y', 'paid' -- T1.leave -= 1 -> 0
2, '20060106','N',
2, '20060107','Y', 'unpaid'
2, '20060108','N'

anyone? thx for ur kind attention.

... sql is fun...

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-18 : 05:55:04

Hi erwine,

Here's one way which seems to give what you're asking...

--data
if object_id('tempdb.dbo.#t1') is not null drop table #T1
create table #T1
(
empID int,
leave int
)

if object_id('tempdb.dbo.#t2') is not null drop table #T2
create table #T2
(
empid int,
workdate datetime,
leave char,
paid char(6)
)

insert into #T1
select 1, 3 union all
select 2, 4

insert into #T2 (empid, workdate, leave)
select 1, '20060101','Y' union all
select 1, '20060102','Y' union all
select 1, '20060103','Y' union all
select 1, '20060104','N' union all
select 1, '20060105','N' union all
select 1, '20060106','N' union all
select 1, '20060107','N' union all
select 1, '20060108','N' union all
select 1, '20060109','Y' union all
select 1, '20060110','Y' union all
select 1, '20060111','Y' union all
select 1, '20060112','N' union all
select 1, '20060113','N' union all
--------------------------------------
select 2, '20060101','Y' union all
select 2, '20060102','Y' union all
select 2, '20060103','N' union all
select 2, '20060104','Y' union all
select 2, '20060105','Y' union all
select 2, '20060106','N' union all
select 2, '20060107','Y' union all
select 2, '20060108','N'

--calculation
update t2 set paid = 'paid'
from #t1 t1 inner join  #t2 t2 on t1.empid = t2.empid and t2.leave = 'Y'
where (select count(*) from #t2 where empid = t2.empid and leave = 'Y' and workdate < t2.workdate) < t1.leave

update #t2 set paid = 'unpaid' where leave = 'Y' and paid is null

--results
select * from #t2

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources