Mike Johnston - 1 year ago 46

SQL Question

I have 2 tables. One is a list of quarters and the other is a list of years. I need to write a query to return the years, and append the word "partial" at the end of the years that contain less than 4 quarters.

An example of a couple of rows from the year table:

`YearName | YearUUID`

2015 | 15C33

2016 | 16C33

An example of the quarters table:

`QuarterNumber | StartDate | YearUUID`

1 | 2015-01-01 | 15C33

2 | 2015-03-01 | 15C33

3 | 2015-06-01 | 15C33

4 | 2015-09-01 | 15C33

1 | 2016-01-01 | 16C33

2 | 2016-03-01 | 16C33

See that in the above example, 2016 will be a partial year. So my query results should be as follows:

`YearsList`

2015

2016 - Partial

I've used the Concat function before, but never with a condition such as this. Without knowing how to condition it, I created the following:

`SELECT DISTINCT CONCAT(Years.YearName, ' - Partial') as YearsList`

FROM dbo.Quarters Quarters,

dbo.Years Years

WHERE Quarters.YearUuid = Years.YearUuid

AND Quarters.ActiveStatus = 'ACTIVE'

AND Quarters.BroadcastQuarterType = 'STANDARD'

ORDER BY YearsList ASC;

This gives me a list of all of the years, but appends 'Partial' to every single one of them. There is obviously no condition in there. How do I incorporate a condition into that query so that I get full years (years that contain 4 quarters) without 'Partial' appended on the end, while also maintaining the DISTINCT and maintaining the ORDER BY, as well.

Answer

You could use `GROUP BY`

and a `CASE WHEN`

construct:

```
SELECT CASE WHEN COUNT(Quarters.QuarterNumber) < 4
THEN CONCAT(Years.YearName, ' - Partial')
ELSE CAST(Years.YearName AS varchar)
END as YearsList
FROM dbo.Quarters Quarters,
dbo.Years Years
WHERE Quarters.YearUuid = Years.YearUuid
AND Quarters.ActiveStatus = 'ACTIVE'
AND Quarters.BroadcastQuarterType = 'STANDARD'
GROUP BY Years.YearName
ORDER BY Years.YearName ASC;
```