-1

I have two tables 

Table 01
========
**ApprovedLeave**
UserName varchar
DateFrom varchar
DateTo varchar
NumofWorkingDays int
LeaveType varchar
Dept varchar
LStatus1 int 
LStatus2 int

Table02
=======
**LeaveEntitlement**
UserName varchar
TotalLeaveRemaining int

I need to get the NumofWorkingDays int value and subtract it from TotalLeaveRemaining value in Table 02 and save it in the same Location. Please help. This is my code so far. 

select (A.TotalLeaveRemaining-B.NumofWorkingDays)
from LeaveEntitlement as A join 
     ApprovedLeave as B
     on A.UserName= B.UserName
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Omesh
  • 45
  • 1
  • 1
  • 7
  • It does the mathematical operation. I need to update the TotalLeaveRemaining value to the new value receiving from the query. @TabAlleman – Omesh Jan 28 '16 at 16:28
  • 1
    Possible duplicate of [UPDATE from SELECT using SQL Server](http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server) – Tab Alleman Jan 28 '16 at 16:30

1 Answers1

0

A join is just the start. You also need to aggregate for each user and then do the arithmetic:

select A.UserName, (A.TotalLeaveRemaining - SUM(B.NumofWorkingDays))
from LeaveEntitlement as A join 
     ApprovedLeave as B
     on A.UserName = B.UserName
group by A.UserName, A.TotalLeaveRemaining;

In SQL Server, you would do the update using a similar query:

update le
    set TotalLeaveRemaining = al.sum_NumofWorkingDays
    from LeaveEntitlement le join
         (select UserName, sum(NumofWorkingDays) as sum_NumofWorkingDays
          from ApprovedLeave
          group by UserName
         ) al
         on le.UserName = al.UserName;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I think the problem is he doesn't know how to do the UPDATE. – Tab Alleman Jan 28 '16 at 16:28
  • more information , how to update the existing value to the value passing from the query – Omesh Jan 28 '16 at 16:33
  • Brilliant. Update works query works. Can you help me in passing this query in a Procedure where the input will come from a ASP.NET form? – Omesh Jul 11 '16 at 20:14
  • One problem is that it doesn't do the following operation ; TotalLeaveRemaing=TotalLeaveRemaing- NumofWorkingDays Please help. Thank you in advance. – Omesh Jul 11 '16 at 20:37
  • @omesh . . . New questions should be asked as questions and not in comments. – Gordon Linoff Jul 12 '16 at 02:32