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

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

Answer 1

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

Answer 2

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

Answer 3

The Output Should Match With My Excel Sheet , which is attached.

---

bala krishna

Answer 4

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?

Answer 5

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

Answer 6

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

Answer 7

Create a calendar table.

See:

http://social.technet.microsoft.com/wiki/contents/articles/22776.t-sql-calendar-table.aspx

Answer 8

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