 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Services / Reporting Services / July 2008Subtract two business days from one column and display into another column.
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? 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!!! >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 [quoted text clipped - 3 lines] >output of the Due to PM date. For example " Friday, June 23, 2008". Please >help me or show me how to do this? > 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 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 >> Here is an update to the previous post. This is my expression for the DUE to >> QA column [quoted text clipped - 14 lines] >1) > END |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.