sg91 sg91 - 6 days ago 5
SQL Question

SQL test on year of dates in a table

I have a Contract table with the fields ContractTypeID, ContractID and ContractEndDate. One ContractTypeID can have multipe ContractIDs each with their own ContractEndDate.

For all ContractTypeIDs that have a Contract with a ContractEndDate in 2016, I am interested to see a SQL query that returns a results table with the ContractTypeID and Result column. The result column should read accordingly:


  1. If the ContractTypeID has contracts that end in 2016, 2017 and
    2018 each return, 'Has a contract that ends next year and year
    after'

  2. If the ContractTypeID has contracts that end in 2016 and
    2017 only with no contract ending in 2018, read, 'Has a contract
    that ends next year'

  3. if the ContractTypeID only has contracts
    that end in 2016 and no contracts that end in 2017 0r 2018 then
    read, 'Has a contract that ends this year'



I have attached screenshots for the Sample Data and the result. You may notice in the Result, ContractTypeID = 2 doesn't appear as it has no contract that ends in 2016 and hence is not valid for this test.

Many thanks in advance

enter image description here

Answer

Break the problem down into parts to solve it: (I recommend you run each query I list here so you can see how this works.)

Make flags for the years of interest

SELECT ContractID,  
  MAX(CASE WHEN YEAR(ContractEndDate) = 2016 THEN 1 ELSE 0 END) AS YEAR16,
  MAX(CASE WHEN YEAR(ContractEndDate) = 2017 THEN 1 ELSE 0 END) AS YEAR17,
  MAX(CASE WHEN YEAR(ContractEndDate) = 2018 THEN 1 ELSE 0 END) AS YEAR18
FROM Contract
GROUP BY ContractID

Use your logic on this result:

SELECT ContractID,
  CASE WHEN YEAR16 = 1 AND YEAR17 = 1 AND YEAR18 = 1 THEN 
    'Has a contract that ends next year and year after' 
  CASE WHEN YEAR16 = 1 AND YEAR17 = 1 THEN 
    'Has a contract that ends next year' 
  CASE WHEN YEAR16 = 1 THEN 
    'Has a contract that ends this year' 
  ELSE 'Something is wrong'
END 
FROM (
  SELECT ContractID,  
    MAX(CASE WHEN YEAR(ContractEndDate) = 2016 THEN 1 ELSE 0 END) AS YEAR16,
    MAX(CASE WHEN YEAR(ContractEndDate) = 2017 THEN 1 ELSE 0 END) AS YEAR17,
    MAX(CASE WHEN YEAR(ContractEndDate) = 2018 THEN 1 ELSE 0 END) AS YEAR18
  FROM Contract
  GROUP BY ContractID
) X
Comments