Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

Reinsert records only if condition meet

I thought I had this beat but it is still happening.  When a user closes out a record manually I only want it to reinsert if bitOverridden is not checked.  It still seems to be reinerting no matter.  How do i fix this. The attached file has a record sample to show what is happening.


INSERT INTO tblPermTask (
		intTaskID, intAssignedId,
		intFilterId, intPositionId, 
		strTaskName, dtFound, 
		dtCompleted, strStatus, 
		dtStatus, strSSN, 
		strName, strPositionData, 
		strSoldierData, strNotes,
		strProponent, dtNotification, 
		dtCompleteTask)
 SELECT t.intTaskID, t.intAssignedId,
		t.intFilterId, t.intPositionId, 
		t.strTaskName, t.dtFound, 
		t.dtCompleted, t.strStatus, 
		t.dtStatus, t.strSSN, 
		t.strName, t.strPositionData, 
		t.strSoldierData, t.strNotes,
		t.strProponent, t.dtNotification, 
		t.dtCompleteTask
   FROM tblTask as t INNER JOIN
		tblPermTask as p on p.intassignedId = t.intAssignedId AND p.intFilterID = t.intFilterId AND p.intPositionId = t.intPositionId and p.strSSN = t.strSSN
  WHERE p.dtCompleted is not null and p.bitOverRide is NULL or p.bitOverride = 0
EXCEPT
 SELECT p.intTaskID, p.intAssignedId,
		p.intFilterId, p.intPositionId, 
		p.strTaskName, p.dtFound, 
		p.dtCompleted, p.strStatus, 
		p.dtStatus, p.strSSN, 
		p.strName, p.strPositionData, 
		p.strSoldierData, p.strNotes,
		p.strProponent, p.dtNotification, 
		p.dtCompleteTask
   FROM tblPermTask p

Open in new window

Help.txt
0
kdeutsch
Asked:
kdeutsch
  • 6
  • 4
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
INSERT INTO tblPermTask (
            intTaskID, intAssignedId,
            intFilterId, intPositionId,
            strTaskName, dtFound,
            dtCompleted, strStatus,
            dtStatus, strSSN,
            strName, strPositionData,
            strSoldierData, strNotes,
            strProponent, dtNotification,
            dtCompleteTask)
 SELECT t.intTaskID, t.intAssignedId,
            t.intFilterId, t.intPositionId,
            t.strTaskName, t.dtFound,
            t.dtCompleted, t.strStatus,
            t.dtStatus, t.strSSN,
            t.strName, t.strPositionData,
            t.strSoldierData, t.strNotes,
            t.strProponent, t.dtNotification,
            t.dtCompleteTask
   FROM tblTask as t INNER JOIN
            tblPermTask as p on p.intassignedId = t.intAssignedId AND p.intFilterID = t.intFilterId AND p.intPositionId = t.intPositionId and p.strSSN = t.strSSN
  WHERE p.dtCompleted is not null and ( p.bitOverRide is NULL or p.bitOverride = 0 )
EXCEPT
 SELECT p.intTaskID, p.intAssignedId,
            p.intFilterId, p.intPositionId,
            p.strTaskName, p.dtFound,
            p.dtCompleted, p.strStatus,
            p.dtStatus, p.strSSN,
            p.strName, p.strPositionData,
            p.strSoldierData, p.strNotes,
            p.strProponent, p.dtNotification,
            p.dtCompleteTask
   FROM tblPermTask p

0
 
igni7eCommented:
 WHERE p.dtCompleted is not null and p.bitOverRide<>1
0
 
igni7eCommented:
Just to let you know, if you don't need to, you shouldn't put OR's in your WHERE clause.
They decrease performance.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
kdeutschAuthor Commented:
All,
Ok I tried the first by aneeshattingal, but the record still reinserted,
then I tried the example by igni7e and the record did not reinsert but if I just close the task and don't click on override, it does not reinsert the record like it should.

( p.bitOverRide is NULL or p.bitOverride = 0 )   Reinserts the records no matter

p.bitOverRide is NULL and p.bitOverride <>1   Does not reinsert records if the record is  wasnot marked as overridden.
0
 
igni7eCommented:
Well, is the value changed to 1 when its marked as overridden?

Change X to whatever the overridden value is:
p.bitOverRide is NULL and p.bitOverride <>X
0
 
kdeutschAuthor Commented:
HI,
Correct the value is changed to 1 when overridden.
I have since tried this but now the records don't reinsert id bitOverridden is not checked

 p.dtCompleted is not null and p.bitOverride <> 1
0
 
igni7eCommented:
If tblPermTask contains those records it won't insert as you use EXCEPT
0
 
kdeutschAuthor Commented:
OK,
I took out the except statement but when I closed records but don't override them, they are not reinserting agian like they should., the good news is if I mark them as overridden they are not showing up either.

INSERT INTO tblPermTask (
            intTaskID, intAssignedId,
            intFilterId, intPositionId,
            strTaskName, dtFound,
            dtCompleted, strStatus,
            dtStatus, strSSN,
            strName, strPositionData,
            strSoldierData, strNotes,
            strProponent, dtNotification,
            dtCompleteTask)
 SELECT t.intTaskID, t.intAssignedId,
            t.intFilterId, t.intPositionId,
            t.strTaskName, t.dtFound,
            t.dtCompleted, t.strStatus,
            t.dtStatus, t.strSSN,
            t.strName, t.strPositionData,
            t.strSoldierData, t.strNotes,
            t.strProponent, t.dtNotification,
            t.dtCompleteTask
   FROM tblTask as t INNER JOIN
            tblPermTask as p on p.intassignedId = t.intAssignedId AND p.intFilterID = t.intFilterId AND p.intPositionId = t.intPositionId and p.strSSN = t.strSSN
  WHERE             p.dtCompleted is not null and p.bitOverRide <> 1


0
 
igni7eCommented:
Only insert if it's closed and not overridden right?
WHERE p.dtCompleted is not null and p.bitOverRide <> 1

You may need to change p.dtCompleted:
p.dtCompleted = 'YES'
Change YES to whatever value it is when closed.
0
 
igni7eCommented:
If my above comment doesn't work, just make sure the values in the DB are updated correctly before the insert statement is run.
0
 
kdeutschAuthor Commented:
Ok got this to wrok last week with the following code.
INSERT INTO tblPermTask (
            intTaskID, intAssignedId,
            intFilterId, intPositionId,
            strTaskName, dtFound,
            dtCompleted, strStatus,
            dtStatus, strSSN,
            strName, strPositionData,
            strSoldierData, strNotes,
            strProponent, dtNotification,
            dtCompleteTask)
 SELECT t.intTaskID, t.intAssignedId,
            t.intFilterId, t.intPositionId,
            t.strTaskName, t.dtFound,
            t.dtCompleted, t.strStatus,
            t.dtStatus, t.strSSN,
            t.strName, t.strPositionData,
            t.strSoldierData, t.strNotes,
            t.strProponent, t.dtNotification,
            t.dtCompleteTask
   FROM tblTask as t INNER JOIN
            tblPermTask as p on p.intassignedId = t.intAssignedId AND p.intFilterID = t.intFilterId AND p.intPositionId = t.intPositionId and p.strSSN = t.strSSN
  WHERE             p.dtCompleted is not null and p.bitOverRide is null
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now