Day of Week in derived table

[Former Member]

Hi,

Can someone provide assistance with a SQL date calculation in a derived table? Basically what I need to do is capture prior business day records. The report will be daily (Mon.-Fri.) - when the report updates on Monday I need to capture Friday through Sunday data. When the report is run on Tuesday I need Monday's data, and so on. Please advise, and thanks in advance for your help!

[Former Member]

If you are using oracle database you can use this simple code against your date.

A.Date = (select decode(to_char(sysdate-1,'fmDAY'),'SUNDAY',to_char(sysdate-3,'dd-Mon-yyyy'),to_char(sysdate-1,'dd-Mon-yyyy')) from dual)

Regards,

Aqeel

[Former Member]

got that in select for you, using oracle 10 or 11

its complicated, found the info on the net so if anyone regonizes code thank you very much indeed.

We first defined now in Central European Time Zone, because we want it like that

But change it to a timezone you need or just use now() everywhere this is used.

This is using oracle 10 or 11, dont know if it would work in lower versions.

Now - TZ CET(yyyy-mm-dd HH24:mm:ss) +(meaning today in timezone central euro. and displayed with time) +

CAST((FROM_TZ(CAST(SYS_EXTRACT_UTC(systimestamp) AS TIMESTAMP),'+00:00') AT TIME ZONE 'CET') AS DATE)

and

Now - TZ CET(yyyy-mm-dd)

trunc(@Select(Calendar\Now - TZ CET(yyyy-mm-dd HH24:mm:ss)))

Then we make sure that monday is regarded as the first day of the week.

If your database is set to american its sunday, european its monday

This code make it database setting irreleavant, says monday is 1, tuesday 2 etc.

Now - TZ CET - Day of the Week

mod(to_char(@Select(Calendar\Now - TZ CET(yyyy-mm-dd)), 'J'), 7) + 1

All dimensions are in a class called calendar.

The filter runs from yesterday 16:00 to today 16:00, however when today is a Monday, Saterday and Sunday as shown together.

FilterName DtTm - TZ User - From Yesterday(16:00) to Today(16:00)

@Select(DATE TO RUN ON)

between

@Select(Calendar\Now - TZ User(yyyy-mm-dd)) +4/24 -

Case when @Select(Calendar\Now - TZ User - Day of the Week)=1

then 2

else 1

end

and @Select(Calendar\Now - TZ User(yyyy-mm-dd))+4/24

I think removing the first 4/24 and changing the last +4/24 into +1 (otherwise it will run as today at 00:00:00) will do it for you.

Adding the 1 is tomorrow 00:00:00, same effect as being today,

good luck with it

Edited by: I.Heijnen on Mar 31, 2011 6:05 PM

[Former Member]

Hi, just use oracle fucntionality.

Sysdate()-1 gives prior day.

You can also try with date() bo built in function

[Former Member]

Gowtham Sen Potnuru, please read the question.

If it is Monday, your answer will give Sunday's data, which is clearly not what was wanted.

Regards,

Mark

[Former Member]

Hi LuAnn,

This will depend on the database; what are you using? E.g. SQL Server, Oracle, etc?

For SQL Server, this is the sort of thing you want

Where cast(convert(char(10),SalesDate,23) as datetime) between 
(CASE WHEN substring(datename(dw,getdate()),1,1)='M' THEN cast(convert(char(10),getdate()-3,23) as datetime) ELSE cast(convert(char(10),getdate()-1,23) as datetime) END)
and cast(convert(char(10),getdate()-1,23) as datetime)

Regards,

Mark

[Former Member]

Hi Mark,

Apologies for the delayed response - was pulled onto a critical project which consumed all of my time. The database is SQL Server. Any assistance is greatly appreciated! Thanks!