I have some data such as this:
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)
Select * from @D
I'd like to prep this data for a bell curve, and not certain about the formula, the Bell Curve weight should peak at 75% of the way during the timeline, not the exact center.
This needs to be calculated based across the Id field, and the ProjAmt Field should start and end at zero, and have the predominate "bell" project amount around the 4/5/21 timeframe (the approximate 75% timeline factor).
What would be the formula in TSQL to convert this data appropriately ?
I'm trying to convert the flat line data noted above so that the bulk of the data appears 75% of the way into the timeline and the starting and ending points are zero, similar to the example here in this spreadsheet. I believe the formula would pertain to something similar to a Bell Curve formula the exact results aren't in green - it is the concept: weight most of the period amounts into the 75% portion of the timeline. How would I do this ?



