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
 General SQL Server Forums
 New to SQL Server Programming
 Problem regarding with the query

Author  Topic 

wormz666
Posting Yak Master

110 Posts

Posted - 2008-11-11 : 11:02:34
this is my table

working_day
-------------
month_work
year_work
workingdays

dtr
--------
empid
datelogin
timein
timeout

emp_mas
------------
empid
lname
fname
mname

emp_det
----------
empid
posid

position
--------------
posid
pos_desc
salary

i want to view all the employee the work on the month on june and
compare his/her workingdays on the working_day if the employee working days is not equal on the working days in the month on june
then it will get the difference of the salary

salary_per_day=salary/workingdays
i am having problem with the working_day table
please help.......

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-11 : 12:42:52
What is working day in hours? 8 hours??
Go to Top of Page

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-11 : 12:51:38
Since there is no datatype DATE and TIME, I assume Date and TIMEs are in Varchar.

declare @workhourperday int
set @workhourperday = 8

select p.salary, em.lname, em.fname, em.mname,
case when sum(datediff(ss,datelogin+' '+timein, datelogin+' '+timeout))/(3600*@workhourperday) <> (select workingdays from working_day)
THEN salary/(sum(datediff(ss,datelogin+' '+timein, datelogin+' '+timeout))/(3600*@workhourperday)) else salary/(select workingdays from working_day) end as salary_per_day
FROM
DTR d inner join emp_mas em
on d.empid = em.empid
inner join emp_det ed
on em.empid = ed.empid
inner join position p
on p.posid = ed.empid
WHERE
datepart(month, datelogin) = 6 and datepart(year, datelogin) = datepart(year, getdate())
Go to Top of Page

gedwards
Starting Member

19 Posts

Posted - 2008-11-12 : 11:59:55
We have a column in our table with a 1 for a workday, and a 0 for a non work day. That way we can include all days, and do a sum of fiscal days in a month.
Greg E

Greg E
Go to Top of Page
   

- Advertisement -