CODEWITHSUNDEEP
sqlsql-server
legollas007
legollas007

Reputation: 193

Sum data from one table if date range is in another table

In SQL Server I have two query results like this:

enter image description here

table 2

I would like to use this 2 queries and then SUM the Seconds of the second table (Mains - Charging) if that specific device (43 in this case) was connected in the date range of the first table for that period using the left and right date limits. So SUM only if that condition is met.

Any suggestion on how I can accomplish this ?

This is the table script and some sample data:

First table script:

CREATE TABLE firstTable(
   DeviceId     INTEGER  NOT NULL
  ,Date         DATE  NOT NULL
  ,DisplayValue VARCHAR(16) NOT NULL
  ,LeftLimit    DATETIME NOT NULL
  ,RigthLimit   DATETIME NOT NULL
  ,Seconds      INTEGER  NOT NULL
);
INSERT INTO firstTable(DeviceId,Date,DisplayValue,LeftLimit,RigthLimit,Seconds) VALUES (43,'2017-06-27','Connected','2017-06-27 00:00:00.000','2017-06-27 10:17:54.460',37074);
INSERT INTO firstTable(DeviceId,Date,DisplayValue,LeftLimit,RigthLimit,Seconds) VALUES (43,'2017-06-27','Connected','2017-06-27 10:17:54.460','2017-06-27 10:17:56.293',2);
INSERT INTO firstTable(DeviceId,Date,DisplayValue,LeftLimit,RigthLimit,Seconds) VALUES (43,'2017-06-27','Connected','2017-06-27 10:17:56.293','2017-06-27 10:17:56.330',0);
INSERT INTO firstTable(DeviceId,Date,DisplayValue,LeftLimit,RigthLimit,Seconds) VALUES (43,'2017-06-27','Connected','2017-06-27 10:17:56.330','2017-06-27 10:17:57.430',1);
INSERT INTO firstTable(DeviceId,Date,DisplayValue,LeftLimit,RigthLimit,Seconds) VALUES (43,'2017-06-27','Connected','2017-06-27 10:17:57.430','2017-06-27 10:17:57.440',0);
INSERT INTO firstTable(DeviceId,Date,DisplayValue,LeftLimit,RigthLimit,Seconds) VALUES (43,'2017-06-27','Connected','2017-06-27 10:17:57.440','2017-06-27 10:17:58.637',1);
INSERT INTO firstTable(DeviceId,Date,DisplayValue,LeftLimit,RigthLimit,Seconds) VALUES (43,'2017-06-27','Connected','2017-06-27 10:17:58.637','2017-06-27 10:17:58.783',0);
INSERT INTO firstTable(DeviceId,Date,DisplayValue,LeftLimit,RigthLimit,Seconds) VALUES (43,'2017-06-27','Connected','2017-06-27 10:17:58.783','2017-06-27 10:17:59.657',1);
INSERT INTO firstTable(DeviceId,Date,DisplayValue,LeftLimit,RigthLimit,Seconds) VALUES (43,'2017-06-27','Connected','2017-06-27 10:17:59.657','2017-06-27 10:17:59.903',0);
INSERT INTO firstTable(DeviceId,Date,DisplayValue,LeftLimit,RigthLimit,Seconds) VALUES (43,'2017-06-27','Connected','2017-06-27 10:17:59.903','2017-06-27 10:18:00.977',1);
INSERT INTO firstTable(DeviceId,Date,DisplayValue,LeftLimit,RigthLimit,Seconds) VALUES (43,'2017-06-27','Connected','2017-06-27 10:18:00.977','2017-06-27 10:18:01.027',1);
INSERT INTO firstTable(DeviceId,Date,DisplayValue,LeftLimit,RigthLimit,Seconds) VALUES (43,'2017-06-27','Connected','2017-06-27 10:18:01.027','2017-06-27 10:18:01.517',0);

