I am relatively new to writing SQL. I have a requirement where I have to display the top 5 records as it is and consolidate the rest as 1 single record and append it as the 6th record. I know top 5 selects the top 5 records, but I am finding it difficult to put together a logic to consolidate the rest of the records and append it at the bottom of the result set.
weekof sales year weekno
-------------------------------------------------------------
07/01 - 07/07 2 2012 26
07/08 - 07/14 2 2012 27
07/29 - 08/04 1 2012 30
08/05 - 08/11 1 2012 31
08/12 - 08/18 32 2012 32
08/26 - 09/01 2 2012 34
09/02 - 09/08 8 2012 35
09/09 - 09/15 46 2012 36
09/16 - 09/22 26 2012 37
I want this to be displayed as
weekof sales
----------------------
09/16 - 09/22 26
09/09 - 09/15 46
09/02 - 09/08 8
08/26 - 09/01 2
08/12 - 08/18 32
07/01 - 08/11 6
Except when weekof spans years, this will get the data you want and in the correct order:
;WITH x AS
(
SELECT weekof, sales,
rn = ROW_NUMBER() OVER (ORDER BY [year] DESC, weekno DESC)
FROM dbo.table_name
)
SELECT weekof, sales FROM x WHERE rn <= 5
UNION ALL
SELECT MIN(LEFT(weekof, 5)) + ' - ' + MAX(RIGHT(weekof, 5)), SUM(sales)
FROM x WHERE rn > 5
ORDER BY weekof DESC;
When the rows being returned span a year, you may have to return the rn as well (and just ignore it at the presentation layer):
;WITH x AS
(
SELECT weekof, sales,
rn = ROW_NUMBER() OVER (ORDER BY [year] DESC, weekno DESC)
FROM dbo.table_name
)
SELECT weekof, sales, rn FROM x WHERE rn <= 5
UNION ALL
SELECT MIN(LEFT(weekof, 5)) + ' - ' + MAX(RIGHT(weekof, 5)), SUM(sales), rn = 6
FROM x WHERE rn > 5
ORDER BY rn;
Here's an alternate version that displays in the correct order even when crossing years, and requires a single scan instead of two (important only if the amount of data is large). It will work in SQL 2005 and up:
WITH Sales AS (
SELECT
Seq = Row_Number() OVER (ORDER BY S.yr DESC, S.weekno DESC),
*
FROM dbo.SalesByWeek S
)
SELECT
weekof =
Substring(Min(Convert(varchar(11), S.yr) + Left(S.weekof, 5)), 5, 5)
+ ' - '
+ Substring(Max(Convert(varchar(11), S.yr) + Right(S.weekof, 5)), 5, 5),
sales = Sum(S.sales)
FROM
Sales S
OUTER APPLY (SELECT S.Seq WHERE S.Seq <= 5) G
GROUP BY G.Seq
ORDER BY Coalesce(G.Seq, 2147483647)
;
There's also a query possible that might perform even better for very large data sets, since it doesn't calculate row number (using TOP once instead). But this is all theoretical without real data and performance testing:
SELECT
weekof =
Substring(Min(Convert(varchar(11), S.yr) + Left(S.weekof, 5)), 5, 5)
+ ' - '
+ Substring(Max(Convert(varchar(11), S.yr) + Right(S.weekof, 5)), 5, 5),
sales = Sum(S.sales)
FROM
dbo.SalesByWeek S
LEFT JOIN (
SELECT TOP 5 *
FROM dbo.SalesByWeek
ORDER BY yr DESC, weekno DESC
) G ON S.yr = G.yr AND S.weekno = G.weekno
GROUP BY G.yr, G.weekno
ORDER BY Coalesce(G.yr, 9999), G.weekno
;
Ultimately, I really don't like the string manipulation to pull out the date ranges. It would be better if the table had the MinDate and MaxDate as separate columns, and then creating a label for them would be much simpler.
