Calculato

[Jesh19]

Hi All,

I am having the below requirement.

I am having few gates associated with Actual Dates against multiple programs.

For Example :

PRG_ID DESCRIPTION_GATE ACTUAL_DATE_FORMAT 13992 Gate 1 07-05-2015 13992 Gate 2 29-07-2015 13992 Gate 3 26-02-2016 13992 Gate 4 22-07-2016 13992 Gate 5 21-11-2016 13992 Gate 6 13-02-2017

Likewise, there are multiple programs each associated with a gate and actual date

My requirement is to find out Average of Gate 6 actual date minus Gate 1 actual date for programs in the quarter

Also, % of programs with duration 6 months or less in the quarter.

Could you please help me with the solutions for this.

I have tried in all the possible ways, but i am unable to find out a solution for this.

Thanks in advance,

Jeshwanth B

[vishsaggi]

Can you provide some sample data with expected output please? You can use set analysis for calculating average.

[Jesh19]

Hi Nagaraju,

Below is the sample data

   PRG_ID, Gate, Actual Date

12363	Gate 1	-
12363	Gate 2	-
12363	Gate 3	-
12363	Gate 4	07-11-2016
12363	Gate 5	27-02-2017
12363	Gate 6	27-03-2017
13050	Gate 1	-
13050	Gate 2	05-01-2015
13050	Gate 3	05-01-2015
13050	Gate 4	05-01-2015
13050	Gate 5	06-06-2016
13050	Gate 6	07-11-2016
13277	Gate 1	-
13277	Gate 2	-
13277	Gate 3	-
13277	Gate 4	12-12-2016
13277	Gate 5	16-01-2017
13277	Gate 6	06-02-2017
13309	Gate 1	-
13309	Gate 2	-
13309	Gate 3	13-07-2015
13309	Gate 4	02-05-2016
13309	Gate 5	10-10-2016
13309	Gate 6	03-04-2017
13356	Gate 1	12-01-2015
13356	Gate 2	09-02-2015
13356	Gate 3	08-09-2015
13356	Gate 4	14-03-2016
13356	Gate 5	25-07-2016
13356	Gate 6	19-12-2016

Expected Output is like

Reported Period	Q1	Q2	Q3		Q4
Avg Duration	14.18	14.26	13.13		14.2
Programs < 6 months duration	15%	13%	20%		15%

Please do the needful.

Thanks,

Jeshwanth B

[aarkay29]

Actual dates for Gate 1 in the given date set are null. So whats your logic when its null? should we consider Min Date for the respective ID as Gate 1- Actual Date

For suppose 7-11-2016 for ID 12363.

[vishsaggi]

What is the duration calculated in? Like Days or hours ? Some of your Gate 1 dates are null what you want them to be replaced with?

[Jesh19]

Duration needs to be in months.

Actual Date Null Values to be replaced with nothing.

we will consider only the values which are not null.

Regards,

Jeshwanth B

[Jesh19]

No, we should not consider any min date.

It should be calculated only when the value is not null.