Today I will answer a very interesting question from Anthony.
Anthony need to know how many days pass from one sale to the other from a specific type of sale, lets say the sale type is [Code] field and sale date is [DATE SOLD]
The table looks like that:
First I duplicated this table using the duplicate item ![]()
when hovering a table, now we have two identical tables:[Daily Sales By Code] and [Daily Sales By Code2]
Next step is to Add an SQL statement that will combine this tables together:
SELECT a.[CODE],
a.[DATE SOLD],
b.[DATE SOLD],
Daydiff(a.[DATE SOLD], b.[DATE SOLD]) diff
FROM [Daily Sales By Code] a,
[Daily Sales By Code2] b
WHERE a.[CODE] = b.[CODE]
AND a.[DATE SOLD] <> b.[DATE SOLD]
GROUP BY a.[CODE],
a.[DATE SOLD],
b.[DATE SOLD]
This will give us all permutations of the dates while its not the same date (a.[DATE SOLD] <> b.[DATE SOLD]) and both dates are from the same code (a.[CODE] = b.[CODE]) I also added Daydiff(a.[DATE SOLD], b.[DATE SOLD]) to get the number of days between the date pairs.
This SQL statement will return the positive and negative date diff between the pairs so to get all dates with all positive Day Diffs we will wrap it by another sql statment:
SELECT *
FROM (SELECT a.[CODE],
a.[DATE SOLD],
b.[DATE SOLD],
Daydiff(a.[DATE SOLD], b.[DATE SOLD]) diff
FROM [Daily Sales By Code] a,
[Daily Sales By Code2] b
WHERE a.[CODE] = b.[CODE]
AND a.[DATE SOLD] <> b.[DATE SOLD]
GROUP BY a.[CODE],
a.[DATE SOLD],
b.[DATE SOLD]) t1
WHERE t1.diff > -1
Where t1.diff > -1 will give us all positive date pairs.
Build and Sync the eCube, open Prism and calculate the Avg days between each sale from the same type:
Create a pivot with [DATE SOLD]on the rows and Min([Diff]) custom measure on the columns, add a combo with the [Code] dimension on it and you are done.
Note: To get the avg days between sales you can right click on the [Diff] header on the pivot select Sub Totals and Avg.