(013) Daily SQL Learning: Determine the number of workdays between two dates and calculate the number of dates in a year week

1. Determine the number of workdays between two dates

--Determine the number of workdays between two dates
With x0As(Select To_date (‘2018-01-01‘,‘Yyyy-mm-dd‘)As DateFromDualUnionAllSelect To_date (‘2018-01-15‘,‘Yyyy-mm-dd‘)As DateFromDual), X1As--Date side-by-side display (SelectMin (date) Start date,Max (date) End DateFromx0), x2As--Number of days between dates (Select End Date-Start date+1As days, start date, end dateFromX1), x3As--Constructs a collection of dates from the start date to the end date (enumeration date) (Select To_char (Start date+Level-1,‘Dy‘)AsEnumeration dateFrom X2 Connectby level <= days)--Statistical date select sum ( Case when  enumeration date in ( ' 

2. Calculate the number of dates in a year of the week

--Calculate the number of dates in a year of the weekWith x0As(Select To_date (‘2018-01-01‘,‘Yyyy-mm-dd‘)As at the beginningFromDual), X1As(Select early, add_months (early,12)As the beginning of next yearFromx0), x2As(Select early next year, early next year-EarlyAs DaysFromX1), x3As(Select early+Level-1 as date from x2 connect by level <= days), x4 as (select date, To_char (date, dayfrom X3) *) from x4 group Span style= "color: #0000ff;" >by Week                

3. Determine the number of days between the current record and the next record

 withX0 as (Select '1' asName, To_date ('2018-01-01','YYYY-MM-DD') asDate fromDualUnion  AllSelect '2'  asName,to_date ('2018-01-15','YYYY-MM-DD') asDate fromDualUnion  AllSelect '3'  asName, To_date ('2018-01-26','YYYY-MM-DD') asDate fromdual)Selectname, date, Next_d, (Next_d-Date asdifference in days from (      Selectname, date, lead (date,1,NULL) Over(Order  byDate asNext_d fromx0)

The lead function is shown below:(014) Daily SQL Learning: Oracle down LAG and lead analysis functions

(013) Daily SQL Learning: Determine the number of workdays between two dates and calculate the number of dates in a year week