Populating Sales figures by contract length

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Populating Sales figures by contract length

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2014-01-09 : 05:06:43

Hey guys
I need some help
Il try my best to explain the situation
Aim – Take the “One Month Figures”, look at the revenue start date, and calculate the sales by the “Contract Length”, broken down by the year in the “Revenue_State_Date”.

For Example
As the Revenue_State_Date starts on 01/01/2015, you have 12months of full trading in 2015, so you need to calculate 12 months of sales,
This needs to be spread out over the contract length

For eg
Year Sales Months
2015 £2,914,033.33 12
2016 £2,914,033.33 12
2017 £2,914,033.33 12
2018 £2,914,033.33 12
2018 £1,457,016.67 6
Total £13,113,150.00 54

My query is

SELECT
[Total_Contract_Value__c] as TCV,
[Contract_Term__c] As Contract_Length,
[Revenue_Start_Date__c] as Revenue_State_Date,
[Total_Contract_Value__c]/[Contract_Term__c] as One_Month_Sales
FROM [FDMS].[Dan].[Raz_Reporting_LCS]
where ID = '006a0000015qcGsAAI'

Which produces following results

TCV £13,113,150.00
Contract_Length 54
Revenue_State_Date 01/01/2015
One Month Figures £242,836.11

Really Hoping you can help

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2014-01-09 : 11:36:00

The way i got around this is

SELECT
[Name],
Case
when name IS not null then 'New Business - New customer' end as Type,
case
when [BoS_Owner__c] = '00530000007YJGoAAO' then 'Lorraine Barker'
when [BoS_Owner__c] = '00530000003UUg8AAG' then 'Richard Thomas'
when [BoS_Owner__c] = 'NULL' then 'Inactive'
when [BoS_Owner__c] = '00530000007689NAAQ' then 'Liam Hale'
when [BoS_Owner__c] = '00530000003UXTxAAO' then 'Matthew Prior'
when [BoS_Owner__c] = '00530000003UUgCAAW' then 'Andrew Sheridan'
when [BoS_Owner__c] = '005a0000007lj23AAA' then 'Andy Brooks'
when [BoS_Owner__c] = '00530000004lJe3AAE' then 'Brian Geary'
when [BoS_Owner__c] = '0053000000596uTAAQ' then 'Daniel Mason'
when [BoS_Owner__c] = '00530000003UgEsAAK' then 'Pierick Coustumer'
end as 'Opp_Owner',
[StageName],
[CloseDate],
[Revenue_Start_Date__c] as Revenue_Start_Date,
[Implementation_Start_Date__c] as Impl_Date,
[Total_Contract_Value__c] as TCV_GBP,
[Total_Contract_Value__c]/[Contract_Term__c] as GBP_1_Month_Sales,
[EBITDA__c] as Ebitda_Perc,
[Contract_Term__c] as Term,
convert(decimal,([Total_Contract_Value__c]) * (1.58423)) as TCV_USD,
Case when
[Total_Contract_Value__c] * (1.58423)>= '1000000' then 'Over 1M USD' else 'Under 1M USD' end as Indicator,
DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]) as Contract_End_Date,

Case
when year(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c])) < '2014' then 0
when year([Revenue_Start_Date__c]) = '2014' and year(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))<> '2014' then DATEDIFF(M,[Revenue_Start_Date__c],'2015-01-01')
when year([Revenue_Start_Date__c]) < '2014' and year (DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))<> '2014' then 12
When year([Revenue_Start_Date__c]) < '2014' and year (DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))= '2014' then DATEDIFF (m,'2014-01-01',(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c])))
When year([Revenue_Start_Date__c]) ='2014' and year(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))= '2014' then DATEDIFF (m,[Revenue_Start_Date__c],(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c])))
else 0 end As [2014],

