how can get Three consecutive days [message #666628] |
Fri, 17 November 2017 11:01 |
|
hassan08
Messages: 123 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 #666663 is a reply to message #666630] |
Mon, 20 November 2017 01:54 |
|
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 #666686 is a reply to message #666667] |
Mon, 20 November 2017 05:21 |
Solomon Yakobson
Messages: 3285 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.
|
|
|