Function that will select value from SQL table and do a sum based on another column value-CodePudding

I need a query to sum up the hours in a table. Which hours need to be selected depends on the Start Date column. If the start date is in the past, then it should take Remaining Hours, if it's in the future it should take Budgeted Hours.

Start Date	Budgeted Hours	Remaining Hours
Jan 1, 2022	15	3
Feb 1, 2022	12	0
Mar 1, 2022	14	6
Apr 1, 2022	15	13

In the example above the summed up hours should be 24 (3 0 6 15).

CodePudding user response：

You don't need a function for this, just a CASE expression:

DECLARE @now datetime = GETDATE();

SELECT SUM
(
  CASE WHEN [Start Date] < @now 
  THEN [Remaining Hours]
  ELSE [Budgeted Hours] 
  END
)
FROM dbo.tablename;

CodePudding user response：

We can use the function case

create table ops(
startDate date,
Budgeted int,
Remaining int);

insert into ops values
('2022-01-01',15,3),
('2022-02-01',12,0),
('2022-03-01',14,6),
('2022-04-01',15,13),
('2022-05-01',10,18);

select
  startDate,
  Budgeted,
  Remaining,
  case when startDate < getDate()
    then Remaining
  else Budgeted
  end Hours
from ops;

startDate  | Budgeted | Remaining | Hours
:--------- | -------: | --------: | ----:
2022-01-01 |       15 |         3 |     3
2022-02-01 |       12 |         0 |     0
2022-03-01 |       14 |         6 |     6
2022-04-01 |       15 |        13 |    15
2022-05-01 |       10 |        18 |    10

db<>fiddle here