Click here to Skip to main content
15,794,593 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All ,

I have a query that selects all month and the corresponding values in each month . I want to sort the month order based on the date column . Could anyone help me please . Tried different ways but couldn't find a solution . Any help will be really appreciated .

For example :
My output right now :

Leave Jan Feb March Apr May Jun Jul Aug Sep Oct Nov Dec

Annual 0 0 2 2 2 0 0 0 0 0 0 0
Sick 0 0 2 2 2 0 0 0 0 0 0 0

The output I wanted :

Leave March Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb
Annual 2 2 2 0 0 0 0 0 0 0 0 0
Sick 2 2 2 2 2 0 0 0 0 0 0 0

ie the month column order should be based on mte.CalendarDate from the subquery .

What I have tried:

SELECT
		Leave,
		ISNULL([1], 0) AS January,
		ISNULL([2], 0) AS February,
		ISNULL([3], 0) AS March,
		ISNULL([4], 0) AS April,
		ISNULL([5], 0) AS May,
		ISNULL([6], 0) AS June,
		ISNULL([7], 0) AS July,
		ISNULL([8], 0) AS August,
		ISNULL([9], 0) AS September,
		ISNULL([10], 0) AS October,
		ISNULL([11], 0) AS November,
		ISNULL([12], 0) AS December
	FROM
			(
				SELECT
					--YEAR(mte.CalendarDate) AS Years,
					MONTH(mte.CalendarDate) AS Months,
					--DATENAME(month, mte.CalendarDate) AS Calmonth,
					FLOOR(SUM(mte.LeaveDays))AS leaveCount
					,mte.LeaveTypeID
					,mtl.Leave
			
				FROM MST_TBL_Emp_Leaves mte
				LEFT JOIN PPMS_TBL_EmployeeMaster em ON em.Autoid = mte.EmpId
				LEFT JOIN MST_TBL_Leave_Types mtl ON mtl.Autoid = mte.LeaveTypeID
				WHERE
					mte.LeaveTypeID IN (1, 2, 4, 8) AND
					mte.EmpId = 170 AND
					mte.CalendarDate BETWEEN '2023-02-27' AND GETDATE()
				GROUP BY
					--YEAR(mte.CalendarDate),
					MONTH(mte.CalendarDate)
					,mte.LeaveTypeID
					,mtl.Leave
					
			
			) AS source
		PIVOT
		(
			SUM(leaveCount)
			FOR Months IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]) 
		) AS pivotedTable
Posted
Updated 6-Jun-23 3:44am
Comments
Richard Deeming 2-Jun-23 3:33am    
You want the column order to be based on some data we can't see, using some rules you haven't described.

Click the green "Improve question" link and update your question to include the sample data that you want to produce the expected output, and an explanation of why the columns should be ordered in that specific way.

If possible, create a small SQL Fiddle[^] to demonstrate the problem.

1 solution

You have explicitly defined the column order with
SQL
SELECT
		Leave,
		ISNULL([1], 0) AS January,
		ISNULL([2], 0) AS February,
		ISNULL([3], 0) AS March,
		ISNULL([4], 0) AS April,
		ISNULL([5], 0) AS May,
		ISNULL([6], 0) AS June,
		ISNULL([7], 0) AS July,
		ISNULL([8], 0) AS August,
		ISNULL([9], 0) AS September,
		ISNULL([10], 0) AS October,
		ISNULL([11], 0) AS November,
		ISNULL([12], 0) AS December
	FROM
So in theory all you have to do is
SQL
SELECT
		Leave,
		ISNULL([3], 0) AS March,
		ISNULL([4], 0) AS April,
		ISNULL([5], 0) AS May,
		ISNULL([6], 0) AS June,
		ISNULL([7], 0) AS July,
		ISNULL([8], 0) AS August,
		ISNULL([9], 0) AS September,
		ISNULL([10], 0) AS October,
		ISNULL([11], 0) AS November,
		ISNULL([12], 0) AS December,
		ISNULL([1], 0) AS January,
		ISNULL([2], 0) AS February
	FROM
Note similarly, the order that the columns will be returned from your pivot is defined by
SQL
FOR Months IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])

But I suspect there is a bit more to it than that - are you asking that the order should be "start from the month defined in
SQL
mte.CalendarDate BETWEEN '2023-02-27' AND GETDATE()
(i.e. in this case March) and order the subsequent months through to the following March?

If that is the case then you may need to use a Dynamic SQL statement - I described a similar scenario in my article Processing Loops in SQL Server[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900