Please start any new threads on our new site at 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 

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
(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.employeenumber, SUM(scratchpad2.minutes) + SUM(scratchpad4.totalminutes) as summinutes
from scratchpad2
inner join scratchpad4
on =
group by, 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


- Advertisement -