Formating a weekly report - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Formating a weekly report

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2005-02-10 : 21:15:13

Hi all,

We just recently completed building a time card that allows employees to log into the internet everyday to punch in.

At the end of the day, they punch out.

Now, we need to build a weekly report that we will send to management showing individual sign-in, sign-out records.

Does anyone one know to format this in sql please.

The fields will be:

Employee Name nvarchar
Date In Date/time
Time In date/time
Time Out date/time
location nvarchar,
Description

But I will like to have the layout look like this:

Date In | Time In Time Out | Location | Desc
Employee1:

__________________________________________________
*
__________________________________________________

Date In | Time In Time Out | Location | Desc
Employee2:

etc

The way this works, is that employees start punch in their times, Monday through Friday.

The report will be scheduled to run on Monday mornings at 6 am so that when management gets to work, they will see the report and print for their records.

It will be great to have this work as a query instead of stored proc.

Can someone, please assist?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-10 : 22:37:05

My loathing of punch clock systems prohibits me from assisting you in this matter...sorry.

But just be sure your system knows how to deal with times when one of your prisoners needs to punch in and out a couple times in one day, or when some poor sap needs to work past midnight.

Be One with the Optimizer
TG

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2005-02-11 : 08:34:56

I loath it myself and I am not doing this of my free will.

As a prisoner myself, you need to at least try to free me.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-11 : 09:33:50

Well, I hate to see a prisoner suffer...if you post your DDL (the structure of how the data is stored, and a sample of the desired output) I, or someone, can recommend some some sql statements.

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-02-11 : 11:40:00

I can't believe anyone would assist in this INSIDIOUS task!

Semper fi, Xerxes, USMC(Ret.)
-------------------------------------------------------------------------
Once a Marine Programmer Analyst ALWAYS a Marine Programmer Analyst

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2005-02-11 : 13:12:49

Thansk TG!

As stated before, only you own your own business, and I don't, then you have no choice than to do what you are asked, unless you are ready to hit the road.

First, below is our desired ouput format:

Name
Date | Time in/out | Worked | Where
Joe Bean
7 Feb 2005 | 08:30 - 14:45 | 6:45 | Home
8 Feb 2005 | 08:30 - 16:30 | 8:00 | Home
9 Feb 2005 | 10:30 - 17:15 | 7:15 | Home
10 Feb 2005 | 08:30 - 17:45 | 9:15 | Home
11 Feb 2005 | 11:00 - 17:15 | 5:15 | Home
Total hours worked 36:15

The table structures:

emp table:
empID, Fullname (like John Doe)

location table:
LocationID,
Location

TimePunch table:
PunchID,
empID,
LocationID,
DateIn datetime
timeIn datetime
Timeout datetime
description
hoursWorked,

Then I would like some logic to add up hoursworked per weekly

Then total hoursWorked

I appreciate the assistance

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-11 : 14:25:04

couple questions:

1) in your TimePunch table since your DateIn and timeIn columns are both datetime, does that mean they store the same value?

2) in TimePunch, is hours worked stored as varchar <hh:mm>?

3) Does hours worked represent something other than the difference between timein and timeout?

4) has the rounding to 15 min increments already been handled by the time it gets stored in timein and timeout?

Be One with the Optimizer
TG

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2005-02-11 : 15:30:59

Question 1:

No TG, we use date() to store date values in the form of 2/11/2005 and we use time() to store values such as 4:33:55 PM
Maybe that needs to change to varchar(?).

If we do that, will we still be able to do calculation on timein/timeout?

Question 2: They are both stored as hrs:mins:secs like this;
4:33:55 PM

Question 3: Does hours worked represent something other than the difference between timein and timeout?
No

Question 4: has the rounding to 15 min increments already been handled by the time it gets stored in timein and timeout?

I was asked not to round up.

But if rounding up makes it easier, I will go for it.

Thanks again for all the assistance

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-11 : 15:49:19

We aren't talking sql server here, are we?

quote:
No TG, we use date() to store date values in the form of 2/11/2005 and we use time() to store values such as 4:33:55 PM
Maybe that needs to change to varchar(?).

I hope I haven't been wasting your time, simflex, but if this is in access or something I can't help you.
If it is SqlServer then they can't be datetime columns. If it's sql server please generate a create script of those 3 tables and post them.

Be One with the Optimizer
TG

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2005-02-11 : 16:14:35

Here is the TimePunch table:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TimePunch]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TimePunch]
GO

CREATE TABLE [dbo].[TimePunch] (
[PunchID] [int] IDENTITY (1, 1) NOT NULL ,
[datein] [datetime] NULL ,
[timein] [datetime] NULL ,
[timeout] [datetime] NULL ,
[location] [int] NULL ,
[loginName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[empID] [int] NULL
) ON [PRIMARY]
GO

Here is the emp

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Emp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Emp]
GO

