January 7, 2010 at 3:49 am
How to compare two rows and if the IDs are same then accumulate the time?
Ex:
ID Date
12 01/01/2010
14 02/01/2010
12 03/01/2010
15 04/01/2010
In the example two times ID = 12 appears, so i have to check current row and previous rows for ID = 12. Then add the dateand time.
January 7, 2010 at 4:07 am
Please have a look at this post .
Similar problem.
January 7, 2010 at 8:08 am
I need the result in percentage value.
The accumulated date range has to be converted to percentage?
Please help.
January 7, 2010 at 8:26 am
Percentage of what?
Please show us what you've done so far in the same ready to use format as you found on the post I referred to.
January 7, 2010 at 8:39 am
CREATE FUNCTION [dbo].[f_ServiceAvailability]
(
@DateForm DATETIME,
@DateTo DATETIME
)
RETURNS NUMERIC (6, 2)
AS
BEGIN
DECLARE @Availability TABLE
(
EventTypeID INT,
EventTime DATETIME
)
INSERT INTO @Availability
SELECT ISNULL(EventTypeID, 10110),ISNULL(CreationDate, @DateFrom)
FROM EventLog
WHERE CreationDate IN
( SELECT MAX(CreationDate) AS CreationDate
FROM EventLog
WHERE CreationDate <= @DateFrom
AND EventTypeID IN (10110, 10120)
)
INSERT INTO @Availability
SELECT EventTypeID, CreationDate
FROM EventLog
WHERE EventTypeID IN (10110, 10120)
AND CreationDate BETWEEN @DateFrom AND @DateTo
ORDER BY CreationDate
INSERT INTO @Availability
SELECT ISNULL(EventTypeID, 10110),ISNULL(CreationDate, @DateTo)
FROM EventLog
WHERE CreationDate IN
( SELECT MAX(CreationDate) AS CreationDate
FROM EventLog
WHERE CreationDate <= @DateTo
AND EventTypeID IN (10110, 10120)
)
(
SELECT EventTypeID
FROM EventType
WHERE EventTypeID IN (10110,10120)
)
INSERT INTO @Availability
SELECT EventTime = (datediff(ss, @DateTo, @DateFrom)/@DateFrom) * 100
FROM EventLog
WHERE EventTypeID IN (10110,10120)
END
I have to find the date range for values 10110 and 10120 in the table, this will be downtime and then have to divide by the length of the range to get percentage.
January 7, 2010 at 9:05 am
Would you please post some sample data from EventLog so we have something to test against?
Also, please post your expected output.
It seems like this function is a little oversized for what you're trying to do. It should be possible with a "standard" query...
Beside of that: it doesn't return any value (yet?)....
Regarding syntax:
If you have a table with two columns you either have to provide two values or you'd have to declare the column you want to insert the data.
Edit: typo fixed
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply