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.
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 2008B |
|
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 |
|
|
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 2008B
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 tsSET @lv_next_ticket = 'Ticket-' + REPLICATE('0', 4 - LEN(ts.next_value)) + CONVERT(VARCHAR(4), ts.next_value), ts.next_value = ts.next_value + 1FROM [dbo].[ticketSequence] tsSELECT @lv_next_ticketunspammed |
|
|
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 2008B
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. |
|
|
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. |
|
|
|
|
|
|
|