Second table script:

    CREATE TABLE secondTable(
   DeviceId     INTEGER  NOT NULL
  ,Date         DATE  NOT NULL
  ,DisplayValue VARCHAR(16) NOT NULL
  ,LeftLimit    DATETIME NOT NULL
  ,RigthLimit   DATETIME NOT NULL
  ,Seconds      INTEGER  NOT NULL
);
INSERT INTO secondTable(DeviceId,Date,DisplayValue,LeftLimit,RigthLimit,Seconds) VALUES (43,'2017-06-27','Mains - Charging','2017-06-27 00:00:00.000','2017-06-27 10:17:57.523',37077);
INSERT INTO secondTable(DeviceId,Date,DisplayValue,LeftLimit,RigthLimit,Seconds) VALUES (43,'2017-06-27','Mains - Charging','2017-06-27 10:17:57.523','2017-06-27 10:17:59.883',2);
INSERT INTO secondTable(DeviceId,Date,DisplayValue,LeftLimit,RigthLimit,Seconds) VALUES (43,'2017-06-27','Mains - Charging','2017-06-27 10:17:59.883','2017-06-27 10:17:59.953',0);
INSERT INTO secondTable(DeviceId,Date,DisplayValue,LeftLimit,RigthLimit,Seconds) VALUES (43,'2017-06-27','Mains - Charging','2017-06-27 10:17:59.953','2017-06-27 10:18:01.043',2);
INSERT INTO secondTable(DeviceId,Date,DisplayValue,LeftLimit,RigthLimit,Seconds) VALUES (43,'2017-06-27','Mains - Charging','2017-06-27 10:18:01.043','2017-06-27 10:18:01.103',0);
INSERT INTO secondTable(DeviceId,Date,DisplayValue,LeftLimit,RigthLimit,Seconds) VALUES (43,'2017-06-27','Mains - Charging','2017-06-27 10:18:01.103','2017-06-27 10:18:01.553',0);
INSERT INTO secondTable(DeviceId,Date,DisplayValue,LeftLimit,RigthLimit,Seconds) VALUES (43,'2017-06-27','Mains - Charging','2017-06-27 10:18:01.553','2017-06-27 10:18:01.630',0);

Expected Result:

CREATE TABLE expectedResult(
   DeviceId     INTEGER  NOT NULL
  ,LeftLimit    DATETIME NOT NULL
  ,RigthLimit   DATETIME NOT NULL
  ,TotalSeconds INTEGER  NOT NULL
);
INSERT INTO expectedResult(DeviceId,LeftLimit,RigthLimit,TotalSeconds) VALUES (43,'2017-06-27 00:00:00.000','2017-06-27 10:18:02.630',37081);

Expected Result table:

+----------+-------------------------+-------------------------+--------------+
| DeviceId |        LeftLimit        |       RightLimit        | TotalSeconds |
+----------+-------------------------+-------------------------+--------------+
|       43 | 2017-06-27 00:00:00.000 | 2017-06-27 10:18:02.630 |        37081 |
+----------+-------------------------+-------------------------+--------------+

Upvotes: 1

Views: 886

Answers (2)

ihamlin
ihamlin

Reputation: 180

SELECT DISTINCT
 ST.[DeviceId],
 MIN(CASE WHEN FT.[LeftLimit] > ST.[LeftLimit] THEN FT.[LeftLimit] ELSE ST.[LeftLimit] END) AS [LeftLimit],
 MAX(CASE WHEN FT.[RigthLimit] < ST.[RigthLimit] THEN FT.[RigthLimit] ELSE ST.[RigthLimit] END) AS [RigthLimit],
 SUM(DATEDIFF(
  SECOND,
  CASE WHEN FT.[LeftLimit] > ST.[LeftLimit] THEN FT.[LeftLimit] ELSE ST.[LeftLimit] END,
  CASE WHEN FT.[RigthLimit] < ST.[RigthLimit] THEN FT.[RigthLimit] ELSE ST.[RigthLimit] END)) AS [TotalSeconds]
FROM firstTable FT
INNER JOIN secondTable ST ON ST.[DeviceId] = FT.[DeviceId] AND (FT.[LeftLimit] <= ST.[RigthLimit] AND ST.[LeftLimit] <= FT.[RigthLimit])
GROUP BY ST.[DeviceId]

Join the first table on the second, but only where the date ranges overlap. Then take the min left and max right limit and sum.

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31785

JOIN the two tables ON the LeftLimit from the 2nd table being greater than the MIN LeftLimit from the 1st table, AND the RightLimit from the 2nd table being less than the MAX RightLimit from the 1st table. And also on DeviceId of course.

You'll also need to CAST all your limit columns as datetimes so that they compare correctly.

Upvotes: 0

Related Questions