Dynamic Date Range in where clause

Database
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

4 REPLIES
Junior Supporter

Re: Dynamic Date Range in where clause

Hi Vinaynaran,

Hope the below query helps you.

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;

Enthusiast

Re: Dynamic Date Range in where clause

Hi RS,

Thanks for Reply. This is what I am looking for but is not working. please see below qry. its take to much time and does not pull any data.

select calendar_date,
sum(Scanned_Sales_Value) as Daily_Sales_Value
from VWI0DSS_DAILY_STORE_SALES
where calendar_date IN 
(
SELECT
CASE WHEN DAY_OF_WEEK = 2
  THEN CURRENT_DATE - 7
  ELSE CURRENT_DATE - DAY_OF_WEEK + 2
END AS DATE_2
 FROM
  SYS_CALENDAR.CALENDAR
 WHERE
CALENDAR_DATE = CURRENT_DATE
)
group by 1;

if i run same code with below qry its work ok.

select calendar_date,
sum(Scanned_Sales_Value) as Daily_Sales_Value
from VWI0DSS_DAILY_STORE_SALES
where calendar_date IN 
( date '2014-12-15')
group by 1;

do i need to use cast? if yes then please can you tell me how?

Thanks

Vinay

Junior Supporter

Re: Dynamic Date Range in where clause

Hi Vinay,

As per the query you pasted here the data will be pulled (I am quite sure on this. I suppose you ran both queries on different days).

As per the time perspective,various factors come into consideration like stats the environment... But as of now can you please try the below queries check for performance

SELECT 
CALENDAR_DATE
, SUM(SCANNED_SALES_VALUE) AS DAILY_SALES_VALUE
FROM
MDSP_DATAMART_T.VWI0DSS_DAILY_STORE_SALES
WHERE
CALENDAR_DATE
= (
SELECT
CASE
WHEN DAY_OF_WEEK = 2
THEN CURRENT_DATE - 7
ELSE CURRENT_DATE - DAY_OF_WEEK + 2
END AS DATE_2
FROM
SYS_CALENDAR.CALENDAR
WHERE
CALENDAR_DATE = CURRENT_DATE
)
GROUP BY 1;

SELECT
CALENDAR_DATE
, SUM(SCANNED_SALES_VALUE) AS DAILY_SALES_VALUE
FROM
VWI0DSS_DAILY_STORE_SALES
INNER JOIN
(
SELECT
CASE
WHEN DAY_OF_WEEK = 2
THEN CURRENT_DATE - 7
ELSE CURRENT_DATE - DAY_OF_WEEK + 2
END AS DATE_2
FROM
SYS_CALENDAR.CALENDAR
WHERE
CALENDAR_DATE = CURRENT_DATE
) B
ON CALENDAR_DATE = DATE_2
GROUP BY 1;

I suppose the first query must be time reducing since "IN" is a time consuming clause.

Thanks,

Rohan Sawant

Enthusiast

Re: Dynamic Date Range in where clause

Hi Rohan,

It working Perfect. Thank you very much for your time.

Thanks

Vinay