Need some help.

My Table is like below->

Here I have only one project but in real time I have multiple projects.

In this table Recorded_Hours is total hours done per day, Planned_Hours is total hours planned per day and Remaining_Hours is ( Planned_Hours - Recorded_Hours ). I want to sum all the Remaining_Hours till today then distribute that sum evenly to each future Planned_Hours.

In this above table total sum of Remaining_Hours till today is 12 and there is 5 Days left to complete this project. I want to add 12/5 to each 5 Days left Planned_Hours. This should automatically calculate everyday.

My output will be ->

Till now I have reached to this point

SELECT 
 [Project_Name]= CASE WHEN GROUPING(Date) = 0 THEN [Project_Name]ELSE 'Total' END,
  Date,
  [Remaining_Hours] = SUM([Planned_Hours]-[Recorded_Hours])
FROM[dbo].[Projects]

GROUP BY GROUPING SETS (
    ([Project_Name], Date),
    ([Project_Name]));

CodePudding user response：

First, you find Total Remaining Hours up to today for each project (GROUP BY)

select Project, Total_Remaining_Hours = sum(Remaining_Hours),
from   Projects
where  [Date] <= @today 
group by Project

Then you also required no of remaining days

select Project, Remaining_Days = count(*)
from   Projects
where  [Date] > @today 
group by Project

Combining it all together (INNER JOIN) and use CASE expression to check, ignore past date and only adjust the future dates

declare @today date = getdate();

select *,
       New_Planned_Hours = case when p.[Date] <= @today
                                then p.Planned_Hours
                                else p.Planned_Hours 
                                     (t.Total_Remaining_Hours / d.Remaining_Days)
                                end
from   Projects p
       inner join
       (
          select Project,
                 Total_Remaining_Hours = sum(Remaining_Hours)
          from   Projects
          where  [Date] <= @today
          group by Project
       ) t  on p.Project = t.Project
       inner join
       (
          select Project,
                 Remaining_Days = count(*)
          from   Projects
          where  [Date] > @today
          group by Project
       ) d  on p.Project = d.Project
order by p.Project, p.[Date]

db<>fiddle demo