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
 General SQL Server Forums
 New to SQL Server Programming
 Auto Generation Of Tables

Author  Topic 

bijinn
Starting Member

1 Post

Posted - 2013-07-25 : 05:46:40
I have been working on a Human Resource Management software in C# .NET. In its Employee Attendance module i need to keep records of attendance of all workers for one month in a single table.The problem I encounter is to auto generate an exact copy of the Attendance table after a particular month is finished. How can i accomplish this? Do help as i am a beginner in SQL. I use SQL server 2008

B

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-25 : 06:30:31
This is SO wrong. Just have one table, and add a MONTH column. Much easier to maintain...


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

icmmovers
Starting Member

3 Posts

Posted - 2013-07-25 : 08:07:04
quote:
Originally posted by bijinn

I have been working on a Human Resource Management software in C# .NET. In its Employee Attendance module i need to keep records of attendance of all workers for one month in a single table.The problem I encounter is to auto generate an exact copy of the Attendance table after a particular month is finished. How can i accomplish this? Do help as i am a beginner in SQL. I use SQL server 2008

B




You can also try this:

CREATE TABLE [dbo].[ticketSequence]([next_value] [numeric](4, 0) NOT NULL);

Insert initial value:

INSERT INTO [dbo].[ticketSequence]([next_value]) VALUES (1);

Do a select for update on this table each time to get the next value as follows:

DECLARE @lv_next_ticket VARCHAR(50);

UPDATE ts
SET @lv_next_ticket = 'Ticket-' + REPLICATE('0', 4 - LEN(ts.next_value)) + CONVERT(VARCHAR(4), ts.next_value),
ts.next_value = ts.next_value + 1
FROM [dbo].[ticketSequence] ts

SELECT @lv_next_ticket


unspammed
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-25 : 08:17:43
quote:
Originally posted by bijinn

I have been working on a Human Resource Management software in C# .NET. In its Employee Attendance module i need to keep records of attendance of all workers for one month in a single table.The problem I encounter is to auto generate an exact copy of the Attendance table after a particular month is finished. How can i accomplish this? Do help as i am a beginner in SQL. I use SQL server 2008

B

While you can certainly set up some job to auto generate a copy of the attendance table, it is much better to use a single table and add another column to identify the month for which any given row belings to, as Swepeso suggested.

However, don't add just a MONTH column. Either add a MONTH and a YEAR column, or add a date column (and use first of each month as the date). Adding just a month column is SO wrong because you won't be able to use it across time periods that span more than a year.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-07-25 : 09:57:01
Agree with Peso & James except you might want to just record the time/date in an appropriate column rather than separately. It all depends on what you mean by attendance. If you have a log of time in/time out then just record that and work out the details in whatever query/report you need.

The most important thing is to record at least the data you need or think you will ever need in the most appropriate way. After that, everything is relatively easy.
Go to Top of Page
   

- Advertisement -