May 26, 2009 at 8:27 am
I have a situation where I want to take the value from the previous row, add a value (it’s a date so DATEADD) and put the new value in the column on the current row, then repeat the process. I set up a table variable with an identity column so I could distinguish between the rows but not working well. It seems to work for the first 2 records then looses track and stops. I have included the base output and the query I am using to look at the data (it will be an update when I figure it out .. IF). Any help would be appreciated when you get a chance. I am sure it is something small that I am missing but a fresh set of eyes will probably see it right away.
Thanks
New Value OrigID NewID Orig Value Prev Value Orig AddTime Prev Add Time
2009-05-15 08:00:00.000 1 NULL 2009-05-15 08:00:00.000 NULL 0.00 NULL
2009-05-15 08:30:00.000 2 1 2009-05-15 08:30:00.000 2009-05-15 08:00:00.000 0.50 0.00
2009-05-15 10:30:00.000 3 2 2009-05-15 10:00:00.000 2009-05-15 08:30:00.000 2.00 0.50
2009-05-15 14:00:00.000 4 3 2009-05-15 12:00:00.000 2009-05-15 10:00:00.000 4.00 2.00
SELECT time_entrydate = COALESCE(DATEADD(mi,t.time_recorded*60,t1.time_entrydate),t.time_entrydate),
currID=t.time_ID, prevID=t1.time_id,curr = t.time_entrydate, prev = t1.time_entrydate
,currtime_rec=t.time_recorded, prev_timerec=t1.time_recorded
FROM @time t
LEFT JOIN @time t1 ON t1.time_id=t.time_id-1
May 26, 2009 at 10:26 am
Hi,
I've tried solving this with quirky update method, but having some difficulties!
Can't figure out how to add index to table variable? create index throws an error, declaring OrigID as index(1,1) means I can't insert data into table because identity insert is off? (I'd like to know hoe to add index after table creation _and_ allow identity inserts into the table varible)
declare @time table (NewValue datetime, OrigID int, NewID int, OrigValue datetime, PrevValue datetime, OrigAddTime decimal(12,2), PrevAddTime decimal(12,2))
insert into @time
select '2009-05-15 08:00:00.000', 1, Null, '2009-05-15 08:00:00.000', NULL, 0.00, NULL union all
select '2009-05-15 08:30:00.000', 2, 1, '2009-05-15 08:30:00.000', '2009-05-15 08:00:00.000', 0.50, 0.00 union all
select '2009-05-15 10:30:00.000', 3, 2, '2009-05-15 10:00:00.000', '2009-05-15 08:30:00.000', 2.00, 0.50 union all
select '2009-05-15 14:00:00.000', 4, 3, '2009-05-15 12:00:00.000', '2009-05-15 10:00:00.000', 4.00, 2.00 union all
select '2009-05-15 14:34:00.000', 4, 3, '2009-05-15 12:00:00.000', '2009-05-15 10:00:00.000', 4.00, 2.00
select * from @time
declare @runningtime table (OrigID int, NewValue datetime, TimeDiff decimal(12,2))
insert into @runningtime
select OrigID, NewValue, 0 from @time
--create index ind on (@runningtime) (origid)
select * from @runningtime
declare @PrevTimeDiff decimal(12,2)
declare @PrevDate datetime
select @PrevTimeDiff = 0, @PrevDate = '2009-05-15 08:00:00.000'
update @runningtime
set
@PrevTimeDiff = TimeDiff = @PrevTimeDiff + datediff(mi, @PrevDate, NewValue),
@PrevDate = NewValue
-- need table index hint here?
select * from @runningtime
/// Edit
Rod,
I'm only solving the problem based upon the output of your query, if you give the original data of your @time table — ideally in the format of the insert into @time … select union all select union all … format in at the top of my code — I can use that as the basis of solution.
Allister
May 26, 2009 at 10:54 am
Before you can get very far with this type of query, you need to define 'previous row'. What is the relationship between the 'current row' and the 'previous row'.
The next thing we will need to know is the version of SQL Server you are using. There are some things in SQL Server 2005 that could help in this situation (e.g. row_number(), rank(), etc...).
In your current example, you are trying to relate the rows based upon the time. This won't work because the previous row does not appear to be yesterday's date and time. It does not appear to be every hour, or every half hour, or some other increment.
As Allister pointed out - the quirky update (or running totals) solution might work for this. But, we will need table structures, sample data and expected results to provide a better solution. You can review the article I link to in my signature to find out how to post that kind of information.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 26, 2009 at 10:59 am
Allister Reid (5/26/2009)
Hi,I've tried solving this with quirky update method, but having some difficulties!
Can't figure out how to add index to table variable? create index throws an error, declaring OrigID as index(1,1) means I can't insert data into table because identity insert is off? (I'd like to know hoe to add index after table creation _and_ allow identity inserts into the table varible)
declare @time table (NewValue datetime, OrigID int, NewID int, OrigValue datetime, PrevValue datetime, OrigAddTime decimal(12,2), PrevAddTime decimal(12,2))
insert into @time
select '2009-05-15 08:00:00.000', 1, Null, '2009-05-15 08:00:00.000', NULL, 0.00, NULL union all
select '2009-05-15 08:30:00.000', 2, 1, '2009-05-15 08:30:00.000', '2009-05-15 08:00:00.000', 0.50, 0.00 union all
select '2009-05-15 10:30:00.000', 3, 2, '2009-05-15 10:00:00.000', '2009-05-15 08:30:00.000', 2.00, 0.50 union all
select '2009-05-15 14:00:00.000', 4, 3, '2009-05-15 12:00:00.000', '2009-05-15 10:00:00.000', 4.00, 2.00 union all
select '2009-05-15 14:34:00.000', 4, 3, '2009-05-15 12:00:00.000', '2009-05-15 10:00:00.000', 4.00, 2.00
select * from @time
declare @runningtime table (OrigID int, NewValue datetime, TimeDiff decimal(12,2))
insert into @runningtime
select OrigID, NewValue, 0 from @time
--create index ind on (@runningtime) (origid)
select * from @runningtime
declare @PrevTimeDiff decimal(12,2)
declare @PrevDate datetime
select @PrevTimeDiff = 0, @PrevDate = '2009-05-15 08:00:00.000'
update @runningtime
set
@PrevTimeDiff = TimeDiff = @PrevTimeDiff + datediff(mi, @PrevDate, NewValue),
@PrevDate = NewValue
-- need table index hint here?
select * from @runningtime
/// Edit
Rod,
I'm only solving the problem based upon the output of your query, if you give the original data of your @time table — ideally in the format of the insert into @time … select union all select union all … format in at the top of my code — I can use that as the basis of solution.
Allister
You can't add an index to a table variable. You may want to retry this using a tempory table.
May 26, 2009 at 11:10 am
The previous row contains the original value that I want to add the value to. Here is how the logic should go:
1. orginal = 8:00AM
2. Add .5 hours, so 8:00AM + .5hrs = 8:30AM
3. Now the tricky part. The next addition is 2 hrs, but I need it to be added to the previous new value (not the original 8:00AM) so the new value should be 10:30.
etc...
Does that help with the definition of previous row? The server the database is on is currently 2000 so a lot of the new 2005 features will not work for me. If necessary I can move it to one of my 2005/2008 servers but then I have to query on a linked server to get some of my data so that is not the optimal solution.
Here is the code I used to create the @temp table variable.. sorry I should have included that. It shows the identity column definition.
DECLARE @time TABLE(time_ID int identity(1,1),time_empno char(9),time_JobNo char(10),time_WorkCenter char(5),time_LeadPerson bit,time_Setup bit,time_Rework bit,time_QtyComplete decimal(8,2),time_QtyScrap decimal (8,2),time_entryDate datetime,prev_time datetime,time_Action char(10),time_CreateDate datetime,time_dotw char(10),time_weekending datetime,time_recorded decimal(8,2))
May 26, 2009 at 11:15 am
Any chance of some data Rod? if you follow this url: http://www.sqlservercentral.com/articles/Best+Practices/61537/ there are really clear instructions 🙂
May 26, 2009 at 11:38 am
Sure thing.. my apologies for confusion. First time positing something like this. I have simplified to the following and included the update query that I am attempting to use that is not working. The original data was being generated from another procedure that just added the time_recorded value to the original 08:00:00 time value. The application that recieves the data cannot process it in this manner so I need each row after the original to replace the current value and then calculate the next row based on the new value. So the data that is being loaded below in the query should be replaced with:
2009-05-15 08:00:00.000
2009-05-15 08:30:00.000
2009-05-15 10:30:00.000
2009-05-15 14:30:00.000
If this is still unclear or if I am still breaking a best practice let me know and I will try to clarify further. Thanks for your patience.
DECLARE @time TABLE(time_ID int identity(1,1),time_entrydate datetime,time_recorded decimal(8,2))
INSERT INTO @time(time_entrydate, time_recorded)
SELECT '2009-05-15 08:00:00.000',NULL UNION ALL
SELECT '2009-05-15 08:30:00.000',0.5 UNION ALL
SELECT '2009-05-15 10:00:00.000',2.0 UNION ALL
SELECT '2009-05-15 12:00:00.000',4.0
update t
SET time_entrydate = COALESCE(DATEADD(mi,t.time_recorded*60,t1.time_entrydate),t.time_entrydate)
FROM @time t
JOIN @time t1 ON t.time_id=t1.time_id-1
SELECT * from @time
May 26, 2009 at 11:45 am
I may have missed it, but what version of SQL Server are you using?
May 26, 2009 at 11:48 am
Sorry, thought I posted that. The DB is running on SQL 2000 but if this is going to be extermely difficult to accomplish I can probably move it to a 2005/2008 server. It's just that would require me to run queries across a linked server to get the data I need from another DB on the 2000 server so I have been trying to avoid that if possible.
May 26, 2009 at 12:00 pm
Based soley on your posted sample data here is some code to work with. This may need to be modified to meet your actual requirements.
DECLARE @time TABLE(time_ID int identity(1,1),time_entrydate datetime primary key,time_recorded decimal(8,2))
INSERT INTO @time(time_entrydate, time_recorded)
SELECT '2009-05-15 08:00:00.000',NULL UNION ALL
SELECT '2009-05-15 08:30:00.000',0.5 UNION ALL
SELECT '2009-05-15 10:00:00.000',2.0 UNION ALL
SELECT '2009-05-15 12:00:00.000',4.0
declare @NewDateTime datetime;
select * from @time;
update @time set
@NewDateTime = [time_entrydate] = case when time_recorded is null then time_entrydate else dateadd(mi, time_recorded * 60, @NewDateTime) end;
select * from @time;
May 26, 2009 at 12:06 pm
Nice one Lynn, the null was killing me!
May 26, 2009 at 12:09 pm
One statement to make the report... one to correct it!
May 26, 2009 at 12:10 pm
Thanks, but there really is a lot more to the quirky update than is included in the post. It would be better if we had the full requirements for the problem at hand.
The clustered index is what dictates the order of the update, in this case the primary key on [time_entrydate].
May 26, 2009 at 12:16 pm
Thanks Lynn... Just here trying to learn stuff I should of got to grips a while back... answering (trying to) queries gives me excuse to play with sql...
also incomplete requirements is pretty standard situation... my boss read an article about 'extreme programming' techniques, now he thinks he can just give me a few pretty wooly verbal guidelines, then we can fix the problems in the second (or nth) iteration...
May 26, 2009 at 12:16 pm
Thanks to all!! The @NewDateTime = [time_entrydate] = was the part that I was missing. I could not get the thing to re-assign the new values. As always, simple when you are shown the way.
Sorry for all the confusion in the earlier posts.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply