PIVOT NEED HELP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

PIVOT NEED HELP

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-10 : 02:20:18

The following statement able to give wat i require for the year where @startdate and @enddate is in the same year


SELECT	[1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12],[13],[14]
FROM	(
		SELECT * FROM(
		SELECT MONTH(date)as date, sum(qty) as qty 
		FROM #TempSt_trx 
		WHERE date>=@startdate and date<=@enddate 
		GROUP BY date
		UNION
		SELECT 13 as date, SUM(qty)as qty FROM #TempSt_trx where date < @startdate
		UNION
		SELECT 14 as date, SUM(qty*cost) as totalat_cost FROM #TempSt_trx)ABC
		)AS AA
PIVOT	(
		SUM(qty) FOR date IN ([1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12],[13],[14])
		)AS pvt

result will likely become


jan feb mac apr may jun jul aug sep oct  nov  dec  pass year sum cost
[1] [2] [3] [4] [5] [6] [7]  [9] [10] [11] [12]    [13]    [14]
 1   3   2   5   6   7   3   4   7   4    12    5    548     56

but when the @startdate(01/04/07) is smaller den @enddate(31/03/08)


jan feb mac apr may jun jul aug sep oct  nov  dec  pass year sum cost
[1] [2] [3] [4] [5] [6] [7]  [9] [10] [11] [12]    [13]    [14]
 1   3   2   5   6   7   3   4   7   4    12    5    548     56

this is not wat i require, i need something like


apr may jun jul aug sep oct  nov  dec jan feb mac  pass year sum cost
[4] [5] [6] [7]  [9] [10] [11] [12] [1] [2] [3]    [13]    [14]
5   6   7   3   4   7   4    12    5   1   4   3      548     56

can any pros help me....millions thx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-10 : 02:57:05

Change MONTH(date)as date
To DATEDIFF(month, @startdate, date) + 1 as date

and the group by part correspondingly

KH
[spoiler]Time is always against us[/spoiler]