Mike Johnston Mike Johnston - 7 months ago 16
SQL Question

SQL Concat function only when row count less than number

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;