Generating days between two dates

Slide 1

Slide 1 text

Ask TOM Office Hours Generating days between two dates How to create a row per day in a time period Chris Saxon , Developer Advocate @ChrisRSaxon & @SQLDaily https://blogs.oracle.com/sql https://www.youtube.com/c/TheMagicofSQL Your SQL Office Hours begins soon…

Slide 2

Slide 2 text

Slide 3

Slide 3 text

select dt, count ( id ) from dates left join orders on … group by dt How to create these? Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 4

Slide 4 text

Ask TOM Office Hours Generating days between two dates How to create a row per day in a time period Chris Saxon, Developer Advocate @ChrisRSaxon & @SQLDaily https://blogs.oracle.com/sql https://www.youtube.com/c/TheMagicofSQL

Slide 5

Slide 5 text

Slide 6

Slide 6 text

select date'2021-01-01' + level – 1 as dt from dual connect by level <= :N Note –1! Can be any 1 row table Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 7

Slide 7 text

Slide 8

Slide 8 text

with rws as ( select min ( dt ) mn_dt, max ( dt ) mx_dt from … ), dates as ( select trunc ( mn_dt ) + level - 1 dt from rws connect by level <= mx_dt - mn_dt + 1 ) ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 9

Slide 9 text

Slide 10

Slide 10 text

