April 8, 2014 at 12:20 am
Hi team,
Currently I have Type, Internval, Rstartdate,ID in the view. I need to add " Week" to that and following is the business logic to populate the column with either Week1, Week2 or 0.
Every fortnight we have a different timetable and based on that we allocate staff for that.
out first fortnight had started 07-Jan-2013 ( which is Monday ) is Week1 , 14-Jan-2013 is Week2 AND 21-Jan-2013 will be again Week1, as the roster is only for fortnight ( two weeks).
Three conditions you are looking at to populate the “ Week” column is
Condition-1
If Type =1 , Interval =2 , then datediff(07.jan,2013 , Rstartdate) mod 14 < 7 then it is Week1 , else
datediff(07.jan,2013 , Recurrancestart) mod 14 >= 7 then it is Week2
Condition-2
If the Type =1 and Interval =1 , then populate this column as 0
Condition -3
If the Recurrance interval is Daily or monthly, then populate this column as 0
Could some one assist me with this.
Thanks for your assistance.
Regards,
April 8, 2014 at 4:59 pm
Hi Team,
I have started putting together the logic for the above requriement..
CASE
WHEN RecurrenceType =1 and Recurrenceinterval =2 and datediff(day,'07/01/2013', RecurrenceStart)/ 14 < 7 then 'Week1'
When RecurrenceType =1 and Recurrenceinterval =2 and datediff(day,'07/01/2013', RecurrenceStart)/ 14 >= 7 then 'Week2'
Else 0 End WeekSequence
But, it is throwing the following complilation error
Conversion failed when converting the varchar value 'Week2' to data type int.
Could some one suggest what corrections need to be done?
Regards,
Krishna.
April 8, 2014 at 5:53 pm
kish1234 (4/8/2014)
Hi Team,I have started putting together the logic for the above requriement..
CASE
WHEN RecurrenceType =1 and Recurrenceinterval =2 and datediff(day,'07/01/2013', RecurrenceStart)/ 14 < 7 then 'Week1'
When RecurrenceType =1 and Recurrenceinterval =2 and datediff(day,'07/01/2013', RecurrenceStart)/ 14 >= 7 then 'Week2'
Else 0 End WeekSequence
But, it is throwing the following complilation error
Conversion failed when converting the varchar value 'Week2' to data type int.
Could some one suggest what corrections need to be done?
Regards,
Krishna.
CASE
WHEN RecurrenceType =1 and Recurrenceinterval =2 and datediff(day,'07/01/2013', RecurrenceStart)/ 14 < 7 then 'Week1'
When RecurrenceType =1 and Recurrenceinterval =2 and datediff(day,'07/01/2013', RecurrenceStart)/ 14 >= 7 then 'Week2'
Else '0' End WeekSequence
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply