Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have data in my table like following...

PROJ_NBR     STATUS    START_DT        
AL20         AC        1/14/2010 4:31        
AL20         AC        1/14/2010 4:32        
AL20         AC        1/14/2010 4:32        
AL20         DE        1/14/2010 4:34        
AL20         DE        1/14/2010 4:46        
AL20         DE        1/14/2010 4:46        
AL20         DE        1/14/2010 4:46        
AL20         DE        1/14/2010 4:46        
AL20         DE        1/14/2010 4:46        
AL20         DE        1/14/2010 4:46        
AL20         DE        1/14/2010 4:46        
AL20         DE        1/14/2010 4:46        
AL20         DE        1/14/2010 4:46        
AL20         DE        1/14/2010 4:46        
AL20         AC        3/4/2010 4:31        
AL20         AC        3/4/2010 4:39        
AL20         AC        3/21/2010 13:24        
AL20         AC        2/4/2011 13:54        --year changes here
AL20         AC        2/4/2011 14:14        
AL20         AC        2/4/2011 14:27        
AL20         DE        2/11/2011 16:24        

I want data like following...

PROJ_NBR     STATUS    BEGIN                END
AL20         AC        1/14/2010 4:31       1/14/2010 4:32
AL20         DE        1/14/2010 4:34       1/14/2010 4:46
AL20         AC        3/4/2010 4:31        2/4/2011 14:27  --Second time range for AL20, AC
AL20         DE        2/11/2011 16:24      2/11/2011 16:24
share|improve this question
1  
Which SQL platform are you using? –  Eric Humphrey - lotsahelp Feb 20 '11 at 2:55
    
@bernd_k: ah good spot.My bad, –  gbn Feb 20 '11 at 19:35
    
What are the rules for determining what's in a given time span? Some maximum time between them? Are is one span always within a given day (if it never spans days, and there's never more than one time span per day for a given proj/status, it's actually fairly easy) –  Joe Feb 21 '11 at 13:29
1  
@Joe It appears that the data is sorted by date/time and each change of proj_nbr/status should create a new section regardless of time range. Note that the third row in the results spans years yet the fourth is a point in time rather than a range. –  Leigh Riffel Feb 21 '11 at 13:56
    
@Leigh : good point about the change status ... I was just wondering as I've done some similar stuff where there'd be a 2 hr gap between entries, and sometimes you consider that to be one span, sometimes two different ones ... but you're right, the third example shows a massive gap, and they consider it one span. –  Joe Feb 21 '11 at 14:07
add comment

4 Answers

Below a complete solution for sql-server.

I was trying analytical functions, but didn't succeed. I'm tempted to repost the question to SO requiring a solution using analytical functions. I think there must be one.

And here is my working solution. I'm proud not to have used cursors.

set language us_english
Set Nocount ON

create table #t( PROJ_NBR char(4),  STATUS char(2), START_DT datetime);

insert into #t values ('AL20',     'AC', '1/14/2010 4:31');  
insert into #t values ('AL20',     'AC', '1/14/2010 4:32');  
insert into #t values ('AL20',     'AC', '1/14/2010 4:32');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:34');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');  
insert into #t values ('AL20',     'AC', '3/4/2010 4:31');   
insert into #t values ('AL20',     'AC', '3/4/2010 4:39');   
insert into #t values ('AL20',     'AC', '3/21/2010 13:24'); 
insert into #t values ('AL20',     'AC', '2/4/2011 13:54');  
insert into #t values ('AL20',     'AC', '2/4/2011 14:14');  
insert into #t values ('AL20',     'AC', '2/4/2011 14:27');  
insert into #t values ('AL20',     'DE', '2/11/2011 16:24'); 


select
    distinct PROJ_NBR, STATUS, [start], [end]
from(
select 
*,
(select MIN(START_DT) 
    from #t t1 
    where t.STATUS = t1.STATUS 
    and t.START_DT >= t1.START_DT 
    and not exists ( select * from #t t2 
                    where t.STATUS <> t2.STATUS  
                    and  t.START_DT >= t2.START_DT
                    and t1.START_DT <= t2.START_DT)
) [start],
(select Max(START_DT) 
    from #t t1 
    where t.STATUS = t1.STATUS 
    and t.START_DT <= t1.START_DT 
    and not exists ( select * from #t t2 
                    where t.STATUS <> t2.STATUS  
                    and  t.START_DT <= t2.START_DT
                    and t1.START_DT >= t2.START_DT)
) [end]

from #t t
) t1
order by [start]

I think besides column and table names this can easily adapted to Oracle too.

share|improve this answer
    
Ours is Oracle db, yes I can convert this oracle..thanks for your time. –  Sree Feb 21 '11 at 15:43
    
This solution would require five full table scans. –  Leigh Riffel Feb 21 '11 at 16:23
add comment

Oracle Analytics Solution:

SELECT Proj_Nbr, Status, MIN(Start_Dt) AS "BEGIN", MAX(Start_Dt) AS "END"
FROM (
    SELECT Proj_Nbr, Status, Start_Dt, 
       MAX(SectionID) OVER (ORDER BY Start_Dt ROWS UNBOUNDED PRECEDING) SectionID
    FROM
    (
        SELECT Proj_Nbr, Status, Start_Dt, 
           CASE WHEN Proj_Nbr = Lag(Proj_Nbr) OVER (ORDER BY start_dt)
              AND Status = Lag(Status) OVER (ORDER BY start_dt)
                 THEN null
              ELSE rowid
           END SectionID  
        FROM t1
    )
)
GROUP BY Proj_Nbr, Status, SectionID;

Data Generation:

CREATE TABLE t1 (PROJ_NBR VARCHAR2(4),  STATUS VARCHAR2(2), START_DT DATE);

INSERT INTO t1 VALUES ('AL20', 'AC', to_date('01/14/2010 04:31', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'AC', to_date('01/14/2010 04:32', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'AC', to_date('01/14/2010 04:32', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:34', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('01/14/2010 04:46', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'AC', to_date('03/04/2010 04:31', 'MM/DD/YYYY HH24:MI')); 
INSERT INTO t1 VALUES ('AL20', 'AC', to_date('03/04/2010 04:39', 'MM/DD/YYYY HH24:MI')); 
INSERT INTO t1 VALUES ('AL20', 'AC', to_date('03/21/2010 13:24', 'MM/DD/YYYY HH24:MI')); 
INSERT INTO t1 VALUES ('AL20', 'AC', to_date('02/04/2011 13:54', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'AC', to_date('02/04/2011 14:14', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'AC', to_date('02/04/2011 14:27', 'MM/DD/YYYY HH24:MI'));
INSERT INTO t1 VALUES ('AL20', 'DE', to_date('02/11/2011 16:24', 'MM/DD/YYYY HH24:MI')); 
share|improve this answer
    
Thanks for your solution and time..thanks again. –  Sree Feb 21 '11 at 15:43
4  
@Sree The way you say thank you on a Stack Exchange site like this is to up-vote the answer. Your comment is appreciated, but up-voting beneficial answers moves them to the top of the page making the best content easier to find. Based on your comments you should up-vote three answers. At some point you should accept the answer that best resolves your question. –  Leigh Riffel Feb 21 '11 at 16:20
2  
SQL-Server users please go to connect.microsoft.com/SQLServer/feedback/details/254388/… and vote for the addition of the lead and lag function. –  bernd_k Feb 21 '11 at 16:56
add comment

Here's my take: (Thank's to @Leigh for providing the data generation script)

select proj_nbr, status, min(start_dt) start_dt, end_dt 
from (
    select a.*, (
        select max(p.start_dt) 
        from t1 p 
        where P.PROJ_NBR = a.proj_nbr and p.status = a.status 
        and p.start_dt >= a.start_dt
        and not exists (
            select null from t1 q where q.PROJ_NBR = a.proj_nbr 
            and q.status <> p.status 
            and q.start_dt < p.start_dt and q.start_dt > a.start_dt)
    ) end_dt 
    from t1 a
)
group by proj_nbr, status, end_dt
share|improve this answer
2  
Your query does return the correct results. The results are out of order for more recent versions of Oracle, but a simple order by would solve that. More importantly though, the query requires three full table scans which would be a limitation if the table is large or the query is run frequently. –  Leigh Riffel Feb 21 '11 at 12:57
    
Thanks so much for the solution and time, this query works for me..Thanks again. –  Sree Feb 21 '11 at 15:42
    
As sql-server seems not to have lead and lag functions, this seems to be a good suitable solution for sql-server too. –  bernd_k Feb 21 '11 at 17:12
    
@Isaac.Functionally this query works perfectly..but our table has 2,091,348 rows so,query isnt coming back at all if we run it for all the projects..I am trying to tune the query..please through in any ideas.. –  Sree Feb 22 '11 at 20:55
    
@Sree, I think adding index will be fine: create index on t1 (proj_nbr, status, start_date) –  Isaac A. Nugroho Feb 24 '11 at 3:33
add comment
SELECT PROJ_NBR, STATUS, MIN(START_DT) AS _BEGIN, MAX(START_DT) AS _END 
FROM MYTABLE GROUP BY PROJ_NBR, STATUS;

Why the _? Because BEGIN and END are reserved words ;-)

Tho' I have to say that this isn't really a DBA question; it should be on Stack Overflow.

share|improve this answer
3  
That was my initial thought, but I don't believe that's sufficient. The desired output has two different rows with a PROJ_NBR of AL20 and a STATUS of AC. The simple GROUP BY would return a single such row with a start date of 1/14/2010 and an end date of 2/4/2011. I believe we're going to need analytics to detect the changes from one status to another. –  Justin Cave Feb 18 '11 at 23:09
2  
Justin's right, I believe that he needs a sequence column that will be reset when the status changes, and take the group data by that particular sequence, not only grouped by proj_nbr and status. –  Marian Feb 19 '11 at 11:12
3  
Why do you think that is no dba question? I know a lot of developers who would attack it by writing cursors and who is to tell them to stop that? –  bernd_k Feb 19 '11 at 12:44
    
I agree, non-unique key grouping is tricky, see my similar post that I eventually solved by only moving to C# and processing procedurally. –  jcolebrand Feb 21 '11 at 15:46
    
@bernd_k Because there are more developers there. A DBA question would be how to optimize the performance of such a query. –  Gaius Feb 28 '11 at 11:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.