need help with this report
-
hour minute status
1 0 available
1 5 available
1 10 available
1 15 available
1 20 not-available
1 25 not-available
1 30 available
1 35 available
1 40 available
1 45 available
1 50 available
1 55 avaliable
2 0 available
2 5 not-available
I need to generate a following report with the above data
status starttime endtime duaration
available 1:00 1:19 20 mins
not-available 1:20 1:29 10 mins
available 1:30 2:04 35 mins
not_avalable 2:05 2:14 10 mins
avaliable ...............
I need to generate this report for 24 hours period.
Any help would be deeply appreciated.
Thanks.
-
Its difficult to do it in one query. You can either use cursors or write a UDF that takes the start time and returns the end time and call it from a T-SQL.
******************
Dinakar Nethi
Life is short. Enjoy it.
****************** -
It could also be done with a table variable joined against itself and some clever grouping.
For example:
if exists (select * from tempdb.dbo.sysobjects where [name] = '##t')
begin
drop table ##t
end
create table ##t (h int, m int, status varchar(20))
insert ##t values(1,0, 'available')
insert ##t values(1,5, 'available')
insert ##t values(1,10, 'available')
insert ##t values(1,15, 'available')
insert ##t values(1,20, 'not-available')
insert ##t values(1,25, 'not-available')
insert ##t values(1,30, 'available')
insert ##t values(1,35, 'available')
insert ##t values(1,50, 'available')
insert ##t values(1,55, 'available')
insert ##t values(2,0, 'available')
insert ##t values(2,5, 'not-available')
declare @t table (t_ID int IDENTITY, t char(4), status varchar(20))
insert @t(t, status)
select right('00'+convert(varchar(2),replace(h+10,33,9)),2)+right('00'+convert(varchar(2),m),2),status
from ##t where h in
( replace(datepart(hh,'2005-06-07 00:10:28.843')-1,-1,23),
replace(datepart(hh,'2005-06-07 00:10:28.843'),-1,23))
select status,
replace(substring(min(start_t),1,2)-10,'-1','23') start_h ,
substring(min(start_t),3,2) start_m ,
replace(substring(end_t,1,2)-10,'-1','23') end_h ,
substring(end_t,3,2) end_m
from
(
select a.t_ID, a.t start_t, a.status,
min(case when b.status <> a.status and a.t < b.t then b.t else
right('00'+convert(varchar(2),datepart(hh,'2005-06-07 02:10:28.843')+10),2) +
right('00'+convert(varchar(2),datepart(n,'2005-06-07 02:10:28.843')),2) end) end_t,
replace(min(case when b.status <> a.status and a.t < b.t then b.t_ID else '999999' end),
'999999', max(b.t_ID)) end_ID
from @t a cross join @t b
group by a.t_ID,a.t, a.status
 
Xgroup by status, end_ID, end_t
order by convert(int,end_ID)
-
Hmm, this returns nothing for me (tried modifying the dateformat)?
-
yes, Its returning nothing.
-
It's surely something stupid but I don't see it.
-
Try this Store Procedure: (Change the table 'TableBreakdown' to what ever the name of your table is)
CREATE PROCEDURE p_Rpt_AvailStatus (
@tiStartHour tinyint,
@tiStartMinute tinyint,
@tiEndHour tinyint,
@tiEndMinute tinyint)
AS
SET NOCOUNT ON
DECLARE @vcStatus varchar(15),
@tiSHour tinyint,
@tiSMinute tinyint,
@tiEHour tinyint,
@tiEMinute tinyint,
@iDuration int
DECLARE @tblReport table (vcStatus varchar(15), vcStart varchar(4), vcEnd varchar(4), vcDuration varchar(15))
SET @tiSHour = @tiStartHour
SET @tiSMinute = @tiStartMinute
SELECT @vcStatus = Status
FROM TableBreakdown
WHERE Hour = @tiSHour
AND Minute = @tiSMinute
WHILE (@tiSHour < @tiEndHour) OR (@tiSHour = @tiEndHour AND @tiSMinute < @tiEndMinute)
BEGIN
SELECT TOP 1 @tiEHour = Hour,
@tiEMinute = Minute
FROM TableBreakdown
WHERE ((Hour = @tiSHour AND Minute >= @tiSMinute) OR (Hour > @tiSHour))
AND Status = CASE @vcStatus WHEN 'Available' THEN 'Not-Available' ELSE 'Available' END
ORDER BY Hour,
Minute
IF (@tiEHour > @tiEndHour) OR (@tiEHour = @tiEndHour AND @tiEMinute > @tiEndMinute)
BEGIN
SET @tiEHour = @tiEndHour
SET @tiEMinute = @tiEndMinute
END
IF @tiEHour = @tiSHour
SET @iDuration = @tiEMinute - @tiSMinute
ELSE IF (@tiEHour - @tiSHour = 1) AND @tiEMinute < @tiSMinute
SET @iDuration = (60 - @tiSMinute) + @tiEMinute
ELSE IF (@tiEHour - @tiSHour = 1) AND @tiEMinute = @tiSMinute
SET @iDuration = 60
ELSE IF (@tiEHour - @tiSHour = 1) AND @tiEMinute > @tiSMinute
SET @iDuration = 60 + (@tiEMinute - @tiSMinute)
ELSE IF (@tiEHour - @tiSHour > 1) AND @tiEMinute < @tiSMinute
SET @iDuration = (60 * ((@tiEHour - @tiSHour) - 1)) + (60 - @tiSMinute) + @tiEMinute
ELSE IF (@tiEHour - @tiSHour = 1) AND @tiEMinute = @tiSMinute
SET @iDuration = 60 * (@tiEHour - @tiSHour)
ELSE
SET @iDuration = (60 * (@tiEHour - @tiSHour)) + (@tiEMinute - @tiSMinute)
INSERT INTO @tblReport
VALUES (@vcStatus,
CONVERT(varchar(2), @tiSHour) + ':' + RIGHT('00' + CONVERT(varchar(2), @tiSMinute), 2),
CASE @tiEMinute WHEN 0 THEN CONVERT(varchar(2), @tiEHour - 1) + ':59' ELSE CONVERT(varchar(2), @tiEHour) + ':' + RIGHT('00' + CONVERT(varchar(2), @tiEMinute - 1), 2) END,
CONVERT(varchar(10), @iDuration) + ' mins')
SET @tiSHour = @tiEHour
SET @tiSMinute = @tiEMinute
SET @vcStatus = CASE @vcStatus WHEN 'Available' THEN 'Not-available' ELSE 'Available' END
END
SELECT * FROM @tblReport
-
Made it without a loop.
SET NOCOUNT ON
/* Add sample Data Start */
DECLARE @status TABLE
(
RecTime DATETIME,
Status VARCHAR(25)
)
DECLARE @DELAY INT, @DELAY1 INT
DECLARE @CTR INT
DECLARE @Temp VARCHAR(25)
DECLARE @myDate DATETIME SET @myDate = '06/07/2005'
SET @CTR = 0
WHILE @CTR <= (24 * 12) - 1
BEGIN
SET @DELAY = 0
WHILE @DELAY < @DELAY1 * 10
SET @DELAY = @DELAY + 1
SELECT @DELAY1 = CONVERT(INT,SUBSTRING(REVERSE(CONVERT(VARCHAR,RAND(DATEPART(MILLISECOND, GETDATE())))),1,2))
SELECT @Temp = CASE @DELAY1 % 4 WHEN 0 THEN 'NOT AVAILABLE' ELSE 'AVAILABLE' END
INSERT @status VALUES (@myDate, @Temp)
SET @myDate = DATEADD(MINUTE, 5, @myDate)
SELECT @CTR = @CTR + 1
END
/* Add sample Data End */
DECLARE @TmpStatus TABLE
(
RecTime DATETIME,
Status VARCHAR(25),
Start DATETIME NULL,
UPTO DATETIME NULL
)
INSERT @TmpStatus
SELECT TOP 100 PERCENT A.RecTime, A.Status, NULL Start, CASE WHEN A.RecTime = (SELECT MAX(RecTime) FROM @status) THEN A.RecTime WHEN A.Status <> B.Status THEN A.RecTime ELSE NULL END UPTO
FROM @status A
LEFT OUTER JOIN
@status B ON DATEDIFF(MINUTE,A.RecTime, B.RecTime) = - 5
ORDER BY A.RecTime
INSERT @TmpStatus
SELECT TOP 100 PERCENT A.RecTime, A.Status, CASE WHEN A.RecTime = (SELECT MIN(RecTime) FROM @status) THEN A.RecTime WHEN A.Status <> B.Status THEN A.RecTime ELSE NULL END START, NULL UPTO
FROM @status A
LEFT OUTER JOIN
@status B ON DATEDIFF(MINUTE,A.RecTime, B.RecTime) = - 5
ORDER BY A.RecTime
DELETE @TmpStatus WHERE Start IS NULL AND UPTO IS NULL
SELECT A.Status, CONVERT(VARCHAR,DATEPART(HOUR,A.RecTime)) + ':' + CONVERT(VARCHAR,DATEPART(MINUTE,A.RecTime)) + ' - ' + CONVERT(VARCHAR,DATEPART(HOUR,MIN(B.RecTime))) + ':' + CONVERT(VARCHAR,DATEPART(MINUTE,MIN(B.RecTime))) BETWN, DATEDIFF(MINUTE, A.RecTime, MIN(B.RecTime)) [TimeIn Minutes]
FROM
(
SELECT RecTime, MIN(Status) Status
FROM @TmpStatus
GROUP BY RecTime) A
JOIN
(
SELECT RecTime, MIN(Status) Status
FROM @TmpStatus
GROUP BY RecTime) B
ON
A.RecTime < B.RecTime
GROUP BY A.RecTime, A.Status
Regards,
gova -
Hmm
what if you change the design of the table
StartPeriod---DateTime
EndPeriod ---DateTime
Status ----Available/NotAvailable
and when you insert a new row upgrade EndPeriod check the status on last row and if different add a new row...
This way you will safe space ... and the report is easy to retrieve and when you want to know at a certain time if it was available is easy again ...
Index on StartPeriod...
Kindest Regards,Vasc
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply