Group By and compare purchase dates for each customer

[lbunnell]

I would like a load script to determine the earliest opening of an account from a list of a number of customers with multiple accounts. Is there a way to compare the customers purchases to determine  whether a certain product was purchased prior to another? Ultimately, I'd like to be able to determine which customers purchased product A that have not yet purchased Product B, but also which customers have purchased both with A being the first  product purchased. This is what I am working with...

NoConcatenate

FINAL:

Load
CIN, //customer id number
[Account Number] ,

[Account Type], //products

date([Purchase Date],'MM/DD/YYYY') as [Open Date]

Resident Accounts;

left join

Load

CIN,

min([Open Date]) as [Earliest Bank Account Open Date]
Resident Accounts
Group By [CIN];

[Anil_Babu_Samineni]

Is that script getting any trouble?

[lbunnell]

No, the script is working. Just need a method to accomplish tasks described.

[Anil_Babu_Samineni]

Can you describe little in clear, Please?

[lbunnell]

I'm looking for something along the lines of...

if([Earliest Open Date]=[Open Date] and [Account Type]='A','Yes','No') as [Earliest Product A]

but this doesn't seem to work.

From there, I want to know: Of Customers who have purchased both types A and B which had earliest Account Type = A and which had earliest Account Type = B.

[Anil_Babu_Samineni]

May be create The As-Of Table

Or

Sample:

Load [Earliest Open Date],

         [Open Date],

         [Account Type]

From Table;

Sample_Cp:

NoConcatenate

LOAD [Account Type] as [Account Type Cp], [Earliest Open Date], [Open Date]

Resident Sample

Where [Account Type] = 'A';

Concatenate(Sample_Cp)

LOAD [Account Type] as [Account Type Cp], [Earliest Open Date], [Open Date]

Resident Sample

Where [Account Type] = 'B' AND Exists ([Account Type Cp], [Account Type]);

DROP Table Sample_Cp;

RENAME Field [Account Type Cp] to [Account Type];