I need help with, giving the if exists and if not exists condition.
I want to write an if condition, based on date, such that, if any row exists in table1 on current date TARGET_DT for a particular job, then do some action else do other action. like shown below
IF EXISTS(SELECT 1 FROM COD_BLU_OPENING_VOLUME WHERE TARGET_DT=CONVERT(DATETIME,GETDATE(),101) AND JOBID=@OPENJOB)
BEGIN
IF(SELECT COUNT(1) FROM COD_BLU_INVENTORY WITH(NOLOCK) WHERE (JOBID=@JOB AND STATUS='A' AND TARGETDT = CONVERT(DATETIME,@TARGETDT,101)))=0
BEGIN
INSERT INTO COD_BLU_INVENTORY(JOBID,TARGETDT,CARRYOVER,FRESHVOL,TOTALVOL,STATUS,UPDATEDBY,UPDATEDT,OPENINGVOL)
VALUES(@JOB,@TARGETDT,@CARRYOVERVOL,@FRESHVOL,@TOTALVOL,'A',@EMPCODE,GETDATE(),@CARRYOVERVOL)
SELECT '1'
END
ELSE
BEGIN
SELECT'0'
END
END
ELSE IF NOT EXISTS(SELECT 1 FROM COD_BLU_OPENING_VOLUME WHERE TARGET_DT=CONVERT(DATETIME,GETDATE(),101) AND TARGET_DT<CONVERT(DATETIME,GETDATE(),101) AND JOBID=@OPENJOB)
BEGIN
INSERT INTO COD_BLU_OPENING_VOLUME(TARGET_DT,JOBID,OPENING_COUNT,UPDATED_BY,UPDATED_DT,PRE_ASSIGNEDCOUNT)
VALUES(@OPENTARDT,@OPENJOB,@OPENCNT,@OPENEMPCODE,GETDATE(),@PREOPENCNT)
IF (SELECT COUNT(1) FROM COD_BLU_INVENTORY WITH(NOLOCK) WHERE (JOBID=@JOB AND STATUS='A' AND TARGETDT = CONVERT(DATETIME,@TARGETDT,101)))=0
BEGIN
INSERT INTO COD_BLU_INVENTORY(JOBID,TARGETDT,CARRYOVER,FRESHVOL,TOTALVOL,STATUS,UPDATEDBY,UPDATEDT,OPENINGVOL)
VALUES(@JOB,@TARGETDT,@CARRYOVERVOL,@FRESHVOL,@TOTALVOL,'A',@EMPCODE,GETDATE(),@CARRYOVERVOL)
SELECT '1'
END
ELSE
BEGIN
SELECT'0'
END
END
END
but the problem is,if the TARGET_DT is given as future date, it executes the else condition, I want it to execute the if condition, the above is the code which I tired. Any help in solving this will be appreciated. Please help.
What I have tried:
I have given a try like the one give above