Solved

SQL CTE

Posted on 2014-04-13
2
416 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 48

Accepted Solution

by:
PortletPaul earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

943 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now