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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 if range has values then insert them into another

Author  Topic 

silentbob
Starting Member

18 Posts

Posted - 2013-04-29 : 09:40:50
I have a table with 4 columns readdate, tagname, value and amendedvalue in MSSQL 2008. The first 3 are inserted by an sql job running at 8am in the morning and contain data ranging from 7am the previous day to 7am the current day in hourly averages. The amended value can be changed via a web application by the user.

I want to be able to look at the previous days data and if there is a value in the amendedvalue column then update the same range the next day. It will always be for the same range ie if the amended value is changed for tagname1 for the previous 24 hours then the next day will need to show the same value in the amendedvalue column for that 24 hour period.

here is the code I have so far

declare @amendedvalue int

SELECT @AmendedValue = amendedValue
FROM gasdata
Where tagname ='35FI8316' and readDate BETWEEN DateAdd(Hour, 7, DateAdd(Day, DateDiff(Day, 0, GETDATE()), -2))
and dateadd(hour, 6, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))

IF @AmendedValue is not null
BEGIN
update gasdata set amendedValue = ???? where tagname ='35FI8316' and readDate BETWEEN DateAdd(Hour, 7, DateAdd(Day, DateDiff(Day, 0, GETDATE()), -1))
and dateadd(hour, 6, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))
END


I basically need to know what goes in the ???? after amendedvalue = so it picks up the values from the previous date range and inserts them into the latest range

Hope that makes sense

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-29 : 11:17:29
I didn't follow the logic you want to implement - since the data for a day spans 7:00 AM on a given day to 7:00 AM the next day, not clear to me what you meant when you say "next day","previous day" etc. In any case, you will need to do a self-join on the table, something like this:
UPDATE a SET
amendedValue = b.amendedValue
FROM
gasdata a
INNER JOIN gasdata b ON
a.readdate = DATEADD(dd,1,b.readdate)
AND a.tagname= b.tagname
WHERE
-- to get to 7:00 AM yesterday. This can be simplified.
a.readdate >= DATEADD(hh,7,DATEADD(dd,-1,CAST(getdate() AS DATE)));
Go to Top of Page
   

- Advertisement -