Home » SQL & PL/SQL » SQL & PL/SQL » how can get Three consecutive days
how can get Three consecutive days [message #666628] Fri, 17 November 2017 11:01 Go to next message
hassan08
Messages: 122
Registered: June 2011
Location: egypt
Senior Member
i have data in this table like this
emp_code   date1
100        '1/1/2017'
200        '5/12/2017'
300        '12/11/2017'
400        '16/4/2014'
100         '2/1/2017'
500         '26/4/2017'
100         '3/1/2017'
600         '11/9/2017'
100         '4/1/2017'

how can get can get three consecutive
the result must be
emp_code 100 date1 1/1/2017
emp_code 100 date1 2/1/2017
emp_code 100 date1 3/1/2017
Re: how can get Three consecutive days [message #666629 is a reply to message #666628] Fri, 17 November 2017 11:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: how can get Three consecutive days [message #666630 is a reply to message #666629] Fri, 17 November 2017 12:37 Go to previous messageGo to next message
JPBoileau
Messages: 88
Registered: September 2017
Member
select emp_code, date1 from <table>
order by emp_code,date1;

Fairly straight forward. Unless you want something more.

JP

[Updated on: Fri, 17 November 2017 12:37]

Report message to a moderator

Re: how can get Three consecutive days [message #666663 is a reply to message #666630] Mon, 20 November 2017 01:54 Go to previous messageGo to next message
quirks
Messages: 82
Registered: October 2014
Member
I wonder if this one could be done in an easier way:
WITH
    TEST_TAB(EMP_CODE, DATE1)
    AS
        (SELECT 100, TO_DATE('1/1/2017', 'DD/MM/YYYY') FROM DUAL
         UNION ALL
         SELECT 200, TO_DATE('5/12/2017', 'DD/MM/YYYY') FROM DUAL
         UNION ALL
         SELECT 300, TO_DATE('12/11/2017', 'DD/MM/YYYY') FROM DUAL
         UNION ALL
         SELECT 400, TO_DATE('16/4/2014', 'DD/MM/YYYY') FROM DUAL
         UNION ALL
         SELECT 100, TO_DATE('2/1/2017', 'DD/MM/YYYY') FROM DUAL
         UNION ALL
         SELECT 500, TO_DATE('26/4/2017', 'DD/MM/YYYY') FROM DUAL
         UNION ALL
         SELECT 100, TO_DATE('3/1/2017', 'DD/MM/YYYY') FROM DUAL
         UNION ALL
         SELECT 600, TO_DATE('11/9/2017', 'DD/MM/YYYY') FROM DUAL
         UNION ALL
         SELECT 100, TO_DATE('4/1/2017', 'DD/MM/YYYY') FROM DUAL),
    ORDERED_TAB
    AS
        (SELECT   EMP_CODE, DATE1
             FROM TEST_TAB
         ORDER BY DATE1),
    CONSECUTIVE_DATES
    AS
        (SELECT EMP_CODE
               ,DATE1
               ,ROW_NUMBER() OVER(PARTITION BY DATE1 - ROWNUM ORDER BY DATE1) AS SORT_ORDER
               ,COUNT(*) OVER (PARTITION BY DATE1 - ROWNUM)                   AS CONSECUTIVE_DATES
           FROM ORDERED_TAB)
SELECT EMP_CODE, DATE1
  FROM CONSECUTIVE_DATES
 WHERE CONSECUTIVE_DATES > 2 AND SORT_ORDER < 4
Re: how can get Three consecutive days [message #666667 is a reply to message #666663] Mon, 20 November 2017 03:02 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Whats wrong with the Tabibitosan method, its efficient and reasonably "easy"?
Re: how can get Three consecutive days [message #666686 is a reply to message #666667] Mon, 20 November 2017 05:21 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Well, OP didn't explain all the rules.

1. Can there be duplicate dates?
2. Assume we have DATE, DATE + 1, DATE + 2, DATE + 3, DATE + 4. Do we display these 5 dates once or in sets:

DATE
DATE + 1
DATE + 2

DATE + 1
DATE + 2
DATE + 3

DATE + 2
DATE + 3
DATE + 4

SY.

Previous Topic: grouping in matrix
Next Topic: Drop Function gives error
Goto Forum:
  


Current Time: Thu Mar 28 09:05:32 CDT 2024