Solved

SQL CTE

Posted on 2014-04-13
2
450 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 49

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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

In this article I will describe the Detach & Attach 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.
In this article I will describe the Backup & Restore 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.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

626 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