Solved

SQL CTE

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Write a function 5 25
SQL 2012 and SQL 2014 in memory database 11 32
Retention Policy for Backups 1 13
Log Backup 2 13
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
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.

759 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

23 Experts available now in Live!

Get 1:1 Help Now