[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

List of Months and Quarters for given date range

Posted on 2014-03-22
3
Medium Priority
?
608 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 750 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 300 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 70

Assisted Solution

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

649 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