February 28, 2012 at 1:17 pm
How i can write script for following case
suppose we have Date column and various columns(mentioned below) in table .and i want to updates column by comparing the date column with the previous day of package exceution day.For eg i am executing package tonight 2 am .then i want to compare yesterday's date(i.e getdate()-1) with the date column.then according to that i want to update the various columns in table.i want to update these columns by comparing the date dimension
1. current year current month (i want to update that table by a logic that after comparing date column with previous day of package execution day(as mentioned above) if date column = current year current month as of today( execution day) then update that row to yes else no)
2.currentyear_current week (i want to update this column by a logic that if date column is in current year and has current week as of last day of prior month then update to yes else no)
3.previous year_current week (i want to update this column by a logic that if date column is in previous year but has same current week as of last day of prior month then update to yes else no)
4. current year month todate(i want to update this column by a logic that if date column is in current year month to date as of last day of prior month then update to yes else no)
5.previous_year moth todat (i want to update this column by a logic that if date column is in previous year current week as of last day of prior month then update to yes else no)
February 28, 2012 at 1:46 pm
Please provide some ready to use sample data as described in the first link in my signature together with your expected result.
(Having more than 20 reads on no reply usually indicate an issue with the question itself...)
February 28, 2012 at 3:22 pm
You already have your logic outlined, so really, all you need to do is set it inside a case statement (if doing this in T-SQL)
This would be representative of your first scenario. Substitute the [] text with your actual Date fields being set.
Case When [YesterdayYearMonth] = [TodayYearMonth] Then 'Yes' Else 'No' End
February 28, 2012 at 4:13 pm
thanks that what i want .but how we can pass variable for that and set that in case expression.
February 28, 2012 at 6:58 pm
When you say "package" are you using an SSIS Package and executing a stored procedure or sql code via an execute sql task?
Are you executing it via Sql Agent? does your agent have a step that executes sql/stored procedure?
For T-SQL
Declare @RunDate date, @PrevRunDate date
Set @RunDate = --insert your date logic here
Set @PrevRunDate = --insert your date logic here
Update SomeTable
Set SomeColumn =
Case When --code for something happens
Then --do something
When --something else happens for SomeColumn
Then --do this instead
Else --do this instead
End
,SomeOtherColumn = ---
February 28, 2012 at 7:07 pm
i want to use execute sql task..i have posted one more forum for this u having title=sql query and started by =weston_086.if possible please try to provide solution
http://www.sqlservercentral.com/Forums/Topic1259203-391-1.aspx
February 28, 2012 at 10:08 pm
Please read the first article I reference below regarding asking for help. The information you have provided in both posts is incomplete and doesn't provide us enough to work with to help provide you with a solution that we could even test for correctness.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply