Compare 2 dates in the same column based on dimensions

[RogerG]

Hello all,

I have a dataset like this

ID	Project	Timepoint	Date
1	P1	T1	1/1/2022
1	P1	T2	2/1/2022
2	P2	T1	5/1/2023
1	p3	T1	1/1/2022
1	p3	T2	2/15/2022
1	p3	T3	3/1/2022

What I want to do is create an output table that would show

(I don't like tables anymore 🙂

ID   Project   Timepoint Match

Only thing that would show under this is the mismatches

or 

ID Project T1   T2   T3 Aligned

This would show blank for matches and the difference in duration for mismatches, per day, not minutes.  if all were aligned, it would show a True or False

Credit for the date matches

Extra credit for the table.  I am not sure if it would be better to do the cross table in the load script or work with it in a pivot table

Thoughts?

[hic]

It is unclear what you mean with "aligned", but the following structure could probably help you. Note that the dime difference is calculated in the last step, and this you can use for your definition of "aligned".

SourceData:
Load
ID,
Project,
Timepoint,
Date#(Date,'M/D/YYYY') as Date
Inline
[ID,Project,Timepoint,Date
1,P1,T1,1/1/2022
1,P1,T2,2/1/2022
2,P2,T1,5/1/2023
1,p3,T1,1/1/2022
1,p3,T2,2/15/2022
1,p3,T3,3/1/2022];

FlattenedTable1:
Load distinct ID, Project Resident SourceData;
Left Join (FlattenedTable1)
Load ID, Project, Date as T1 Resident SourceData Where Timepoint='T1';
Left Join (FlattenedTable1)
Load ID, Project, Date as T2 Resident SourceData Where Timepoint='T2';
Left Join (FlattenedTable1)
Load ID, Project, Date as T3 Resident SourceData Where Timepoint='T3';

Drop Table SourceData;

FlattenedTable2:
Load
T2-T1 as Diff_T1_T2,
T3-T1 as Diff_T1_T3,
*
Resident FlattenedTable1;

Drop Table FlattenedTable1;

[RogerG]

Sorry, I see the confusion here.  What we have is 2 project, on created from the other (ID) I want to see it T1 date in one project matches the T1 date in the second project for the same ID (aligned)