CODEWITHSUNDEEP
sqlsql-serverif-statementsql-updateexists
Silentbob
Silentbob

Reputation: 3065

update SQL statement and using if exists

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

Answers (1)

rs.
rs.

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

Related Questions