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.

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
``````
Source (Stackoverflow)