SQLTeam.com | Weblogs | Forums

Number of hours active for a week

I am trying to calculate the number of hours and the percentage of the time when an item is active for a week...The total hour in a week is 24*7=168 ..The item is active when the new_status becomes 1 and the the item becomes inactive when the new_status changes to other than 1...The idea is to calculate the number of hours and the percentage of the hours the item is active ..The period for consideration is June 1st to June 7th 2019... Can you please help here...The DDL is below:Also I have shared the snapshot of output below....The problem that I am facing is that For item ID 4213 and in general consideration for a weeks timeframe when the item becomes deactive (new_status=2) after the starting of the week say the 3rd as is the case with 4213 then the meaning is that the item was active fro the starting of the week which is 1st and 2nd and almost half a day on the 3rd which is an addition of 3600 minutes active that makes it total of 6600 minutes of active state for item 4213...But I am getting the active number of minutes as only 3000 for 4213..

Current Code
create table #temp1
(itemid varchar(30),
timett datetime2,
old_status int,
new_status int)

insert into #temp1 values
('4213','2019-06-03 11:56:41.360','1','2'),
('4213','2019-06-05 11:56:41.360','2','1'),
('4213','2019-06-07 13:56:41.360','1','3'),
('5214','2019-06-01 09:56:41.360','3','1'),
('5214','2019-06-02 09:56:41.360','1','2'),
('5214','2019-06-05 09:56:41.360','2','1'),
('7896','2019-06-02 06:26:42.717','2','1'),
('7896','2019-06-02 06:56:42.717','1','2'),
('7896','2019-06-02 08:56:42.717','2','1'),
('7896','2019-06-02 09:20:42.717','1','2'),
('7896','2019-06-03 09:20:42.717','2','1'),
('7896','2019-06-03 09:50:42.717','1','2');

DECLARE @totalWeekMinutes INT = 24760;

WITH CTE_Active
as
(
SELECT itemid, timett, DATEPART(dw, timett) ActiveDW
FROM #temp1
WHERE new_status = 1
),
CTE_Deactive
AS
(
SELECT a.itemid,
a.timett 'ActiveOn',
CASE WHEN o.timett IS NULL AND ActiveDW < 7 THEN CAST(DATEADD(DAY,7-ActiveDW,a.timett) as DATE) --Days except Sunday
WHEN o.timett IS NULL AND ActiveDW = 7 THEN CAST(DATEADD(DAY,7,a.timett) as DATE) --Sunday
ELSE o.timett
END 'DeactiveOn'
FROM CTE_Active a
OUTER APPLY (SELECT TOP 1 itemid, timett FROM #temp1 t WHERE a.itemid = t.itemid and a.timett < t.timett and t.old_status = 1) o),
CTE_IndividualSpans
AS
(
SELECT
itemid,
ActiveOn,
DeactiveOn,
DATEDIFF(MINUTE, ActiveOn, DeactiveOn) 'ElapsedMinutes'
FROM CTE_Deactive)
--select * from CTE_IndividualSpans
SELECT
itemid,
SUM(ElapsedMinutes) 'TotalMinutes',
CAST(SUM(ElapsedMinutes) as FLOAT) / @totalWeekMinutes 'ActiveRate'
FROM CTE_IndividualSpans
GROUP BY itemid

The expected output is

itemid TotalMinutes ActiveRate
4213 6600 0.6547
5214 5164 0.512301587
7896 84 0.008333333

The output that I am getting currently is

itemid TotalMinutes ActiveRate
4213 3000 0.297619048
5214 5164 0.512301587
7896 84 0.008333333

Can you please help here..

Thanks,
Arun

hi arun

Looks like additional details .. logic is not clear !!!!

when i try to do this also
i am "also" getting 4213 ... 3000

the reason is the way i understood what you were saying

The item is active
when the "new_status" becomes 1
and the the item becomes inactive when the "new_status" changes to other than 1

i wrote SQL based on this

SQL ...
SELECT *, 
       CASE 
         WHEN new_status = 1 
              AND Lead(new_status) 
                    OVER( 
                      ORDER BY itemid, timett) <> 1 THEN 
         Datediff(minute, timett, Lead(timett) 
                                    OVER( 
                                      ORDER BY itemid, timett)) 
       END 
FROM   #temp1 
ORDER  BY itemid, 
          timett

image

Hi Harishgg1,

Appreciate your response!..What I am looking is if you observe the first row of input 4213 2019-06-03 11.56.41.36000000 Old_status 1 New_status 2 ...this item became inactive on the 3rd june ( New_status is the current status and the old_status is the status prior to the change...status 2 or anything else is inactive ....Now the time period when i am trying to get the number of hours active is june 1st to 7th (in general a week)...Now if an item becomes inactive on the 4th then it means it was active on the 1st ,2nd and 3rd..so those hours need to be added to that items active status...Which is why I am having problems for getting the number of active hours status for 4213

i think i am understanding your logic arun

i am working on it

:slight_smile: :slight_smile:

hi arun

i think i got it

please check my SQL and results
:slight_smile: :slight_smile:

please give your feedback

drop create data ....
create table #temp1
(itemid varchar(30),
timett datetime2,
old_status int,
new_status int)

insert into #temp1 values
('4213','2019-06-03 11:56:41.360','1','2'),
('4213','2019-06-05 11:56:41.360','2','1'),
('4213','2019-06-07 13:56:41.360','1','3'),
('5214','2019-06-01 09:56:41.360','3','1'),
('5214','2019-06-02 09:56:41.360','1','2'),
('5214','2019-06-05 09:56:41.360','2','1'),
('7896','2019-06-02 06:26:42.717','2','1'),
('7896','2019-06-02 06:56:42.717','1','2'),
('7896','2019-06-02 08:56:42.717','2','1'),
('7896','2019-06-02 09:20:42.717','1','2'),
('7896','2019-06-03 09:20:42.717','2','1'),
('7896','2019-06-03 09:50:42.717','1','2');
go
SQL ..
;WITH cte 
     AS (SELECT *, 
                Row_number() 
                  OVER( 
                    partition BY itemid 
                    ORDER BY timett) AS rn 
         FROM   #temp1), 
     abc_cte 
     AS (SELECT itemid, 
                CASE 
                  WHEN rn = 1 
                       AND new_status <> 1 THEN Datediff(minute, '2019-06-01', 
                                                timett 
                                                ) 
                END AS a, 
                CASE 
                  WHEN new_status = 1 
                       AND Lead(new_status) 
                             OVER( 
                               ORDER BY itemid, timett) <> 1 THEN 
                  Datediff(minute, timett, Lead(timett) 
                                             OVER( 
                                               ORDER BY itemid, timett)) 
                END b, 
                CASE 
                  WHEN rn = Max(rn) 
                              OVER( 
                                partition BY itemid) 
                       AND new_status = 1 THEN 
                  Datediff(minute, timett, '2019-06-08') 
                END c 
         FROM   cte) 
SELECT itemid, 
       Sum(Isnull(a, 0)) + Sum(Isnull(b, 0)) 
       + Sum(Isnull(c, 0)) 
FROM   abc_cte 
GROUP  BY itemid 

go

image

Thanks Harishgg1!...Will get back to you...

may be here i need to add extra clause

change
case when new_status = 1
to
case when new_status = 1 and rn <> max(rn)

Hi Harish,

I am getting the following error on your code...Can you pls advise..

Msg 8120, Level 16, State 1, Line 33
Column 'cte.itemid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Thanks,
Arun

hi arunc

i am not getting that error !!!

dont do this ...dont do this ...

just use this SQL code i gave in the begining

SQL ..
;WITH cte 
     AS (SELECT *, 
                Row_number() 
                  OVER( 
                    partition BY itemid 
                    ORDER BY timett) AS rn 
         FROM   #temp1), 
     abc_cte 
     AS (SELECT itemid, 
                CASE 
                  WHEN rn = 1 
                       AND new_status <> 1 THEN Datediff(minute, '2019-06-01', 
                                                timett 
                                                ) 
                END AS a, 
                CASE 
                  WHEN new_status = 1 
                       AND Lead(new_status) 
                             OVER( 
                               ORDER BY itemid, timett) <> 1 THEN 
                  Datediff(minute, timett, Lead(timett) 
                                             OVER( 
                                               ORDER BY itemid, timett)) 
                END b, 
                CASE 
                  WHEN rn = Max(rn) 
                              OVER( 
                                partition BY itemid) 
                       AND new_status = 1 THEN 
                  Datediff(minute, timett, '2019-06-08') 
                END c 
         FROM   cte) 
SELECT itemid, 
       Sum(Isnull(a, 0)) + Sum(Isnull(b, 0)) 
       + Sum(Isnull(c, 0)) 
FROM   abc_cte 
GROUP  BY itemid 

go

Hi Harish,

Yes now it works!..

Thanks,
Arun

NJOY Arunc :slight_smile: :slight_smile:

what do you do ???? just curious

Hi Harish,

I am currently a technical consultant ..

Thanks,
Arun

Are you in the U.S.A ??

It's very interesting.

Hi Harish,

I am in India...

Thanks,
Arun