# Oracle computing continuous login/working days

Source: Internet
Author: User

Now there is a report that calculates the number of working days of a user. It is found that the row_number analysis function can perfectly calculate this problem. This SQL statement can solve problems such as continuous login, sign-in, work, and absenteeism of computing users.

First, sort row_number by date.

Extract 1 from a date, such as 2016-7-1, and convert it into a number.

Calculate the value of this date number minus row_number and use it as the group number, because the group numbers calculated by the discontinuous values are inconsistent;

Perform group by based on the group number field to calculate the start time, end time, and number of days of each continuous shift.

Such as the original data
2016/7/1
2016/7/2
2016/7/4
2016/7/5
2016/7/6
2016/7/7
......

The calculated result is:
Group number start time end time days
0 2016/7/1 2016/7/2 2
1 2016/7/4 2016/7/9 6
......

The code below
With t1
(
Select date '2014-7-1 'd1 from dual
Union
Select date '2014-7-2 'd1 from dual
Union
Select date '2014-7-4 'd1 from dual
Union
Select date '2014-7-5 'd1 from dual
Union
Select date '2014-7-6 'd1 from dual
Union
Select date '2014-7-7' d1 from dual
Union
Select date '2014-7-8 'd1 from dual
Union
Select date '2014-7-9 'd1 from dual
Union
Select date '2014-7-11 'd1 from dual
Union
Select date '2014-7-12 'd1 from dual
Union
Select date '2014-7-13 'd1 from dual
Union
Select date '2014-7-14 'd1 from dual
Union
Select date '2014-7-18 'd1 from dual
Union
Select date '2014-7-19 'd1 from dual
)
Select gn, min (d1), max (d1), count (*)
From (
Select d1, to_number (to_char (d1, 'dd')-row_number () over (order by d1) gn from t1
) Group by gn order by 2

Related Keywords:
Related Article

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

## A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

• #### Sales Support

1 on 1 presale consultation

• #### After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

• Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.