Case
when year(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c])) < '2015' then 0
when year([Revenue_Start_Date__c]) = '2015' and year(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))<> '2015' then DATEDIFF(M,[Revenue_Start_Date__c],'2016-01-01')
when year([Revenue_Start_Date__c]) < '2015' and year (DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))<> '2015' then 12
When year([Revenue_Start_Date__c]) < '2015' and year (DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))= '2015' then DATEDIFF (m,'2015-01-01',(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c])))
When year([Revenue_Start_Date__c]) ='2015' and year(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))= '2015' then DATEDIFF (m,[Revenue_Start_Date__c],(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c])))
else 0 end As [2015],

case
when year(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c])) < '2016' then 0
when year([Revenue_Start_Date__c]) = '2016' and year(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))<> '2016' then DATEDIFF(M,[Revenue_Start_Date__c],'2017-01-01')
when year([Revenue_Start_Date__c]) < '2016' and year (DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))<> '2016' then 12
When year([Revenue_Start_Date__c]) < '2016' and year (DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))= '2016' then DATEDIFF (m,'2016-01-01',(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c])))
When year([Revenue_Start_Date__c]) ='2016' and year(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))= '2016' then DATEDIFF (m,[Revenue_Start_Date__c],(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c])))
else 0 end As [2016],

case
when year(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c])) < '2017' then 0
when year([Revenue_Start_Date__c]) = '2017' and year(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))<> '2017' then DATEDIFF(M,[Revenue_Start_Date__c],'2018-01-01')
when year([Revenue_Start_Date__c]) < '2017' and year (DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))<> '2017' then 12
When year([Revenue_Start_Date__c]) < '2017' and year (DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))= '2017' then DATEDIFF (m,'2017-01-01',(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c])))
When year([Revenue_Start_Date__c]) ='2017' and year(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))= '2017' then DATEDIFF (m,[Revenue_Start_Date__c],(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c])))
else 0 end As [2017],

case
when year(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c])) < '2018' then 0
when year([Revenue_Start_Date__c]) = '2018' and year(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))<> '2018' then DATEDIFF(M,[Revenue_Start_Date__c],'2019-01-01')
when year([Revenue_Start_Date__c]) < '2018' and year (DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))<> '2018' then 12
When year([Revenue_Start_Date__c]) < '2018' and year (DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))= '2018' then DATEDIFF (m,'2018-01-01',(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c])))
When year([Revenue_Start_Date__c]) ='2018' and year(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))= '2018' then DATEDIFF (m,[Revenue_Start_Date__c],(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c])))
else 0 end As [2018],

case
when year(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c])) < '2019' then 0
when year([Revenue_Start_Date__c]) = '2019' and year(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))<> '2019' then DATEDIFF(M,[Revenue_Start_Date__c],'2020-01-01')
when year([Revenue_Start_Date__c]) < '2019' and year (DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))<> '2019' then 12
When year([Revenue_Start_Date__c]) < '2019' and year (DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))= '2019' then DATEDIFF (m,'2019-01-01',(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c])))
When year([Revenue_Start_Date__c]) ='2019' and year(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c]))= '2019' then DATEDIFF (m,[Revenue_Start_Date__c],(DATEADD(mm,convert(decimal,[Contract_Term__c],2),[Revenue_Start_Date__c])))
else 0 end As [2019]
FROM [FDMS].[Dan].[Raz_Reporting_LCS]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 07:00:31

you dont need to hardcode the year values like this. you can use number table to generate this output
see similar logic used here

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-10 : 07:04:59

so in your case it would be like


SELECT YEAR(DATEADD(mm,v.number,[Revenue_Start_Date__c])) AS DateVal,
SUM([Total_Contract_Value__c]*1.0/[Contract_Term__c]) AS SalesMonth,
COUNT(*) AS Months
FROM [FDMS].[Dan].[Raz_Reporting_LCS] t
CROSS JOIN master..spt_values v
where ID = '006a0000015qcGsAAI'
AND v.number BETWEEN 0 AND [Contract_Term__c]-1
AND v.type = 'p'
GROUP BY YEAR(DATEADD(mm,v.number,[Revenue_Start_Date__c]))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources