0

I am having issues with a sql query and i'm not sure how i can get the results i need.

Depending on the costctr and grade values, this determines the emp function. e.g emp 510 was L Production but is now L staff.

I require two new columns - Valid From and Valid To (highlighted in yellow) which will give me the dates for when the change happened. I have attached an example of what i need to explain things a bit better.

enter image description here

EDIT - Text Format Data.

The Valid From column should show the latest date from the costctr_from or grade_from columns. The Valid To column should show the latest date from the costctr_to or grade_to columns.

EMP_NO  FUNCTION    COSTCTR_VALUE   COSTCTR_FROM    COSTCTR_TO  GRADE_VALUE GRADE_FROM  GRADE_TO        VALID_FROM  VALID_TO
321 Commercial  270 03/09/2019  31/12/9999  L5  03/09/2019  31/03/2020      03/09/2019  31/03/2020
321 Commercial  270 03/09/2019  31/12/9999  L4  01/04/2020  31/12/9999      01/04/2020  31/12/9999
698 Membership  602 03/09/2019  31/12/9999  L4  01/01/2021  31/12/9999      01/01/2021  31/12/9999
698 Membership  602 03/09/2019  31/12/9999  L5  03/09/2019  31/12/2020      03/09/2019  31/03/2020
510 L Production    101 03/09/2019  31/12/9999  4   03/09/2019  31/03/2020      03/09/2019  31/03/2020
510 L Staff 101 03/09/2019  31/12/9999  L6  01/04/2020  31/12/9999      01/04/2020  31/12/9999
530 H Staff 101 03/09/2019  31/12/9999  L6  01/06/2020  31/12/9999      01/06/2020  31/12/9999
530 H Production    101 03/09/2019  31/12/9999  4   01/11/2019  31/05/2020      01/11/2019  31/05/2020
530 H Production    101 03/09/2019  31/12/9999  3   03/09/2019  31/10/2019      03/09/2019  31/10/2019

TIA

Yasir
  • 79
  • 1
  • 5
  • 2
    Your data is unreadable. You can probably simplify it, given that your question does not mention 12+ columns. Sample data as text tables or DDL is much preferred. – Gordon Linoff Apr 12 '21 at 11:30
  • 1
    Make it easy to assist you: [mcve]. – jarlh Apr 12 '21 at 11:50
  • 1
    What is the algorithm to identify those dates? Please, clarify your question and add minimal reproducible example. – astentx Apr 12 '21 at 11:58
  • updated my initial question and added text format data. The Valid From column should show the latest date from the costctr_from or grade_from columns. The Valid To column should show the latest date from the costctr_to or grade_to columns. – Yasir Apr 12 '21 at 12:23
  • Maybe check https://stackoverflow.com/editing-help#tables or https://www.convertcsv.com/csv-to-flat-file.htm – Wernfried Domscheit Apr 12 '21 at 12:32

0 Answers0