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)
 Inserting into another table using if exists
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

silentbob
Starting Member

18 Posts

Posted - 03/26/2013 :  06:33:29  Show Profile  Reply with Quote
Hi

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. 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
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/26/2013 :  06:48:45  Show Profile  Reply with Quote
--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
Go to Top of Page

silentbob
Starting Member

18 Posts

Posted - 03/26/2013 :  07:01:28  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/26/2013 :  07:07:55  Show Profile  Reply with Quote
Can you ellaborate the requirement...?
what is the datatype for JadOPNTotal column?

Based on JadOPNTotal column value you want to insert into second table?
Go to Top of Page

silentbob
Starting Member

18 Posts

Posted - 03/26/2013 :  07:13:12  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/26/2013 :  09:00:13  Show Profile  Reply with Quote

DECLARE @JadOPNTotal real;
if exists (select datesubmitted from JADHist where datesubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-1)))

Begin

Select @JadOPNTotal = JadOPNTotal from JADhist where dateSubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-1))
end
else
begin
Select @JadOPNTotal =jadopntotal from JADhist where dateSubmitted = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()-2))
end

UPDATE [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))



--
Chandu
Go to Top of Page

silentbob
Starting Member

18 Posts

Posted - 03/26/2013 :  09:31:49  Show Profile  Reply with Quote
spot on thanks
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/26/2013 :  09:33:05  Show Profile  Reply with Quote
quote:
Originally posted by silentbob

spot on thanks


Welcome

--
Chandu
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.06 seconds. Powered By: Snitz Forums 2000