How to modify month wise results to quarterly data

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

General SQL Server Forums

New to SQL Server Programming

How to modify month wise results to quarterly data

Author

Topic

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-27 : 01:31:57

This is my query ...which get me that total sales and total bookings from 2006 to 2020....every month....How can i modify the query to get the results for every quarter???

SELECT
DATENAME(Year,BookingDate) as Year, DATENAME(Month,BookingDate) as Month,
COUNT(InvoiceNumber) AS [Total Bookings],
Sum(NegotiatedSellPrice) AS [Total Sale Price]

FROM tblInvoice
WHERE (fk_StatusID = 3) AND
(BookingDate BETWEEN
CONVERT(DATETIME, '2006-01-01 00:00:00', 102) AND
CONVERT(DATETIME, '2020-01-31 23:59:59', 102))
OR
(fk_StatusID = 5) AND
(BookingDate BETWEEN
CONVERT(DATETIME, '2006-01-01 00:00:00', 102) AND
CONVERT(DATETIME, '2020-01-31 23:59:59', 102))
GROUP BY DATENAME(Month,BookingDate) , DATENAME(Year,BookingDate),
DATEPART(MM,BookingDate),DATEPART(YYYY,BookingDate)
ORDER BY DATEPART(YYYY,BookingDate),DATEPART(MM,BookingDate);

Output table looks like:

Year__Month__TotalBookings__TotalSale
2007__March_____1___________1248.000
2007__April_____29__________7595.000
2007__May_____81___________2418.000
2007__june_____79__________7765.000

(I want to add new column which gives me quarterly values of sales and bookings)

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-27 : 02:53:05

1. your WHERE clause is not right. it should be


WHERE 	
	(
		(fk_StatusID = 3) 
	AND	(BookingDate 	BETWEEN	CONVERT(DATETIME, '2006-01-01 00:00:00', 102) 
				AND	CONVERT(DATETIME, '2020-01-31 23:59:59', 102))
	)
OR	(
		(fk_StatusID = 5) AND
		(BookingDate 	BETWEEN	CONVERT(DATETIME, '2006-01-01 00:00:00', 102) 
				AND	CONVERT(DATETIME, '2020-01-31 23:59:59', 102))	
	)

which can be simplified into


WHERE 	BookingDate 	>= '2006-01-01'
AND	BookingDate	<  '2020-02-01'
AND	fk_StatusID	in (3, 5)

2. on the quarterly value


SELECT
	DATENAME(Year,BookingDate) as Year, 
	DATENAME(Month,BookingDate) as Month,
	COUNT(InvoiceNumber) 	as [Total Bookings],
	Sum(NegotiatedSellPrice) as [Total Sale Price],
	SUM(COUNT(InvoiceNumber)) OVER (PARTITION BY DATEPART(YEAR,BookingDate), DATEPART(QUARTER,BookingDate)) as QuarterBooking,
	SUM(Sum(NegotiatedSellPrice)) OVER (PARTITION BY DATEPART(YEAR,BookingDate), DATEPART(QUARTER,BookingDate)) as QuarterSales
FROM 
	tblInvoice
WHERE 	BookingDate 	>= '2006-01-01'
AND	BookingDate	<  '2020-02-01'
AND	fk_StatusID	in (3, 5)
GROUP BY 
	DATENAME(Month,BookingDate) , 
	DATENAME(Year,BookingDate),
	DATEPART(MM,BookingDate),
	DATEPART(YYYY,BookingDate)
ORDER BY 
	DATEPART(YYYY,BookingDate),
	DATEPART(MM,BookingDate);

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

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-27 : 03:34:38

Thanks for your reply.. I ll Sort with your Where Cluse...thanks.

but for Quartely data i am finding an error like this....
Please Help

Msg 130, Level 15, State 1, Line 6
Cannot perform an aggregate function on an expression containing an aggregate or a subquery

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-27 : 06:12:41

what is the version of SQL Server you are using ?

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

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-27 : 06:19:59

SQL SERVER 2008

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-27 : 06:20:40