very challenging - 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 2012 Forums

Transact-SQL (2012)

very challenging

Author

Topic

abhijit1062
Starting Member

4 Posts

Posted - 2015-04-10 : 04:14:00

Table Nmae:: Oppertunity.
Fields Name:: 1] EstimatedValue 2]EstimatedDate

I want Sum of Estimatedvalue of last 3 year by Month wise using EstimatedDate.

OutPut Should be::

EstimatedValue Year MonthName
3567.00 2015 January
1173.60 2015 February
. . . .
. . . .
2212.21 2015 December
54545.21 2014 January
. 2014 .
. 2014 .
2312.21 2014 December
54545.1 2013 January
. 2013 .
. 2013 .
2312.21 2013 December

magmo
Aged Yak Warrior

558 Posts

Posted - 2015-04-10 : 04:23:57

How about this...


SELECT      SUM(EstimatedValue), DATENAME(year, EstimatedDate) AS Year, DATENAME(month, EstimatedDate) AS MonthName
FROM         dbo.Oppertunity

GROUP BY DATENAME(month, EstimatedDate), DATENAME(year, EstimatedDate), MONTH(EstimatedDate)
ORDER BY Year, MonthNumber

abhijit1062
Starting Member

4 Posts

Posted - 2015-04-10 : 05:34:21

Yes its close...
I just detailed my output again.
The condition is that it should show all months in a year with there 0 value in EstimatedValue column.
Output::

EstimatedValue Year MonthName
356 2015 January
117 2015 February
1212. 2015 March
0.00 2015 April
0.00 2015 May
0.00 2015 June
0.00 2015 July
0.00 2015 August
0.00 2015 September
0.00 2015 October
0.00 2015 November
0.00 2015 December

and same again for year 2014 and so on.

Thanks Magmo

magmo
Aged Yak Warrior

558 Posts

Posted - 2015-04-10 : 06:03:34

In that case just add WHERE SUM(EstimatedValue) = 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources