x

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

,
more ▼

asked Oct 17, 2012 at 04:03 AM in Default

madhavibharani gravatar image

madhavibharani
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.
more ▼

answered Oct 17, 2012 at 08:20 AM

mjharper gravatar image

mjharper
1.4k 3 4 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x941
x84
x52
x41

asked: Oct 17, 2012 at 04:03 AM

Seen: 844 times

Last Updated: Oct 17, 2012 at 08:20 AM