Report generating with dynamic SQL

Database
Enthusiast

Report generating with dynamic SQL

Hi,

I have a scenario, where i need to generate report based on Logic on date.

Am running my report on Tuesday of every week. And pull all the details for the last week.

But have a problem when the last week contains dates from two different months (Ex: 28th Nov, 29th Nov, 30th Nov, 1st Dec, 2nd Dec, 3rd Dec, 4th Dec).

In this particular scenario i need to genarate a report which contains data for "NOV Full month" appended with data from "1st, 2nd, 3rd and 4th of December".

If not it should pull the past weeks data.

Scenarios:

IF ((CURRENT_DATE - extract(day from CURRENT_DATE)+1) AND (CURRENT_DATE - 1)) >= 7

THEN ONE WEEK DATA

ELSE

IF (CURRENT_DATE - extract(day from CURRENT_DATE)+1) AND (CURRENT_DATE - 1) < 7

THEN PREV_WEEK + PREV_MONTH_DATA

FI

Can i achieve this inside my bteq? please suggest an approach for this.

Am using BTEQ for my SQL.

2 REPLIES
Junior Contributor

Re: Report generating with dynamic SQL

Hi Mallesh

this should be what you're looking for:

WHERE x BETWEEN 
CASE
WHEN EXTRACT(DAY FROM CURRENT_DATE) >= 7 THEN CURRENT_DATE - 7
ELSE
ADD_MONTHS(CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE)-1), -1)
END
AND CURRENT_DATE

Dieter

Enthusiast

Re: Report generating with dynamic SQL

Dieter,

Yes thats correct. It worked very well for me.

But tweaked to get my final result as expected.

BETWEEN

      CASE

            WHEN EXTRACT(DAY FROM CURRENT_DATE) >= 7 THEN  (CURRENT_DATE - extract(day from CURRENT_DATE)+1)

      ELSE

            ADD_MONTHS(CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE)-1), -1)

      END

AND CURRENT_DATE

Thanks a lot.

_Mallesh