Friday, July 9, 2010

Oracle SQL Query to get Date Range as Single row Block

Here is an Oracle SQL Query to return rows , with each day as a single row range value. This query can be used in reporting , when things on different day has to be queried as a date range and as a single row.

create table test_acy(acy_cd varchar2(5),start_dt date,end_dte date);

insert into test_acy values('AB','01Jan2010','02Jan2010');
insert into test_acy values('AB','02Jan2010','03Jan2010');
insert into test_acy values('AB','03Jan2010','04Jan2010');
insert into test_acy values('AB','06Jan2010','07Jan2010');
insert into test_acy values('AB','09Jan2010','10Jan2010');
insert into test_acy values('AB','10Jan2010','11Jan2010');

insert into test_acy values('CD','11Jan2010','12Jan2010');

If My table has rows like this, and would like to get rows as

AB        01Jan2010        04Jan2010
AB        06Jan2010        07Jan2010
AB        09Jan2010        11Jan2010
CD        11Jan2010        12Jan2010.

SQL Query for this
SELECT   acy_cd,
FROM     (SELECT acy_cd,
                 SUM(win) OVER(PARTITION BY acy_cd ORDER BY start_dt)
          FROM   (SELECT   acy_cd,
                              WHEN start_dt !=
                                     LAG(end_dte) OVER(PARTITION BY acy_cd ORDER BY start_dt)
                                 THEN 1
                              ELSE 0
                           END win
                  FROM     test_acy
                  ORDER BY acy_cd, start_dt))
GROUP BY acy_cd, win_group


SELECT   acy_cd,
         MIN(start_dt) start_dt,
         MAX(end_dte) end_dte
FROM     (SELECT     LEVEL lvl,
                           ) path_root
          FROM       test_acy t
          CONNECT BY acy_cd = PRIOR acy_cd  AND
                     start_dt = PRIOR end_dte
          START WITH t.start_dt IN(SELECT t2.start_dt
                                   FROM   test_acy t2 LEFT OUTER JOIN test_acy t3
                                          ON t2.start_dt = t3.end_dte AND
                                            t2.acy_cd = t3.acy_cd
                                   WHERE  t3.end_dte IS NULL))
GROUP BY acy_cd, path_root

ACY_CD        START_DT        END_DTE

AB        1/6/2010        1/7/2010
AB        1/9/2010        1/11/2010
AB        1/1/2010        1/4/2010
CD        1/11/2010        1/12/2010



Post a Comment


ORA-BLOG. Copyright 2008 All Rights Reserved Revolution Two Church theme by Brian Gardner Converted into Blogger Template by Bloganol dot com