Re: Pure sql solution to date period

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance


CREATE CURSOR xx (id Int,date Date)
INSERT INTO xx VALUES (1,DATE()-20)
INSERT INTO xx VALUES (1,DATE()-15)
INSERT INTO xx VALUES (1,DATE()-10)
INSERT INTO xx VALUES (2,DATE()-21)
INSERT INTO xx VALUES (2,DATE()-14)
INSERT INTO xx VALUES (2,DATE()-7)

SELECT id, date as start, NVL((SELECT MIN(x1.date) FROM xx AS x1;
WHERE X1.id=xx.id AND x1.date>xx.date),DATE()) AS end, ;
NVL((SELECT MIN(X1.date) FROM xx AS x1;
WHERE X1.id=xx.id AND x1.date>xx.date),DATE())-xx.date AS duration;
FROM xx ;
INTO CURSOR durations
BROWSE LAST NOWAIT

Result on todays date:
Id Start End Duration
1 2007-11-13 2007-11-18 5
1 2007-11-18 2007-11-23 5
1 2007-11-23 2007-12-03 10
2 2007-11-12 2007-11-19 7
2 2007-11-19 2007-11-26 7
2 2007-11-26 2007-12-03 7

-Anders


"Lew" <lew@xxxxxxxxxxx> wrote in message
news:eGGyO6UNIHA.4480@xxxxxxxxxxxxxxxxxxxxxxx
I've got a semi sql solution to this problem, but can I do it in pure sql?
Here it is:

Given a table in the form name, date, rate, create a result cursor in the
form name, startdate, enddate, duration (it's crosstab ish). The idea is
for each name, show when the rate went into effect, when it changed and
how long the rate was in effect. The procedural + sql solution goes like
this:

index the rates table on name + dtos(date)
scan
dStart = Date
cName = Name
select min(date) as nextdate from ratestable where Name = cName and
date
dStart;
into cursor results
insert into durationtable values (cName, dStart, results.NextDate,
results.NextDate - dStart)
endscan

This is unbelievably fast with the help of a few add'l tags, but I wonder
if there isn't a single sql statement that'll do the whole thing.

--
-Lew
The workers took the robot for Maria.



.