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 |
silentbob
Starting Member
18 Posts |
Posted - 2013-03-26 : 06:33:29
|
HiI am trying to select a value from one table and insert it into a specfic section of another table. I have the following sql (mssql 2008)if exists (select datesubmitted from JADHist where datesubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-1)))BeginSelect JadOPNTotal from JADhist where dateSubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-1))end elsebeginSelect jadopntotal from JADhist where dateSubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-2)) end This always retrieves one value. I have another table which is 2 columns, one is the date with the hour next to it and there is an entry for the next 2 weeks for every hour. The format is 2013-03-26 01:00:00.000 and so on. I want to insert the value I get from the code above into the 2nd table for one 24 hour period (24 entries). THe 24 period will be today from 6am till tomorrow at 6am.Hope this all makes sense, im just confused how best to go about this. |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-26 : 06:48:45
|
--Query for Today 6AM to tomorrow 6AM;with H24Formatcte(StartDate) as ( SELECT DATEADD(HH, 6, DATEDIFF(dd, 0, GETDATE())) -- Today 6 AM UNION ALL SELECT DATEADD(HH, 1, StartDate) FROM H24Formatcte WHERE StartDate <DATEADD(HH, 6, DATEDIFF(dd, 0, GETDATE()+1)))SELECT * FROM H24Formatcte |
|
|
silentbob
Starting Member
18 Posts |
Posted - 2013-03-26 : 07:01:28
|
thanks for that, I already have the dates and times set up, I just need to be able to update the table through a certain date range with the value obtained from my code.I have been trying to amend the update to script in mssql to fit in my if exists query but I cant get it right. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-26 : 07:07:55
|
Can you ellaborate the requirement...?what is the datatype for JadOPNTotal column?Based on JadOPNTotal column value you want to insert into second table? |
|
|
silentbob
Starting Member
18 Posts |
Posted - 2013-03-26 : 07:13:12
|
the datatype of JadOPNTotal is real. the if exists code in my first post gets me that value, I have have the following code with a test value of 10 in it just to prove the code updates opndata correctly.UPDATE [GasNominations].[dbo].[OPNData] SET [Value] = '10' WHERE NomSubmittedDate between DATEADD(HH, 6, DATEDIFF(dd, 0, GETDATE())) and DATEADD(HH, 5, DATEDIFF(dd, 0, GETDATE()+1))GO I want to replace the '10' value with the if exists statement but I cant get the syntax correct. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-26 : 09:00:13
|
[code]DECLARE @JadOPNTotal real;if exists (select datesubmitted from JADHist where datesubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-1)))BeginSelect @JadOPNTotal = JadOPNTotal from JADhist where dateSubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-1))endelsebeginSelect @JadOPNTotal =jadopntotal from JADhist where dateSubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-2))endUPDATE [GasNominations].[dbo].[OPNData] SET [Value] = @JadOPNTotal WHERE NomSubmittedDate between DATEADD(HH, 6, DATEDIFF(dd, 0, GETDATE())) and DATEADD(HH, 5, DATEDIFF(dd, 0, GETDATE()+1))[/code]--Chandu |
|
|
silentbob
Starting Member
18 Posts |
Posted - 2013-03-26 : 09:31:49
|
spot on thanks |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-26 : 09:33:05
|
quote: Originally posted by silentbob spot on thanks
Welcome--Chandu |
|
|
|
|
|
|
|