⚡️Power BI: Preparatory Steps for Creating a Power BI Report

When creating a Power BI report consider the following steps when starting the actual work. The first five steps can be saved to a "template" that can be reused as starting point for each report.

Step 0: Check Power BI Desktop's version

Check whether you have the latest version, otherwise you can download it from the Microsoft website.
Given that most of the documentation, books and other resources are in English, it might be a good idea to install the English version.

Step 1: Enable the recommended options

File >> Options and settings >> Options >> Global >> Data Load:
.>> Time intelligence >> Auto date/time for new files >> (uncheck)
.>> Regional settings >> Application language >> set to English (United States)
.>> Regional settings >> Model language >> set to English (United States)

You can consider upon case also the following options (e.g. when the relationships are more complex than the feature can handle):
File >> Options and settings >> Options >> Current >> Data load:
.>> Relationship >> Import relationships from data sources on first load >> (uncheck)
.>> Relationship >> Autodetect new relationships after data is loaded >> (uncheck)

Step 2: Enable the options needed by the report

For example, you can enable visual calculations:
File >> Options and settings >> Options >> Preview features >> Visual calculations >> (check)

Comment:
Given that not all preview features are stable enough, instead of activating several features at once, it might be a good idea to do it individually and test first whether they work as expected. 

Step 3: Add a table for managing the measures

Add a new table (e.g. "dummy" with one column "OK"):

Results = ROW("dummy", "OK")

Add a dummy measure that could be deleted later when there's at least one other measure:
Test = ""

Hide the "OK" column and with this the table is moved to the top. The measures can be further organized within folders for easier maintenance. 

Step 4: Add the Calendar if time analysis is needed

Add a new table (e.g. "Calendar" with a "Date" column):

Calendar = Calendar(Date(Year(Today()-3*365),1,1),Date(Year(Today()+1*365),12,31))

Add the columns:

Year = Year('Calendar'[Date])
YearQuarter = 'Calendar'[Year] & "-Q" & 'Calendar'[Quarter]
Quarter = Quarter('Calendar'[Date])
QuarterName = "Q" & Quarter('Calendar'[Date])
Month = Month('Calendar'[Date])
MonthName = FORMAT('Calendar'[Date], "mmm")

Even if errors appear (as the columns aren't listed in the order of their dependencies), create first all the columns. Format the Date in a standard format (e.g. dd-mmm-yy) including for Date/Time for which the Time is not needed.

To get the values in the visual sorted by the MonthName:
Table view >> (select MonthName) >> Column tools >> Sort by column >> (select Month)

To get the values in the visual sorted by the QuarterName:
Table view >> (select QuarterName) >> Column tools >> Sort by column >> (select Quarter)

With these changes the filter could look like this:

Step 5: Add the corporate/personal theme

Consider using a corporate/personal theme at this stage. Without this the volume of work that needs to be done later can increase considerably. 

There are also themes generators, e.g. see powerbitips.com, a tool that simplifies the process of creating complex theme files. The tool is free however, users can save their theme files via a subscription service.

Set canvas settings (e.g. 1080 x 1920 pixels).

Step 6: Get the data

Consider the appropriate connectors for getting the data into the report. 

Step 7: Set/Validate the relationships

Check whether the relationships between tables set by default are correct, respectively set the relationships accordingly.

Step 8: Optimize the data model

Look for ways to optimize the data model.

Step 9: Apply the formatting

Format numeric values to represent their precision accordingly.
Format the dates in a standard format (e.g. "dd-mmm-yy") including for Date/Time for which the Time is not needed.

The formatting needs to be considered for the fields, measures and metrics added later as well. 

Step 10: Define the filters

Identify the filters that will be used more likely in pages and use the Sync slicers to synchronize the filters between pages, when appropriate:
View >> Sync slicers >> (select Page name) >> (check Synch) >> (check Visible)

Step 11: Add the visuals

At least for report's validation, consider using a visual that holds the detail data as represented in the other visuals on the page. Besides the fact that it allows users to validate the report, it also provides transparence, which facilitates report's adoption. 

💎SQL Reloaded: Some Notes on Dates

   The Date is one of the most difficult data types to handle, given the fact that it might take different formats (e.g. DD/MM/YYYY, MM/DD/YYYY, DD/MM/YY, DD-MON-YYYY, etc.), and even if the various formats make a limited list, the fact that each database, machine or application could work with a different date format or set of date formats, this makes developers’ life a nightmare. The representational complexity resides also in the fact that each country adopts one or more standard date formats, a mixture between day, month, year and the various delimiters used (e.g. “.”, “/”, “-”). For example the SQL Server Books Online lists several country-specific date formats that could be used with Convert function, while in Date and Time Formats tutorial could be found a list with the formats and literals used in Oracle. Even .

     The various frameworks have attempted to introduce modalities of handling dates (e.g. culture info), though none of them is bulletproof given the interaction/integration between the various layers. Sometimes I prefer to store the date as a (standard) text string and transmit it thus between layers, following to transform (convert) it as needed by the various data consumers (UI, components, databases, etc.). What format to choose is depends entirely on habitude, special requests/constraints or on the flexibility of converting a given format to other formats. A flexible approach is the ISO 8601 format that combines date and time representations (<date>T<time>) as YYYY-MM-DDThh:mi:ss.mmm (e.g. 2010-12-22T23:10:29.300). It’s important to note that in SQL Server this format is not affected by the SET DATEFORMAT or SET LANGUAGE settings (see ISO 8601 Format), fact that makes the ISO format a good candidate for representing dates.

   The ISO format allows also to sort lexicographically a date, fact that makes it valuable when combining strings with dates, for example when naming files, though then some of the delimiters need to be removed. I prefer to remove all the delimiters thus storing a file with the timestamp in the format <meaningful_name><timestamp>.<extension> (e.g. ‘Report 20101222T231029.rpt’).

    When the time is not tracked, the date could be stored also as an integer in YYYYMMDD format, thus for example 2010-12-22 could be stored as 20101222. I’ve seen old databases making use of this technique, I found it awkward at the beginning though I discovered its performance benefits when doing comparisons, the difference of days between two dates reducing at a simple subtraction. Actually storing the date as integer is just a simplification of the way on how dates are stored, for example in SQL Server the date is stored as two integers,  in which the first integer stores the date itself and the second the time in clock ticks after midnight (see here). The danger comes when attempting to work with the integer format in order to express the difference between two dates in higher time units than the day (e.g. weeks, months, years), in such cases being recommended to work with built-in date functions. Given the fact that dates on SQL Server could be expressed also as float values (see Joe Celko’s article on Temporal Data Types in SQL Server), the same danger occurs when using the numeric values in calculations in the detriment of date functions.

    In ERP systems or other “activity-based” systems is useful to store a calendar together with other calendar specific information – whether it’s a working date, the period in which the date falls, the fiscal week, month, years, etc. If in OLTP systems such structures are used to store the time dimension, thus each time record being referenced directly, in OLAP systems there could be a mix between between direct referencing of calendar records or of storing directly the date, following in developers’ attributions to retrieve additional information about the date when needed. As it doesn’t make sense to argue in here the benefits of the two approaches, I limit myself to note that the integer value of the date could be used as a foreign key when the time component is not needed to be stored. By storing a date as integer or to a shorter date format  (e.g. YYYYMM, YYYYWW, YYYY) could be impacted applications’ flexibility. Now it depends, there are cases and cases, different problems requiring different solutions. Just think twice when choosing on how to store a date, think not only about the current requirements but also how the requirements could change given a change in the process/business dynamics!

💎SQL Reloaded: More Date-related Functionality II

Arithmetic Progression Sets

In yesterday’s post on the same topic I was showing how to create a sequence of dates on the fly with the help of common table expressions and multi-statement table-valued functions. I was mentioning that similar functionality could be obtained using a numeric sequence (interval), following to create the dates when calling the function. Here’s the function that provides the respective functionality, the difference from fGetDatesInInterval, excepting the different data types, is that it has a third parameter for the increment value (default 1), being thus possible to generate a list of odd/even numbers or any other type of arithmetic progressions. 

-- SQL Server 2005+: Numeric Interval
CREATE FUNCTION dbo.fGetInterval(
@Start int = 0
, @End int = 0
, @Step int=1)
RETURNS @Interval TABLE(
Value int PRIMARY KEY)
AS
BEGIN
DECLARE @Sign smallint
SET @Sign = IsNull(NullIf(Sign(@End-@Start), 0), 1);
WITH CTE(Sequence)
AS(
     SELECT COALESCE(@Start, @End, 0) Sequence
     UNION ALL
     SELECT CTE.Sequence + @Step * @Sign Sequence
     FROM CTE
     WHERE (@End-CTE.Sequence-@Step)*@Sign>=0
)
INSERT @Interval
SELECT Sequence
FROM CTE
OPTION(MAXRECURSION 0)
RETURN
END

-- numeric interval
SELECT *
FROM dbo.fGetInterval(0, 1000, 1)
-- numeric interval (3rd parameter NULL)
SELECT *
FROM dbo.fGetInterval(0, 999, NULL)
-- even numbers
SELECT *
FROM dbo.fGetInterval(0, 1000, 2)
-- odd numbers
SELECT *
FROM dbo.fGetInterval(1, 1000, 2)
-- single value
SELECT *
FROM dbo.fGetInterval(1, 1, 2)

 
     The numeric values returned by the function could be used to form a date sequence by using the DateAdd function and a Cast to the date data type. The downside with this approach is that if the dates are used in more expressions within the same query, the expression used to calculate the dates increases the visual complexity of the query, a function like fGetDatesInInterval could be easier to use instead.

-- creating a date sequence
SELECT Value
, Cast(DATEADD(d, Value, GETDATE()) as Date) DateSequence
FROM dbo.fGetInterval(0, 1000, 1)

     The fGetInterval has a second particularity, the Value attribute from the table used to retrieve the data was declared as PRIMARY KEY, fact that allows to improve queries’ performance when searches are performed on table’s values. The DateSequence from the table returned by fGetDatesInInterval could be defined as PRIMARY KEY too.

    The common table expression and its use resumes in writing a single statement, and considering that the logic from fGetInterval function could be written as a single statement, in theory we could write an inline table-valued function instead. This technique works as long the OPTION clause is not used within the inline function, when this is attempted is returned the following error: Incorrect syntax near the keyword 'OPTION'. As it seems the OPTION clause can’t be used with single-statement objects like views or inline table-valued functions. Therefore common table expressions can be used in single-statement objects, the functionality being thus restrained to the 100 default limit number of recursion.

Missing Values in a Data Set

    While searching for something on the Web, today I found Aaron Akin’s post on Recursive Common Table Expressions, he’s using them in order to determine the missing numbers/dates in a sequence. This can be easily achieved using for example the fGetDatesInInterval  or fGetInterval functions with a left join to the tested data set. Using the AdventureWorks database and fGetDatesInInterval function, supposing that I would like to see the working dates within a time interval with no POs, the query could be written as follows:

-- working days with no PO
SELECT DI.DateSequence
FROM dbo.fGetDatesInInterval('2003-01-01', '2003-12-31') DI
LEFT JOIN ( -- list of PO dates
    SELECT OrderDate
    FROM Purchasing.PurchaseOrderHeader 
    GROUP BY OrderDate
) POH
ON DATEDIFF(d, DI.DateSequence, POH.OrderDate)=0
WHERE DATEPART(dw, DI.DateSequence) BETWEEN 2 AND 6 --working days
AND POH.OrderDate IS NULL

     A similar approach could be used also in case of numeric data sets, here’s an example based on fGetInterval  and SplitList table-valued functions:

SELECT DI.Value
FROM dbo.fGetInterval(1, 10, 1) DI
   LEFT JOIN dbo.SplitList('1,3,6,7,10', ',') SL
      ON DI.Value = SL.Value
WHERE SL.Value IS NULL

💎SQL Reloaded: More Date-related Functionality I

Date Intervals Generation 

 
     In reports is sometimes requested to aggregate transactional data at day level, showing all existing data for a given time interval. This can be easily solved by limiting the time interval in the WHERE clause, though the problem is that it might happen that there are no data in the system for one or more dates and the users want to see that in the report. The easiest approach is to do a left join between the table in which are stored the calendar days and the actual data aggregation, though such a table it’s not always available. What shall we do then? So we need a table-valued function that takes two parameters – the start and end date – and creates all the dates in between. Actually it would be enough to have a sequence of numbers between 0 and the number of days between the two dates, and use the DateAdd function on them. Both problems could be easily solved with the help of common table expressions introduced with SQL Server 2005, while for previous versions could be used a simple loop.
    Here’s the multi-statement table valued function supposed to create the sequence of dates:
-- SQL Server 2005+: Date Sequence in Interval
CREATE FUNCTION dbo.fGetDatesInInterval(
@StartDate
date , @EndDate date)
RETURNS @Dates TABLE (
DateSequence date)
AS
BEGIN
    DECLARE @Sign smallint
    SET @Sign = Sign(DateDiff(d, @StartDate, @EndDate));  
  
   WITH CTE(DateSequence)
   AS (
   SELECT @StartDate DateSequence
    UNION ALL
    SELECT Cast(DATEADD(d, @Sign, CTE.DateSequence) as Date) DateSequence
   FROM CTE
   WHERE DATEDIFF(d, CTE.DateSequence, @EndDate)*@Sign>0 )
 
    INSERT @Dates
    SELECT DateSequence
    FROM CTE
    OPTION(MAXRECURSION 0)
RETURN
END
Note:
     The OPTION (MAXRECURSION 0) has been introduced in order to avoid the 100 default limit for the number of recursions allowed for a query, a value of 0 equating with an unlimited number of recursions. The function might run also without this option, though if the number of days between the two input dates is greater or equal than 100 then you’ll get the following error:
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
 
   For example in order to get the dates within a year the function call would be:
-- days of a given year (positive increment)
SELECT DateSequence
, DATEPART(wk, DateSequence) WeekOfYear
, DATEPART(mm, DateSequence) MonthOfYear
, DATENAME(w, DateSequence) DayOfWeekName
, DATEPART(dw, DateSequence) DayOfWeek
FROM dbo.fGetDatesInInterval('2010-01-01', '2010-12-31')
     The DatePart and DateName functions could be used to get the various date parts of a date. As can be seen in this case the EndDate is greater than the StartDate, what happens if the users inverse the two dates? The above function already handles this situation with the help of @Sign variable, the following query in which the two parameters are inversed returning the same date sequence as above:
-- days of a given year (negative increment)
SELECT DateSequence
FROM dbo.fGetDatesInInterval('2010-12-31', '2010-01-01')
   The function handles also NULL values, returning the not-NULL value, and in case both parameters are NULL then it returns the current date:
-- second parameter is NULL
SELECT DateSequence
FROM dbo.fGetDatesInInterval('2010-12-31', NULL)
-- first parameter is NULL
SELECT DateSequence
FROM dbo.fGetDatesInInterval(NULL, '2010-12-31')
-- both parameters are NULL
SELECT DateSequence
FROM dbo.fGetDatesInInterval(NULL, NULL)

   The above function can’t be used in earlier versions than SQL Server 2005, though the same output could be obtained using a loop – here’s the modified function:
-- SQL Server 2000: Date Sequence in Interval
CREATE FUNCTION dbo.fGetDatesInIntervalOld(
@StartDate
date , @EndDate date)
RETURNS @Dates TABLE
(DateSequence date)
AS
BEGIN
    DECLARE @NumberDays int
    DECLARE @Counter int
    DECLARE @Sign smallint
    SET @NumberDays = IsNull(DateDiff(d, IsNull(@StartDate, @EndDate), @EndDate), 0)
    SET @Counter = 0
    SET @Sign = IsNull(NUllIf(Sign(@NumberDays), 0), 1)
    WHILE(@Counter* @Sign<=@NumberDays * @Sign)
    BEGIN
   INSERT @Dates
   SELECT DateAdd(d, @Counter, COALESCE(@StartDate, @EndDate, GetDate()))

   SET @Counter = @Counter + @Sign
END
RETURN
END
   As can be seen from the below test cases the function returns the same output as the common table expression-based implementation:
-- days of a given year (positive increment)
SELECT DateSequence
FROM dbo.fGetDatesInIntervalOld('2010-01-01', '2010-12-31')
-- days of a given year (negative increment)
SELECT DateSequence
FROM dbo.fGetDatesInIntervalOld('2010-12-31', '2010-01-01')
-- second parameter is NULL
SELECT DateSequence
FROM dbo.fGetDatesInIntervalOld('2010-12-31', NULL)
-- first parameter is NULL
SELECT DateSequence
FROM dbo.fGetDatesInIntervalOld(NULL, '2010-12-31')
-- both parameters are NULL
SELECT DateSequence
FROM dbo.fGetDatesInIntervalOld(NULL, NULL)

Number of Working Days
   Given the fact that for each date could be obtained the day of the week, any of the two above functions could be used also to determine the number of (not) working days in a certain time interval:
-- number of (not) working days
SELECT SUM(CASE WHEN DATEPART(dw, DateSequence) BETWEEN 2 AND 6 THEN 1 ELSE 0 END) WorkingDays
, SUM(CASE WHEN DATEPART(dw, DateSequence) IN (1,7) THEN 1 ELSE 0 END) WorkingDays
, COUNT(1) NumberDays
FROM dbo.fGetDatesInIntervalOld('2010-01-01', '2010-12-31')
 
   Even if handy maybe this isn’t the best approach in order to get the number of (not) working days. Some time ago I developed a similar function in VBA, the respective function subtracting the number of weekends falling in the extended time interval. Because I’ve calculated the number of weekends falling in the extended time interval (including first day of the week in which the Start Date falls, respectively the last day of the week in which the End Date falls), I have to add them again – this being the meaning of the two CASE uses.
-- SQL Server 2000+: Date Sequence in Interval
CREATE FUNCTION dbo.fGetWorkingDays(
@StartDate
date , @EndDate date)
RETURNS int
AS
BEGIN
DECLARE @NumberDays int
SET @NumberDays = IsNull(DateDiff(d, IsNull(@StartDate, @EndDate), @EndDate), 0)
RETURN @NumberDays -- difference days
- 2 * (@NumberDays + DatePart(dw, @StartDate) + (7 - DatePart(dw, @EndDate))) / 7 -- number weekends in extended weeks
+ (1 - CASE DatePart(dw, @StartDate) WHEN 1 THEN 1 ELSE 0 END) -- weekend to be added (first week)
+ (1 - CASE DatePart(dw, @EndDate) WHEN 7 THEN 1 ELSE 0 END) -- weekend to be added (last week)
+ 1 -- first day
END

   Should be enough to test the week for the various combinations of weekend/working days for 2-3 consecutive weeks:
SELECT dbo.fGetWorkingDays('2010-01-30', '2010-02-13') -- Sa-So (10 days)
SELECT dbo.fGetWorkingDays('2010-01-31', '2010-02-13') -- So-So (10 days)
SELECT dbo.fGetWorkingDays('2010-02-01', '2010-02-13') -- Mo-So (10 days)
SELECT dbo.fGetWorkingDays('2010-02-01', '2010-02-12') -- Mo-Sa (10 days)
SELECT dbo.fGetWorkingDays('2010-01-30', '2010-02-12') -- So-Sa (10 days)
SELECT dbo.fGetWorkingDays('2010-01-31', '2010-02-12') -- So-Sa (10 days)
SELECT dbo.fGetWorkingDays('2010-02-06', '2010-02-12') -- Sa-Sa (5 days)
SELECT dbo.fGetWorkingDays('2010-02-06', '2010-02-13') -- Sa-Sa (5 days)
SELECT dbo.fGetWorkingDays('2010-02-06', '2010-02-11') -- Sa-Th (4 days)
SELECT dbo.fGetWorkingDays('2010-02-07', '2010-02-11') -- So-Th (4 days)
SELECT dbo.fGetWorkingDays('2010-02-06', '2010-02-07') -- Sa-So (0 days)
SELECT dbo.fGetWorkingDays('2010-02-06', '2010-02-08') -- Sa-Mo (1 day)
SELECT dbo.fGetWorkingDays('2010-02-05', '2010-02-08') -- Fr-Mo (2 days)
SELECT dbo.fGetWorkingDays('2010-02-05', '2010-02-05') -- same day (1 day)
 
   In case of doubts the dbo.fGetDatesInIntervalOld could be used to check whether the results are correct. For easier checking of the output of the above test queries here’s the calendar covering the 2-3 weeks considered.

💎SQL Reloaded: Oracle vs. SQL Server (Handling Missing Dates)

    What do we do with missing values? How do they influence the data analysis? Two questions each developer should ask users when creating/modifying a report. In general for numbers things are pretty simple, just replace the NULL values with a 0, this impacting the report minimally. With Dates is a little more complicated because maybe is needed to remove records having a certain date null, replace it with another date representing a certain event or maybe with the current date.

    As NULLIF, COALESCE and CASE functions are available in both systems, they could be used in particular to handle missing dates or other type of data types, especially when intended to make code portable between the two platforms. In addition SQL Server provides the IsNull function, the equivalent in Oracle being the NVL, extended by NVL2. Oracle provides also an equivalent for simple CASE function, namely the DECODE function. Here are some examples based on Product table from AdventureWorks database: 

  

-- SQL Server NULL handling 
SELECT ProductID, SellStartDate, SellEndDate, DiscontinuedDate, GetDate() CurrentDate 
, IsNull(SellEndDate, GetDate()) Example1 
, COALESCE(SellEndDate, GetDate()) Example3 
, COALESCE(DiscontinuedDate, SellEndDate, GetDate()) Example4 
, CASE 
     WHEN SellEndDate IS NULL THEN GetDate() 
     ELSE SellEndDate 
END Example6 
, NullIf(SellEndDate, SellStartDate) Example7 
FROM Production.Product 

-- Oracle NULL handling 
SELECT PRODUCTID, SELLSTARTDATE, SELLENDDATE, DISCONTINUEDDATE, SYSDATE 
, NVL(SELLENDDATE, SYSDATE) EXAMPLE1 
, NVL2(DISCONTINUEDDATE, SELLENDDATE, SYSDATE) EXAMPLE2 
, COALESCE(SELLENDDATE, SYSDATE) EXAMPLE3 
, COALESCE(DISCONTINUEDDATE, SELLENDDATE, SYSDATE) EXAMPLE4 
, DECODE(SELLENDDATE, NULL, SYSDATE, SELLENDDATE) EXAMPLE5 
, CASE 
    WHEN SELLENDDATE IS NULL THEN SYSDATE 
    ELSE SELLENDDATE 
END EXAMPLE6 
, NULLIF(SELLENDDATE, SELLSTARTDATE) EXAMPLE7 
FROM PRODUCT 

      The simple CASE function can’t be use to check for NULL values unless the NULL value is implied within the ELSE branch, and this because the IS NULL clause needs to be used for checking whether a value is missing. An exception from this rule seems to be the DECODE function, as can be seen from 5th example from the Oracle-based query.

     Aggregated functions ignore NULL values in both systems, therefore any of the above techniques could be used to handle the missing values if other behavior is expected. The selection of the date used in case the target date is missing depends on the requirements, usually is used the current date when the reports focus on today’s status, while for the calculation of lead/cycle times is preferred to use the closest date to the target date. 

-- SQL Server: handling nulls in aggregations 
SELECT AVG(DateDiff(d, SELLSTARTDATE, IsNull(SELLENDDATE, GETDATE()))) -- handling null values 
, AVG(DateDiff(d,SELLSTARTDATE, SELLENDDATE)) -- ignoring null values 
FROM Production.Product 

-- Oracle: handling nulls in aggregations 
SELECT AVG(TRUNC(NVL(SELLENDDATE, SYSDATE)) - TRUNC(SELLSTARTDATE)) -- handling null values 
, AVG(TRUNC(SELLENDDATE) - TRUNC(SELLSTARTDATE)) -- ignoring null values 
FROM PRODUCT 

    Another scenario when missing date values need to be handled is in joins, often for such cases a missing value denoting that the record is still active, like in the below example: 

-- SQL Server: getting the Start Date for current List Price 
SELECT ITM.ProductID 
, ITM.ProductNumber 
, ITM.ListPrice 
, PPH.StartDate 
FROM Production.Product ITM 
JOIN Production.ProductListPriceHistory PPH 
ON ITM.ProductID = PPH.ProductID 
AND PPH.EndDate IS NULL 

   
   The above query could be rewritten also in the following form that can be useful to get the record in use during a certain event:

-- SQL Server: getting the List Price when the product was last time modified 
SELECT ITM.ProductID 
, ITM.ProductNumber 
, PPH.ListPrice 
, PPH.StartDate 
, PPH.EndDate 
, ITM.ModifiedDate 
FROM Production.Product ITM 
JOIN Production.ProductListPriceHistory PPH 
ON ITM.ProductID = PPH.ProductID 
AND DateDiff(d, ITM.ModifiedDate, IsNull(PPH.EndDate, GETDATE()))>=0 
AND DateDiff(d, PPH.StartDate, ITM.ModifiedDate)>=0 

    In Oracle special attention must be given to LEAST and GREATEST functions that consider the smallest/greatest values from a list of attributes because if one of the vales is NULL then function’s value is NULL too, therefore if one of the attributes could take NULL values then it should be replaced with the values from a NOT NULL attribute used in the functions, like in 3rd and 4th example from the below example, this change having no impact on functions’ output.

-- Oracle Greatest/Least example 
SELECT PRODUCTID, SELLSTARTDATE, SELLENDDATE, SYSDATE 
, GREATEST(SELLSTARTDATE, SELLENDDATE, SYSDATE) EXAMPLE1 
, LEAST(SELLSTARTDATE, SELLENDDATE, SYSDATE) EXAMPLE2 
, GREATEST(SELLSTARTDATE, NVL(SELLENDDATE, SYSDATE), SYSDATE) EXAMPLE3 
, LEAST(SELLSTARTDATE, NVL(SELLENDDATE, SYSDATE), SYSDATE) EXAMPLE4 
FROM PRODUCT