Re: Track work extents - Database Discussion Boards

First Prev Next

Re: employee timecard

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

Re: employee timecard

εїзεїзεїз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

Re: employee timecard

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

Re: employee timecard

uglyeyes1-Oct-09 13:41

uglyeyes

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?

Re: employee timecard [modified]

uglyeyes5-Oct-09 13:58

uglyeyes

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

uglyeyes

15-Oct-09 13:10

Hi, thanks I went to codeplex but I couldnt find the solution i was looking for. could you please give me an idea how the table relationship might look like between timecard and time slices. thanks once again

Re: Track work extents

David Mujica16-Oct-09 10:03

David Mujica

16-Oct-09 10:03

Here is a rough idea of how you could design a time keeping database ...

Table: Timecard_Header
HID - Primary key Auto generated sequence number. (Header ID)
EMPID - Key to the employee table
TCDATE - Timecard date
TIME_IN - Datetimestamp of when the timecard begins (shift start) Include the date portion incase the shift begins at 11pm and runs across midnight
TIME_OUT - Datetimestamp

Table: Timecard_Details
DID - Primary key. Sequence number (detail ID)
HID - Foreign key reference back to the header record
TIME_START - Beginning time for this time slice
TIME_END - Ending time for this time slice
TDTYPE - Timecard deail type (Use this to indicate Work, Break, Absence, etc)
TDCODE - Used as a further explaination of the type, for example Absence - Vacation or Absence - Jury duty
PCODE - Paycode (0 - Unpaid, 1 - Regular pay, 2 - Overtime, 3 - Doubletime, etc)
PRate - Pay rate show the actual rate paid for this time slice. $10/hr Regular pay or $15 / hr for overtime

This should give you an idea of one way of looking at the data, you should extend this model to meet your requirements.

Openrowset across a network

Mustafa Ismail Mustafa30-Sep-09 2:13

Mustafa Ismail Mustafa

30-Sep-09 2:13

This should work, but it keeps returning a "could not be opened. Operating system error code 5(Access is denied.)."

select *  from Openrowset (BULK '\\nvBeast\dropzone\img.jpg',SINGLE_BLOB) AS [Image]

Googling shows that many have the same problem, but I could not find any answers.

Anyone got a clue?

If the post was helpful, please vote, eh!

Current activities:
Book: Devils by Fyodor Dostoyevsky
Project: Hospital Automation, final stage
Learning: Image analysis, LINQ

Now and forever, defiant to the end.
What is Multiple Sclerosis[^]?

Re: Openrowset across a network

Henry Minute30-Sep-09 3:14

Henry Minute

30-Sep-09 3:14

Have you read the Books Online ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/18a64236-0285-46ea-8929-6ee9bcc020b9.htm, the Security Considerations section?

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Re: Openrowset across a network

Sujit Mandal30-Sep-09 23:09

Sujit Mandal

30-Sep-09 23:09

nice link.

Thanks and Regards
Sujit Kr. Mandal

Re: Openrowset across a network

Md. Marufuzzaman1-Oct-09 7:14

Md. Marufuzzaman

1-Oct-09 7:14

OPENROWSET: Includes all connection information necessary to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data using OLE DB. The OPENROWSET function can be referenced in the FROM clause of a query as though it is a table name. The OPENROWSET function can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement, subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENROWSET returns only the first one.

If you use any network path, please confirm that your SQL login user is permitted to perform bulk load on the Operating System.

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

Table Structure for Product -> Book

Brendan Vogt29-Sep-09 23:39

Brendan Vogt

29-Sep-09 23:39

Hi there,

I have a generic class called Product that has general properties that each product will have. Then I have a class called Book, and Computer. This was easy to do in C# code, but now I have to create the table structures. I'm not sure what is the best way to do it? I was thinking of creating a table called Product, and a table called Book. And then have a product identifier in the Book table to reference the Product table? What will the relation be between these 2 tables? 1:1? Can someone please give me some direction as to what to do and what is the best way of doing it?

Regards
Brendan

Re: Table Structure for Product -> Book

Mycroft Holmes30-Sep-09 0:34

Mycroft Holmes

30-Sep-09 0:34

Book should not be a table, it is a TYPE of product (as is Computer). If you go down the path of having a table for each product type (because each product type has different attributes after all) then you will get your self into a maintenance (and data) nightmare.

Have a product table with all the general attributes as you have done, add a large description field and see if that will meet your needs.

IF you absolutely MUST have additional attributes you might consider an attribute type and attribute table but this is going to add a level of complexity that will make support difficult.

There are entire books written and consultants make fortunes writing product structures so don't be scared to experiment.

Never underestimate the power of human stupidity
RAH

Re: Table Structure for Product -> Book

Brendan Vogt30-Sep-09 2:43

Brendan Vogt

30-Sep-09 2:43

Thanks for the reply. Book was just an example. There will be other product types as well. Like computers, clothes, etc etc. So I think it is a little impractical to have all the attributes in one table. Or what do you think?

What product structures are you talking of? Give some examples?

Re: Table Structure for Product -> Book [modified]

Andy_L_J30-Sep-09 21:29

Andy_L_J

30-Sep-09 21:29

The structure should be something like this (as Mycroft suggested):

ProductType ' (Book, Manchester, Cookware, ...)
  ID INT IDENTITY(1,1) PRIMARY KEY,
  Name VarChar(50) UNIQUE NOT NULL

ProductCategory ' (Fiction, NonFiction, Sheets, Pillow Cases, Pots, Pans,...)
  ID INT IDENTITY(1,1) PRIMARY KEY,
  ProductType_ID INT NOT NULL FOREIGN KEY REFERENCES ProductType(ID),
  Name VarChar(50)

Product
  ID INT IDENTITY(1,1) PRIMARY KEY,
  ProductCategory_ID INT NOT NULL FOREIGN KEY REFERENCES ProductCategory(ID),
  Name VarChar(50) NOT NULL
  ...
  ...

To add additional attributes, extend this to Properties...

ProductProperty  ' (Price, Dimension, Volume, ...)
  ID INT IDENTITY(1,) PRIMARY KEY
  ProductType_ID INT NOT NULL FOREIGN KEY REFERENCES ProductType(ID),
  Name VarChar(50) NOT NULL,
  UOM VarChar(20) NOT NULL,
  Description VarChar(50) 

ProductPropertyValue
  ID INT IDENTITY(1,1) PRIMARY KEY,
  ProductProperty_ID INT NOT NULL FOREIGN KEY REFERENCES ProductProperty(ID),
  Product_ID INT NOT NULL FOREIGN KEY REFERENCES Product(ID)
  Value VarChar(MAX)

Wire these together with Foreign Keys and you are away - As Mycroft said, this adds aonother layer of complexity.

I don't speak Idiot - please talk slowly and clearly

I don't know what all the fuss is about with America getting it's first black president. Zimbabwe's had one for years and he's sh*t. - Percy Drake , Shrewsbury

Driven to the arms of Heineken by the wife

modified on Thursday, October 1, 2009 6:28 AM

Re: Table Structure for Product -> Book

Brendan Vogt30-Sep-09 22:55

Brendan Vogt

30-Sep-09 22:55

Thanks, it makes a lot more sense now. I'm just confused to your product category and your product type. Could you explain a little more please?

Lets say I have a book product. Product category would be like religion and auto biographies, and product type would be book?

Why do you have the following ProductType ' (Price, Dimension, Volume,...) Shouldn't price be generalised into the product table?

Re: Table Structure for Product -> Book [modified]

Andy_L_J1-Oct-09 0:18

Andy_L_J

1-Oct-09 0:18

OK,

ProductType would be Book,

ProductCategory would be NonFiction

Product: ProductCategory would be NonFiction
Name would be Book Title

You could then select from your tables as so:

SELECT * FROM Product p
  JOIN ProductCategory pc
    ON p.ProductCategory_ID = pc.ID
  JOIN ProductType pt
    ON pc.ProductType_ID = pt.ID
WHERE pt.Name = 'Book'

this gives you much flexibility selecting by Type, Category and Name

etc...

Hope this makes things clearer. Create a dummy DB in SSMS and generate a Database diagram - the structure will become clearer.

I don't speak Idiot - please talk slowly and clearly

I don't know what all the fuss is about with America getting it's first black president. Zimbabwe's had one for years and he's sh*t. - Percy Drake , Shrewsbury

Driven to the arms of Heineken by the wife

modified on Thursday, October 1, 2009 6:27 AM

pivot error

snouto29-Sep-09 21:28

snouto

29-Sep-09 21:28

hi everybody .

i am trying to use pivot
i use northwind data base

i try to execute the following sql statement in my sql server management studio

SELECT * FROM Orders
PIVOT (COUNT(employeeID) FOR employeeID IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])) p

but it gives me that error , i don't know what is wrong with the above statement , actually i didn't change the query from the article that i learn from , i copied and pasted it inside the sql management studio

i got that error

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.

Human knowledge belongs to the world.

Re: pivot error

Mycroft Holmes30-Sep-09 0:42

Mycroft Holmes

30-Sep-09 0:42

I used this on AdventureWorks (I done't have northwind) and it worked fine. I got the IDs with a select distinct. Dammed if I can see a difference.

SELECT * FROM Sales.SalesOrderHeader
PIVOT (COUNT(SalesPersonID) FOR SalesPersonID IN ([274],[275],[276],[277])) p

Never underestimate the power of human stupidity
RAH

Re: pivot error

Syed Mehroz Alam30-Sep-09 0:50

Syed Mehroz Alam

30-Sep-09 0:50

Hi,

Your Northwind db may not have compatibility level of 90 which is a requirement for the Pivot clause. Try this:

ALTER DATABASE [NorthWind] SET COMPATIBILITY_LEVEL = 90

Hope that helps.

Regards,

Syed Mehroz Alam
My Blog | My Articles

Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

Re: pivot error

i.j.russell30-Sep-09 1:15

i.j.russell

30-Sep-09 1:15

It works fine on my machine! I am running Express 2005 but I have the extended Northwind database from http://www.linqdev.com/PublicPortal/publicportal/home.aspx[^].

how to backup oracle 10g

E_Gold29-Sep-09 9:58

E_Gold

29-Sep-09 9:58

hi
how i can backup oracle 10g like backup and restore in sql server ?

i want to backup tables and data

thank's in advance

Re: how to backup oracle 10g

Mycroft Holmes29-Sep-09 19:31

Mycroft Holmes

29-Sep-09 19:31

I thought the title was dumb - and even had this ready for you backup[^] but then I read the content.

Correct me if I'm wrong but you seem to be asking if you can back up an Oracle database and restore it into SQL Server, assuming I have not completely misinterpreted you question.

You CANNOT do that. There may be tools around that will allow you to generate a script from Oracle to create the equivalent database in SQL Server and then query the data across but backup is not going to work.

help required in adding dynamic columns and search anywhere in table ????

jarajeshwaran29-Sep-09 7:17

jarajeshwaran

29-Sep-09 7:17

Hi all,

I would like to get your help and guidance in implementing the following requirements.

please consider the following situation.
We have an address book application. The app will have fields like name/age/Sex by default. I want to give the user the freedom to add his own fields to the application. Like website address/email etc. This wil be decided by the user while the sets up the application for the first time. I have a clear understanding of doing this at the UI level(using dynamic controls to hold the value) how can the same be handled at the database level? Like how should the table be designed to accomodate this dynamic column creation.

my second requirement is
While we search our inbox in GMAIL using the default search box(not the advanced options) we key in the keyword. And gmail returns the results irrespective of where the search keyword appears( it may be in the subject line/ message body/from email address etc.)
I want the same feature be implemented in a windows application( I want the search to be done against all the columns in a given table or given set of tables).

www.BeginWithDisbelief.com

hi hi hi hi

Last Visit: 31-Dec-99 18:00 Last Update: 5-Jun-24 21:50

Refresh

ᐊ Prev 1...752 753 754 755756757 758 759 760 761 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.