November 15, 2010 at 1:12 pm
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
November 15, 2010 at 1:15 pm
If you can post actual table names and structures, this can be done in a single query pretty easily.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 15, 2010 at 2:22 pm
Here's the DDL for both tables:
CREATE TABLE [dbo].[scratchpad2] (
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[employeenumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dateonly] [datetime] NULL ,
[minutes] [decimal](10, 2) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[scratchpad4] (
[Employeenumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Exceptiondate] [datetime] NULL ,
[varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[totalminutes] [int] NULL
) ON [PRIMARY]
GO
I hope that thats enough information.
November 16, 2010 at 6:32 am
Try something like this, see if it does what you need:
SELECT
Employeenumber,
Name,
CASE WHEN SUM(totalminutes) > 480 THEN 480
ELSE SUM(totalminutes)
END AS Minutes
FROM
dbo.scratchpad4
WHERE
code IN ('ETO', 'Sick Leave', 'Vacation')
AND Exceptiondate >= @StartDate
AND Exceptiondate < @EndDate ;
GROUP BY
Employeenumber,
Name ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 16, 2010 at 9:43 am
GSquared,
Thank you. That was exactly what I was looking for. Works just like it should.
Doug
November 16, 2010 at 2:45 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply