Solved

# List of Months and Quarters for given date range

Posted on 2014-03-22
581 Views
Hello,

I want a function that takes Start date and End date, and should return me a result set containing Year, Month number, Month Name, Quarter Number, and Quarter Name for each of the Year in that given date range.

How to achieve this?

Thanks!
0
Question by:sath350163

LVL 48

Accepted Solution

PortletPaul earned 250 total points
ID: 39947080
what does this bit mean? "for each of the Year in that given date range."
perhaps you could provide some examples?

Do you have any tables to use in this? (You most probably should have.)
try this EE article by Jim Horn
Date-Fun-Part-One-Build-your-own-SQL-calendar-table-to-perform-complex-date-expressions

details:
is "month number" simply 1 to 12 based on the calendar month of any date? (i.e. the month number isn't a financial calendar is it?)

& can we assume quarters aren't related to a financial calendar also.
0

LVL 35

Assisted Solution

David Todd earned 100 total points
ID: 39949212
Hi,

select
dateadd( year, datediff( year, 0 , getdate()), 0 ) as StartOfYear
, dateadd( quarter, datediff( quarter, 0, getdate()), 0 ) as StartOfQuarter
, dateadd( month, datediff( month, 0, getdate()), 0 ) as StartOfMonth

HTH
David
0

LVL 69

Assisted Solution

ScottPletcher earned 150 total points
ID: 39950899
The function is below.  Not sure what you meant by "quarter name", so change it as required:

--sample usage of function after it is created:
--SELECT * FROM dbo.Qtrs_and_Months ( '20130623 11:42:17', getdate()) order by year, month
GO

CREATE FUNCTION dbo.Qtrs_and_Months (
@start_date datetime,
@end_date datetime
)
RETURNS TABLE
AS
RETURN (
WITH
cteDigits AS (
SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
SELECT [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS tally
FROM cteDigits [1s]
CROSS JOIN cteDigits [10s]
CROSS JOIN cteDigits [100s]
)
SELECT
YEAR(current_month) AS Year,
DATEPART(MONTH, current_month) AS Month,
DATENAME(MONTH, current_month) AS Month_Name,
DATEPART(QUARTER, current_month) AS Quarter,
'Quarter' + CAST(DATEPART(QUARTER, current_month) AS varchar(1)) AS Quarter_Name
FROM (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @start_date), 0) AS start_month,
DATEADD(MONTH, DATEDIFF(MONTH, 0, @end_date), 0) AS end_month
) AS param_values
INNER JOIN cteTally t ON
t.tally BETWEEN 0 AND DATEDIFF(MONTH, start_month, end_month)
CROSS APPLY (
SELECT DATEADD(MONTH, t.tally, start_month) AS current_month
) AS ca1
)

GO
0

## Featured Post

### Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

#### Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!