with rws as ( select min ( dt ) mn_dt, max ( dt ) mx_dt from … ), dates as ( select trunc ( mn_dt ) + level - 1 dt from rws connect by level <= mx_dt - mn_dt + 1 ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 11

Slide 11 text

with rws as ( select min ( dt ) mn_dt, max ( dt ) mx_dt from … ), dates as ( select trunc ( mn_dt ) + level - 1 dt from rws connect by level <= mx_dt - mn_dt + 1 ) Can be any subquery Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 12

Slide 12 text

Slide 13

Slide 13 text

with rws as ( select date'2021-01-01' + level - 1 as dt from dual connect by level <= ( date'2022-01-01' - date'2021-01-01' ) ) select * from rws where to_char ( dt, 'FMDay' ) = 'Monday' Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 14

Slide 14 text

Slide 15

Slide 15 text

with rws as ( select date'2021-01-01' + level - 1 as dt from dual connect by level <= ( date'2022-01-01' - date'2021-01-01' ) ) select * from rws where to_char ( dt, 'FMDay' ) = 'Monday' Return 52 Generate 365 Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 16

Slide 16 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Only select what you need Photo by Gabrielle Henderson on Unsplash You can do better

Slide 17

Slide 17 text

Slide 18

Slide 18 text

select next_day ( date'2021-01-01' - 1, 'Monday' ) + ( level - 1 ) * 7 as dt from dual connect by level <= ( date'2021-12-31' – next_day ( date'2021-01-01' - 1, 'Monday' ) + 7 ) / 7 NLS_LANGUAGE! Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 19

Slide 19 text

select next_day ( date'2021-01-01' - 1, 'Monday' ) + ( level - 1 ) * 7 as dt from dual connect by level <= ( date'2021-12-31' – next_day ( date'2021-01-01' - 1, 'Monday' ) + 7 ) / 7 Normalize to weeks Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 20

Slide 20 text

select next_day ( date'2021-01-01' - 1, 'Monday' ) + ( level - 1 ) * 7 as dt from dual connect by level <= ( date'2021-12-31' – next_day ( date'2021-01-01' - 1, 'Monday' ) + 7 ) / 7 Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 21

Slide 21 text

Slide 22

Slide 22 text

select add_months ( date'2021-01-01', level - 1 ) as dt from dual connect by level <= months_between ( date'2021-12-31', date'2021-01-01' ) + 1 Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 23

Slide 23 text

Slide 24

Slide 24 text

Slide 25

Slide 25 text

Slide 26

Slide 26 text

select date'2021-01-01' + numtoyminterval ( level - 1, 'year' ) as dt from dual connect by level <= ( months_between ( date'2021-12-31', date'2021-01-01' ) / 12 ) + 1 Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 27

Slide 27 text

Slide 28

Slide 28 text

Start date End date 15 Jan 2021 31 Jan 2021 01 Feb 2021 28 Feb 2021 01 Mar 2021 31 Mar 2021 01 Apr 2021 15 Apr 2021 Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 29

Slide 29 text

Slide 30

Slide 30 text

Slide 31

Slide 31 text

select add_months ( date'2021-01-01', level - 1 ) as dt from dual connect by level <= months_between ( date'2021-12-31', date'2021-01-01' ) + 1 with mths as ( ) Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 32

Slide 32 text

select case rownum when 1 then dt else trunc ( dt, 'mm' ) end start_date, lead ( trunc ( dt, 'mm' ) - 1, 1, dt ) over ( order by dt ) end_date from mths Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 33

Slide 33 text

select case rownum when 1 then dt else trunc ( dt, 'mm' ) end start_date, lead ( trunc ( dt, 'mm' ) - 1, 1, dt ) over ( order by dt ) end_date from mths Start date for 1st row Get month start Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 34

Slide 34 text

select case rownum when 1 then dt else trunc ( dt, 'mm' ) end start_date, lead ( trunc ( dt, 'mm' ) - 1, 1, dt ) over ( order by dt ) end_date from mths Get next month Period end for last row Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 35

Slide 35 text

Slide 36

Slide 36 text

Slide 37

Slide 37 text

create function generate_days ( start_date date, end_date date, day_increment integer default 1 ) return varchar2 sql_macro as stmt varchar2(4000); begin … Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 38

Slide 38 text

stmt := 'select start_date + ( level - 1 ) * day_increment as dt from dual connect by level <= ( ( ( end_date - start_date ) + day_increment ) / day_increment )'; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 39

Slide 39 text

Slide 40

Slide 40 text

Slide 41

Slide 41 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL "a code grinder is going to figure it out, while a database programmer is going to spell it out" - Ken Downs https://database-programmer.blogspot.com/ 2007/11/database-skills-introdution.html

Slide 42

Slide 42 text

Slide 43

Slide 43 text

create table cal_dates ( cal_date date check ( cal_date = trunc ( cal_date ) ) not null primary key, is_working_day integer check ( is_working_day in ( 0, 1 ) ) not null, day_of_week varchar2(10 char), … ) organization index; Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 44

Slide 44 text

create table cal_dates ( cal_date date check ( cal_date = trunc ( cal_date ) ) not null primary key, is_working_day integer check ( is_working_day in ( 0, 1 ) ) not null, day_of_week varchar2(10 char), … ) organization index; Ensure 1 row/day Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 45

Slide 45 text

create table cal_dates ( cal_date date check ( cal_date = trunc ( cal_date ) ) not null primary key, is_working_day integer check ( is_working_day in ( 0, 1 ) ) not null, day_of_week varchar2(10 char), … ) organization index; Table is PK index Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 46

Slide 46 text

Slide 47

Slide 47 text

Generate  No objects needed  Can create any date  Rule change = code change Store  Give control to business  Better stats  May have dates missing Generate vs store Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL

Slide 48

Slide 48 text

Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL Further reading Live SQL Script https://livesql.oracle.com/apex/livesql/file/content _LIHBDFVF9IUU6AFZB4H6NVLWL.html How to Find the Next Business Day and Add or Subtract N Working Days with SQL https://blogs.oracle.com/sql/how-to-find-the- next-business-day-and-add-or-subtract-n- working-days-with-sql

Slide 49

Slide 49 text

Generate connect by level < :N Reuse Create SQL macros Store Gives business control Ensure you have enough rows! Getting days in range Copyright © 2021 Oracle and/or its affiliates | @ChrisRSaxon * blogs.oracle.com/sql * www.youtube.com/c/TheMagicofSQL