SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generate Months from unique records with different date ranges


Generate Months from unique records with different date ranges

Author
Message
Kyalo
Kyalo
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 19
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
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13434 Visits: 4077
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;-)
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13434 Visits: 4077
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;-)
Kyalo
Kyalo
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 19
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
Jason-299789
Jason-299789
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5031 Visits: 3232
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
Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13434 Visits: 4077
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;-)
Jason-299789
Jason-299789
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5031 Visits: 3232
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
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95967 Visits: 38978
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Kyalo
Kyalo
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 19
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.
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18033 Visits: 6431
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search