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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 if range has values then insert them into another
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

silentbob
Starting Member

18 Posts

Posted - 04/29/2013 :  09:40:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 04/29/2013 :  11:17:29  Show Profile  Reply with Quote
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
  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.03 seconds. Powered By: Snitz Forums 2000