Search code examples
sqlsql-serverssasmdx

How to find if there is a match in a certain time interval between two different date fields SQL?

I have a column in my fact table that defines whether a Supplier is old or new based on the following case-statement:

CASE
             WHEN (SUBSTRING([Reg date], 1, 6) = SUBSTRING([Invoice date], 1, 6) 
             THEN ('New supplier')
             ELSE('Old supplier')
END as [Old/New supplier]

So for example, if a Supplier was registered 201910 and Invoice date was 201910 then the Supplier would be considered a 'New supplier' that month. Now I want to calculate the number of Old/New suppliers for each month by doing an distinct count on Supplier no, which is not a problem. The last step is where it gets tricky, now I want to count the number of New/Old suppliers over a 12-month period(if there has been a match on Invoice date and reg date in any of the lagging 12 months). So I create the following mdx expression:

aggregate(parallelperiod([D Time].[Year-Month-Day].[Year],1,[D Time].[Year-Month-Day].currentmember).lead(1) : [D Time].[Year-Month-Day].currentmember ,[Measures].[Supplier No Distinct Count])

The issue I am facing is that it will count Supplier no "1234" twice since it has been both new and old during that time period. What I wish is that, if it finds one match it would be considered a "New" Supplier for that 12- month period.

enter image description here

This is how the result ends up looking but I want it to be zero for "Old" since Reg date and Invoice date matched once during that 12-month period it should be considered new for the whole Rolling 12 month on 201910

enter image description here

Any help, possible approaches or ideas are highly appreciated.

Best regards, Rubrix

Solution

  • Aggregate first at the supplier level and then at the type level:

    select type, count(*)
from (select supplierid,
             (case when min(substring(regdate, 1, 6)) = min(substring(invoicedate, 1, 6))
                   then 'new' else 'old'
              end) as type
      from t
      group by supplierid
     ) s
group by type;

    Note: I assume your date columns are in some obscure string format for your code to work. Otherwise, you should be using appropriate date functions.