samedi 10 septembre 2016

sql server if condition based on date

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.

Aucun commentaire:

Enregistrer un commentaire