I am trying to sort an array in SQL Server By month

Question

EDIT: I listed the rest of the Code

The array looks like [Aug],[Nov],[Oct],[Sep].

I would like this to be dynamic to always be in the right order of month for what ever I query.

This is the code that I am using.

   ALTER PROCEDURE [dbo].[CustomerFlowByMonth]
@start datetime,
@end datetime

as 
declare @day varchar(max) = ''  
select @day  = @day +','+'['+CONVERT(varchar(3),DATENAME(MONTH,tdate))+']'
FROM someDB.[dbo].[someTable]
Where tdate between @start and @end
group by DATENAME(MONTH,tdate)
order by DATENAME(MONTH,tdate)
set @day = substring(@day, 2, (len(@day)) )

--select @day
declare @query varchar(max) =
'
select * 
from 
(
SELECT Customer,  CONVERT(varchar(3),DATENAME(MONTH,tdate)) AS OrderMonth
 ,SUM(saleAmount) as amount
   FROM someDB.[dbo].[SomeTable]
  Where tdate between '+''''+ convert(varchar,@start) +''' and '''+convert(varchar,@end) +'''
  group by Customer, CONVERT(varchar(3),DATENAME(MONTH,tdate))
 ) as pp
 --order by Customer, DATENAME(MONTH,tdate)
 pivot (sum(amount) for OrderMonth in ('+@day+')) as total'

 --print @query
 execute (@query)

I'm not the best at SQL Server so any help would be greatly appreciated

score 0 · Accepted Answer · edited Jul 29 '20 at 18:42

The issue you are having is that you are sorting by the string month name, so it is natural that it should follow alphabetic order, if you want it in chronological order you need to sort by month number (DATEPART(MONTH, tDate)), which just means adding that to the GROUP BY, so that you can sort on it. This won't affect the cardinality, since any given month name, can only have a single month number.

That being said, you should not use variable assignment to concatenate strings, it is not guaranteed to return the correct results, let alone follow the order by you state. The actual result you end up with will depend on the internal pathways taken by the optimiser. Instead use FOR XML PATH().

DECLARE @start DATETIME = '2016-08-03 00:00',
        @end DATETIME = '2016-11-08 00:00',
        @day VARCHAR(MAX) = '';


SET @day = STUFF((SELECT ',[' + LEFT(DATENAME(MONTH, tDate), 3) + ']'
                FROM someDB.[dbo].[someTable]
                WHERE tdate BETWEEN @start AND @end
                GROUP BY LEFT(DATENAME(MONTH, tDate), 3), DATEPART(MONTH, tDate)
                ORDER BY DATEPART(MONTH, tDate)
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SELECT @day;

It is worth noting that you might not get the results you want if your date range spans multiple years, and it is personal preference, but I tend to steer clear of BETWEEN for date ranges for reasons set out here

If you are going to build and execute dynamic SQL like this, I would advise you use sp_executesql so that you can pass your parameters with proper types, rather than having to do this awful concatenation to apply your date filters:

'+''''+ convert(varchar,@start) +''' and '

instead you can just declare and pass paramters:

DECLARE @query NVARCHAR(MAX) = 'SELECT ... WHERE tDate BETWEEN @start AND @end';

EXECUTE sp_executesql @Query, N'@Start DATETIME, @end DATETIME', @Start, @end;

N.B You should always specify a length when converting to, or declaring a varchar

Finally, when working with DATETIME the literal date format xxxx-xx-xx is ambiguous, it can mean both yyyy-MM-dd and yyyy-dd-MM, so depending on regional settings your start parameter can be both 3rd August, and 8th March. The only culture invariant format for DATETIME is yyyyMMdd. For more reading see Bad habits to kick : mis-handling date / range queries

So your full query might look something like:

DECLARE @start DATETIME = '20160803 00:00',
        @end DATETIME = '20161108 00:00',
        @day VARCHAR(MAX) = '';


SET @day = STUFF((SELECT ',[' + LEFT(DATENAME(MONTH, tDate), 3) + ']'
                FROM someDB.[dbo].[someTable]
                WHERE tdate BETWEEN @start AND @end
                GROUP BY LEFT(DATENAME(MONTH, tDate), 3), DATEPART(MONTH, tDate)
                ORDER BY DATEPART(MONTH, tDate)
                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @Query NVARCHAR(MAX) = 
    'SELECT *
    FROM (SELECT Customer, LEFT(DATENAME(MONTH,tdate), 3) AS OrderMonth, SUM(saleAmount) AS Amount
            FROM SomeDB.[dbo].[SomeTable]
            WHERE tdate >= @start 
            AND tDate <= @end
            GROUP BY Customer, LEFT(DATENAME(MONTH,tdate), 3)) AS pp
        PIVOT (SUM(Amount) FOR OrderMonth IN (' + @day + ')) AS pvt';

EXECUTE sp_executesql @Query, N'@Start DATETIME, @end DATETIME', @Start, @end;

I did not set out to link to so many of Aaron Bertrand's articles, it just so happens that you have fallen into a lot of the traps he has blogged about. As such I though I may as well finish off with a link to the full list of his "Bad Habits to Kick" articles, they are well worth a read.

Thank you for the quick response. The goal is to have a column of Row of customers, and a column of Months. Years aren't and issue. It would look like Jan Feb Cust A 500 200 Cust B 200 300 — Paul Kiejdan, Nov 29 '16 at 18:16
I apologize that my question was not complete. I added the rest of the code if that can be any help. Thanks in advance. — Paul Kiejdan, Nov 29 '16 at 18:24
Amazing explanation. The issue that I am having is DateKey? What is that representing. I just have red underline for that. — Paul Kiejdan, Nov 29 '16 at 18:51
That is me being an idiot and pasting the query I was using to test rather than a query that would work for you. I have updated the answer with the correct query. — GarethD, Nov 29 '16 at 19:34

score 0 · Answer 2 · answered Nov 29 '16 at 18:17

0

your problem is the order by

order by CONVERT(varchar(3),DATENAME(MONTH,tdate))

This will order the dates in alphabetic montly order. (eg April First)

Instead order by the date itself

order by tdate

answered Nov 29 '16 at 18:17

john McTighe

1,181
6
8

score 0 · Answer 3 · answered Nov 29 '16 at 18:34

0

Your solution should be

ORDER BY MONTH(tDate)

MONTH (Transact-SQL)

Returns an integer that represents the month of the specified date.

answered Nov 29 '16 at 18:34

Ricardo C

2,205
20
24

I am trying to sort an array in SQL Server By month

3 Answers3