October 29, 2014 at 6:06 pm
Hello,
Can someone please help me with this in SSRS 2008 Reporting Services.
Here are the conditions.
If Delivery_Date is prior to Current week, then output should be Fri of Current week.
Ex
Delivery_Date FriDay
10/01/2014 10/31/2014 ( Fri of Current Week )
For Current Week and Following weeks, the output should be the Fri of that corresponding week ( Note : if the Delivery_Date is past Fri of that week, then the output should be the next Fri.
Ex
Delivery_Date FriDay
10/28/2014 10/31/2014 ( Fri of Current Week )
11/1/2014 (Sat) 11/7/2014 ( Fri of Next Week)
I tried IIF Conditions, but I'm not able to achieve the result. Getting the first part right. But not able to get ( Fri of next week ).
Please help.
Puma.
October 30, 2014 at 1:58 am
Do you have a date table in your database or data warehouse?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 30, 2014 at 10:29 am
Data Table
October 30, 2014 at 1:37 pm
Puma123 (10/30/2014)
Data Table
I mean a calendar table that stores the dates and relevant information.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 30, 2014 at 1:50 pm
Delivery_Date FriDay
10/01/2014 10/31/2014 ( Fri of Current Week )
The delivery_dt is stored in the Database Table.
The FriDay ( Calculated ) Date is a new column added.
October 30, 2014 at 3:11 pm
October 30, 2014 at 3:24 pm
Thank You for your reply!
There is no calendar table.
database - Oracle 11.2.0.3.
Here is what I got so far.
If the Delivery_due_date is prior to the current week, then the output is Fri of current week. Else, the output should be Fri of the Deleivery_due_date. The problem is for Sat. It picks the Fri of that week(1 day before). If its a Sat, it should display Fri of next week.
Maybe I need to add another iif condition here?
=iif((Fields!Delivery_due_date.Value) < (DateAdd("d", -1 - DatePart("w",Today) , Today)),
(DateAdd("d", 6 - DatePart("w",Today) , Today)),
(DateAdd("d", 6 - DatePart("w",Fields!Delivery_due_date.Value), Fields!Delivery_due_date.Value )))
October 30, 2014 at 6:13 pm
In SQL, you would want something along these lines. This is a function that uses does the same sort of thing.
CREATE FUNCTION [dbo].[sfn_SetEffectiveDate]
( @InputDateDATETIME )
RETURNS DATETIME
AS
BEGIN
-- We need to check what day of week @InputDate is. If it's a Friday (6), Saturday (7), or Sunday (1) then it's OK.
-- If not, then we need to find the date for the previous Saturday, set the effective date to first thing Sunday morn.
DECLARE @EffectiveDateDATETIME
DECLARE @WeekdayINT = DATEPART(WEEKDAY, @InputDate)
SET @EffectiveDate = CONVERT(DATETIME,
CONVERT(DATE, (CASE WHEN @Weekday = 7 THEN DATEADD(DAY, 1, @InputDate)
WHEN @Weekday = 6 THEN DATEADD(DAY, 2, @InputDate)-- Friday
WHEN (@Weekday < 6 AND @Weekday > 1) THEN DATEADD(DAY, -(@Weekday - 1), @InputDate)
ELSE @InputDate
END)
))
RETURN @EffectiveDate
END
The date functions are slightly different on the Oracle side, but the logic is the same. If you choose to do it on the client (aka Report) side, use a SWITCH rather than a mess of nested IIF statements, the SWITCH is a lot easier to read AND it is more robust. With a SWITCH, for your last default action, i.e. the "ELSE" in a CASE, just use "1=1, DO WHATEVER".
October 30, 2014 at 6:20 pm
Thank You, I will try it and let you know!
October 31, 2014 at 4:29 am
This should give you the next Friday
set datefirst 6
print dateadd(wk,datepart(wk,getdate())-1,'2014-01-01')+2
October 31, 2014 at 5:35 pm
Most of the time I see systems having Sunday's as the first day of the week, as is the default for the Outlook calendar.
Have a look with this :
SELECT @@DATEFIRST
You will probably get 7. If so can use this in the SQL that pulls for your report if you have version 2008 and up::
DECLARE @dd datetime='2014-10-11'; /* THE DELIVERY DATE, play with this value */
SELECT
CASE
WHEN (@DD < DATEADD(D,7-DATEPART(DW,GETDATE()), CAST(GETDATE() AS DATE)))
THEN DATEADD(D,6-DATEPART(DW,GETDATE()), CAST(GETDATE() AS DATE))
ELSE
DATEADD(D,6-DATEPART(DW,@DD), @DD)
END AS SCHEDULED_DELIVERY_DATE
If you have version 2005 let me know for there are a couple ways to disregard the time portion from the output value
----------------------------------------------------
November 5, 2014 at 3:56 pm
Finally got the expected result with a CASE stmt.
CASE
WHEN i.delivery_due_dt < SYSDATE
THEN trunc(sysdate, 'DAY')+5 -- Fri of Current Week.
WHEN i.delivery_due_dt > TRUNC(SYSDATE , 'Day')+5 (Fri of current Week) AND i.delivery_due_dt > trunc(i.delivery_due_dt, 'DAY')+5 --Fri of Delivery_due_dt Week
THEN TRUNC(i.delivery_due_dt+7 , 'Day')+5 -- Next Friday of the Delivery_due_dt week.
ELSE trunc(i.delivery_due_dt, 'DAY')+5 -- Fri of Delivery_due_dt week
END) as Fri_Due_Dt,
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply