plz solve this query as soon as possible

[komalrade]

Main table


inst flag
1 y
2 y
3 y
4 n
5 n
6 y
10 n
11 n
12 n
20 y
21 y


ouput should be contain 2tables which r print &wastage table(use cursor)

print table
from to
1 3
6 6
20 21


wastage table
from to
4 5
10 12

[debasisdas]

kindly post what you have tried sofar.

[amitpatel66]

Hi Komalrade ,

Welcome to TSDN!!

Please make sure you follow POSTING GUIDELINES every time you post in this forum!!

MODERATOR

[chritzuk]

Here's a start...

Good luck!

SELECT DISTINCT NVL (start_range,
                     LAG (start_range) OVER (ORDER BY inst)
                    ) AS "from",
                NVL (end_range,
                     LEAD (end_range) OVER (ORDER BY inst)
                    ) AS "to"
FROM            (SELECT inst,
                        flag,
                        start_range,
                        end_range
                 FROM   (SELECT inst,
                                flag,
                                CASE
                                  WHEN (flag <> LAG (flag) OVER (ORDER BY inst))
                                OR    (LAG (flag) OVER (ORDER BY inst) IS NULL) THEN inst
                                  ELSE NULL
                                END start_range,
                                CASE
                                  WHEN (flag <> LEAD (flag) OVER (ORDER BY inst))
                                OR    (LEAD (flag) OVER (ORDER BY inst) IS NULL) THEN inst
                                  ELSE NULL
                                END end_range
                         FROM   main_table)
                 WHERE  (   start_range IS NOT NULL
                         OR end_range IS NOT NULL)
                 AND    flag = 'y')
ORDER BY        "from"

[nileshpardeshid]

can u plz tell in detail ? cant understand query,

[vikas000000a]

Following is the result of my research of one hour:

For print table:

SELECT MIN(inst), MAX(inst) FROM( 
SELECT inst, inst-ROWNUM AS criteria FROM tmp 
WHERE flg='y' ORDER BY inst) GROUP BY criteria

For waste table:

SELECT MIN(inst), MAX(inst) FROM( 
SELECT inst, inst-ROWNUM AS criteria FROM tmp 
WHERE flg='n' ORDER BY inst) GROUP BY criteria

Enjoyed a lot in solving this problem. Hope it works for you