Forum Stats

  • 3,728,529 Users
  • 2,245,647 Discussions
  • 7,853,567 Comments

Discussions

SQL: exclude weekend while comparing results from previous day.

User_5MAND
User_5MAND Member Posts: 1 Green Ribbon

Hi,

I am trying to compare the result from today to previous working day,

ex: select * from T1 where  ASOF = trunc(sysdate)

select * from T2 where ASOF = trunc(sysdate-1)

select  t1.asof,   t1.Count-t2.count as difference from T1,T2

but if there will be any weekend then i need to compare the today's result with last working day(friday) dynamically, i can write this as ASOF = trunc(sysdate-3) but this will hardcode and will not work for last working day.

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,222 Silver Trophy

    Well, the normal solution is to have a table that will contain all the legal holidays, like Christmas, second day after Christmas, Thanksgiving and whatever applies to that country. That is the way we dealt with this kind of problem at banks so that we would know to have transactions affecting balances only on working days and not on holidays.

  • BEDE
    BEDE Oracle Developer Member Posts: 2,222 Silver Trophy

    The structure of such a calendar table would be like below:

    crate table transaction_calendar (

    actual_date date not null

    ,transaction_date date not null

    )

    ;

    alter table transaction_calendar add constraint transaction_calendar_pk primary key (actual_date);

    And there, for actual date 2021-04-03 and 2021-04-04 you would have transaction_date 2021-04-05. And for actual_date 2021-04-05, transaction_date will be 2021-04-05, that being a working day. For 2020-12-25, which is Christmas, you will have transaction_date 2020-12-28, on the next working day after Christmas.

    Then you may create a function add_working_days(p_date date, p_days number) which you will use in the future wherever you may need.

  • User_H3J7U
    User_H3J7U Member Posts: 86 Blue Ribbon

    Mo->Fr, Tu->Mo, We->Tu, Th->We, Fr->Th, Sa->Fr, Su->Fr

    least(d-1, trunc(d-1,'iw')+4)
    
    Frank Kulashcormaco
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,322 Red Diamond
    edited April 5

    Hi, @User_5MAND

    Bede is right; it's not much work to create and populate a calendar table. You only need to do that once, then you can use it not only to solve problems like this one, but also things like "What is the 10th business day after ...?" or "How many work days are between ... and ...?" See PL/SQL function to calculate non-working days — oracle-tech

    If you must do it without calendar table here's another way:

    You want an expression that normally returns the date from 1 day ago, but, when you run it on a Monday, it should return the date from 3 days ago. That's

    TRUNC (SYSDATE) - CASE TO_CHAR (SYSDATE, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH')
       		      WHEN 'MON' THEN 3
    		      WHEN 'SUN' THEN 2 -- If needed
    		                 ELSE 1
       		  END
    


Sign In or Register to comment.