Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Query to get period

Answered Query to get period

  • Saturday, May 12, 2012 2:54 AM
     
     
    Sign In to Vote
    0

    Hi

    here is an example,


    Lease start date  08/15/2005  
    Lease End  08/15/2016

    Current year : 2012
    Current Month :  5
    trying to get  current lease period and until eof lease

    query result should be like


    Lease period based on current year

    From

    08/15/2011  -   08/14/2012
    08/15/2012  -   08/14/2013
    08/15/2013  -   08/14/2014
    08/15/2014  -   08/14/2015
    08/15/2015  -   08/14/2016


    Is this possible without stp?

    Thanks
    V


    • Edited by Vaishu Saturday, May 12, 2012 2:56 AM correction
    •  
     

All Replies

  • Saturday, May 12, 2012 3:44 AM
     
     
    Sign In to Vote
    2

    You could work the below query :

    DECLARE

    @TABLE TABLE



    (

    startDate date ,EndDate date)

    DECLARE

    @STARTDate DATE


    if

    GETDATE()<  CONVERT(date ,'08/15/'+cast(YEAR(getdate())as varchar (10)))

    select

    @STARTDate= CONVERT(date ,'08/15/'+cast((YEAR(getdate())-1 )as varchar (10)))

    else


    select

    @STARTDate=CONVERT(date ,'08/15/'+cast((YEAR(getdate()))as varchar (10)))

    while

    (@STARTDate <='08/15/2015')

    begin

    insert

    into @TABLE



    select

    @STARTDate , dateADD(D, -1 ,DATEADD(YEAR,1,@STARTDate))



    SET

    @STARTDate=DATEADD(YEAR,1, @STARTDATE)

    END

    SELECT

    * FROM @TABLE

    Shehap (DB Consultant/DB Architect)

    Think More deeply of DB Stress Stabilities


    • Edited by Shehap Saturday, May 12, 2012 3:47 AM
    •  
     
  • Saturday, May 12, 2012 9:18 AM
     
     Answered Has Code
    Sign In to Vote
    0
     
    -- Prepare sample data
DECLARE	@LeaseStartDate DATE = '20050815',
	@LeaseEndDate DATE = '20160815',
	@CurrentYear SMALLINT = 2012,
	@CurrentMonth TINYINT = 5;

-- SwePeso
;WITH cteSource(StartDate, EndDate, CurrentStart, CurrentEnd)
AS (
	SELECT	DATEADD(YEAR, Number, @LeaseStartDate) AS StartDate,
		DATEADD(YEAR, Number + 1, DATEADD(DAY, -1, @LeaseStartDate)) AS EndDate,
		DATEADD(MONTH, 12 * @CurrentYear + @CurrentMonth - 22801, '19000101') AS CurrentStart,
		DATEADD(MONTH, 12 * @CurrentYear + @CurrentMonth - 22801, '19000131') AS CurrentEnd
	FROM	master.dbo.spt_values
	WHERE	[Type] = 'P'
		AND Number BETWEEN 0 AND DATEDIFF(YEAR, @LeaseStartDate, @LeaseEndDate)
)
SELECT	StartDate,
	EndDate
FROM	cteSource
WHERE	(
		EndDate >= CurrentStart
		OR EndDate >= CurrentEnd
	)
	AND EndDate <= @LeaseEndDate

    N 56°04'39.26"
    E 12°55'05.63"