?
Solved

SQL CTE

Posted on 2014-04-13
2
Medium Priority
?
478 Views
Last Modified: 2014-04-13
Hi Experts,
I have attached a sample file of my table along with the data. I require and output which is marked in yellow columns.

I am also attaching the SQL view and a cte which i have used to derive the output. I am gettting the desired output for a single year (April-March) but when there are multiple years the output goes haywhere.

Also can i use the cte in the view to get the output.
cutoff.xlsx
uvw-CutoffDate.sql
cte.sql
0
Comment
Question by:vikas_nm
2 Comments
 
LVL 50

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39997155
This result:
| MONTHID | DIVISIONID |   YEARMONTH |   STARTDATE |     ENDDATE |
|---------|------------|-------------|-------------|-------------|
|       1 |          1 | 30 Apr 2013 | 01 Apr 2013 | 24 Apr 2013 |
|       2 |          1 | 31 May 2013 | 25 Apr 2013 | 28 May 2013 |
|       3 |          1 | 30 Jun 2013 | 29 May 2013 | 27 Jun 2013 |
|       4 |          1 | 31 Jul 2013 | 28 Jun 2013 | 27 Jul 2013 |
|       5 |          1 | 31 Aug 2013 | 28 Jul 2013 | 26 Aug 2013 |
|       6 |          1 | 30 Sep 2013 | 27 Aug 2013 | 29 Sep 2013 |
|       7 |          1 | 31 Oct 2013 | 30 Sep 2013 | 26 Oct 2013 |
|       8 |          1 | 30 Nov 2013 | 27 Oct 2013 | 24 Nov 2013 |
|       9 |          1 | 31 Dec 2013 | 25 Nov 2013 | 22 Dec 2013 |
|      10 |          1 | 31 Jan 2014 | 23 Dec 2013 | 25 Jan 2014 |
|      11 |          1 | 28 Feb 2014 | 26 Jan 2014 | 25 Feb 2014 |
|      12 |          1 | 31 Mar 2014 | 26 Feb 2014 | 28 Mar 2014 |
|      13 |          1 | 30 Apr 2014 | 29 Mar 2014 | 24 Apr 2014 |
|      14 |          1 | 31 May 2014 | 25 Apr 2014 | 28 May 2014 |
|      15 |          1 | 30 Jun 2014 | 29 May 2014 | 27 Jun 2014 |
|      16 |          1 | 31 Jul 2014 | 28 Jun 2014 | 27 Jul 2014 |
|      17 |          1 | 31 Aug 2014 | 28 Jul 2014 | 26 Aug 2014 |
|      18 |          1 | 30 Sep 2014 | 27 Aug 2014 | 29 Sep 2014 |
|      19 |          1 | 31 Oct 2014 | 30 Sep 2014 | 26 Oct 2014 |
|      20 |          1 | 30 Nov 2014 | 27 Oct 2014 | 24 Nov 2014 |
|      21 |          1 | 31 Dec 2014 | 25 Nov 2014 | 22 Dec 2014 |
|      22 |          1 | 31 Jan 2015 | 23 Dec 2014 | 25 Jan 2015 |
|      23 |          1 | 28 Feb 2015 | 26 Jan 2015 | 25 Feb 2015 |
|      24 |          1 | 31 Mar 2015 | 26 Feb 2015 | 28 Mar 2015 |

Open in new window

produce by the following query:
WITH CTE AS (
            SELECT
                    MonthId
                  , DivisionId
                  , YearMonth
                  , row_number() over (ORDER BY YearDate) AS rn
                  , YearDate
            FROM uvw_CutOffDate
            )
SELECT
  MonthId
, DivisionId
, CONVERT(VARCHAR(11),YearMonth,13) AS YearMonth
, CONVERT(VARCHAR(11),StartDate,13) AS StartDate
, CONVERT(VARCHAR(11),EndDate,13)   AS EndDate
FROM (

      SELECT
              ct1.MonthId
            , ct1.DivisionId
            , ct1.YearMonth
            , isnull(DATEADD(DAY,1,ct2.YearDate),dateadd(DAY, -(DAY(ct1.YearDate)-1), ct1.YearDate))  AS StartDate
            , ct1.YearDate AS EndDate
      FROM CTE AS ct1
      LEFT JOIN CTE AS ct2 ON ct1.rn = ct2.rn+1

     ) AS X
;
	
-- http://sqlfiddle.com/#!3/db57b/19

Open in new window

0
 

Author Closing Comment

by:vikas_nm
ID: 39997343
Thank you
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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 this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

589 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