Need Urgent Help...Calculation Between Rows

[#1]

Hi Team,

I have one table ,

Date             CustomerId     ErrorCode          Status

12/1/2016            A                       1              UnResolved

12/2/2016            A                       2              UnResolved

12/2/2016            A                       3              UnResolved

12/3/2016            B                      1               UnResolved

12/3/2016            B                       2              UnResolved

12/3/2016            B                       3              UnResolved

12/2/2016            A                       1              Resolved

12/3/2016            A                       2              UnResolved

12/3/2016            A                       3              UnResolved

12/3/2016            A                       2              Resolved

12/3/2016            A                       3              UnResolved

I have few queries

How can calculate number of Unresolved CustomerIds (where Status is Unresolved) ..in above example, Cutomer id A is unresolved because m error Code 3 for Customer id A is still unresolved,

How to create cycle time, for resolved Error, for example Cutomer Id A and error 1 is resolved on 12.2.2016 so my Cycle time is 12/2/2016-12/1/2016+1

How can I calculate Aging for Unresolved cases

Thanks for your help in advance

Regards

Siddharth

[MK_QSL]

Temp:

Load

  Id,

  Code,

  Id & Code as %Key,

  Date(Date#(FileDate,'MM/D/YYYY')) as FileDate,

  Status

Inline

[

  Id, Code, FileDate, Status

  1, A, 12/1/2016, UnResolved

  1, B, 12/2/2016, UnResolved

  2, A, 12/2/2016, UnResolved

  1, A, 12/2/2016, Resolved

  1, B, 12/3/2016, UnResolved

  1, B, 12/4/2016, UnResolved

  2, A, 12/4/2016, UnResolved

  2, A, 12/5/2016, Resolved

];

Left Join (Temp)

Load %Key, Max(FileDate) as MaxDate, Min(FileDate) as MinDate Resident Temp Group By %Key;

Final:

Load Id, Code, FileDate, If(FileDate=MaxDate,1,0) as MaxFlag, If(FileDate=MinDate,1,0) as MinFlag Resident Temp;

Drop Table Temp;

[#3]

Thanks Manish,

You are my hero ,Thanks a Ton !!!

It really helped me solve my problem !!!

Thankyou once again !!!

Regards

Siddharth