July 23, 2015 at 11:36 pm
Hallo,
I am trying to calculate the number of hours a device has been used and I cant find how...Any help guys...I need a query that calculated and does an average of the number of hrs used in a week
July 24, 2015 at 12:13 am
kmamenya (7/23/2015)
Hallo,I am trying to calculate the number of hours a device has been used and I cant find how...Any help guys...I need a query that calculated and does an average of the number of hrs used in a week
Hello, and welcome to SSC. It's impossible to answer your question without knowing the structure of your tables.
If you would like a working, tested solution to your problem, please take the time to read the first link in my signature & post your problem in the way it describes.
July 24, 2015 at 12:22 am
USE Device
GO
DECLARE @deviceMinBatt TABLE
(
IMEI VARCHAR (50),
BattDate DATETIME,
MinBatt FLOAT,
[State] VARCHAR (50)
)
DECLARE @deviceMaxBatt TABLE
(
IMEI VARCHAR (50),
BattDate DATETIME,
MaxBatt FLOAT,
[State] VARCHAR (50)
)
DECLARE @deviceState TABLE
(
IMEI VARCHAR(50),
[State] VARCHAR(50)
)
DECLARE @deviceValid TABLE
(
IMEI VARCHAR (50),
[State] VARCHAR (50)
)
DECLARE @deviceUse TABLE
(
IMEI VARCHAR (50),
Usage FLOAT,
[State] VARCHAR (50)
)
INSERT INTO @deviceMinBatt
SELECT CSN.Imei, CONVERT(DATE, CSN.DateGeneratedOnDevice, 2), MIN(CSN.BatteryVoltage), NULL
FROM dbo.Notifications CSN
WHERE CONVERT(DATE, CSN.DateGeneratedOnDevice, 11) > '07-12-15' AND CONVERT(DATE, CSN.DateGeneratedOnDevice, 11) < '07-18-15'
GROUP BY CSN.Imei, CONVERT(DATE, CSN.DateGeneratedOnDevice, 2)
INSERT INTO @deviceMaxBatt
SELECT CSN.Imei, CONVERT(DATE, CSN.DateGeneratedOnDevice, 2), MAX(CSN.BatteryVoltage), NULL
FROM dbo.Notifications CSN
WHERE CONVERT(DATE, CSN.DateGeneratedOnDevice, 11) > '07-12-15' AND CONVERT(DATE, CSN.DateGeneratedOnDevice, 11) < '07-18-15'
GROUP BY CSN.Imei, CONVERT(DATE, CSN.DateGeneratedOnDevice, 2)
INSERT INTO @deviceValid
SELECT Tempy.Imei, NULL
FROM (
SELECT dms.Imei , MIN(dMb.MaxBatt-dms.MinBatt) As Usage
FROM @deviceMinBatt dms
INNER JOIN @deviceMaxBatt dMb ON dms.IMEI = dMb.IMEI AND dms.BattDate = dMb.BattDate
GROUP BY dms.Imei
) Tempy
WHERE Tempy.Usage >100
INSERT INTO @deviceState
SELECT CSN.Imei, NULL
FROM dbo.Notifications CSN
Where Imei IN
(
)
INSERT INTO @deviceUse
SELECT dms.Imei, SUM(dMb.MaxBatt-dMs.MinBatt), NULL
FROM @deviceMinBatt dms
INNER JOIN @deviceMaxBatt dMb ON dms.IMEI = dMb.IMEI AND dms.BattDate = dMb.BattDate
GROUP BY dms.Imei
SELECT dUs.Imei, dUs.Usage
FROM @deviceUse dUs
INNER JOIN @deviceState dST ON dST.IMEI = dUs.IMEI
INNER JOIN @deviceValid dVd ON dVd.Imei = DUs.Imei
GROUP BY dUs.Imei, dUs.Usage
ORDER BY dUs.Imei DESC
July 24, 2015 at 12:50 am
If I were to run this in my local SSMS, it would fail.
July 24, 2015 at 1:04 am
Why
July 24, 2015 at 1:18 am
I do not have dbo.Notifications.
Did you read the link in my signature?
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply