Min function in sql server is not working properly giving 0

Question

i want to substract the todays max value form the todays min value . im getting the max value properly but min value getting as 0. how to substract todays min value from max value of kvarh.

Data type kvarh - float, devicetimestamp - datetime

code

   select(
  SELECT MAX(kvarh)
  FROM [Transformer].[dbo].[Total_Power]
 WHERe DeviceTimeStamp < GETDATE()
 and DEVICEIMEI = '868996')
 -
  (SELECT MIN(kvarh)
  FROM [Transformer].[dbo].[Total_Power]
 WHERe DeviceTimeStamp < GETDATE()
 and DEVICEIMEI = '868996')
  as Difference

sample data

     A_id   DeviceImei  DeviceTimeStamp           KWH       KVARH       
    985302  868996     2020-10-16 10:30:30.000  36682.5     424107.1    
    985298  868996     2020-10-16 10:15:31.000  36678.94    424103.3    
    985296  8689960    2020-10-16 10:00:29.000  36675.88    424099.8    

Answer 1

I have check your query and it is working fine. You can reduce it a little bit like this:

DECLARE @DataSource TABLE
(
    [A_id] INT  
   ,[DeviceImei]  INT
   ,[DeviceTimeStamp] DATETIME
   ,[KWH] DECIMAL(9,2)
   ,[KVARH] DECIMAL(9,2)
);

INSERT INTO @DataSource ([A_id], [DeviceImei], [DeviceTimeStamp], [KWH], [KVARH])
VALUES (985302, 868996, '2020-10-16 00:30:30.000', 36682.5, 424107.1)
      ,(985298, 868996, '2020-10-16 00:15:31.000', 36678.94, 424103.3)
      ,(985296, 8689960, '2020-10-16 00:00:29.000', 36675.88, 424099.8);


SELECT MAX(kvarh) - MIN(kvarh) AS Difference
FROM @DataSource
WHERE DeviceTimeStamp < GETDATE()
   and DEVICEIMEI = '868996';

The MIN function will not return 0 with this sample data - if no rows are found you will get NULL as a result. You will get 0 only when there is only one row matching your condition, because you will substract the same value.

For example:

DECLARE @DataSource TABLE
(
    [A_id] INT  
   ,[DeviceImei]  INT
   ,[DeviceTimeStamp] DATETIME
   ,[KWH] DECIMAL(9,2)
   ,[KVARH] DECIMAL(9,2)
);

INSERT INTO @DataSource ([A_id], [DeviceImei], [DeviceTimeStamp], [KWH], [KVARH])
VALUES (985302, 868996, '2020-10-16 00:30:30.000', 36682.5, 424107.1)
      --,(985298, 868996, '2020-10-16 00:15:31.000', 36678.94, 424103.3)
      ,(985296, 8689960, '2020-10-16 00:00:29.000', 36675.88, 424099.8);


SELECT MAX(kvarh) - MIN(kvarh) AS Difference
FROM @DataSource
WHERE DeviceTimeStamp < GETDATE()
   and DEVICEIMEI = '868996';