October 11, 2011 at 12:44 pm
Hi all
I got a problem to calculate the power on status during a specific date range. Any help would be appreciated.
For example: I load into a table with these data following: Within a particular date, for example, I got two parameters @StartDate, @Enddate, calculate how many hours the Power is on for each ID. Note: Powerstatus=1 means powerOn ; Powerstatus=0 means poweroff
With the following example, If my @startDate='2011-07-03 12:45:33.570' @enddate='2011-07-03 19:45:33.570' The Result should be like
vm_id ; HoursOn
525 ;1
2323 ; 7
3319 ; 0
Declare @myTable Table (VMID Int, CREATETIME Datetime, PowerStatus Bit)
Insert Into @myTable
Select 525, '2011-07-03 18:45:33.570', 1 Union All
Select 525, '2011-07-03 19:48:41.520', 0 Union All
Select 2323, '2011-06-30 17:19:14.550', 0 Union All
Select 2323, '2011-06-30 17:20:13.627', 1 Union All
Select 2323, '2011-06-30 18:15:01.930', 0 Union All
Select 2323, '2011-06-30 18:59:23.970', 1 Union All
Select 3319, '2011-06-06 14:38:18.070', 0
October 11, 2011 at 12:46 pm
What queries have you tried so far?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 11, 2011 at 12:54 pm
How do you figure HoursOn for 2323 is 7?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
October 11, 2011 at 12:57 pm
toddasd (10/11/2011)
How do you figure HoursOn for 2323 is 7?
I come up with ~5h55m on that one.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 11, 2011 at 1:00 pm
SQLRNNR (10/11/2011)
toddasd (10/11/2011)
How do you figure HoursOn for 2323 is 7?I come up with ~5h55m on that one.
The lastest status for 2323 on 6/30/2011, the status is on after that day since no status change. So, within the given date range, ID2323 is powered on.
I just subtract the hours @EndDate-@StartDate=7 hours
October 11, 2011 at 1:21 pm
memostone86 (10/11/2011)
SQLRNNR (10/11/2011)
toddasd (10/11/2011)
How do you figure HoursOn for 2323 is 7?I come up with ~5h55m on that one.
The lastest status for 2323 on 6/30/2011, the status is on after that day since no status change. So, within the given date range, ID2323 is powered on.
I just subtract the hours @EndDate-@StartDate=7 hours
Step by step: latest status for 2323 is '2011-06-30 18:59:23.970'
No status change after that, so we use the end date in the range: '2011-07-03 19:45:33.570'
Nevermind, I got it. The range is '2011-07-03 12:45:33.570' to '2011-07-03 19:45:33.570' which ID2323 does not fall in the range, but it is powered on in the last status. So we're simply subtracting the range dates.
Ok, cool. So back to Jason's question.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
October 11, 2011 at 1:39 pm
SQLRNNR (10/11/2011)
What queries have you tried so far?
I am trying to create a calendar table with all datetime for the year 2011. And join calendar table and mytable.
But I got one problem is: The following is calendar of 2011 without time, If I want to create datetime, system spt_values has limited numbers only. Should I create a numbers table and fill this calendar datetime table?
DECLARE @Calendar TABLE
(
MyDate date
)
-- Load the Calendar Table for all of 2011
INSERT INTO @Calendar
SELECT DATEADD(DD, s.number, '2011-01-01')
FROM master..spt_values AS s
WHERE s.type = 'P'
AND s.Number < 355;
October 11, 2011 at 1:55 pm
I separated the recordset into power on times and power off times. Then join those on the ID where the power off times come after the power on times. Then it is a matter of using the createtime from the table or the range date provided. Here is what I was able to come up with in the time I have. Be forewarned, it may need some tweaking.
Declare @myTable Table (VMID Int, CREATETIME Datetime, PowerStatus Bit)
Insert Into @myTable
Select 525, '2011-07-03 18:45:33.570', 1 Union All
Select 525, '2011-07-03 19:48:41.520', 0 Union All
Select 2323, '2011-06-30 17:19:14.550', 0 Union All
Select 2323, '2011-06-30 17:20:13.627', 1 Union All
Select 2323, '2011-06-30 18:15:01.930', 0 Union All
Select 2323, '2011-06-30 18:59:23.970', 1 Union All
Select 3319, '2011-06-06 14:38:18.070', 0
declare @startDate datetime
declare @enddate datetime
set @startDate='2011-07-03 12:45:33.570'
set @enddate='2011-07-03 19:45:33.570'
select * from (
select a.vmid,
datediff(MI,
case when a.CREATETIME < @startDate then @startDate else a.CREATETIME end,
case when b.CREATETIME < @enddate then b.CREATETIME else @enddate end)/60.0 hours
from (
select VMID, CREATETIME
from @myTable
where PowerStatus = 1
) a
left join (
select VMID, CREATETIME
from @myTable
where PowerStatus = 0
) b on a.VMID = b.VMID and a.CREATETIME < b.CREATETIME) x
where x.hours > 0
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
October 11, 2011 at 2:27 pm
Thank you so much Toddasd, it works great
October 11, 2011 at 2:52 pm
memostone86 (10/11/2011)
Thank you so much Toddasd, it works great
Actually, it doesn't work correctly. It assumes that there is only one change in the power status for each VM in a given time frame. This may be reasonable for short time frames, but it is unlikely to hold up over longer periods.
Furthermore, it uses hidden RBAR, so there's likely to be a huge performance hit as your table grows.
It also doesn't include rows where the power status was continuously off during the specified time frame.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 12, 2011 at 2:28 am
For that wise , we could come up with that DML query :
Declare @myTable Table (VMID Int, CREATETIME Datetime, PowerStatus Bit)
Insert Into @myTable
Select 525, '2011-07-03 18:45:33.570', 1 Union All
Select 525, '2011-07-03 19:48:41.520', 0 Union All
Select 2323, '2011-06-30 17:19:14.550', 0 Union All
Select 2323, '2011-06-30 17:20:13.627', 1 Union All
Select 2323, '2011-06-30 18:15:01.930', 0 Union All
Select 2323, '2011-06-30 18:59:23.970', 1 Union All
Select 3319, '2011-06-06 14:38:18.070', 0
create table #temptable (VMID float , Starttime datetime, Endtime datetime )
create clustered index #temptable_index1 on #temptable (VMID asc)
insert into #temptable (VMID ,Starttime) select VMID ,CREATETIME from @myTable where PowerStatus=1 GROUP BY VMID,CREATETIME
UPDATE #temptable SET Endtime= S.Endtime from(SELECT VMID , CREATETIME AS ENDTIME from @myTable where PowerStatus=0 ) S inner join #temptable on s.VMID=#temptable.VMID and s.ENDTIME>#temptable.Starttime
select vmid,DATEDIFF (HOUR,Starttime, Endtime ) AS Worjhours from #temptable
DROP TABLE #temptable
But you have please to review back data entity exist there since I see some havn't relevant stop time.
[font="Arial Black"]Performance Guard[/font]
[font="Courier New"]Just ask me to get 0 sec for all queries[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply