locked
How to get Four Quarter End Dates Based on the Given Date RRS feed

  • Question

  • Hi All,

    i have a simple following table which is having only one date column.

    CREATE TABLE TEST_DATE
    (
     InputDate DATE
    )
    GO
    INSERT INTO TEST_DATE VALUES('01-01-2015')
    INSERT INTO TEST_DATE VALUES('06-25-2015')
    INSERT INTO TEST_DATE VALUES('11-23-2014')
    GO
    SELECT * FROM TEST_DATE;

    and the expected out put would be as follows:

    i want to derive a Four Quarter End Date based on Date selected.

    For Example if i select 01-01-2015 then

    First Quarter End Date would be Previous Quarter End Date

    Second Quarter End Date would be Current Quarter End Date

    Third Quarter End Date would be Next Quarter End Date

    Fourth Quarter End Date would be Next +1 Quarter End Date Like that


    bala krishna

    Wednesday, June 3, 2015 8:51 AM

Answers

  • I think this

    SELECT InputDate,DATEADD(qq,DATEDIFF(qq,0,InputDate) + m.n,-1)
    FROM YourTable t
    CROSS APPLY(VALUES (0),(1),(2),(3)) m(n)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Eric__Zhang Thursday, June 4, 2015 2:09 AM
    • Marked as answer by Eric__Zhang Thursday, June 11, 2015 2:14 AM
    Wednesday, June 3, 2015 12:04 PM
  • Hi balakrishna141,

    To achieve your requirement, you can reference the below sample as well.

    CREATE TABLE TEST_DATE
    (
     InputDate DATE
    )
    GO
    INSERT INTO TEST_DATE VALUES('01-01-2015')
    INSERT INTO TEST_DATE VALUES('06-25-2015')
    INSERT INTO TEST_DATE VALUES('11-23-2014')
    GO
    
     ;WITH Cte AS
     (
     SELECT InputDate,0 LEVEL FROM TEST_DATE
     UNION ALL
     SELECT InputDate,LEVEL+1 FROM Cte
     WHERE LEVEL<3
     )
     SELECT InputDate,DATEADD(DAY,-1,DATEADD(QQ,DATEDIFF(QQ,0,InputDate)+LEVEL,0)) FROM Cte ORDER BY 1
    

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support


    • Proposed as answer by Eric__Zhang Wednesday, June 10, 2015 2:39 AM
    • Marked as answer by Eric__Zhang Thursday, June 11, 2015 2:14 AM
    Thursday, June 4, 2015 2:19 AM

All replies

  • SELECT * FROM TEST_DATE
    CROSS APPLY(
    SELECT DATEADD(QUARTER, d.q, DATEADD(YEAR, DATEDIFF(YEAR, 0, InputDate), 0))
    AS FromDate,
    DATEADD(QUARTER, d.q + 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, InputDate), -1))
    AS ToDate
    FROM (
    SELECT 0 UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3
    ) AS d(q)
    ) AS Der 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, June 3, 2015 9:00 AM
    Answerer
  • Thanks for your response, There small code change request .

    If we observe "First Quarter End Date would be Previous Quarter End Date" and then so on ...

    Can you please Let me know the changes based on above


    bala krishna

    Wednesday, June 3, 2015 9:05 AM
  • The Output Should Match With My Excel Sheet , which is attached.

    bala krishna


    Wednesday, June 3, 2015 9:18 AM
  • I am doing something for you but according yours picture I can't understand why Given the Data 23-11-2014 your QTDEnddate start with 31-12-2014...it should be 30-09-2014. Is it right?
    Wednesday, June 3, 2015 9:52 AM
  • Ok, see if this helps

    SELECT * FROM TEST_DATE
    CROSS APPLY(
    SELECT DATEADD(QUARTER, d.q, DATEADD(YEAR, DATEDIFF(YEAR, 0, InputDate), 0)-1)
    AS EndDate
    FROM (
    SELECT 0 UNION ALL
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3
    ) AS d(q)
    ) AS Der 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, June 3, 2015 11:18 AM
    Answerer
  • I think this

    SELECT InputDate,DATEADD(qq,DATEDIFF(qq,0,InputDate) + m.n,-1)
    FROM YourTable t
    CROSS APPLY(VALUES (0),(1),(2),(3)) m(n)


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Eric__Zhang Thursday, June 4, 2015 2:09 AM
    • Marked as answer by Eric__Zhang Thursday, June 11, 2015 2:14 AM
    Wednesday, June 3, 2015 12:04 PM
  • Hi balakrishna141,

    To achieve your requirement, you can reference the below sample as well.

    CREATE TABLE TEST_DATE
    (
     InputDate DATE
    )
    GO
    INSERT INTO TEST_DATE VALUES('01-01-2015')
    INSERT INTO TEST_DATE VALUES('06-25-2015')
    INSERT INTO TEST_DATE VALUES('11-23-2014')
    GO
    
     ;WITH Cte AS
     (
     SELECT InputDate,0 LEVEL FROM TEST_DATE
     UNION ALL
     SELECT InputDate,LEVEL+1 FROM Cte
     WHERE LEVEL<3
     )
     SELECT InputDate,DATEADD(DAY,-1,DATEADD(QQ,DATEDIFF(QQ,0,InputDate)+LEVEL,0)) FROM Cte ORDER BY 1
    

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support


    • Proposed as answer by Eric__Zhang Wednesday, June 10, 2015 2:39 AM
    • Marked as answer by Eric__Zhang Thursday, June 11, 2015 2:14 AM
    Thursday, June 4, 2015 2:19 AM