Generate Months from unique records with different date ranges

  • Hello

    I have a database that records contracts and their amounts (each contract has a start and end date); each contract is unique.

    I am looking at a way of listing each contract and the months between the start and end date (for each contract) and the approximate amount commited per month

    So for example I have two contracts:

    A) Contract Number 1234, contract startdate 1/7/2012, contract enddate 30/11/2012, contract amount $5000

    B) Contract Number 5678, contract startdate 1/7/2012, contract enddate 30/06/2013, contract amount $12000

    What I would like to show for both is:-

    ContractNo Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun

    1234 $1000 $1000 $1000 $1000 $1000

    5678 $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000

    Does anyone know of a way to make this calculation in SQL /T-SQL

    Any help is highly appreciated

    Regards Kyalo

  • i seems that you are new to SSC .Welcome here.

    one suggestion.

    While posting to any forum you must include/post table defintion along with test data. it will help others to give you solution quickly.people devote their time from their busy schedules.sometimes they dont have time to frame everything on theri own.

    So please post some data plus table defintion.

    and now on your problem. you would be needed to play with pivot apprach here.

    see link http://blog.sqlauthority.com/2008/05/22/sql-server-pivot-table-example/

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Kyalo (1/6/2013)


    Contract Number 1234, contract startdate 1/3/2012, contract enddate 30/09/2012, contract amount $5000

    And how will you show output result for above

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Thank you Folks, yes I am brand new in this forum. From the two questions, it appears as if I have already included the suggested test data (the two examples).

    let us assume there is table named Contracts with the following attributes

    Contracts Number (varchar10)

    startdate (datetime)

    enddate (datetime)

    contractAmount (currency).

    In my previous post I have already identified what I would like the output to be formatted as.

    Hope this helps

  • Welcome to SSC, DDL would be nice but this is not really a difficult problem so I've mocked up the data to suit, but for future refrence the DDL (table defs) and sample data would be nice.

    You can solve this with a Calendar Table, once you have a Calendar table in place its simple to then run a Cross tab to get the values, in place.

    One minor issue could be if a contract starts and/or ends mid month, as you would need to split the contract value on a pro-rata basis for the partial months.

    eg Contract Start date : 21/07/2012, contract end date = 31/12/2012.

    which means that you may actually need to go to week or day level and then aggreate this back up to do the maths.

    This should get you part way there, the only thing missing is the Cross Tab/Pivot of the values.

    /*

    Set up

    */

    Declare @Base as Date='31-Dec-2000';

    Create Table #Data

    (

    ContractNumber int

    ,ContractStartDate Datetime

    ,ContractEndDate Datetime

    ,ContractAmount Money

    );

    Create Table #Calendar

    (

    CalendarDate datetime

    ,CalendarMonth tinyint

    ,CalendarDay tinyint

    ,CalendarWeek smallint

    ,YearMonth int

    );

    /*

    Create In line Tally Table

    */

    WITH

    L0 AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows

    L1 AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows

    L2 AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B), -- 16 rows

    L3 AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows

    L4 AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 rows

    L5 AS(SELECT 1 AS C FROM L4 AS A CROSS JOIN L4 AS B), -- 4,294,967,296 rows

    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5)

    /*

    Populate The Calendar table with

    */

    Insert into #Calendar

    Select Convert(Datetime,DateAdd(d,n,@Base)) CalendarDate

    ,DatePart(Month, DateAdd(d,n,@Base)) CalendarMonth

    ,DatePArt(d, DateAdd(d,n,@Base)) CalendarDay

    ,DatePArt(wk, DateAdd(d,n,@Base)) CalendarWeek

    ,convert(varchar(6),DateAdd(d,n,@Base),112) YearMonth

    From Nums n

    Where n<10000

    /*

    populate the Contract Table

    */

    Insert into #Contract

    Values (1234,'01-Jul-2012','30-Nov-2012',5000.00)

    ,(5678,'01-Jul-2012','30-jun-2013',12000.00)

    ;With Cte_Months

    AS

    (

    Select Distinct

    ContractNumber

    ,YearMonth

    ,DateDiff(m, d.ContractStartDate, d.ContractEndDate)+1 Months

    From #Data d

    JOIN #Calendar cal on Cal.CalendarDate Between d.ContractStartDate and d.ContractEndDate

    )

    Select

    d.ContractNumber

    ,YearMonth

    ,Months

    ,ContractAmount/Months

    From

    #Data d

    JOIN Cte_Months M on M.ContractNumber=d.ContractNumber

    the problem you will face with the Cross tab/Pivot out of the data is that the Number of columns is likley to be variable, unless you can hand on heart state that no contract will be greater than a number of months.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Jason-299789 (1/7/2013)


    You can solve this with a Calendar Table, once you have a Calendar table in place its simple to then run a Cross tab to get the values, in place..

    Do you have any link for this ? for Calendar table

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • I tend to have a generic calendar table build of which this is a cut down version.

    This is the one I tend to use as a prepopulation staging table for a Dim.Calendar, all I need to do is insert the Fulldate and everything is worked out for me.

    CREATE TABLE #Calendar --drop table #dates_to_add

    (

    FullDate DATETIME

    ,YearMonthDay AS Convert(varchar(8),FullDate,112)

    ,YearMonth AS Convert(varchar(6),FullDate,112)

    ,RetentionYear INT

    ,DayWeekNo AS DatePart(dw,FullDate)

    ,DayWeekCode AS CAST(LEFT(DATENAME(weekday,FullDate),3) as Char(3))

    ,DayWeekDesc AS CAST(DATENAME(weekday,FullDate) as Varchar(10))

    ,DayMonthNo AS CAST(DAY(FullDate) as SMALLINT)

    ,DayYearNo AS Cast(DatePart(dy,FullDate) AS SMALLINT)

    ,WeekNo AS CAST(DatePart(wk,FullDate) AS TINYINT)

    ,MonthNo AS CAST(MONTH(FullDate) AS TINYINT)

    ,MonthCode AS CAST(DATENAME(Month,FullDate) AS CHAR(3))

    ,MonthDesc AS CAST(DATENAME(Month,FullDate) AS VARCHAR(10))

    ,RetentionMonthNo INT

    ,QuarterNo AS CAST(DATEPART(qq,FullDate) as TINYINT)

    ,YearNo AS CAST(Year(FullDate) AS INT)

    ,IsMonthEnd AS CAST(CASE

    WHEN ( [FullDate] < '9999-11-30 00:00:00.000' ) -- avoid overflow with poor data in PHX

    THEN

    CASE CONVERT(DATE, [FullDate])

    WHEN CONVERT(DATE, DATEADD(S,-1,DATEADD(mm, DATEDIFF(m,0,[FullDate])+1,0))) THEN 1

    ELSE 0

    END

    ELSE 0

    END AS BIT)

    ,IsQuarterEnd AS CAST(CASE CONVERT(DATE, [FullDate])

    WHEN CONVERT(DATE, DATEADD(qq,DATEDIFF(qq,-1, [FullDate]),-1)) THEN 1

    ELSE 0

    END AS BIT)

    ,IsYearEnd AS CAST(CASE CONVERT(DATE, [FullDate])

    WHEN DATEADD(yy,DATEDIFF(yy,-1,[FullDate]),-1) THEN 1

    ELSE 0

    END AS BIT)

    ,IsWeekend AS CAST(CASE WHEN DateName(DW,FullDate) IN ('Saturday','Sunday') THEN 1 ELSE 0 END AS BIT)

    ,IsHoliday BIT DEFAULT (0)

    ,AggregationType VARCHAR(20) DEFAULT ('Period')

    )

    ;

    The Insert into this is simply

    Insert into #Calendar

    Select FullDate

    From atable

    --Or

    Insert into #Calendar

    Select DateAdd(d,N,@Base)

    From Tally

    Then loading the dim.Calendar from this table as part of the ETL.

    You could create this as a fixed table with all the calculated columns included but there may be a performance hit.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Kyalo (1/7/2013)


    Thank you Folks, yes I am brand new in this forum. From the two questions, it appears as if I have already included the suggested test data (the two examples).

    let us assume there is table named Contracts with the following attributes

    Contracts Number (varchar10)

    startdate (datetime)

    enddate (datetime)

    contractAmount (currency).

    In my previous post I have already identified what I would like the output to be formatted as.

    Hope this helps

    Actually, you didn't. What you provided requires that we take the time to create your table(s), create the insert statements to populate the table(s) with your data. We are all volunteers on this site helping others on our own time. The more you can do for us, the better answers you will get.

    Please read the article I reference below in my signature block regarding asking for help. It will show you what you should post and how to do it when posting questions in the future.

  • Ok Lynn, I see what you mean, I need to provide the actual DDL(create statements) and DML (insert statements) so that you can recreate the table(s) in your envinronment and then use it/them for working out a solution. will do so in the future.

    Apologies to Bhuvnesh and thanks to Jason for the suggested solution. I will give it a try today and post the outcome of my attempt.

  • If you don't want to create a calendar table, you can do this (which really just creates the calendar table on the fly).

    CREATE TABLE #Contracts

    (

    ContractNumber INT

    ,ContractStartDate DATETIME

    ,ContractEndDate DATETIME

    ,ContractAmount MONEY

    );

    INSERT INTO #Contracts

    VALUES (1234,'01-Jul-2012','30-Nov-2012',5000.00)

    ,(5678,'01-Jul-2012','30-jun-2013',12000.00)

    ;WITH StartMonth (sm, nm) AS (

    SELECT MIN(ContractStartDate)

    ,DATEDIFF(month, MIN(DATEADD(month, DATEDIFF(month, 0, ContractStartDate), 0))

    ,MAX(DATEADD(month, 1, DATEADD(month, DATEDIFF(month, 0, ContractEndDate), 0))-1))

    FROM #Contracts),

    Tally (n) AS (

    SELECT 0 UNION ALL

    SELECT TOP (SELECT nm FROM StartMonth) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM (VALUES ($),($),($),($),($),($),($),($),($),($)) a(n)

    CROSS JOIN (VALUES ($),($),($),($),($),($),($),($),($),($)) b (n)),

    MyContracts AS (

    SELECT ContractNumber

    ,ContractMonth

    ,ContractAmount=CASE WHEN ContractStartDate > ContractMonth OR

    ContractEndDate < ContractMonth THEN 0

    ELSE ContractAmount/(DATEDIFF(month, ContractStartDate, ContractEndDate)+1) END

    FROM #Contracts a

    CROSS APPLY Tally b

    CROSS APPLY (SELECT ContractMonth=DATEADD(month, n, ContractStartDate)) c)

    SELECT ContractNumber

    ,[Jul]=MAX(CASE WHEN MONTH(ContractMonth) = 7 THEN ContractAmount END)

    ,[Aug]=MAX(CASE WHEN MONTH(ContractMonth) = 8 THEN ContractAmount END)

    ,[Sep]=MAX(CASE WHEN MONTH(ContractMonth) = 9 THEN ContractAmount END)

    ,[Oct]=MAX(CASE WHEN MONTH(ContractMonth) = 10 THEN ContractAmount END)

    ,[Nov]=MAX(CASE WHEN MONTH(ContractMonth) = 11 THEN ContractAmount END)

    ,[Dec]=MAX(CASE WHEN MONTH(ContractMonth) = 12 THEN ContractAmount END)

    ,[Jan]=MAX(CASE WHEN MONTH(ContractMonth) = 1 THEN ContractAmount END)

    ,[Feb]=MAX(CASE WHEN MONTH(ContractMonth) = 2 THEN ContractAmount END)

    ,[Mar]=MAX(CASE WHEN MONTH(ContractMonth) = 3 THEN ContractAmount END)

    ,[Apr]=MAX(CASE WHEN MONTH(ContractMonth) = 4 THEN ContractAmount END)

    ,[May]=MAX(CASE WHEN MONTH(ContractMonth) = 5 THEN ContractAmount END)

    ,[Jun]=MAX(CASE WHEN MONTH(ContractMonth) = 6 THEN ContractAmount END)

    FROM MyContracts

    GROUP BY ContractNumber

    DROP TABLE #Contracts

    If you have an unknown number of months, you'd need to convert this to Dynamic SQL, making the column headers something like [mmm-yy] and modifying the MONTH = test within the CASE accordingly.

    You might note how I have normalized the contract start dates to the first of the month and the contract end dates to the end of the month.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Nice Alternative dwain, love the use of cross applys with the tally and inline month generator, as well as the normalisation of the month to always be the first.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Jason-299789 (1/8/2013)


    Nice Alternative dwain, love the use of cross applys with the tally and inline month generator, as well as the normalisation of the month to always be the first.

    Thank you sir!

    For some reason, I get a lot of pushback around here when I suggest using an actual Calendar table and I'm not sure why. I've even developed a nifty GenerateCalendar FUNCTION (that Jeff Moden took pity on and improved to warp speed) to take its place. Thought that was a bit much to post on this question although the technique is not dissimilar.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Id love to see the GenerateCalendar function.

    Coming from a DW background the persisted calendar table is second nature, and I'm surprised more traditional OLTP systems dont implement one as part of a standard build as its very low maintenance.

    At worst you might have to extend it and put in any regional public holiday's and business specific information (financial periods etc) as part of a once a year maintenance routine.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Jason-299789 (1/8/2013)


    Id love to see the GenerateCalendar function.

    Coming from a DW background the persisted calendar table is second nature, and I'm surprised more traditional OLTP systems dont implement one as part of a standard build as its very low maintenance.

    At worst you might have to extend it and put in any regional public holiday's and business specific information (financial periods etc) as part of a once a year maintenance routine.

    PM'd you with the FUNCTION.

    I think most of the push back I get is because of a lack of understanding. I meant, by the way, here at work and not on the forums where Calendar tables are well established.

    I like to keep holidays in a separate table that can be joined on the Calendar table when needed. The Calendar table is then like, set it and forget it. Only the holidays table then needs maintenance annually. And can also then be country or state/province specific.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Thank you Dwain, this works like Magic and I don't have to create a calendar....much appreciated

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply