SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Auto Generation Of Tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bijinn
Starting Member

India
1 Posts

Posted - 07/25/2013 :  05:46:40  Show Profile  Reply with Quote
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

Sweden
30282 Posts

Posted - 07/25/2013 :  06:30:31  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
3 Posts

Posted - 07/25/2013 :  08:07:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3760 Posts

Posted - 07/25/2013 :  08:17:43  Show Profile  Reply with Quote
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 - 07/25/2013 :  09:57:01  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000