SQL query 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)

SQL query HelP

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-06-01 : 23:12:21

Hi..
I have problem.

Create table T
(empNo varchar(8),
workdate datetime,
empLeave integer,
leavePaid varchar(1)
)

insert into T
select '1','20060102',3,'Y' union
select '1','20060106',3,'Y' union
select '1','20060110',3,'Y' union
select '1','20060116',3,'Y' union
select '1','20060120',3,'Y' union
select '2','20060102',2,'Y' union
select '2','20060110',2,'Y' union
select '2','20060120',2,'Y' union
select '2','20060124',2,'Y' union
select '2','20060129',2,'Y'

Look at empNo 1, he has 3days leave but he was off for 5days.
i want to update the last two days to leavePaid = 'N'

Similarly for empNo 2, he has 2days leave but he was off for 5days.
i want to update the last three days to leavePaid = 'N'

anyone has idea?

... sql is fun...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-01 : 23:26:23

if you are still on the design, better redesign...

have an employeetable with the LeaveCredits, leavesTaken field...

update this field as leaves are consumed

you can still have a leave_transaction table that monitors when the leave was taken

so you can, insert into the leave_transaction table and update the LeaveCredits field without recomputing (so if you allow leaves even if your leave credits are 0,then you'll get a negative number on the leavestaken column

also, you can avoid this by handling it in your application, I think you've asked this before or was it another member? (odd you have the same requirement and design though)

--------------------
keeping it simple...

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-06-01 : 23:37:25

Yes..
Unfornately the design cant be changed anymore.
It has been deployed, and im now modifying the program
that is not designed and programmed by me, for better
performance.

Do you have any idea instead of redesigning it?

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-02 : 00:20:50

[code]
update T
set leavePaid = 'N'
from T
where (select count(*) from T x where x.empNo = T.empNo and x.workdate <= T.workdate) > T.empLeave[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources