Logic loop to check variables - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Logic loop to check variables

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2010-11-15 : 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
FROM
(select distinct Employeenumber,[Name] from Scratchpad1) AS s1
inner JOIN
(select employeenumber, exceptiondate, code, sum(duration) as totalminutes
from scratchpad3
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
from (
select scratchpad2.name, scratchpad2.employeenumber, SUM(scratchpad2.minutes) + SUM(scratchpad4.totalminutes) as summinutes
from scratchpad2
inner join scratchpad4
on scratchpad2.name = scratchpad4.name
group by scratchpad2.name, scratchpad2.employeenumber
) t
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.

Thank you

Doug

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources