Compare values in SSIS tables

Question

hi, I have mn name workweek in a table i want to compare the value of current workweek with the max of workweek and if they are equal i need to delete the data from the same table in SSIS...,Hii,

DELETE  FROM SKL_GT_SV_Pre_Si_Indicators_Abhishek
WHERE   CASE WHEN DATEPART(dw,
                           '01-01-' + CAST(DATEPART(yy, GETDATE()) AS CHAR(4))) = 7 /* 1st day of year is Saturday*/
             THEN CASE WHEN DATEPART(wk, GETDATE()) = 54 /* MS Anomaly*/
                       THEN CAST(DATEPART(yyyy, GETDATE()) + 1 AS CHAR(4))
                            + ' 01'
                       ELSE CAST(DATEPART(yyyy, GETDATE()) AS CHAR(4)) + ' '
                            + RIGHT('0'
                                    + CAST(DATEPART(wk, GETDATE()) AS VARCHAR),
                                    2)
                  END
             ELSE CASE WHEN DATEPART(wk, GETDATE()) = 53 /* Actually first week of next year*/
                       THEN CAST(DATEPART(yyyy, GETDATE()) + 1 AS CHAR(4))
                            + ' 01'
                       ELSE CAST(DATEPART(yyyy, GETDATE()) AS CHAR(4)) + ' '
                            + RIGHT('0'
                                    + CAST(DATEPART(wk, GETDATE()) AS VARCHAR),
                                    2)
                  END
        END = ( SELECT  MAX(WW) AS MAX_WW
                FROM    SKL_GT_SV_Pre_Si_Indicators_Abhishek
              )

This is the code I am trying but it deletes all the data from table

,

Answer 1

I think the problem occurs as neither the left or right hand side of the WHERE clause references your table. Once you work through the case statements, based on today's date you end up with the WHERE clause of:

WHERE '2012 42' = (
 SELECT MAX(WW) AS MAX_WW
 FROM SKL_GT_SV_Pre_Si_Indicators_Abhishek
   )

I assume your MAX(WW) also equals '2012 42' and therefore the WHERE clause becomes

WHERE '2012 42' = '2012 42'

which will be true for all rows and therefore all rows are deleted.

It's hard to suggest an alternative without knowing more about your data, but I would expect you want something like this:

DELETE FROM SKL_GT_SV_Pre_Si_Indicators_Abhishek 
WHERE WK = DATEPART(wk, GETDATE()) 
 AND [Year] = DATEPART(yyyy, GETDATE())

This will delete all records where the WK in the table matches the current WK. I don't know if you have a [Year] column, but you'll need something like that to stop it also deleting this WK for previous years.

Compare values in SSIS tables

Follow this question

Related Questions