asked
Compare values in SSIS tables
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 ,
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.
9 People are following this question.
