Number of hours active for a week

arunc · June 21, 2019, 9:48am

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

harishgg1 · June 21, 2019, 10:31am

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

arunc · June 21, 2019, 10:43am

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

harishgg1 · June 21, 2019, 10:49am

i think i am understanding your logic arun

i am working on it

harishgg1 · June 21, 2019, 11:19am

hi arun

i think i got it

please check my SQL and results

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

arunc · June 21, 2019, 11:21am

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

harishgg1 · June 21, 2019, 12:53pm

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)

arunc · June 24, 2019, 9:06am

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

harishgg1 · June 24, 2019, 9:26am

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

arunc · June 24, 2019, 9:28am

Hi Harish,

Yes now it works!..

Thanks,
Arun

harishgg1 · June 24, 2019, 9:29am

NJOY Arunc

what do you do ???? just curious

arunc · June 27, 2019, 7:13am

Hi Harish,

I am currently a technical consultant ..

Thanks,
Arun

harishgg1 · June 27, 2019, 1:03pm

Are you in the U.S.A ??

Justify · June 28, 2019, 11:03am

It's very interesting.

arunc · July 3, 2019, 10:14am

Hi Harish,

I am in India...

Thanks,
Arun