March 16, 2010 at 2:20 pm
All,
Please help. I have the following SP and it is really behaving strangely. I want it to give me one row per employee and it does the first employee correctly but not the rest.
SP:
SET NOCOUNT ON;
declare @segment1 decimal(10,2)
declare @segment2 decimal(10,2)
declare @clockinDay datetime
declare @clockinLunch datetime
declare @clockoutLunch datetime
declare @clockoutDay datetime
declare @workingEmpID int
declare @timeEntryID int
declare @timeEntry datetime
declare @timeEntryType nvarchar(50)
declare @empName nvarchar(50)
declare @empID int
declare @totalHrs decimal(10,2)
declare @startDate as DateTime
declare @endDate as DateTime
set @startDate = '03/15/2010'
set @endDate = '03/16/2010'
create table #tmpWorkingHours
(
[EmpName] nvarchar(50) NULL,
[EmpID] [int] NULL,
[TotalHours] decimal(10,2) NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL
)
set @workingEmpID = 1
set @segment1 = 0.00
set @segment2 = 0.00
set @totalHrs = 0.00
declare #punches cursor for
select TimeEntryID, ClockInDay, ClockOutLunch, ClockInLunch, ClockOutDay, p.EmpID, EmpName
--, DatePart(hour, timeentry) as xhour, DatePart(minute, timeentry) as xminutes
from Punch p
inner join Employee e on p.EmpID = e.EmpID
where DatePart(dayofyear, ClockInDay) >= DatePart(dayofyear, @startDate)
and DatePart(dayofyear, ClockInDay) <= DatePart(dayofyear, @endDate)
group by DatePart(day, ClockInDay), TimeEntryID, ClockInDay, ClockOutLunch, ClockInLunch, ClockOutDay, p.EmpID, EmpName
order by EmpID
open #punches
Fetch Next from #punches into @timeEntryID, @clockInDay, @clockOutLunch, @clockInLunch, @clockOutDay , @empID, @empName
set @workingEmpID = @empID
while (@@Fetch_Status = 0)
begin
while @workingEmpID = @empID and @@Fetch_Status = 0
begin
set @segment1 = case
when (DateDiff(minute, @clockInDay, isnull(@clockOutLunch,@clockInDay))/60) = 0
THEN DateDiff(minute, @clockInDay, isnull(@clockOutLunch, @clockInDay))*.01
when (DateDiff(minute, @clockInDay, isnull(@clockOutLunch,@clockInDay))/60) != 0
THEN (DateDiff(minute, @clockInDay, isnull(@clockOutLunch, @clockInDay))/60)
end
set @segment2 = (DateDiff(minute, isnull(@clockinLunch, @clockInDay), @clockoutDay)/60)
set @totalHrs = ((@segment1 + @segment2))
set @segment1 = 0.00
set @segment2 = 0.00
INSERT INTO #tmpWorkingHours
([EmpName],[EmpID],[TotalHours],[StartDate],[EndDate])
VALUES
(@empName,@workingEmpID,@totalHrs,@startDate,@endDate)
Fetch Next from #punches into @timeEntryID, @clockInDay, @clockOutLunch, @clockInLunch, @clockOutDay , @empID, @empName
end
set @workingEmpID = @empID
set @totalHrs = 0.00
end
close #punches
deallocate #punches
select EmpID, EmpName, StartDate, EndDate, TotalHours as dayHrs, sum(TotalHours) as TotalHours
from #tmpWorkingHours group by EmpID, EmpName, StartDate, EndDate, TotalHours
drop table #tmpWorkingHours
DDL:
USE [TimeTracker]
GO
/****** Object: Table [dbo].[Punch] Script Date: 03/16/2010 13:13:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Punch](
[TimeEntryID] [int] IDENTITY(1,1) NOT NULL,
[ClockInDay] [datetime] NULL,
[ClockOutLunch] [datetime] NULL,
[ClockInLunch] [datetime] NULL,
[ClockOutDay] [datetime] NULL,
[EmpID] [int] NULL,
CONSTRAINT [PK_Punch] PRIMARY KEY CLUSTERED
(
[TimeEntryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
USE [TimeTracker]
GO
/****** Object: Table [dbo].[Employee] Script Date: 03/16/2010 13:14:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee](
[EmpID] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpAddress] [nvarchar](50) NULL,
[EmpAddress2] [nvarchar](50) NULL,
[EmpCity] [nvarchar](50) NULL,
[EmpState] [nvarchar](2) NULL,
[EmpZip] [nvarchar](5) NULL,
[EmpHomePhone] [nvarchar](14) NULL,
[EmpCellPhone] [nvarchar](14) NULL,
[EmpEmail] [nvarchar](50) NULL,
[VacDaysPerYear] [int] NULL,
[PersonalDaysPerYear] [int] NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmpID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Inserts:
INSERT INTO [TimeTracker].[dbo].[Employee]
([EmpName])
VALUES
('John')
INSERT INTO [TimeTracker].[dbo].[Employee]
([EmpName])
VALUES
('Tom')
INSERT INTO [TimeTracker].[dbo].[Employee]
([EmpName])
VALUES
('Dick')
INSERT INTO [TimeTracker].[dbo].[Employee]
([EmpName])
VALUES
('Harry')
INSERT INTO [TimeTracker].[dbo].[Employee]
([EmpName])
VALUES
('Sally')
INSERT INTO [TimeTracker].[dbo].[Punch]
([ClockInDay]
,[ClockOutLunch]
,[ClockInLunch]
,[ClockOutDay]
,[EmpID])
VALUES
('3/16/2010 8:21:38 AM'
,'3/16/2010 11:21:49 AM'
,'3/16/2010 12:22:05 PM'
,'3/16/2010 6:38:26 PM'
,'1')
INSERT INTO [TimeTracker].[dbo].[Punch]
([ClockInDay]
,[ClockOutLunch]
,[ClockInLunch]
,[ClockOutDay]
,[EmpID])
VALUES
('3/16/2010 8:21:38 AM'
,'3/16/2010 11:21:49 AM'
,'3/16/2010 12:22:05 PM'
,'3/16/2010 6:38:26 PM'
,'3')
INSERT INTO [TimeTracker].[dbo].[Punch]
([ClockInDay]
,[ClockOutLunch]
,[ClockInLunch]
,[ClockOutDay]
,[EmpID])
VALUES
('3/16/2010 8:21:38 AM'
,'3/16/2010 11:21:49 AM'
,'3/16/2010 12:22:05 PM'
,'3/16/2010 6:38:26 PM'
,'2')
INSERT INTO [TimeTracker].[dbo].[Punch]
([ClockInDay]
,[ClockOutLunch]
,[ClockInLunch]
,[ClockOutDay]
,[EmpID])
VALUES
('3/16/2010 8:21:38 AM'
,'3/16/2010 11:21:49 AM'
,'3/16/2010 12:22:05 PM'
,'3/16/2010 6:38:26 PM'
,'4')
INSERT INTO [TimeTracker].[dbo].[Punch]
([ClockInDay]
,[ClockOutLunch]
,[ClockInLunch]
,[ClockOutDay]
,[EmpID])
VALUES
('3/16/2010 8:21:38 AM'
,'3/16/2010 11:21:49 AM'
,'3/16/2010 12:22:05 PM'
,'3/16/2010 6:38:26 PM'
,'1')
INSERT INTO [TimeTracker].[dbo].[Punch]
([ClockInDay]
,[ClockOutLunch]
,[ClockInLunch]
,[ClockOutDay]
,[EmpID])
VALUES
('3/16/2010 8:21:38 AM'
,'3/16/2010 11:21:49 AM'
,'3/16/2010 12:22:05 PM'
,'3/16/2010 6:38:26 PM'
,'3')
March 16, 2010 at 2:50 pm
group by EmpID, EmpName, StartDate, EndDate, TotalHours
This is just a snapshot answer, but if you only expect one row per employee, I would expect to see a group by EmpID ONLY, or at worst a group by EmpID, EmpName.
That said, let me compliment you on a beautiful job of setting up the problem with scripts. I'll be back when I've had a chance to run them.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 16, 2010 at 2:57 pm
Yes that is what I would expect as well but when I do that I get the following error.
Msg 8120, Level 16, State 1, Line 34
Column 'Punch.TimeEntryID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Only way that I have found to clear it is to add all of the select fields to the Group By, which makes no sense to me.
March 16, 2010 at 3:01 pm
Odd....
Here are the results I'm getting with your test data. Looks like one row per employee to me, with this data.
What are you expecting your results to look like?
[font="Courier New"]EmpID EmpNameStartDate EndDate dayHrs TotalHours
1 John2010-03-15 00:00:00.0002010-03-16 00:00:00.0009.00 36.00
2 Tom2010-03-15 00:00:00.0002010-03-16 00:00:00.0009.00 18.00
3 Dick2010-03-15 00:00:00.0002010-03-16 00:00:00.0009.00 36.00
4 Harry2010-03-15 00:00:00.0002010-03-16 00:00:00.0009.00 18.00[/font]
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 16, 2010 at 3:07 pm
**Many explatives**
It seems like I had a data problem, program testing probably. Deleted everything and inserted from my post inserts and it is working.
Thanks I would have spent days on this before I looked at the data.
March 16, 2010 at 3:07 pm
Why do you need to run a cursor for what you're trying to do?
Wouldn't it be easier to do it set based? As a start something like
SELECT empid,
SUM(CASE
WHEN (DATEDIFF(MINUTE, clockInDay, ISNULL(clockOutLunch,clockInDay))/60) = 0
THEN DATEDIFF(MINUTE, clockInDay, ISNULL(clockOutLunch, clockInDay))*.01
ELSE (DATEDIFF(MINUTE, clockInDay, ISNULL(clockOutLunch, clockInDay))/60)
END
+ (DATEDIFF(MINUTE, ISNULL(clockinLunch, clockInDay), clockoutDay)/60)
) AS TotalHours
FROM punch
WHERE ClockInDay >='20100315'
AND ClockInDay <DATEADD(dd,1,'20100316')
GROUP BY empid
March 16, 2010 at 3:10 pm
I echo Lutz' question.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 16, 2010 at 3:56 pm
I guess to be frank, I am a newbie and don't really know any better. I have learned this all by myself so it is the best I can do at this point in time. That said, I am always open to learning so feel free to guide me into better T-SQL programming, I am all ears.
March 16, 2010 at 4:24 pm
What type of guidance do you ask for?
The kind of "Follow that path over there until you reach a crossroad at the hill. Then take a left because if you'd go to the right then you'll end up in a swamp".
Or more like "Take a seat, close your eyes and I'll wake you up when we get there?"
It is not my intention to sound offensive in any way! If so, I apologize.
It's just to learn about your expectation.
If it's the former then we'll (I'll) give you some directions including the why and how, you give it a try, figure out how it works, adapt it to your situation and get back here if you get stuck.
If it's the latter then we'll (I'll) (just) give you a tested solution.
March 16, 2010 at 4:49 pm
Frankly, it's always a pleasure to hear someone just admit that they are new to SQL and open to learning.
If you keep looking at articles in SSC, you will hear us talking about set-based programming rather than procedural programming. Procedural code is very step-by-step: (1) retrieve a row, (2) populate some variables, (3) do some calculations, store totals in the variables, etc (4) write the results out somewhere else.
While most languages work best that way, T-SQL does not. In SQL, the most efficient way to do something is usually to do it to an entire set of data at a time. For example the following code produces the total monthly sales of every item in inventory that sold in the month of January.
SELECT ItemNo, SUM(SalesAmount) as TotalSalesAmount
FROM SalesHistory
WHERE SalesDate >= '1/1/2010' and SalesDate < '2/1/2010'
GROUP BY ItemNo
ORDER BY TotalSalesAmount DESC
Game over. The code above does not read a row at a time, doesn't assign values to variables, doesn't require a loop to take totals and reset for each new ItemNo. The database just handles all that for the developer. It's sort of like an automatic transmission on a car. But the important thing is that left to it's own devices the set-based code runs much faster than row-at-a-time procedural code.
Take Lutz up on his offer. He knows what he's talking about. Getting started early learning how to think in terms of set-based coding will pay you HUGE dividends in your production environment.
Good luck 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 16, 2010 at 4:53 pm
I am much more inclined to the 'Show me the path and let me figure it out' method. So fire away.
March 16, 2010 at 4:55 pm
Thanks, I will bang away on my SP and see if I can't make it more set friendly.
Thanks.
March 16, 2010 at 5:33 pm
The Dixie Flatline (3/16/2010)
...Take Lutz up on his offer. He knows what he's talking about. ...
:blush: May I quote you when talking to my boss next time about a wage rise?
Really appreciate the compliment! :blush:
If you hang around on this forum for a while you might notice that this is not really a forum. It's a community. Most of us have one thing in common: we don't consider using SQL just being part of our job. We consider it being a passion.
So: Welcome aboard! And we hope we'll be able to show you how much fun it is dealing with SQL Server hoping it becomes a passion for you, too!! Right, Bob?
Side note: this site is one of the (if not THE) most professional site for SQL stuff. Please forgive us, if we don't stay focussed all the time. That's the community part of it... 😉
March 16, 2010 at 5:56 pm
Thanks. If any of you get stuck with C# I can help out. 🙂
March 16, 2010 at 9:09 pm
john-902052 (3/16/2010)
Thanks. If any of you get stuck with C# I can help out. 🙂
And there you go - that is exactly why we call this a community and not a forum. Welcome aboard...:hehe:
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply