Subtract two business days from one column and display into another column.
in [Reports]
|
Prev: Localized date formats with 2 digit year
Next: Report in Webarchive Format does not display correct in Outlook 20
From: Mila on 27 Jun 2008 11:20 Hi, I am new to Reporting services and the problem I'm having is displaying a day that is two business days before another date. I have two columns Due Date to QA and Due Date to PM. I want to subtract two business days from Due to PM date, excluding Saturday and Sunday, and display this new date in the Due to QA column. Also if this helps, I used the properties to format the output of the Due to PM date. For example " Friday, June 23, 2008". Please help me or show me how to do this?
From: Mila on 27 Jun 2008 11:56 Here is an update to the previous post. This is my expression for the DUE to QA column =iif(((fields!date_time_end.value="Monday", reportitems!textbox78 = fields! daydiff.value, iif(fields!date_time_end.value = "Tuesday", reportitems! textbox78 = fields!daydiff2.value, iif(fields!date_time_end.Value = "Wednesday", reportitems!textbox78 = fields!daydiff3.value, iif(fields! date_time_end.Value = "Thursday", reportitems!textbox78 = fields!daydiff3. value, iif(fields!date_time_end.Value = "Friday", reportitems!textbox78 = fields!daydiff3.value)))) Error: The Value expression for the textbox 'textbox78' refers to the field 'daydiff3'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope. Which is wierd because I've declared this as DATEADD(day, -2, v_SO_Activity_Company.Date_Time_End.value) in the dataset. Again, if you can offer any suggestions as to how to solve this problem that would be so helpful!!! Mila wrote: >Hi, I am new to Reporting services and the problem I'm having is displaying a >day that is two business days before another date. I have two columns Due >Date to QA and Due Date to PM. I want to subtract two business days from Due >to PM date, excluding Saturday and Sunday, and display this new date in the >Due to QA column. Also if this helps, I used the properties to format the >output of the Due to PM date. For example " Friday, June 23, 2008". Please >help me or show me how to do this?
From: trussman on 30 Jun 2008 11:39 On Jun 27, 11:56 am, "Mila" <u44513(a)uwe> wrote: > Here is an update to the previous post. This is my expression for the DUE to > QA column > > =iif(((fields!date_time_end.value="Monday", reportitems!textbox78 = fields! > daydiff.value, iif(fields!date_time_end.value = "Tuesday", reportitems! > textbox78 = fields!daydiff2.value, iif(fields!date_time_end.Value = > "Wednesday", reportitems!textbox78 = fields!daydiff3.value, iif(fields! > date_time_end.Value = "Thursday", reportitems!textbox78 = fields!daydiff3. > value, iif(fields!date_time_end.Value = "Friday", reportitems!textbox78 = > fields!daydiff3.value)))) > > Error: The Value expression for the textbox textbox78 refers to the field > daydiff3. Report item expressions can only refer to fields within the > current data set scope or, if inside an aggregate, the specified data set > scope. > > Which is wierd because I've declared this as DATEADD(day, -2, > v_SO_Activity_Company.Date_Time_End.value) in the dataset. > > Again, if you can offer any suggestions as to how to solve this problem that > would be so helpful!!! > > > > Mila wrote: > >Hi, I am new to Reporting services and the problem I'm having is displaying a > >day that is two business days before another date. I have two columns Due > >Date to QA and Due Date to PM. I want to subtract two business days from Due > >to PM date, excluding Saturday and Sunday, and display this new date in the > >Due to QA column. Also if this helps, I used the properties to format the > >output of the Due to PM date. For example " Friday, June 23, 2008". Please > >help me or show me how to do this?- Hide quoted text - > > - Show quoted text - I do it in the the SELECT statment like this: Designstart = CASE WHEN DATENAME(dw, to_dueship) = 'FRIDAY' THEN CONVERT(nvarchar(25),DATEADD(dd, -11, to_dueship),1) WHEN DATENAME(dw,DATEADD(dd, -13, to_dueship)) = 'SATURDAY' THEN CONVERT(nvarchar(25),DATEADD(dd, -15, to_dueship),1) WHEN DATENAME(dw,DATEADD(dd, -13, to_dueship)) = 'SUNDAY' THEN CONVERT(nvarchar(25),DATEADD(dd, -16, to_dueship),1) ELSE CONVERT(nvarchar(25),DATEADD(dd, -13, to_dueship), 1) END
From: Mila via SQLMonster.com on 1 Jul 2008 10:59
Hey trussman, Thanks for your help, it worked. I input your code directly into the query statement as suggested and found that by also changing one of the nvarchar(25) to datetime it gave me the result of viewing the date along with the actual day which is what i need to display ex. "Monday, June 1, 2008" Here is the code I modified: CASE WHEN DATENAME(dw, v_SO_Activity_Company.Date_Time_End) = 'FRIDAY' THEN CONVERT(datetime, DATEADD(dd, - 2, v_SO_Activity_Company.Date_Time_End), 1) WHEN DATENAME(dw, v_SO_Activity_Company.Date_Time_End) = 'THURSDAY' THEN CONVERT(nvarchar(25), DATEADD(dd, - 2, v_SO_Activity_Company.Date_Time_End), 1) WHEN DATENAME(dw, v_SO_Activity_Company.Date_Time_End) = 'WEDNESDAY' THEN CONVERT(nvarchar(25), DATEADD(dd, - 2, v_SO_Activity_Company.Date_Time_End), 1) WHEN DATENAME(dw, v_SO_Activity_Company.Date_Time_End) = 'TUESDAY' THEN CONVERT(nvarchar(25), DATEADD(dd, - 4, v_SO_Activity_Company.Date_Time_End), 1) WHEN DATENAME(dw, v_SO_Activity_Company.Date_Time_End) = 'MONDAY' THEN CONVERT(nvarchar(25), DATEADD(dd, - 3, v_SO_Activity_Company.Date_Time_End), 1) WHEN DATENAME(dw, DATEADD(dd, - 3, v_SO_Activity_Company.Date_Time_End)) = 'SATURDAY' THEN CONVERT(nvarchar(25), DATEADD(dd, - 1, v_SO_Activity_Company. Date_Time_End), 1) WHEN DATENAME(dw, DATEADD(dd, - 3, v_SO_Activity_Company.Date_Time_End)) = 'SUNDAY' THEN CONVERT(nvarchar(25), DATEADD(dd, - 2, v_SO_Activity_Company. Date_Time_End), 1) ELSE CONVERT(nvarchar(25), DATEADD(dd, 0, v_SO_Activity_Company.Date_Time_End), 1) END AS Expr2 trussman wrote: >> Here is an update to the previous post. This is my expression for the DUE to >> QA column >[quoted text clipped - 27 lines] >> >> - Show quoted text - > >I do it in the the SELECT statment like this: > >Designstart = CASE > WHEN DATENAME(dw, to_dueship) = 'FRIDAY' THEN >CONVERT(nvarchar(25),DATEADD(dd, -11, to_dueship),1) > WHEN DATENAME(dw,DATEADD(dd, -13, to_dueship)) = >'SATURDAY' THEN CONVERT(nvarchar(25),DATEADD(dd, -15, to_dueship),1) > WHEN DATENAME(dw,DATEADD(dd, -13, to_dueship)) = >'SUNDAY' THEN CONVERT(nvarchar(25),DATEADD(dd, -16, to_dueship),1) > ELSE CONVERT(nvarchar(25),DATEADD(dd, -13, to_dueship), >1) > END -- Message posted via http://www.sqlmonster.com |