Dynamic Date Range in where clause

General
Enthusiast

Dynamic Date Range in where clause

Hi all,

I have below query where I have hardcoded date-2 and date-1

select

Calendar_Date,

sum(Sales_Value)

from SL_Table

WHERE calendar_date between date-2 and date-1

group by 1

 

 

Is there any change where I can have dynamic date range?

 

Like...

if day is Monday then daterange should be last week monday to sunday.(Complete previous week)

if day is not monday then daterange should be monday to Previous day.(ie. for Friday it should give Monday to Thursday)

 

Please help me!

 

Vinay

2 REPLIES
Senior Apprentice

Re: Dynamic Date Range in where clause

Hi Vinay,

why do you post the same question twice?

What's your TD release?

WHERE calendar_date BETWEEN NEXT_DAY(CURRENT_DATE-8, 'mon') AND CURRENT_DATE-1

WHERE calendar_date BETWEEN TD_MONDAY(CURRENT_DATE-1) AND CURRENT_DATE-1
Enthusiast

Re: Dynamic Date Range in where clause

Hi Vinaynaran,

I have already posted the same reply in your previous post:

The above solution provided by Dieter is for TD 14 release I suppose.

In case you need a solution for previous realeases use the below query:


SELECT 

CURRENT_DATE AS YOUR_CALENDAR_DATE

, CASE

WHEN DAY_OF_WEEK = 2

THEN YOUR_CALENDAR_DATE - 7

ELSE YOUR_CALENDAR_DATE - DAY_OF_WEEK + 2

END AS DATE_2

, CASE

WHEN DAY_OF_WEEK = 2

THEN YOUR_CALENDAR_DATE - 1

ELSE YOUR_CALENDAR_DATE - 1

END AS DATE_1

FROM

SYS_CALENDAR.CALENDAR

WHERE

CALENDAR_DATE = YOUR_CALENDAR_DATE;