CODEWITHSUNDEEP
sqlsql-server
Ravi Kiran Ayinampudi
Ravi Kiran Ayinampudi

Reputation: 599

How to perform calculations in sql query

My requirement is to calculate PAID DAYS in a month.It is done PAID DAYS=NoOfDaysInMonth-LEAVES.

For getting No of days i'm using 

 declare @DATE datetime
 declare @NoOfDaysInMonth int
 set @DATE='2014-3-01'

 select @NoOfDaysInMonth= DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@DATE),0)))
 print @NoOfDaysInMonth

This returns 31 days.

Below is my previous query which is not working.

  *******  datediff(day, MONTH(getdate()), dateadd(month, 1, month(getdate())))-(SELECT TOTALUNPAIDLEAVES FROM  FN_GETTOTALLEAVESFORANEMPLOYEE(VW_EMPLOYEE_DETAILS.EMPLOYEEID,'''+@YEAR+''','''+@MONTH+''')) as [Paid Days] FROM  VW_EMPLOYEE_DETAILS'

In place of

 datediff(day, MONTH(getdate()), dateadd(month, 1, month(getdate())))

I need to pass the variable @NoOfDaysInMonth.

 @NoOfDaysInMonth-(SELECT TOTALUNPAIDLEAVES FROM  FN_GETTOTALLEAVESFORANEMPLOYEE(VW_EMPLOYEE_DETAILS.EMPLOYEEID,'''+@YEAR+''','''+@MONTH+''')) as [Paid Days] FROM  VW_EMPLOYEE_DETAILS

After modifying my code is

SET @COMMANDS=@COMMANDS+' (SELECT TOTALLEAVESUSED FROM FN_GETTOTALLEAVESFORANEMPLOYEE(VW_EMPLOYEE_DETAILS.EMPLOYEEID,'''+@YEAR+''','''+@MONTH+''')) as [Total Leaves Used],(SELECT TOTALUNPAIDLEAVES FROM FN_GETTOTALLEAVESFORANEMPLOYEE(VW_EMPLOYEE_DETAILS.EMPLOYEEID,'''+@YEAR+''','''+@MONTH+''')) as [Total UnPaid  Leaves], ( select @DaysInMonth -(SELECT TOTALUNPAIDLEAVES FROM FN_GETTOTALLEAVESFORANEMPLOYEE(VW_EMPLOYEE_DETAILS.EMPLOYEEID,'''+@YEAR+''','''+@MONTH+'''))) as [Paid Days] FROM  VW_EMPLOYEE_DETAILS'

Upvotes: 0

Views: 1672

Answers (1)

Kenan Zahirovic
Kenan Zahirovic

Reputation: 1597

Your calculation is fine. The last statement should be:

select @NoOfDaysInMonth-(SELECT TOTALUNPAIDLEAVES FROM ...) as Result

Keep in mind to execute this as a batch (this statement together with previous code, as a group of statements). Otherwise, the variable @NoOfDaysInMonth will be lost.

Upvotes: 1

Related Questions