0

# HANA SQL:- FIND LAST SATURDAY OF EVERY CALENDAR MONTH IN THE YEAR

Jan 16 at 02:59 PM

63

Former Member

Hi

Database: HANA SQL

View: Hana Calculation with Script Based View

I have to display the last Saturday of every month in the calendar year in HANA SQL query.

Example:-

Year 01-01-2017 to 31-12-2017.

The logic is,

Need to display the list of all "LAST" Saturday in each month should be like 'Y' and rest of all dates = 'N'.

I tried the below concepts.

Assumption: CAL_DATE = '01-01-2017' to '31-12-2017'

DAYNAME

(

CASE

WHEN DAYNAME(LAST_DAY(CAL_DATE)) = 'SUNDAY' THEN ADD_DAYS(LAST_DAY(CAL_DATE),-1)

WHEN DAYNAME(LAST_DAY(CAL_DATE)) = 'MONDAY' THEN ADD_DAYS(LAST_DAY(dt7.CAL_DATE),-2)

WHEN DAYNAME(LAST_DAY(CAL_DATE)) = 'TUESDAY' THEN ADD_DAYS(LAST_DAY(CAL_DATE),-3)

WHEN DAYNAME(LAST_DAY(CAL_DATE)) = 'WEDNESDAY' THEN ADD_DAYS(LAST_DAY(CAL_DATE),-4)

WHEN DAYNAME(LAST_DAY(CAL_DATE)) = 'THURSDAY' THEN ADD_DAYS(LAST_DAY(CAL_DATE),-5)

WHEN DAYNAME(LAST_DAY(CAL_DATE)) = 'FRIDAY' THEN ADD_DAYS(LAST_DAY(CAL_DATE),-6)

ELSE LAST_DAY(CAL_DATE) END) AS MONTH_END_FLAG

BUT the result is all dates are coming like 'SATURDAY'.

any other suggestion or simplification please provide your valuable inputs.

Thanks

R Sanjay

G B Jan 18 at 02:08 AM
0

Hi Sanjay

You can use M_TIME_DIMENSION table(needs to be populated) to achieve the same.

We filter on the M_TIME_DIMENSION on year and day of week i.e 5 which is Saturday. We would then fetch the max date per month which becomes our last Saturday. In the final step, I am flagging all the Last saturday's as N and rest as Y using a NOT EXISTS check.

```SELECT A.DATE_SQL ,
CASE WHEN NOT EXISTS (SELECT LAST_SAT FROM (SELECT MONTH, MAX(DATE_SQL) as LAST_SAT FROM "_SYS_BI".M_TIME_DIMENSION
WHERE YEAR = '2017' AND DAY_OF_WEEK_INT = 5
GROUP BY MONTH) AS B WHERE A.DATE_SQL = B.LAST_SAT) THEN 'Y' ELSE 'N' END AS FLAG
FROM "_SYS_BI".M_TIME_DIMENSION AS A  WHERE A.YEAR = '2017'
GROUP BY A.DATE_SQL;
```
Share