How to get date between to values

[#1]

Hi guys!

I have a person that has colors and signed dates. Is there somehow i could isolate the dates between the colors. Like i need the signed dates for the colors red so the the dates should be so if the color is red it should get the the date after .

Example how it should look based on the data in the end

2014-05-08 to 2014-11-05

2015-05-12 to 2016-06-22

2016-06-22 to 2016-07-15

2016-08-01 to 2016-08-09

And if thats the last signed date it should stop.

Any ideas om stuck

Person	Signed	PGValue	Color
Alfred	2014-05-08	8	Red
Alfred	2014-11-05	5	Yellow
Alfred	2015-05-12	5	Red
Alfred	2016-06-22	5	Red
Alfred	2016-07-15	8	Grön
Alfred	2016-07-20	5	Gul
Alfred	2016-08-01	5	Red
Alfred	2016-08-09	5	Red

[avinashelite]

Did not get your requirement could you please elaborate bit more on that ..with sample data and the desired output you required 

[#3]

Hi

Output these dates! Based on the data below, so the date from Red color to the next color

2014-05-08 to 2014-11-05

2015-05-12 to 2016-06-22

2016-06-22 to 2016-07-15

2016-08-01 to 2016-08-09

Data

Person	Signed	PGValue	Color
Alfred	2014-05-08	8	Red
Alfred	2014-11-05	5	Yellow
Alfred	2015-05-12	5	Red
Alfred	2016-06-22	5	Red
Alfred	2016-07-15	8	Grön
Alfred	2016-07-20	5	Gul
Alfred	2016-08-01	5	Red
Alfred	2016-08-09	5	Red

[#4]

The signed date i mean from every red color to the signed date of next color

[Gysbert_Wassenaar]

Perhaps like this:

Data:

LOAD

  Person,

  Date(Date#(Signed,'YYYY-MM-DD'),'YYYY-MM-DD') as Signed,

  PGValue,

  Color

INLINE [  

    Person, Signed, PGValue, Color

    Alfred, 2014-05-08, 8, Red

    Alfred, 2014-11-05, 5, Yellow

    Alfred, 2015-05-12, 5, Red

    Alfred, 2016-06-22, 5, Red

    Alfred, 2016-07-15, 8, Grön

    Alfred, 2016-07-20, 5, Gul

    Alfred, 2016-08-01, 5, Red

    Alfred, 2016-08-09, 5, Red

];

Result:

LOAD * WHERE Start;

LOAD

  Person, Signed as End,

  If(Person=Previous(Person) AND Previous(Color)='Red',Previous(Signed)) as Start

RESIDENT

  Data

ORDER BY

  Person, Signed

  ;

[#6]

Sorry did not get the desired outcome.

[Gysbert_Wassenaar · Accepted solution]

Fine. Try this then:

Data:

LOAD

  Person,

  Date(Date#(Signed,'YYYY-MM-DD'),'YYYY-MM-DD') as Signed,

  PGValue,

  Color

INLINE [ 

    Person, Signed, PGValue, Color

    Alfred, 2014-05-08, 8, Red

    Alfred, 2014-11-05, 5, Yellow

    Alfred, 2015-05-12, 5, Red

    Alfred, 2016-06-22, 5, Red

    Alfred, 2016-07-15, 8, Grön

    Alfred, 2016-07-20, 5, Gul

    Alfred, 2016-08-01, 5, Red

    Alfred, 2016-08-09, 5, Red

];

Result:

LOAD *, Start & ' to ' & End as Interval  WHERE Start;

LOAD

  Person, Signed as End,

  If(Person=Previous(Person) AND Previous(Color)='Red',Previous(Signed)) as Start

RESIDENT

  Data

ORDER BY

  Person, Signed

  ;

[#8]

Ok how would the the code look if the data was loaded from SQL?

[Gysbert_Wassenaar]

Depends. You could replace the INLINE load with an sql statement. If you want to do all the calculations in SQL then ask your local friendly database administrator/developer

[#10]

I looked for a qick fix thanks, il manage to solve it. The data i already there with all the calculations. Thank for your help