Re: Query help? Return the date which is n business days before d (from a table with all days in it)

From: dean <deanbrown3d_at_yahoo.com>
Date: 14 Mar 2007 22:52:39 -0700
Message-ID: <1173937959.788410.325620@e1g2000hsg.googlegroups.com>

On Mar 14, 7:45 pm, "sherifffruitfly" <sherifffruit..._at_gmail.com> wrote:
> Hi all,
>
> I've got a table with columns:
>
> DATE (date type; contains all of the actual calendar days)
> IS_WORKDAY (0 or 1, depending on whether the DATE in that row is a
> work day)
>
> What I want:
>
> Suppose today is 1/20/2007. I want to know how many ACTUAL days back =
> 5 BUSINESS days back. For this example, the answer would be 6 (the
> 15th was apparently a holiday, according to our table).
>
> Another example: If today is a Monday, and I want the ACTUAL # of days
> corresponding to 1 BUSINESS day ago, three would be the answer
> (assuming Friday was a work day).
>
> What query will answer this question for me in general? A co-worker
> suggested the LAG function, but didn't have time to explain it to me
> in greater detail (and I don't totally follow the online
> documentation).
>
> This is going to be responding to a c# client, so hopefully an answer
> won't involve a lot of round-tripping.
>
> Thanks for any assistance,
>
> cdj

Table T holds 1 month here for clarity. THE_DAY is the day number in the entire year, you can use a TDateTime if you like instead.

BUS holds a flag saying whether the day is a business ('Y') or not ('N').

create table T (THE_DAY number, BUS varchar2(1));

insert into T values (1,'Y');
insert into T values (2,'N');
insert into T values (3,'N');
insert into T values (4,'Y');
insert into T values (5,'Y');
insert into T values (6,'Y');
insert into T values (7,'Y');
insert into T values (8,'Y');
insert into T values (9,'N');
insert into T values (10,'N');
insert into T values (11,'Y');
insert into T values (12,'Y');
insert into T values (13,'Y');
insert into T values (14,'Y');
insert into T values (15,'Y');
insert into T values (16,'N');
insert into T values (17,'N');
insert into T values (18,'Y');
insert into T values (19,'Y');
insert into T values (20,'Y');
insert into T values (21,'Y');
insert into T values (22,'Y');
insert into T values (23,'N');
insert into T values (24,'N');
insert into T values (25,'Y');
insert into T values (26,'Y');
insert into T values (27,'Y');
insert into T values (28,'Y');
insert into T values (29,'Y');
insert into T values (30,'N');
insert into T values (31,'N');

with ALL_BUS_DAYS as
( select THE_DAY, BUS, rownum R from
  ( select * from T
    where BUS = 'Y'
    order by THE_DAY ) ),
BUS_DAYS_FIVE_APART as
(
  select B2.THE_DAY FIRST_BUS_DAY_BEFORE_DATE, B1.THE_DAY FIFTH_BUS_DAY_BACK from ALL_BUS_DAYS B1 inner join ALL_BUS_DAYS B2   on B1.R = (B2.R - 5) -- <- change to 4 to used current day as one business day
),
DAYS_WITH_LAST_BUS_DAYS as
(
  select THE_DAY, BUS,
  (select max(THE_DAY) from T T2 where T2.THE_DAY <= T.THE_DAY and BUS = 'Y') LAST_BUS_N
  from T
)
select THE_DAY, FIFTH_BUS_DAY_BACK, (THE_DAY - FIFTH_BUS_DAY_BACK) as ACTUAL_DAYS_DIFF
from DAYS_WITH_LAST_BUS_DAYS inner join BUS_DAYS_FIVE_APART on DAYS_WITH_LAST_BUS_DAYS.LAST_BUS_N = BUS_DAYS_FIVE_APART.FIRST_BUS_DAY_BEFORE_DATE; ANSWER:   THE_DAY FIFTH_BUS_DAY_BACK ACTUAL_DAYS_DIFF

---------- ------------------ ----------------
         8                  1                7
         9                  1                8
        10                  1                9
        11                  4                7
        12                  5                7
        13                  6                7
        14                  7                7
        15                  8                7
        16                  8                8
        17                  8                9
        18                 11                7
        19                 12                7
        20                 13                7
        21                 14                7
        22                 15                7
        23                 15                8
        24                 15                9
        25                 18                7
        26                 19                7
        27                 20                7
        28                 21                7
        29                 22                7
        30                 22                8
        31                 22                9

Dean

---

NOTES: If you are using ADO, you may need to encompass that statement in a select * from (above query).

ALL_BUS_DAYS gives the ordering row number of all the 'Y' days::

   THE_DAY B R
---------- - ----------

         1 Y          1
         4 Y          2
         5 Y          3
         6 Y          4
         7 Y          5
         8 Y          6
        11 Y          7
        12 Y          8
        13 Y          9
        14 Y         10
        15 Y         11
        18 Y         12
        19 Y         13
        20 Y         14
        21 Y         15
        22 Y         16
        25 Y         17
        26 Y         18
        27 Y         19
        28 Y         20
        29 Y         21

BUS_DAYS_FIVE_APART gives the day number of the current day with the day number of the 5th back business day, taking into account weekends and holidays ('N' days):

FIRST_BUS_DAY_BEFORE_DATE FIFTH_BUS_DAY_BACK

------------------------- ------------------
                        8                  1
                       11                  4
                       12                  5
                       13                  6
                       14                  7
                       15                  8
                       18                 11
                       19                 12
                       20                 13
                       21                 14
                       22                 15
                       25                 18
                       26                 19
                       27                 20
                       28                 21
                       29                 22

DAYS_WITH_LAST_BUS_DAYS gives Received on Thu Mar 15 2007 - 00:52:39 CDT