November 20, 2013 at 7:00 am
Hi guys! I'm not saying that actually have a problem... 😛
I have this table: ID(int) | START(datetime) | STOP(datetime) lets call it DELAYS
I have this input "parameters": START, STOP, STEP (in hours) the parameters to call for ex: SELECT * FROM myStorProc(START, STOP, STEP);
-------------------------------------------------------------------
I want to achieve the below return; with the input START: 1. october 2013 00:00 STOP: 1. october 2013 23:59 STEP: 1h
HOUR(of day) | DURATION(in minutes)
0 | 10
1 | 60
2 | 41
3 | 0
.... (continue)
23 | 20
====================================================
The problem I'm facing; I have never worked with SQL server stored procedure and I'm kinda newbie on this section.
And the 2nd problem is that for example a record in my table can start at let's say 1 and somethng and finish at 2 and something... in my case where the step is set to 1h the duration in this case cant be more than 60min... the other portion of time must be considered in the other hour ie. 2h. Also 0 (ZERO) durations must be displayed, can also happen that no delays are present for few hours 🙂
Studyng the case the can be 4 different cases of HOUR to be aware of:
1. delay start at time and finish at she same HOUR
2. delay start HOUR-1 before HOUR and finish at HOUR
3. -- same as 2. but opposite
4. delay start BEFORE and finish AFTER HOUR... meaning the max amount possible 60 min (in case of step 1h, step 2h max will be 120min)
Visually:
1 2 3
|---|
|------|
|------|
|----------------------|
Can someone be so kind and give me a draft, source code is fully requested as this will be my first stored procedure.
Thanks Luka
November 20, 2013 at 7:12 am
So far I implemented this:
CREATE PROCEDURE myStoProc(@start datetime, @stop datetime, @step int)
AS
SELECT *
FROM DLY_DELAY
WHERE START_DELAY>=@start AND END_DELAY<=@stop;
November 20, 2013 at 8:50 am
Please provide a sample table with sample data and expected outputs. We can't help easily based on verbal descriptions.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 21, 2013 at 1:00 am
This is my table
CREATE TABLE [dbo].[DLY_DELAY] (
[DELAY_CNT] [dbo].[LongInt] NOT NULL,
[START_DELAY] [dbo].[DTimeNull] NULL,
[END_DELAY] [dbo].[DTimeNull] NULL)
Some data...
insert into [dbo].[DLY_DELAY]([DELAY_CNT],[START_DELAY],[END_DELAY]) values (0,'2013-06-15 13:25:02','2013-09-24 14:57:26');
insert into [dbo].[DLY_DELAY]([DELAY_CNT],[START_DELAY],[END_DELAY]) values (1,'2013-06-15 13:25:02','2013-06-16 13:25:03');
insert into [dbo].[DLY_DELAY]([DELAY_CNT],[START_DELAY],[END_DELAY]) values (2,'2013-06-16 13:25:02','2013-06-16 13:30:02');
insert into [dbo].[DLY_DELAY]([DELAY_CNT],[START_DELAY],[END_DELAY]) values (3,'2013-06-16 13:25:02','2013-06-16 13:45:02');
insert into [dbo].[DLY_DELAY]([DELAY_CNT],[START_DELAY],[END_DELAY]) values (4,'2013-06-17 11:26:57','2013-06-17 12:16:57');
insert into [dbo].[DLY_DELAY]([DELAY_CNT],[START_DELAY],[END_DELAY]) values (5,'2013-06-18 13:26:57','2013-06-18 13:44:57');
insert into [dbo].[DLY_DELAY]([DELAY_CNT],[START_DELAY],[END_DELAY]) values (6,'2013-06-19 17:09:57','2013-06-19 17:26:57');
insert into [dbo].[DLY_DELAY]([DELAY_CNT],[START_DELAY],[END_DELAY]) values (7,'2013-06-20 13:26:57','2013-06-20 15:26:57');
insert into [dbo].[DLY_DELAY]([DELAY_CNT],[START_DELAY],[END_DELAY]) values (8,'2013-06-21 13:26:57','2013-06-21 13:56:57');
insert into [dbo].[DLY_DELAY]([DELAY_CNT],[START_DELAY],[END_DELAY]) values (9,'2013-06-24 13:26:57','2013-06-24 13:21:57');
insert into [dbo].[DLY_DELAY]([DELAY_CNT],[START_DELAY],[END_DELAY]) values (10,'2013-06-25 13:26:57','2013-06-25 13:56:57');
insert into [dbo].[DLY_DELAY]([DELAY_CNT],[START_DELAY],[END_DELAY]) values (11,'2013-06-26 13:09:57','2013-06-26 13:16:57');
insert into [dbo].[DLY_DELAY]([DELAY_CNT],[START_DELAY],[END_DELAY]) values (12,'2013-06-27 13:26:57','2013-06-23 13:51:57');
insert into [dbo].[DLY_DELAY]([DELAY_CNT],[START_DELAY],[END_DELAY]) values (13,'2013-06-28 13:26:57','2013-06-28 13:56:57');
The expected result is in the attachment.
I'll integrate the result into Crystal Report.
But first of all I need row data... I know there are many ways to get data correctty... but in my case I'm forced to use SP!
I need a simple table output with two columns like this:
HOUR | DURATION
------+------------
HOUR
- Can be 00-23 and goes like this 0, 1, 2, ... (@step=1) or for example 0, 2, 4, ... (@step=2)... and so on...
DURATION
- an be from 0-(@step*60)
- where no delay is present DURATION=0
Thanks, Luka
November 21, 2013 at 10:34 am
The corresponding Oracle solution of my problem.
There are actually 2 functions; one doing the number HOUR (==@step) and the other GetIntervalDuration do the select and calculates the DELAY :w00t:
CREATE OR REPLACE FUNCTION GetIntervalDuration(startdate in DATE, step in number) RETURN NUMBER IS
duration NUMBER(24,10);
CURSOR cDuration is
select a.c1+b.c1+c.c1+d.c1
from
( SELECT coalesce(sum(end_delay - start_delay)*1440,0) as c1
FROM stp_stoppage
WHERE start_delay >= startdate
AND end_delay <= startdate + step) a
,
( SELECT coalesce(sum(startdate + step - start_delay)*1440,0) as c1
FROM stp_stoppage
WHERE start_delay >= startdate
AND start_delay < startdate + step
AND end_delay > startdate + step) b
,
( SELECT coalesce(sum(end_delay - startdate)*1440,0) as c1
FROM stp_stoppage
WHERE start_delay < startdate
AND end_delay <= startdate + step
AND end_delay > startdate) c
,
( SELECT coalesce(sum(step)*1440,0) as c1
FROM stp_stoppage
WHERE start_delay < startdate
AND end_delay > startdate + step) d
;
BEGIN
OPEN cDuration;
FETCH cDuration INTO duration;
CLOSE cDuration;
RETURN duration;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END GetIntervalDuration;
/
CREATE OR REPLACE FUNCTION GetDelayIntervalData(startdate in DATE, enddate in DATE, step in number) RETURN DelayChatReportTable IS
tempTable DelayChatReportTable;
indexCycle number;
i number;
cycleMaxNo number;
temp1 number;
tempDate date;
hourInitial number;
tempStep number(24,10);
labelNo int;
tempLabelDay date;
temp2 int;
BEGIN
tempStep := step / 24;
tempTable := DelayChatReportTable();
tempDate := startdate;
temp1 := enddate - startdate;
temp1 := temp1 / tempStep;
cycleMaxNo := round(temp1);
select to_number(to_char(startdate, 'HH24')) into hourInitial from dual;
indexCycle := 1;
tempLabelDay := startdate;
labelNo := hourInitial;
for i in 1..cycleMaxNo
LOOP
tempTable.extend;
if (tempStep >= 1) then begin
select to_number(to_char(tempLabelDay, 'DD')) into labelNo from dual;
temp2 := labelNo;
end;
else
if (labelNo > 23) then temp2 := (labelNo) mod 24 ; else temp2 :=labelNo; end if;
end if;
tempTable(indexCycle) := DelayChatReport(temp2, GetIntervalDuration(tempDate, tempStep));
tempDate := tempDate + tempStep;
indexCycle := indexCycle + 1;
if (tempStep >= 1) then
tempLabelDay := tempLabelDay + tempStep;
else
labelNo := labelNo + step;
end if;
END LOOP;
return tempTable;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END GetDelayIntervalData;
November 21, 2013 at 4:12 pm
I am sorry Luka, but I still have no idea what you need here. Your data doesn't match the graphic you gave (which covers 2010-03-26 06:00 through 2010-03-27 06:00), you have a weird data point (the first one) which spans over 3 months, I don't know what delay_cnt is nor @step=1, @step=2, etc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 22, 2013 at 2:07 am
TheSQLGuru (11/21/2013)
I am sorry Luka, but I still have no idea what you need here. Your data doesn't match the graphic you gave (which covers 2010-03-26 06:00 through 2010-03-27 06:00), you have a weird data point (the first one) which spans over 3 months, I don't know what delay_cnt is nor @step=1, @step=2, etc.
Forget about that, is the parser which removes blank _ spaces :hehe:
I'll post a new topic with some working SQL source 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply