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)
 Insert or Replace...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ATG
Starting Member

USA
35 Posts

Posted - 04/19/2013 :  14:05:09  Show Profile  Reply with Quote
Hey everyone

I have created a table that I dump data into everyday. It has 2 key fields of job number and month. The idea is that everyday a SQL job runs that updates these records or inserts new ones if no record exists. The first day of each new month a new record is created because of the month key field. After that day, each record is updated until a new month begins. The problem I'm having is that if a new job is created in the middle of the month, a new record is not created after other ones have already been created. My current code uses IF EXISTS...ELSE which seems like it should work, but I'm pretty sure the problem lies in the select statement after IF EXISTS.

IF EXISTS (SELECT udEstMonthlySnapshot.Mth, udEstMonthlySnapshot.Job FROM udEstMonthlySnapshot WHERE udEstMonthlySnapshot.Mth=CAST(CAST(YEAR(GetDate()) AS varchar) + '-' + CAST(Month(GETDATE()) AS varchar) + '-' + CAST(1 AS varchar) AS DATETIME))
UPDATE udEstMonthlySnapshot SET udEstMonthlySnapshot.CurrentCost=isNull(JCCM.ContractAmt,0)
FROM udEstMonthlySnapshot
LEFT JOIN JCCM on JCCM.JCCo=udEstMonthlySnapshot.Co and JCCM.Contract=udEstMonthlySnapshot.Job
WHERE JCCM.ContractStatus in (0,1,2) and JCCM.JCCo=1 and udEstMonthlySnapshot.Mth=CAST(CAST(YEAR(GetDate()) AS varchar) + '-' + CAST(Month(GETDATE()) AS varchar) + '-' + CAST(1 AS varchar) AS DATETIME)

ELSE

INSERT INTO udEstMonthlySnapshot(Co,CurrentCost,Job,Mth,OriginalCost,JobStatus)
select JCCM.JCCo, JCCM.ContractAmt, JCCM.Contract, CAST(CAST(YEAR(GetDate()) AS varchar) + '-' + CAST(Month(GETDATE()) AS varchar) + '-' + CAST(1 AS varchar) AS DATETIME), JCCM.OrigContractAmt, JCCM.ContractStatus

from JCCM

where JCCM.ContractStatus in (0,1,2) and JCCM.JCCo=1

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/19/2013 :  14:31:59  Show Profile  Reply with Quote
Hi there,
Did you try running the select statement within the IF EXISTS Clause?
can you share some of the relevant problematic data?
Go to Top of Page

ATG
Starting Member

USA
35 Posts

Posted - 04/19/2013 :  15:08:48  Show Profile  Reply with Quote
I was able to figure it out. I actually created 2 separate job steps. One that looks for any existence of that particular job in my table. If it doesnt exist, it creates a new record. The second step just updates existing records. :)
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/19/2013 :  18:33:47  Show Profile  Reply with Quote
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