January 30, 2014 at 3:32 am
Hi,
I need to work around with dates. Help me on this.
Divide one year by 4 quarters
Jan Fab, Mar --> Q1
Apr,May,June --> Q2
July,Aug,Sep --> Q3
Oct,Nov, Dec --> Q4
I should sum the financial data for all dates in each quaters wise and this is dynamic for multiple years.
Please help me to write the query based on the above assumption to manipulate the dates in quater wise for n number of years
January 30, 2014 at 3:37 am
It should be between two dates.
Min Date : 2012-04-01
Max Date : 2014 -03-31
I need somethings like this
2012-04-01 to 2012-06-30
2012-07-01 to 2012-09-30
2012-10-01 to 2012-12-31
2013-01-01 to 2013-03-31
2013-04-01 to 2013-06-30
2013-07-01 to 2012-03-30
2013-10-01 to 2013-12-31
2014-01-01 to Maxdate(2014-03-31)
The metric data based on the date so sum on the speficied dates.
January 30, 2014 at 4:18 am
You could create a derived column indicating the year and quarter in which a date belongs to. Something like this:
CAST(YEAR(DateColumn) AS NVARCHAR(4))
+ 'Q' + DATENAME(Quarter, DateColumn) AS YearQuarter
and then you can summarize amounts by grouping them by the derived column. i.e.
WITH cte AS
(
SELECT
CAST(YEAR(DateColumn) AS NVARCHAR(4))
+ 'Q' + DATENAME(Quarter, DateColumn) AS YearQuarter,
Amount
FROM YourTable
WHERE DateColumn > some_date1 AND DateColumn < some_date2
)
SELECT YearQuarter, SUM(Amount) AS TotalAmount
FROM cte
GROUP BY YearQuarter
___________________________
Do Not Optimize for Exceptions!
January 30, 2014 at 4:54 am
My favourite resource for date manipulation is https://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply