Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Generate Months from unique records with different date ranges Expand / Collapse
Author
Message
Posted Sunday, January 6, 2013 10:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 5:31 PM
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
Post #1403405
Posted Monday, January 7, 2013 12:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 2:36 AM
Points: 2,840, Visits: 3,970
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
Post #1403436
Posted Monday, January 7, 2013 1:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 2:36 AM
Points: 2,840, Visits: 3,970
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
Post #1403445
Posted Monday, January 7, 2013 1:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 5:31 PM
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
Post #1403471
Posted Monday, January 7, 2013 1:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 3, 2014 9:25 AM
Points: 895, Visits: 2,432
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
Post #1403473
Posted Monday, January 7, 2013 2:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 2:36 AM
Points: 2,840, Visits: 3,970
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
Post #1403497
Posted Monday, January 7, 2013 2:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 3, 2014 9:25 AM
Points: 895, Visits: 2,432
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
Post #1403501
Posted Monday, January 7, 2013 6:29 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:17 AM
Points: 20,739, Visits: 32,524
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.



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)
Post #1403570
Posted Monday, January 7, 2013 3:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 5:31 PM
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.
Post #1403893
Posted Monday, January 7, 2013 6:15 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!
Post #1403938
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse