SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Logic loop to check variables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dougancil
Posting Yak Master

USA
217 Posts

Posted - 11/15/2010 :  10:27:56  Show Profile  Send dougancil a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000