Hello, I asked a question a few weeks ago on retrieving data for TSQL Bell Curve, and very much appreciate the answer.
Here is the original question:
https://docs.microsoft.com/en-us/answers/questions/268142/prep-data-formula-for-a-bell-curve.html
Here was the response:
declare @D table (ID varchar(7), ProjDate Date, ProjAmt numeric(19,5))
insert Into @D(ID, ProjDate, ProjAmt) values
('C2147', '2021-01-01', 6.60869565217391),
('C2147', '2021-01-04', 6.60869565217391),
('C2147', '2021-01-11', 6.60869565217391),
('C2147', '2021-01-18', 6.60869565217391),
('C2147', '2021-01-25', 6.60869565217391),
('C2147', '2021-02-01', 6.60869565217391),
('C2147', '2021-02-08', 6.60869565217391),
('C2147', '2021-02-15', 6.60869565217391),
('C2147', '2021-02-22', 6.60869565217391),
('C2147', '2021-03-01', 6.60869565217391),
('C2147', '2021-03-08', 6.60869565217391),
('C2147', '2021-03-15', 6.60869565217391),
('C2147', '2021-03-22', 6.60869565217391),
('C2147', '2021-03-29', 6.60869565217391),
('C2147', '2021-04-05', 6.60869565217391),
('C2147', '2021-04-12', 6.60869565217391),
('C2147', '2021-04-19', 6.60869565217391),
('C2147', '2021-04-26', 6.60869565217391),
('C2147', '2021-05-03', 6.60869565217391),
('C2147', '2021-05-10', 6.60869565217391),
('C2147', '2021-05-17', 6.60869565217391),
('C2147', '2021-05-24', 6.60869565217391),
('C2147', '2021-05-31', 6.60869565217391)
---
declare @mindate as date = (select min(ProjDate) from @d)
;
with E1 as
(
select *
from @D
cross apply (values (datediff(day, @mindate, ProjDate))) t1(x)
),
E2 as
(
select cast(min(x) as float) as a, cast(max(x) as float) as c
from E1
),
E3 as
(
select distinct E2.*, cast(percentile_disc( 0.75 ) within group (order by x) over () as float) as b
from E1, E2
),
E4 as
(
select *,
power(x - a, alpha - 1 ) * power(c - x, beta - 1) /
(dbo.BetaApprox(alpha, beta) * power(c - a, alpha + beta - 1)) as val
from E1, E3
cross apply (values (1 + 4 * (b - a) / (c - a), 1 + 4 * (c - b) / (c - a))) t1(alpha, beta)
)
select ID, ProjDate, val * 100 / max(val) over (partition by null) as ProjAmt, replicate('*', val * 1000)
from E4
order by ProjDate
--Helper Function
create or alter function dbo.BetaApprox( @x float, @y float )
returns float
as begin
return sqrt(2 * pi()) * power(@x, @x - 0.5) * power(@y, @y - 0.5) /
power(@x + @y, @x + @y - 0.5)
end
My question is: What would be the appropriate parameter to make the density of the Bell Curve Sharper or more Broad?
How can I account for changing a variable to change the density of the bell curve, so that it is more steep or more gradual ?
Thank you so very much
