InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 21:46:40 GMT20http://www.sqlservercentral.com/Forums/Topic1403405-391-1.aspxThank you Dwain, this works like Magic and I don't have to create a calendar....much appreciatedMon, 11 Feb 2013 17:49:03 GMTKyalohttp://www.sqlservercentral.com/Forums/Topic1403405-391-1.aspx[quote][b]Jason-299789 (1/8/2013)[/b][hr]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.[/quote]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.Tue, 08 Jan 2013 00:54:16 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1403405-391-1.aspxId 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.Tue, 08 Jan 2013 00:44:14 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1403405-391-1.aspx[quote][b]Jason-299789 (1/8/2013)[/b][hr]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. [/quote]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.Tue, 08 Jan 2013 00:22:22 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1403405-391-1.aspxNice 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. Tue, 08 Jan 2013 00:18:01 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1403405-391-1.aspxIf you don't want to create a calendar table, you can do this (which really just creates the calendar table on the fly).[code="sql"]CREATE TABLE #Contracts( ContractNumber INT ,ContractStartDate DATETIME ,ContractEndDate DATETIME ,ContractAmount MONEY);INSERT INTO #ContractsVALUES (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 MyContractsGROUP BY ContractNumberDROP TABLE #Contracts[/code]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.Mon, 07 Jan 2013 18:15:57 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1403405-391-1.aspxOk 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.Mon, 07 Jan 2013 15:18:58 GMTKyalohttp://www.sqlservercentral.com/Forums/Topic1403405-391-1.aspx[quote][b]Kyalo (1/7/2013)[/b][hr]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 attributesContracts 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[/quote]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.Mon, 07 Jan 2013 06:29:35 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1403405-391-1.aspxI 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. [code="sql"] 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') ) ;[/code]The Insert into this is simply[code="sql"]Insert into #CalendarSelect FullDateFrom atable--Or Insert into #CalendarSelect DateAdd(d,N,@Base)From Tally[/code]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.Mon, 07 Jan 2013 02:52:56 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1403405-391-1.aspx[quote][b]Jason-299789 (1/7/2013)[/b][hr]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..[/quote] Do you have any link for this ? for Calendar tableMon, 07 Jan 2013 02:37:45 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1403405-391-1.aspxWelcome 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.[code="sql"]/* 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 #CalendarSelect 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) YearMonthFrom Nums nWhere n<10000/* populate the Contract Table */Insert into #ContractValues (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/MonthsFrom #Data d JOIN Cte_Months M on M.ContractNumber=d.ContractNumber[/code]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.Mon, 07 Jan 2013 01:47:53 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1403405-391-1.aspxThank 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 attributesContracts 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 helpsMon, 07 Jan 2013 01:43:43 GMTKyalohttp://www.sqlservercentral.com/Forums/Topic1403405-391-1.aspx[quote][b]Kyalo (1/6/2013)[/b][hr]Contract Number 1234, contract startdate 1/3/2012, contract enddate 30/09/2012, contract amount $5000[/quote] And how will you show output result for aboveMon, 07 Jan 2013 01:20:28 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1403405-391-1.aspxi 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 [url]http://blog.sqlauthority.com/2008/05/22/sql-server-pivot-table-example/[/url] Mon, 07 Jan 2013 00:24:25 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1403405-391-1.aspxHelloI 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 monthSo for example I have two contracts:A) Contract Number 1234, contract startdate 1/7/2012, contract enddate 30/11/2012, contract amount $5000B) Contract Number 5678, contract startdate 1/7/2012, contract enddate 30/06/2013, contract amount $12000What I would like to show for both is:-[b]ContractNo Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun [/b]1234 $1000 $1000 $1000 $1000 $10005678 $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000 $1000Does anyone know of a way to make this calculation in SQL /T-SQLAny help is highly appreciatedRegards KyaloSun, 06 Jan 2013 22:30:34 GMTKyalo