SQL CTE

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
vikas_nmAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor Commented:
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
 
vikas_nmAuthor Commented:
Thank you
0
All Courses

From novice to tech pro — start learning today.