CREATE TABLE [dbo].[Emp] (
[empID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[empNo] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[employeeName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[status] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[type] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[jobCode] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[deparment] [nvarchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[schedHours] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[empPassword] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[emailAdd] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

As you can see from the TimePunch table, timein and timeout are datetime datatypes but if you feel like changing them to make this work, please do so, it will be appreciated.

And Here is the Location table:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Location]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Location]
GO

CREATE TABLE [dbo].[Location] (
[locationID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Location] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-11 : 16:51:55

Ok, what do you think of something like this?
paste this in a QA window and run it:


declare @emp table
	(empID int
	,Fullname varchar(50))


declare @location table
	(LocationID int
	,Location varchar(25))

declare @TimePunch TABLE 
	([PunchID] [int] NOT NULL ,
	[datein] [datetime] NULL ,
	[timein] [datetime] NULL ,
	[timeout] [datetime] NULL ,
	[locationid] [int] NULL ,
	[empID] [int] NULL )

set nocount on
insert @Emp (empid, fullname) values (1, 'Joe Bean')
insert @location values (1, 'Home')
insert @Timepunch values (1,'2/7/2005 8:30',	'2/7/2005 8:30',	'2/7/2005 14:45',1,1)
insert @Timepunch values (1,'2/8/2005 8:30',	'2/8/2005 8:30',	'2/8/2005 16:30',1,1)
insert @Timepunch values (1,'2/9/2005 10:30',	'2/9/2005 10:30',	'2/9/2005 17:15',1,1)
insert @Timepunch values (1,'2/10/2005 8:30',	'2/10/2005 8:30',	'2/10/2005 17:45',1,1)
insert @Timepunch values (1,'2/11/2005 11:00',	'2/11/2005 11:00',	'2/11/2005 17:15',1,1)

--Weekly detail
Select 	FullName
	,convert(varchar(12), Datein, 106) 				as [Date]

	,datename(hour, timein) + ':' + datename(minute, timein)
	 + ' - ' +
	 datename(hour, timeout) + ':' + datename(minute, timeout) 	as [Time in/out]

	,convert(varchar(3), dateDiff(minute, timein, timeout) / 60)
	 + ':' + 
	 convert(varchar(2), dateDiff(minute, timein, timeout) % 60)	as [worked]

	,Location							as [Where]
From	@Timepunch tp
JOIN	@emp e
		ON tp.empid = e.empid
JOIN	@location l
		ON tp.locationID = l.locationID
Where	datepart(week, datein) = 7
AND	tp.empid = 1
order by datein

--Totals by employee for week
Select	fullname
	,convert(varchar(3), sum(dateDiff(minute, timein, timeout)) / 60)
	+ ':' + 
	convert(varchar(3), sum(dateDiff(minute, timein, timeout)) % 60)
From	@Timepunch tp
JOIN	@emp e
		ON tp.empid = e.empid
Where	datepart(week, datein) = 7
Group by fullname

Be One with the Optimizer
TG

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-02-11 : 23:00:24

Why are you storing the date and the time in sepatate columns? What are you storing in the time portion of the date datetime and what are you storing in the date portion of the time datetime? Is the DateIn date ever different from the date of the TimeIn time? Is the TimeOut date ever different from the DateOut date?

Wouldn't the chronological arithmetic be much easier if the date and the time were part of a datetime?

HTH

=================================================================
Egotism is the anesthetic that dulls the pain of stupidity. -Frank William Leahy, football coach (1908-1973)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-12 : 08:05:46

I asked similar questions (above). Maybe if he hears em again from you BustaZ, it'll sink in.

I hoping he notices that in my sample data, the datain and timein values are identical. I think all he's interested in is the code to format the output.

quote:

couple questions:

1) in your TimePunch table since your DateIn and timeIn columns are both datetime, does that mean they store the same value?

2) in TimePunch, is hours worked stored as varchar <hh:mm>?

3) Does hours worked represent something other than the difference between timein and timeout?

4) has the rounding to 15 min increments already been handled by the time it gets stored in timein and timeout?

Be One with the Optimizer
TG

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-02-12 : 15:23:10

>>> I asked similar questions (above). <<<

Just trying to belabor the glaringly obvious....

I had to work recently on a time and billing system that made the same design decision regarding separation of time and date. What a mess!

HTH

=================================================================
Egotism is the anesthetic that dulls the pain of stupidity. -Frank William Leahy, football coach (1908-1973)

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2005-02-13 : 16:52:38

First to Tg,

I believe you when you said that you seriously loathe time clocks for what it represents.

As I indicated, I feel the same. I am doing this because I *have* to.

The point I am getting at is that despite how you feel, you still went out of your way to help; I appreciate that.

Now the date time issue,

Even though they wanted them separated, I can do it the way you guys are suggesting.

First, the fields will now look like dateIn and DateOut?

We make them datetime data types.

How can we calculate the difference then?

dateOut - dateIn (to get hours worked?)

Thanks again (to both of you) for explaining, not just providing the solutions.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-13 : 17:43:06

simflex, though I hate working for a company with a punchclock, I'm happy to help you. Really!

now to your question about calculating the difference. Here is a segment of the code (above).
it uses the sql function DateDiff to do the calculation. Because of your desired output format, I'm using it twice. Once to get the hours then again to get the minutes. I seperate the 2 values with ":".
lookup up DataDiff on books online. (you would just need to change timein and timeout to datein and dateout)


,convert(varchar(3), dateDiff(minute, timein, timeout) / 60)
 + ':' + 
 convert(varchar(2), dateDiff(minute, timein, timeout) % 60)	as [worked]

edit: btw, I don't have any problem with "TimeKeeping" systems. Companies couldn't gage their own costs or bill their clients without them. In fact, I think companies (in general) don't do a good enough job knowing what their true costs are.

Punchclock systems however are a pain in the butt. I have yet to see one that was effective. The systems I've seen simply knows that a punch occured and just assumes it was a punch IN or a punch OUT based on the time of day. One company I worked for had a Punch system that actually subtracted 1 hour from your total time assuming that you took an hour to eat lunch. Since I stayed very late occasionally, the system would think I forgot to punch out and registererd my punch out as a punch in for the next day. Then when I did punch in the next day it assumed I was leaving for the day, etc, etc... Anyway, I appologize if it seemed I was unwilling to help. That was more of an inside joke (inside with only myself).

TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources