September 22, 2015 at 11:49 am
Hi I am trying to split the annual cost into monthly numbers based on the contract Period.
Since the contract period varies from company to company not sure how to implement the logic.
create table #Invoice
(
Company Varchar(50),
Startdate2015 DateTime,
EndDate2015 DateTime,
ContractPeriod2015 Int,
ContractAmount2015 Float,
)
insert into #Invoice values('Excel', '2015-05-22 00:00:00.000','2016-05-21 00:00:00.000',12,24000)
insert into #invoice values('Dummy', '2015-03-22 00:00:00.000','2016-03-21 00:00:00.000',36,48000)
Output Expected
create table #Output
(
Company Varchar(50),
May222015 INT,
Jun222015 FLOAT ,
Jul222015 FLOAT ,
Aug2220015 FLOAT ,
Sep222015 FLOAT ,
Oct222015 FLOAT ,
Nov222015 FLOAT ,
Dec222015 FLOAT ,
Jan222016 FLOAT ,
Feb222016 FLOAT ,
Mar222016 FLOAT ,
Aprl222016 FLOAT ,
May212016 FLOAT )
Insert INTO #Output values
('EXCEL', 2000,2000,2000,2000,2000,2000,2000,2000,2000,2000,2000,2000,2000)
September 22, 2015 at 2:02 pm
sharonsql2013 (9/22/2015)
Hi I am trying to split the annual cost into monthly numbers based on the contract Period.Since the contract period varies from company to company not sure how to implement the logic.
create table #Invoice
(
Company Varchar(50),
Startdate2015 DateTime,
EndDate2015 DateTime,
ContractPeriod2015 Int,
ContractAmount2015 Float,
)
insert into #Invoice values('Excel', '2015-05-22 00:00:00.000','2016-05-21 00:00:00.000',12,24000)
insert into #invoice values('Dummy', '2015-03-22 00:00:00.000','2016-03-21 00:00:00.000',36,48000)
Output Expected
create table #Output
(
Company Varchar(50),
May222015 INT,
Jun222015 FLOAT ,
Jul222015 FLOAT ,
Aug2220015 FLOAT ,
Sep222015 FLOAT ,
Oct222015 FLOAT ,
Nov222015 FLOAT ,
Dec222015 FLOAT ,
Jan222016 FLOAT ,
Feb222016 FLOAT ,
Mar222016 FLOAT ,
Aprl222016 FLOAT ,
May212016 FLOAT )
Insert INTO #Output values
('EXCEL', 2000,2000,2000,2000,2000,2000,2000,2000,2000,2000,2000,2000,2000)
It is not totally clear what you are trying to do here but I think that maybe you are looking for a dynamic crosstab perhaps???
Not really sure what the "Dummy" row is supposed to do since it isn't in the desired output.
The first challenge you have is generating the rows for all the months. This is pretty easy with a tally table. You can read more about them here. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
Here is the tally table in action to generate the rows you want.
if OBJECT_ID('tempdb..#Invoice') is not null
drop table #Invoice
create table #Invoice
(
Company Varchar(50),
Startdate2015 DateTime,
EndDate2015 DateTime,
ContractPeriod2015 Int,
ContractAmount2015 numeric(9,2)
);
insert into #Invoice values('Excel', '2015-05-22 00:00:00.000','2016-05-21 00:00:00.000',12,24000);
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select *
, ContractAmount2015 / ContractPeriod2015
, DATEADD(month, N - 1, StartDate2015)
from #Invoice i
join cteTally t on t.N <= ContractPeriod2015
;
From here you just have to add the crosstab. I am not sure from your post if this is static or dynamic. You can read about crosstabs by following the links in my signature.
Please post back if you can provide more details about the desired output or if you have any issues implementing this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 22, 2015 at 3:38 pm
That really helped. Thanks
September 22, 2015 at 7:44 pm
sharonsql2013 (9/22/2015)
That really helped. Thanks
Do you understand how it works and why?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply