January 30, 2003 at 11:40 am
Sure could use some advice! I need to take the table below:
CREATE TABLE [dbo].[TimeTable] (
[EmployeeID] [int] NOT NULL ,
[RecordDate] [datetime] NOT NULL ,
[BeginWork] [smallint] NOT NULL ,
[lunchOut] [smallint] NULL ,
[lunchIn] [smallint] NULL ,
[EndWork] [smallint] NULL ,
[CostCenter] [int] NULL ,
) ON [PRIMARY]
GO
and create a procedure to seperate any record with an Endwork time > 1200 into two records.
The new record would be inserted back into the source table (TimeTable)with a Beginwork time of 0000 with the other field values being inherited from the original record.
January 30, 2003 at 11:54 am
Test this and see if it works:
Insert into [dbo].[TimeTable]
([EmployeeID],
[RecordDate],
[Beginwork],
[lunchOut],
[lunchIn],
[EndWork],
[CostCenter])
select [EmployeeID],
[RecordDate],
0000,
[lunchOut],
[lunchIn],
[EndWork],
[CostCenter] from [dbo].[TimeTable]
where
[EndWork] > 1200
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
January 30, 2003 at 1:41 pm
Greg,
When I try this it tells me it completed successfully but not records are added. Is there syntax I am missing?
January 30, 2003 at 1:55 pm
Run this to verify that you do have [EndWork] values > 1200:
select [EmployeeID],
[RecordDate],
0000,
[lunchOut],
[lunchIn],
[EndWork],
[CostCenter] from [dbo].[TimeTable]
where
[EndWork] > 1200
If this command returns no records then there is no records to split. This command is the select portion of the command I sent you.
Modify this select statements where clause to reflect the condition you really need. Is it possibly that [EndWork] is 1200 so the where statement should read something like:
Select ....
where
[EndWork] >= 1200
Let me know how it goes.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
January 30, 2003 at 2:34 pm
I have several thousand records that have an EndWork greater than 1200 (this should have been 2400), but they won't have a Beginwork time of 0000 until after I create the new record.
Since no new records were created, I have none that have a Beginwork time of 0000.
Here is an example of what I want to do:
Original Record
EmployeeID RecordDate BeginWork EndWork
1 1/23/03 2200 0630
After procedure runs:
EmployeeID RecordDate BeginWork EndWork
1 1/23/03 2200 2400
1 1/23/03 0000 0630
January 30, 2003 at 2:41 pm
Did I misread your post? Your example shows a record with an EndWork value of less than 1200 (0630 is less than 1200). Try this:
Insert into [dbo].[TimeTable]
([EmployeeID],
[RecordDate],
[Beginwork],
[lunchOut],
[lunchIn],
[EndWork],
[CostCenter])
select [EmployeeID],
[RecordDate],
0000,
[lunchOut],
[lunchIn],
[EndWork],
[CostCenter] from [dbo].[TimeTable]
where
[EndWork] < 1200
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
January 30, 2003 at 2:50 pm
I misread it again. Looks like you also want to set the EndWork on those original records set to 2400.
Think this should do it:
Update [dbo].[TimeTable]
set [EndWork] = 2400
where [Endwork] < 1200 and [BeginWork] > 0
I would verify this works with your data.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
January 30, 2003 at 2:55 pm
No, you didn't misread it. The EndWork of 0630 would be the result on the new record created. If the record crosses midnight then I need to create the second record that shows BeginWork of 0000 and EndWork of 0630(the hours worked after midnight so I can do a calculation to get total hours worked). The record just cannot cross midnight because of the software I'm passing the information to.
Original Record
EmployeeID RecordDate BeginWork EndWork
1 1/23/03 2200 0630
After procedure runs:
EmployeeID RecordDate BeginWork EndWork
1 1/23/03 2200 2400
1 1/23/03 0000 0630 *what I'm trying to create
January 30, 2003 at 3:02 pm
So did my second post, using the LESS THAN 1200 work?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
January 31, 2003 at 7:41 am
No, it's not the select statement that's causing the problem. If I try to Insert the records into a different table then the source table it works just fine. The problem I think is that the destination table and source table are the same. Any thoughts?
February 2, 2003 at 12:03 pm
Greg,
This actually works great, thanks!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply