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 |
|
ATG
Starting Member
35 Posts |
 Posted - 2013-04-19 : 14:05:09
|
| Hey everyoneI 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) ELSEINSERT 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.ContractStatusfrom JCCM where JCCM.ContractStatus in (0,1,2) and JCCM.JCCo=1 |
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-04-19 : 14:31:59
|
| Hi there, Did you try running the select statement within the IF EXISTS Clause?can you share some of the relevant problematic data? |
 |
|
|
ATG
Starting Member
35 Posts |
Posted - 2013-04-19 : 15:08:48
|
| 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. :) |
|
|
|
MuMu88
Aged Yak Warrior
549 Posts |
Posted - 2013-04-19 : 18:33:47
|
 |
|
|
|
|
|
|
|
|
Insert or Replace...