Solved

List of Months and Quarters for given date range

Posted on 2014-03-22
3
598 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 49

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 eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

691 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