Database

εїзεїзεїз30-Sep-09 21:16

εїзεїзεїз

30-Sep-09 21:16

You can create a Job , which db are you using ???

Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it

sris 42630-Sep-09 22:39

30-Sep-09 22:39

I will be using both MSSQL and MySQL data bases.

Thanks,
Srinivas Mateti

Mycroft Holmes30-Sep-09 23:22

Mycroft Holmes

30-Sep-09 23:22

MSSQL you can create a stored procedure to do the insert and then schedule a job to execute the procedure when required.

MySQL - I don't know.

As suggested you could use a windows service to do both inserts.

sris 42630-Sep-09 23:35

30-Sep-09 23:35

Thanks for the reply.Here the constraint is I cannot use Windowse services.So, I will schedule a job whichi will fire on each day at specific time.In this job i will call the SP which will insert or update the desired record.I hore in MYSQL ALSO it will works.Right....

Thanks,
Srinivas Mateti

Mycroft Holmes30-Sep-09 23:38

Mycroft Holmes

30-Sep-09 23:38

If MySQL does not support jobs then you may be able to trick SQL Server into doing the job on MySQL as a remote server or a linked server, nasty but it may be possible.

sris 4261-Oct-09 0:05

1-Oct-09 0:05

Oho..This will create more problem for me.I cannot use SQLSERVER to trigger MYSQL.I canot use windowse services.But I need the auto insert action...Is there any other option in MySQl which is similar to MSSQL jobs feature.Please guide me.

Thanks in advance,
Srinivas Mateti

David Skelly1-Oct-09 2:39

David Skelly

1-Oct-09 2:39

MySQL has an event scheduler which can be used to fire off tasks at specified times or intervals.

Blikkies30-Sep-09 22:36

Blikkies

30-Sep-09 22:36

Triggers execute in response to certain events when INSERT, UPDATE or DELETE

sris 42630-Sep-09 22:40

30-Sep-09 22:40

So in this case I cant use triggers..right?

Blikkies1-Oct-09 4:17

Blikkies

1-Oct-09 4:17

Correct

Sujit Mandal30-Sep-09 23:04

Sujit Mandal

30-Sep-09 23:04

u hv to write a windows service for this.where u can set the exact time.

Thanks and Regards
Sujit Kr. Mandal

εїзεїзεїз30-Sep-09 23:26

εїзεїзεїз

30-Sep-09 23:26

no need to invent the wheel again Sql server job will do the job

Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it

Md. Marufuzzaman1-Oct-09 7:11

Md. Marufuzzaman

1-Oct-09 7:11

I think you can create a stored procedure for the insertion and call it from a SQL job that's it;

I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.

Don't forget to click [Vote] / [Good Answer] on the post(s) that helped you.

Thanks
Md. Marufuzzaman

employee timecard

uglyeyes30-Sep-09 14:27

30-Sep-09 14:27

Hi I am in process of designing a online employee timesheet application which allows employee to record hours and manager to approve number of hours worked. could someone please point me to links which gives me idea on how to create tables i.e. employee, timecard, etc

leckey30-Sep-09 16:07

leckey

30-Sep-09 16:07

Hi uglyeyes--
You have been a member long enough that CodeProject is not here to answer generic questions such as these. Your question is one where most would probably tell you to "just Google it." Creating a basic database and its tables is part of the learning process. Sit down, take some time, and think it out.

Back in the blog beatch!
http://CraptasticNation.blogspot.com/[^]

dan!sh 30-Sep-09 16:27

dan!sh

30-Sep-09 16:27

What exactly is your question?

How to create table called employee, timecard etc in some database?

or

You are looking for guidance related to database design?

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

εїзεїзεїз30-Sep-09 21:32

εїзεїзεїз

30-Sep-09 21:32

Which Tools are you using to do this , you have not explain that you are going to use Sql Server or MySql , for online System i will recommend MySql any how here is the link for Ms Sql Server

http://databases.about.com/od/sqlserver/ss/sqlservertables.htm[^]

Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it

Sujit Mandal30-Sep-09 23:08

Sujit Mandal

30-Sep-09 23:08

