Calculate previous working day

building reports webintelligence
1

Hello there,

I am new to the forum and I looked for similar topic but no luck.
I am using webi xi and i want to create a query that will allow me to retrieve the previous working day (therefore excluding Saturday/sunday). so far I got this written:

Select
trunc(sysdate)
Case
when (trunc(sysdate) - trunc(sysdate, ‘IW’)) in (0,5,6)
Then (trunc(sysdate - 1, ‘IW’) + 4)
Else trunc(sysdate - 1)
end

From databasexxx

This code when I run it gives me today’s date and not 8th September 15.

The format of the date in my database is DD/MM/YYYY 12:00:00 Am

Thanks in advance for any input and help on this.

didil (BOB member since 2015-09-08)

2

You dont say what database it is, so assuming due to the trunk function, that its Oracle. I think theres a few ways to do this. You could try;

select trunc(to_date(‘07/09/2015’,‘dd/mm/yyyy’) - 1,‘IW’) +
least(trunc(to_date(‘07/09/2015’,‘dd/mm/yyyy’) - 1) -
trunc(to_date(‘07/09/2015’,‘dd/mm/yyyy’) - 1,‘IW’),4) prev_work_day from dual

ABILtd :uk: (BOB member since 2006-02-08)

3

try this,

Select 
 Case 
 when to_char(sysdate, 'd')=1 then sysdate-2
 when  to_char(sysdate, 'd')=2 then sysdate-3 
 Else sysdate-1 end PrevDay
from dual

Thanks,
Zaif

zaif235 :us: (BOB member since 2010-06-15)

4

Thanks Abiltd for your prompt answer but I literally past this query and got an error message saying :" The Sql query has 1 instead of 3 columns. (Wis 10810)"

didil (BOB member since 2015-09-08)

5

Hi Zaif, thanks also for your input. Unfortunately i got the same error as with Abiltd query: sql query has 1 instead of 3 columns

didil (BOB member since 2015-09-08)

6

Sorry 1 instead of 2 columns for both query tried

didil (BOB member since 2015-09-08)

7

Dude, we need more info.

What database are you using and where are you testing the query? I wrote that script in Oracle SQL developer and it works fine (otherwise I wouldn`t have posted it!).

ABILtd :uk: (BOB member since 2006-02-08)

8

Hello I am using Oracle as well and it works actually!
Iwas using the query on the wrong object. Both queries works! :slight_smile:
Many thanks

didil (BOB member since 2015-09-08)

9

I would go with Zaifs response, less functions, so better performance.

ABILtd :uk: (BOB member since 2006-02-08)

10

I have 2 other query guys:

  • I would like to list all the dates within the current quarter(quarter to date) so something like that (as we are in Q3 2015):
    01/07/2015
    02/07/2015
    03/07/2015
    etc … up to 07/09/2015 (which is yesterday data because it is the latest data available). If I run it tomorrow then i will have 01/07/2015 up to 08/09/2015.

-same as above but on the year to date basis (from 01/01/2015 up to 07/09/2015.

Is it doable?

thanks for your help!

didil (BOB member since 2015-09-08)

11

Current quarter start and end.

select TRUNC(sysdate+1, ‘Q’) StartOfCurQuarter,TRUNC(ADD_MONTHS(sysdate, +3), ‘Q’)-1 EndOfCurQuarter from dual

For current year, can`t you just get the

Current Year start and end.

select TRUNC(sysdate,‘Y’) StartOfThisYear, sysdate from dual

I would do some research around the Oracle Trunc function, it`s overloaded so does a ton of different things depending on the types and parameters passed.

ABILtd :uk: (BOB member since 2006-02-08)

12

Hi AbiLtd,

Problem is that i want one single column listing all the dates between
stert of current quarter and end of current quarter.
I am certainly missing something here to link the two.

I really am a beginner and surfed a lot on this topic and also my initial query before coming into this forum.

Thanks

didil (BOB member since 2015-09-08)

13

Leaning by loosing…I found the solution using a simple between and function!!.

Thanks again :slight_smile:

didil (BOB member since 2015-09-08)