Solved

List of Months and Quarters for given date range

Posted on 2014-03-22
3
587 Views
Last Modified: 2014-04-05
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
Comment
Question by:sath350163
3 Comments
 
LVL 48

Accepted Solution

by:
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

by:David Todd
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

by:Scott Pletcher
Scott Pletcher 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question