Quarterly data from monthly data in sql server
-
Hello All,
I have a table having data like below which has trendtype as monthly.
Now my requirement is I need to create a query to get quarterly data from the monthly data.
As I have 12 months data here i need to show 4 quarters data.
Could any one please help how to do.
So the new query should have the values TrendType as Quarterly,Date and Sales values.
-
The best-practice way of doing such things is first to create and populate a generic calendar table. This could contain columns such as
Date, Year, Month, Day, Quarter, Week, MonthName, DayName
and pretty much any other columns which are relevant to your needs. Further reading here. Most importantly, for performance reasons, this needs to have a clustered primary key on the Date column.
Once you have this in place, your requirement becomes fairly trivial, as you get the quarter number directly from the calendar table after joining to it on Date, ready to be grouped on and aggregated.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Could you post directly usable data -- CREATE TABLE and INSERT statement(s) -- rather than just a picture? We can't write SQL against a picture :-).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
How are you calculating the monthly totals now?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply