Equipment up time query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Equipment up time query

Author

Topic

danielqk
Starting Member

4 Posts

Posted - 2008-02-03 : 20:32:57

I have a table with equipment ID, transaction ID, date of transaction. How can I write a sql so that the result is a list of

equipment with % up time during 2007.

Transaction ID: 100 is down, 200 is up. Example is below the table

Equipment ID Transaction ID Date

1 200 1/12/2007

2 100 2/12/2007

1 100 2/25/2007

3 100 3/10/2007

2 200 3/14/2007

1 200 5/02/2007

3 200 7/13/2007

From the table I know that equiment 2 was down for 32 days and up time % during 2007 is (365-32)/365*100

How can I write a sql in order to achieve that?
Can I use IF THEN or CASE in my query?

Daniel

sql learner

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-04 : 08:53:34

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
EqupID INT,
Trans INT,
Date DATETIME
)

SET DATEFORMAT MDY

INSERT @Sample
SELECT 1, 200, '1/12/2007' UNION ALL
SELECT 2, 100, '2/12/2007' UNION ALL
SELECT 1, 100, '2/25/2007' UNION ALL
SELECT 3, 100, '3/10/2007' UNION ALL
SELECT 2, 200, '3/14/2007' UNION ALL
SELECT 1, 200, '5/02/2007' UNION ALL
SELECT 3, 200, '7/13/2007'

DECLARE @theWantedYear SMALLINT, -- User supplied parameter
@FromDate DATETIME,
@ToDate DATETIME,
@TotalDays SMALLINT

SELECT @theWantedYear = 2007,
@FromDate = DATEADD(YEAR, @theWantedYear - 1900, '19000101'),
@ToDate = DATEADD(YEAR, 1, @FromDate),
@TotalDays = DATEDIFF(DAY, @FromDate, @ToDate)

-- Show the expected output
;WITH Yak (EqupID, Trans, Date)
AS (
SELECT w.EqupID,
100,
@FromDate
FROM (
SELECT EqupID,
Trans,
ROW_NUMBER() OVER (PARTITION BY EqupID ORDER BY Date) AS RecID
FROM @Sample
) AS w
WHERE w.RecID = 1
AND w.Trans = 200

UNION ALL

SELECT EqupID,
Trans,
Date
FROM @Sample
), Peso (EqupID, Trans, Date, Grp)
AS (
SELECT EqupID,
Trans,
Date,
(ROW_NUMBER() OVER (PARTITION BY EqupID ORDER BY Date) - 1) / 2 AS Grp
FROM Yak
)

SELECT e.EqupID,
@TotalDays AS DaysInYear,
@TotalDays - e.DownDays AS UpDays,
e.DownDays,
1.0 * (@TotalDays - e.DownDays) / @TotalDays AS UpTime
FROM (
SELECT k.EqupID,
SUM(DATEDIFF(DAY, k.DownDay, k.UpDay)) AS DownDays
FROM (
SELECT EqupID,
MAX(CASE WHEN Trans = 100 THEN Date ELSE '19000101' END) AS DownDay,
MAX(CASE WHEN Trans = 200 THEN Date ELSE '19000101' END) AS UpDay
FROM Peso
GROUP BY EqupID,
Grp
) AS k
GROUP BY k.EqupID
) AS e
ORDER BY e.EqupID[/code]

E 12°55'05.25"
N 56°04'39.16"

danielqk
Starting Member

4 Posts

Posted - 2008-02-05 : 09:41:14

Thanks for the complicate codes
It is hard to digest all
Can you please explain how it work?
Thanks again
daniel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-05 : 09:44:16

If earliest record for a Equipment is "UP" then I add a corresponding "DOWN" for the first of the current year.
If latest record for a Equipment is "DOWN" then I add a corresponding "UP" for the first of the following year.

Then I just add upp downtime and check that with number of days in current year.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-05 : 09:46:30

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
EqupID INT,
Trans INT,
Date DATETIME
)

SET DATEFORMAT MDY

INSERT @Sample
SELECT 4, 100, '12/15/2007' UNION ALL
SELECT 1, 200, '1/12/2007' UNION ALL
SELECT 2, 100, '2/12/2007' UNION ALL
SELECT 1, 100, '2/25/2007' UNION ALL
SELECT 3, 100, '3/10/2007' UNION ALL
SELECT 2, 200, '3/14/2007' UNION ALL
SELECT 1, 200, '5/02/2007' UNION ALL
SELECT 3, 200, '7/13/2007'

DECLARE @theWantedYear SMALLINT, -- User supplied parameter
@FromDate DATETIME,
@ToDate DATETIME,
@TotalDays SMALLINT

SELECT @theWantedYear = 2007,
@FromDate = DATEADD(YEAR, @theWantedYear - 1900, '19000101'),
@ToDate = DATEADD(YEAR, 1, @FromDate),
@TotalDays = DATEDIFF(DAY, @FromDate, @ToDate)

[green]-- Show the expected output[green]
;WITH Yak (EqupID, Trans, Date)
AS (
SELECT w.EqupID,
100,
@FromDate
FROM (
SELECT EqupID,
Trans,
ROW_NUMBER() OVER (PARTITION BY EqupID ORDER BY Date) AS RecID
FROM @Sample
) AS w
WHERE w.RecID = 1
AND w.Trans = 200

UNION ALL

SELECT EqupID,
Trans,
Date
FROM @Sample

UNION ALL

SELECT w.EqupID,
200,
@ToDate
FROM (
SELECT EqupID,
Trans,
ROW_NUMBER() OVER (PARTITION BY EqupID ORDER BY Date DESC) AS RecID
FROM @Sample
) AS w
WHERE w.RecID = 1
AND w.Trans = 100

), Peso (EqupID, Trans, Date, Grp)
AS (
SELECT EqupID,
Trans,
Date,
(ROW_NUMBER() OVER (PARTITION BY EqupID ORDER BY Date) - 1) / 2 AS Grp
FROM Yak
)

SELECT e.EqupID,
@TotalDays AS DaysInYear,
@TotalDays - e.DownDays AS UpDays,
e.DownDays,
1.0 * (@TotalDays - e.DownDays) / @TotalDays AS UpTime
FROM (
SELECT k.EqupID,
SUM(DATEDIFF(DAY, k.DownDay, k.UpDay)) AS DownDays
FROM (
SELECT EqupID,
MAX(CASE WHEN Trans = 100 THEN Date ELSE '19000101' END) AS DownDay,
MAX(CASE WHEN Trans = 200 THEN Date ELSE '19000101' END) AS UpDay
FROM Peso
GROUP BY EqupID,
Grp
) AS k
GROUP BY k.EqupID
) AS e
ORDER BY e.EqupID[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources