Help with Transact SQL

Question

Hello all,

I have a table with columns as below

DECLARE @meter TABLE
(prop_id int, 
 min_calc_date datetime, 
 max_calc_date datetime,
 fuel_rate  int
)

INSERT INTO @meter
VALUES 
(123,'01-01-2020','08-19-2020', 50)  ,
(123,'01-01-2020','08-19-2020', 60) ,
(123,'01-01-2020','08-19-2020', 10) ,
(123,'01-30-2020','08-19-2020', 20),
(456,'01-01-2020','08-18-2020', 30),
(456,'01-01-2020','08-18-2020', 40),
(678,'01-01-2020','08-19-2020', 10) ,
(678,'01-01-2020','08-19-2020', 20),
(678,'01-01-2020','08-10-2020', 60)

prop_id min_calc_date max_calc_date fuel_rate

123 01/01/2020 08/19/2020 50
123 01/01/2020 08/19/2020 60
123 01/01/2020 08/19/2020 10
123 01/30/2020 08/19/2020 20
456 01/01/2020 08/18/2020 30
456 01/01/2020 08/18/2020 40
678 01/01/2020 08/19/2020 10
678 01/01/2020 08/19/2020 20
678 01/01/2020 08/10/2020 60

So, here I need to calculate the sum of fuel_rate for each prop_id based on the min_Calc_date and max_calc_date range. Since we have two different min_Calc_date for prop_id = 123 (01/01/2020 and 01/30/2020) I will have two sets of sum of fuel_rate. one sum value will be between 01/01/2020 and 01/29/2020 and other sum value will be between 01/30/2020 and 08/19/2020 and same will be the case for prop_id = 678 one sum value will be between 01/01/2020 and 08/09/2020 and other sum value will be between 08/10/2020 and 08/19/2020.
This is the output I am expecting to see

prop_id min_calc_date max_calc_date fuel_rate

123 01/01/2020 01/29/2020 120 (50+60+10)
123 01/30/2020 08/19/2020 20
456 01/01/2020 08/18/2020 70 (30+40)
678 01/01/2020 08/09/2020 60
678 08/10/2020 08/19/2020 30 (10+20)

Could you please help me in achieving this?

Appreciate your help. Thank you so much

Answer 1

Hi SriKotte-6865,

The following code can achieve part of your needs：

create table meter   
 (prop_id int,   
  min_calc_date datetime,   
  max_calc_date datetime,  
  fuel_rate  int  
 )  
 INSERT INTO meter  
 VALUES   
 (123,'01-01-2020','08-19-2020', 50)  ,  
 (123,'01-01-2020','08-19-2020', 60) ,  
 (123,'01-01-2020','08-19-2020', 10) ,  
 (123,'01-30-2020','08-19-2020', 20),  
 (456,'01-01-2020','08-18-2020', 30),  
 (456,'01-01-2020','08-18-2020', 40),  
 (678,'01-01-2020','08-19-2020', 10),  
 (678,'01-01-2020','08-19-2020', 20),  
 (678,'01-01-2020','08-10-2020', 60)  
  
with cte1   
as(select *,max(min_calc_date) over(partition by prop_id) maxdate from meter)  
  ,cte2 as  
  (select prop_id,min_calc_date,  
          case when min_calc_date<>maxdate then dateadd(dd,-1,maxdate) else max_calc_date end max_calc_date,fuel_rate   
   from cte1)  
   ,cte3 as  
     (select prop_id,min_calc_date,max_calc_date,sum(fuel_rate ) fuel_rate from cte2  
   group by prop_id,min_calc_date,max_calc_date)  
select * from cte3  
  
drop table meter  

Min_calc_date with different prop_id should have the same rules,otherwise it is difficult to implement.So if your inserted data is slightly modified,change(678,'01-01-2020','08-19-2020', 10) ,(678,'01-01-2020','08-19-2020', 20), to (678,'08-10-2020','08-19-2020', 10),(678,'08-10-2020','08-19-2020', 20),then you can have the output you expect：

create table meter  
(prop_id int,  
min_calc_date datetime,  
max_calc_date datetime,  
fuel_rate int  
)  
INSERT INTO meter  
VALUES  
(123,'01-01-2020','08-19-2020', 50) ,  
(123,'01-01-2020','08-19-2020', 60) ,  
(123,'01-01-2020','08-19-2020', 10) ,  
(123,'01-30-2020','08-19-2020', 20),  
(456,'01-01-2020','08-18-2020', 30),  
(456,'01-01-2020','08-18-2020', 40),  
(678,'08-10-2020','08-19-2020', 10),  
(678,'08-10-2020','08-19-2020', 20),  
(678,'01-01-2020','08-10-2020', 60)  
  
 with cte1   
 as(select *,max(min_calc_date) over(partition by prop_id) maxdate from meter)  
   ,cte2 as  
   (select prop_id,min_calc_date,  
           case when min_calc_date<>maxdate then dateadd(dd,-1,maxdate) else max_calc_date end max_calc_date ,fuel_rate   
    from cte1)  
    ,cte3 as  
      (select prop_id,min_calc_date,max_calc_date,sum(fuel_rate ) fuel_rate from cte2  
       group by prop_id,min_calc_date,max_calc_date)  
 select * from cte3  
      
 drop table meter  

Best Regards
Echo