Home > Mobile >  How to calculate the sum of a column and distribute evenly to another columns each data in SQL?
How to calculate the sum of a column and distribute evenly to another columns each data in SQL?

Time:03-11

Need some help.

My Table is like below->

Project_Name Date Recorded_Hours Planned_Hours Remaining_Hours
Civil 06-03-2022 5 10 5
Civil 07-03-2022 3 7 4
Civil 08-03-2022 9 9 0
Civil 09-03-2022 4 10 6
Civil 10-03-2022 8 5 -3
Civil 11-03-2022 8 8 0
Civil 12-03-2022 0 5 -5
Civil 13-03-2022 0 4 -4
Civil 14-03-2022 0 3 -3
Civil 15-03-2022 0 4 -4
Civil 15-03-2022 0 5 -5

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 ->

Project_Name Date Recorded_Hours Planned_Hours Remaining_Hours
Civil 06-03-2022 5 10 5
Civil 07-03-2022 3 7 4
Civil 08-03-2022 9 9 0
Civil 09-03-2022 4 10 6
Civil 10-03-2022 8 5 -3
Civil 11-03-2022 8 8 0
Civil 12-03-2022 0 7.4 -7.4
Civil 13-03-2022 0 6.4 -6.4
Civil 14-03-2022 0 5.4 -5.4
Civil 15-03-2022 0 6.4 -6.4
Civil 15-03-2022 0 7.4 -7.4

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

  • Related