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

Please check if this solution solve your needs:

;With MyCTE AS(
 select 
 prop_id,  min_calc_date,  max_calc_date,
 Sum(fuel_rate)  total 
 from  meter1
 group by  prop_id, min_calc_date, max_calc_date
)
SELECT 
 prop_id,  min_calc_date
 ,  max_calc_date = ISNULL(DATEADD(DAY,-1, LEAD  (min_calc_date) OVER (PARTITION BY prop_id order by prop_id, min_calc_date, max_calc_date)), max_calc_date)
 , total  
FROM MyCTE
GO

Note! This solution based on assumptions. For example it assume that your ranges are not overlapping. overlapping ranges make the scenario more complex. It assume that you works with DATEs and not DATE and Time (which mean you should change the DDL code and use DATE for the table) - using this assumption I could use "DAY" in order to get the previous max value.

Answer 2

Please try the following solution.

-- DDL and data sample population, start
DECLARE @meter TABLE 
 (prop_id int, 
  min_calc_date date, 
  max_calc_date date,
  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);
-- DDL and data sample population, end

;WITH rs AS 
(
 SELECT *
 , series = ROW_NUMBER() OVER (PARTITION BY prop_id ORDER BY min_calc_date) - 
 (ROW_NUMBER() OVER (PARTITION BY prop_id, CAST(min_calc_date AS CHAR(10)) 
 + CAST(max_calc_date AS CHAR(10)) ORDER BY min_calc_date))
 FROM @meter
)
SELECT prop_id, MIN(min_calc_date) AS Date_Start, MAX(max_calc_date) AS Date_End
 , SUM(fuel_rate) AS fuel_rate_sum
FROM rs
GROUP BY prop_id, series
ORDER BY prop_id, MIN(min_calc_date) ASC, MAX(max_calc_date);

Output

+---------+------------+------------+---------------+
| prop_id | Date_Start |  Date_End  | fuel_rate_sum |
+---------+------------+------------+---------------+
|     123 | 2020-01-01 | 2020-08-19 |           120 |
|     123 | 2020-01-30 | 2020-08-19 |            20 |
|     456 | 2020-01-01 | 2020-08-18 |            70 |
|     678 | 2020-01-01 | 2020-08-10 |            60 |
|     678 | 2020-01-01 | 2020-08-19 |            30 |
+---------+------------+------------+---------------+

Answer 3

Hello, @Sri Kotte ,

Can you explain the output for 678

678 01/01/2020 08/09/2020 60
678 08/10/2020 08/19/2020 30 (10+20)

Not

678 01/01/2020 08/10/2020 60
678 08/11/2020 08/19/2020 30 (10+20)

Answer 4

Good day @Sri Kotte

Unfortunately, no one can solve your request at this time as it make no sense

You base your request on the assumption that your rows has this order:

123	2020-01-01 00:00:00.000	2020-08-19 00:00:00.000	50  
123	2020-01-01 00:00:00.000	2020-08-19 00:00:00.000	60  
123	2020-01-01 00:00:00.000	2020-08-19 00:00:00.000	10  

But the fact is that SQL Server do not guarantee the order of the columns unless you use "ORDER BY ___".

All the three rows above can returns in different order each time that you execute the query. This mean that in some cases we will get the "fuel_rate" in the order of 50->60->10 which fit your requirement, but in other cases we might get the returned rows in the order of 10-60-50 which will lead to totally different result!

In order to solve your request, you must have a clear way to sort the data. For example, if you had an identifier column which us the property "identity" then we could move to the next issue with your request

Issue 2: missing information. Please clarify if you always have 2 rows for each range (mining you always have one row for the min_Calc_date followed by a row with the max_Calc_date, or maybe you might get multiple rows in each range

In the first case (always one row for min_Calc_date followed by a row for max_Calc_date) the solution is much simpler. In the more flexible case, you will need to find the border of the ranges first - this is type of question called "gaps and islands" and there are many toturials on how to solve questions like this.

We only need to clarify what is your case, and again... we first need to have information to sort the data, or you entire request make no sense

Answer 5

 select prop_id,  min_calc_date,  max_calc_date,
  Sum(fuel_rate)  total from  @meter 
group by  prop_id, min_calc_date, max_calc_date