0

I have two tables in SQL, Contract_Period and Payment_Period, both of which have an Employee Key and start/end dates amongst other columns. The dates are linked to a calendar dimension but simplified just as date fields for the purpose of this. The employee key can occur multiple times on each table, or not at all.

Contract_Period:

Contract_Period_Key (PK), Employee_Key, Contract_Start_Date, Contract_End_Date

Payment_Period:

Payment_Period_Key (PK), Employee_Key, Payment_Period_Start_Date, Payment_Period_End_Date

What I am trying to achieve is to look at the Payment_Period table and establish whether, at any point between the start and end dates, the employee was also under contract. It doesn't have to be for the whole period, there just needs to be at least 1 day which exists in both date ranges.

The output I'd like to achieve is something like:DaysInPeiod

Would really appreciate any help!

TIA.

Billythewhizz
  • 1
  • 1
    Add sample table data and the expected result. (As formatted text, not images or links to images.) Also show us your current query attempt. – jarlh Dec 04 '17 at 13:54
  • Also: which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? Postgres? Oracle? "SQL" is just a query language, not the name of a specific database product. –  Dec 04 '17 at 14:41
  • People have been know to completely [overanalyze overlap logic](https://stackoverflow.com/a/5601502/15498) when in fact [far simpler logic](https://stackoverflow.com/a/325964/15498) suffices. – Damien_The_Unbeliever Dec 04 '17 at 14:41
  • Using SSMS 2014 with T-SQL. Will post expected result shortly. The multiple attempts I've made at creating the query have all gone horribly wrong, so I think the best thing to do is start from scratch! – Billythewhizz Dec 05 '17 at 15:54

0 Answers0