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

Wednesday, June 3, 2015 8:51 AM

• 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)```

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

• Proposed as answer by Thursday, June 4, 2015 2:09 AM
• Marked as answer by 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
)
```

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

Eric Zhang
TechNet Community Support

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

All replies

• SELECT * FROM TEST_DATE
CROSS APPLY(
AS FromDate,
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
• 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(
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
• 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)```

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

• Proposed as answer by Thursday, June 4, 2015 2:09 AM
• Marked as answer by Thursday, June 11, 2015 2:14 AM
Wednesday, June 3, 2015 12:04 PM
• Wednesday, June 3, 2015 12:13 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
)