Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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

SQL
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
Posted
Updated 9-Sep-16 23:28pm

1 solution

Stop converting dates to strings to compare them: when you do that you get a string comparison which means that the result is based on the first different character between the two strings.
You need a DATE or DATETIME comparison: so change your DB to store dates as DATE or DATETIME and compare that value against the GETDATE() return value directly.

Always store values in the most appropriate format, not as strings. It may seem to be simpler, but it causes huge problems every time you want to use them.
 
Share this answer
 
Comments
Member 12724138 10-Sep-16 5:38am    
thank u...but can u help with this one please
OriginalGriff 10-Sep-16 5:39am    
Which bit? Have you fixed your DB already?
Member 12724138 10-Sep-16 6:02am    
yes..can u help me with the if conditions
OriginalGriff 10-Sep-16 6:40am    
If your DB now stores them as DATETIME, then just compare them!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900