Reputation: 3065
I 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)))
Begin
Select JadOPNTotal from JADhist where dateSubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-1))
end
else
begin
Select jadopntotal from JADhist where dateSubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-2))
end
This always retrieves one value with datatype real. 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.
I also 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.
Upvotes: 0
Views: 400
Reputation: 27467
try this
declare @total varchar(10)
if exists (select datesubmitted from JADHist
where datesubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-1)))
Begin
Select @total = JadOPNTotal from JADhist
where dateSubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-1))
end
else
begin
Select @total = jadopntotal from JADhist
where dateSubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-2))
end
UPDATE [GasNominations].[dbo].[OPNData]
SET
[Value] = @total
WHERE NomSubmittedDate between DATEADD(HH, 6, DATEDIFF(dd, 0, GETDATE()))
and DATEADD(HH, 5, DATEDIFF(dd, 0, GETDATE()+1))
GO
Upvotes: 1