u can create this sample tables
CREATE TABLE [dbo].[ATTENDANCE](
[AttendanceID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeID] [int] NULL,
[AttendanceCreatedDate] [datetime] NULL,
[AttendanceCreatedByUserID] [int] NULL,
[AttendanceDate] [datetime] NULL,
[InTime] [datetime] NULL,
[OutTime] [datetime] NULL,
[WorkDuration] [int] NULL,
[IsOverTime] [bit] NULL CONSTRAINT [DF_Attendance_IsOverTime] DEFAULT ((0)),
[AttendanceComments] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Attendance] PRIMARY KEY CLUSTERED
(
[AttendanceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_Attendance] UNIQUE NONCLUSTERED
(
[EmployeeID] ASC,
[AttendanceDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Thanks and Regards
Sujit Kr. Mandal

uglyeyes1-Oct-09 13:41

Re: employee timecard [modified]

1-Oct-09 13:41

Hi Guys,

thanks for your response. I should have been more specific. I have already created few tables which are as below.
Employee, EmployeeType, Jobtitle,
Period (PeriodId PeriodStartDate PeriodEndDate),
timeCard(TimeCardId,PeriodId,EmployeeId,Date,StartTimeAM,EndTimeAM,StartTimePM,EndTimePM,BreakTaken,UpdatedDate,IsApproved)

period
17 2009-09-21 00:00:00 2009-09-27 00:00:00
timecard for one week for one employee
1 17 1 2009-09-21 00:00:00 1900-01-01 06:15:00 1900-01-01 11:30:00 1900-01-01 17:15:00 1900-01-01 12:00:00 1900-01-01 01:00:00 2009-10-01 11:38:00 0
2 17 1 2009-09-22 00:00:00 1900-01-01 06:15:00 1900-01-01 11:30:00 1900-01-01 17:15:00 1900-01-01 12:00:00 1900-01-01 01:00:00 2009-10-01 11:38:00 0
3 17 1 2009-09-23 00:00:00 1900-01-01 06:15:00 1900-01-01 11:30:00 1900-01-01 17:15:00 1900-01-01 12:00:00 1900-01-01 01:00:00 2009-10-01 11:38:00 0
4 17 1 2009-09-24 00:00:00 1900-01-01 06:15:00 1900-01-01 11:30:00 1900-01-01 17:15:00 1900-01-01 12:00:00 1900-01-01 01:00:00 2009-10-01 11:38:00 0
5 17 1 2009-09-25 00:00:00 1900-01-01 06:15:00 1900-01-01 11:30:00 1900-01-01 17:15:00 1900-01-01 12:00:00 1900-01-01 01:00:00 2009-10-01 11:38:00 0
6 17 1 2009-09-26 00:00:00 1900-01-01 06:15:00 1900-01-01 11:30:00 1900-01-01 17:15:00 1900-01-01 12:00:00 1900-01-01 01:00:00 2009-10-01 11:38:00 0
7 17 1 2009-09-27 00:00:00 1900-01-01 06:15:00 1900-01-01 11:30:00 1900-01-01 17:15:00 1900-01-01 12:00:00 1900-01-01 01:00:00 2009-10-01 11:38:00 0

for you to understand I have provided columns of period and timecard. this web application is used by hospitality industry.
employee works shifts like 7am-12pm and 6pm-2am
Now, I am thinking I shouldnt store computed values.
My problem is how do i work it out below:

If they work after mid-night they should get penalty and those two hours after midnight should be moved to record

also I dont know how to calculate their pay.

could you please help?

uglyeyes5-Oct-09 13:58

5-Oct-09 13:58

Ok, to simply it. how can I compare the time value as an input. my idea is if they type endtime anything bigger than 12.00am I need to compare mid-night time and @endtime, if @endtime is after midnight I need to add to append the hours next day in next row.

declare @starttime smalldatetime
declare @endtime smalldatetime

set @starttime='18:20'
set @endtime ='2:00'

print DATEADD(hh, DATEPART(hour,@EndTime), DATEADD(mi,DATEPART(mi,@EndTime),DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)) )
print DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0)

Hope that makes sense?

thank you

modified on Monday, October 5, 2009 8:45 PM

Track work extents

David Mujica7-Oct-09 3:28

David Mujica

7-Oct-09 3:28

To make your design more flexible, you need to implement a parent / child relationship where the master is the Timecard and the child records represent slices of time within that timecard. Each time slice can have attributes like Paid/Unpaid, Payrate, Workcode (Work, Absence, Break), Absence code, start time, end time.

For example:
WORK 7:00 - 9:00
BREAK 9:00 - 9:15
WORK 9:15 - 12:00
BREAK 12:00 - 13:00
WORK 13:00 - 17:00

Consider going to CodePlex to see what other people have developed, there may be a solution already out there. Cool | :cool:

Re: Track work extents

uglyeyes15-Oct-09 13:10