December 7, 2010 at 8:59 am
I have the following query:
SELECT DISTINCT [ScratchPad5].EmployeeNumber, Sum([ScratchPad5].sumhours),
case when [sumhours]>40 THEN [sumhours]-40 ELSE 0 END AS ot, case when [sumhours]>40 THEN [sumhours]-[ot] ELSE [sumhours] END AS RegHours
FROM ScratchPad5
GROUP BY [ScratchPad5].EmployeeNumber, sumhours, ot
and the result set that it is giving me is this:
Employeenumber (No Column Name) ot RegHours
8247 23.683166 .000000 23.683166
8330 3.000000 .000000 3.000000
8378 29.226166 .000000 29.226166
8389 27.221166 .000000 27.221166
8428 32.371000 .000000 32.371000
8433 31.898833 .000000 31.898833
8442 29.853500 .000000 29.853500
8454 17.349500 .000000 17.349500
8455 25.910500 .000000 25.910500
8462 35.770833 .000000 35.770833
8464 38.410332 .000000 19.205166
8465 30.944500 .000000 30.944500
8466 29.450666 .000000 29.450666
8467 51.734000 11.734000 NULL
and it what I'm needing it to do is for entries like the last one, to show me both the total time worked (No Column Name) and the OT but then it should show 40 hours for Reg Hours.
I also tried this query:
SELECT [ScratchPad5].EmployeeNumber, Sum([ScratchPad5].sumhours),
case when [sumhours]>40 THEN [sumhours]-40 ELSE 0 END AS ot,
case when [sumhours]>40 THEN [sumhours]-coalesce([ot],0) ELSE [sumhours] END AS RegHours
FROM ScratchPad5
GROUP BY [ScratchPad5].EmployeeNumber, sumhours, ot
here is my the line of my result set that shows me that I'm not quite getting the information that I need:
Employeenumber (No column name) ot Regular Hours
8467 51.734000 11.734000 51.734000
and what this should have for Regular Hours is 40 hours. As you can see from the query that it shows me the total number of hours, not just the total number of "regular hours."
Can anyone please assist with this?
Thank you
Doug
December 7, 2010 at 10:38 am
This isn't too bad, but you just need to wrap an override on the first column with a case statement so that when it's over 40 hours you simply show 40. Kind of like how you did with the OT, but instead of 0, do 40.
If you post the DDL of the table and a bit of sample data (see the first link in my sig), someone will most likely hand you a tested piece of code to show you.:-)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 7, 2010 at 11:25 am
The DDL for that table is
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[scratchpad5]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[scratchpad5]
GO
CREATE TABLE [dbo].[scratchpad5] (
[EmployeeNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[summinutes] [decimal](38, 2) NULL ,
[sumhours] [decimal](38, 6) NULL ,
[specminutes] [decimal](38, 2) NULL ,
[ot] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Some sample data that you can use is as follows:
8466 Akieva Saunders 1767.04 29.450666120
8465Alisha Byrd 1856.67 30.9445 0
8467Brenda Brown 3104.04 51.734 7
8464Chanel Jones 1152.31 19.2051660
8330Christine Seippel 180 3 180
8455Christy Clayton 1554.63 25.9105 255
I hope that that's clear enough.
Thank you
Doug
December 9, 2010 at 1:44 pm
Did I post enough information? Can anyone please assist with this?
Thank you
Doug
December 9, 2010 at 2:36 pm
Sorry, swung in, saw that the sample data wasn't consumable (do read that link, it explains why), meant to come back and forgot. Btw, the second set of sample data ignores a column, either specminutes or ot. Not sure which.
You were close on the first pass, try this (it's untested):
SELECT DISTINCT
[ScratchPad5].EmployeeNumber,
Sum([ScratchPad5].sumhours),
SUM( casewhen [sumhours]>40
THEN [sumhours]-40
ELSE 0
END ) AS TotalOT,
SUM( casewhen [sumhours]>40
THEN 40
ELSE [sumhours]
END ) AS TotalRegHours
FROM
ScratchPad5
GROUP BY
[ScratchPad5].EmployeeNumber,
sumhours,
ot
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 10, 2010 at 12:22 pm
Craig,
Sorry about not getting the data right on that. I tested your query and it works the way that I need it to.
Thanks
Doug
December 10, 2010 at 2:12 pm
doug 40899 (12/10/2010)
Craig,Sorry about not getting the data right on that. I tested your query and it works the way that I need it to.
Thanks
Doug
Great to hear it's working. And no worries, sometimes it just takes some practice to understand why it's much easier with the little bit of extra work.
See ya next time. 😉
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply