Get the total usage (in percent) between two specified dates I have table at SQL server 2017:



Number 1 represent the test equipment, it is connecting and 0 is disconnected. 
need your help to define query that can give me the amount of usage (in percentage) to :


Connect Statistics In accordance with the selected range of DateTime in 
specific "DeviceName" and "PcName"
Running Statistics In accordance with the selected range of DateTime 
in specific "PcName"


Thank you for your help

 USE [LabDevices]  
  GO
  INSERT INTO [dbo].[Statistics]
               ([DeviceName]
               ,[ConnectStatistics]
               ,[RunningStatistics]
               ,[DateTime]
               ,[PcName])
         VALUES
               (&lt;DeviceName, varchar(50),&gt;
               ,&lt;ConnectStatistics, bit,&gt;
               ,&lt;RunningStatistics, bit,&gt;
               ,&lt;DateTime, datetime,&gt;
               ,&lt;PcName, varchar(50),&gt;)
  GO
  INSERT INTO [dbo].[Statistics] (DeviceName,ConnectStatistics,RunningStatistics,DateTime,PcName)
    VALUES ('Regatron_1', 1 , 0 , '2019-02-27 08:00:00','PC1'),
           ('Regatron_1', 1 , 1 , '2019-02-27 08:10:00','PC1'),
           ('Regatron_1', 1 , 0 , '2019-02-27 08:50:00','PC1'),
           ('Regatron_1', 1 , 1 , '2019-02-27 09:20:00','PC1'),
           ('Regatron_1', 0 , 0 , '2019-02-27 09:40:00','PC1'),
           ('Regatron_2', 1 , 0 , '2019-02-27 08:00:00','PC2'),
           ('Regatron_2', 1 , 1 , '2019-02-27 08:10:00','PC2'),
           ('Regatron_2', 1 , 0 , '2019-02-27 08:50:00','PC2'),
           ('Regatron_2', 1 , 1 , '2019-02-27 09:20:00','PC2'),
           ('Regatron_2', 0 , 0 , '2019-02-27 09:40:00','PC2')


Hi

First thank for your help

Let me clarify the question and expected result:

The query should generate the used time for each device in percent

In case the selected time is between '2019-02-27 08:00:00' to '2019-02-27 09:40:00' -> time range is 1.4

Regatron_1_ConnectStatistics -> from 8:00 to 09:40 = 1.4

Regatron_1_RunningStatistics -> from 08:10 to 08:50 and 09:20 to 09:40 = 1

Regatron_2_ConnectStatistics -> from 8:00 to 09:40 = 1.4

Regatron_2_RunningStatistics -> from 08:10 to 08:50 and 09:20 to 09:40 = 1 The answer:

Regatron_1_ConnectStatistics(%) = (1.4/1.4)*100

Regatron_1_RunningStatistics(%) = (1/1.4)*100

Regatron_2_ConnectStatistics(%) = (1.4/1.4)*100

Regatron_2_RunningStatistics(%) = (1/1.4)*100

Can you help to define the correct query?

Thanks
if I understand your requirment correctly your command should be like this, if not please provide example result.

Select DeviceName, (Count(ConnectStatistics)* 100 / (Select Count(*) From [dbo].[Statistics])) as Score, pcname
From [dbo].[Statistics]
where datetime = '2019-02-27 09:40:00'
Group By DeviceName,pcname