Posting Yak Master
Posted - 11/15/2010 : 10:27:56
| I am trying to create a SQL query that will check if 3 variables ('ETO', 'Sick Leave', 'Vacation') + value ('code') total more than 40 hours (or 480 minutes) and then if they do, to reduce the total to 40 hours (or 480 minutes) and I want to make sure that I'm not missing anything when I write the query.
I have these two queries:
this one shows how many minutes per code that each employee has
SELECT s1.Employeenumber, s1.[Name], s2.Exceptiondate, code, s2 totalminutes into scratchpad4
(select distinct Employeenumber,[Name] from Scratchpad1) AS s1
(select employeenumber, exceptiondate, code, sum(duration) as totalminutes
where exceptiondate between '10/1/2010' and '10/15/2010'
group by employeenumber, exceptiondate, code) as s2
ON s1.Employeenumber = s2.Employeenumber
order by exceptiondate asc
this one sums those values (along with the amount of minutes they were logged in) and sums them as total minutes.
select name, employeenumber, summinutes, sum(summinutes/60) as total
select scratchpad2.name, scratchpad2.employeenumber, SUM(scratchpad2.minutes) + SUM(scratchpad4.totalminutes) as summinutes
inner join scratchpad4
on scratchpad2.name = scratchpad4.name
group by scratchpad2.name, scratchpad2.employeenumber
group by name, employeenumber, summinutes
I think what I need to do is to have a step between these that will check that the variables + totalminutes <=480, then check to see if code = 'ETO', 'Sick Leave', 'Vacation' but I'm just not sure of the logic here. Can someone please assist.