# writing a query to calculate month salary of Employee

• ### General discussion

• i have written a query, but the answer coming is not correct. please tell me if there is a problem in using the cross join and inner joins

select

Attend.EmpCode, (Count(Distinct CONVERT(Date,Attend.TimeIn))) *

(

Emp.BasicSal/(DATEDIFF(DAY,'2011-10-01','2011-10-31')+1-COUNT(TransDate)-(dbo.CountSundays('2011-10-01','2011-10-31'

))))

As MonthSal

from

Holiday cross join Emp inner join Attend on Emp.EmpCode=Attend.EmpCode

where

TransDate between '2011-10-01' And '2011-10-31' And CONVERT(Date,TimeIn) between '2011-10-01' And '2011-10-31'

group

by Attend.EmpCode,Emp.BasicSal

the answer coming is

EmpCode  Monthsal

1             908

2             908

but here the multiplication is not correct, the answer should be 832

Friday, November 25, 2011 1:42 PM

### All replies

Try this sql. If you are not getting the correct result, give here the structure of your used tables with dummy data.

One suggestion for you that for T-sql related problems use this link. Because here you will get more relevant Answers related to T-sql problems:

select

Attend.EmpCode, (Count(Distinct CONVERT(Date,Attend.TimeIn))) *

(

Emp.BasicSal/((DATEDIFF(DAY,'2011-10-01','2011-10-31')+1)-(COUNT(TransDate)+(dbo.CountSundays('2011-10-01','2011-10-31'))))

)

As MonthSal

from

Holiday cross join Emp inner join Attend on Emp.EmpCode=Attend.EmpCode

where

TransDate between '2011-10-01' And '2011-10-31' And CONVERT(Date,TimeIn) between '2011-10-01' And '2011-10-31'

group

by Attend.EmpCode,Emp.BasicSal

Aftab Ansari
Monday, November 28, 2011 7:21 AM