-1

Consider the following scenario. I have a Customer table, which includes RowStart and EndDate logic, thus writing a new row every time a field value is updated.

Relevant fields in this table are:

  • RowStartDate

  • RowEndDate

  • CustomerNumber

  • EmployeeFlag

For this, I'd like to write a query, which will return an employee's period of tenure (EmploymentStartDate, and EmploymentEndDate). I.e. The RowStartDate when EmployeeFlag first became 'Y', and then the first RowStartDate where EmployeeFlag changed to 'N' (Ordered of course, by the RowStartDate asc). There is an additional complexity in that the Flag value may change between Y and N multiple times for a single person, as they may become staff, resign and then be employed again at a later date.

Example table structure is:
| CustomerNo | StaffFlag | RowStartDate | RowEndDate |
| ---------- | --------- | ------------ | ---------- |
| 12         | N         | 2019-01-01   | 2019-01-14 |
| 12         | N         | 2019-01-14   | 2019-03-02 |
| 12         | Y         | 2019-03-02   | 2019-10-12 |
| 01         | Y         | 2020-03-13   | NULL       |
| 12         | N         | 2019-10-12   | 2020-01-01 |
| 12         | Y         | 2020-01-01   | NULL       |

Output could be something like

| CustomerNo | StaffStartDate | StaffEndDate |
| ---------- | -------------- | ------------ |
| 12         | 2019-03-02     | 2019-10-12   |
| 01         | 2020-03-13     | NULL         |
| 12         | 2021-01-01     | NULL         |

Any ideas on how I might be able to solve this would be really appreciated.

Maciej Los
  • 8,468
  • 1
  • 20
  • 35
JW1996
  • 21
  • 6
  • 1
    Sample data and desired outputs would help. – Serg Jul 05 '21 at 07:06
  • Tried adding a table to show output and table structure, formatting doesnt seem to be working – JW1996 Jul 05 '21 at 07:22
  • 1
    `SELECT FROM YourTable WHERE StaffFlag = 'Y'`? – Maciej Los Jul 05 '21 at 07:25
  • 1
    From your example, the rows with `N` flag seem to be completely irrelevant, so just `select * from table where staffflag = 'Y'` If that doesn't meed your requirements, you need a better example illustrating the actual problem. – derpirscher Jul 05 '21 at 08:12
  • Agreed your sample data is likely not representative of your actual goal, your desired results is simply the rows where the flag='Y'. Also, note you appear to be reinventing the wheel here as SQL Server has built-in support for this kind of data using *temporal tables*. – Stu Jul 05 '21 at 09:56

1 Answers1

1

Make sure you order the columns by ID and by dates:

select * 
from yourtable
order by CustomerNumber asc, 
EmployeeFlag desc,
RowStartDate asc, 
RowEndDate asc

This gives you a list of all changes over time per employee.

Subsequently, you want to map two rows into a single row with two columns (two dates mapped into overall start and end date). Others have done this using the lead() function. For details please have a look here: Merging every two rows of data in a column in SQL Server

AlexanderP
  • 126
  • 6