none
writing a query to calculate month salary of Employee RRS feed

  • 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


    madeeha
    Friday, November 25, 2011 1:42 PM

All replies

  • Hi Madeeha,

    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:

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/threads

     

     

    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