Rolling 12 month filter criteria in SQL

Having an issue in SQL script where I’m trying to achieve filter criteria of rolling 12 months in the day column which stored data as a text in server.

Goal is to count sizes for product at retail store location over the last 12 months from the current day. Currently, in my query I’m using the criteria of year 2019 which only counts the sizes for that year but not for rolling 12 months from current date.

CALENDARDAY column is in text field in the data set and data stores in yyyymmdd format.

When trying to run below script in Tableau with GETDATE and DATEADD function it is giving me a functional error. I am trying to access SAP HANA server with below query.

Any help would be appreciated

Select

SKU, STYLE_ID, Base_Style_ID, COLOR, SIZEKEY, STORE, Year,
count(SIZEKEY)over(partition by STYLE_ID,COLOR,STORE,Year) as SZ_CNT

from 
(
select 
a."RAW" As SKU,
a."STYLENUM" As STYLE_ID,
mat."BASENUM" AS Base_Style_ID, 
a."COLORNUM" AS COLOR,
a."SIZE" AS SIZEKEY,
a."STORENUM" AS STORE,
substring(a."CALENDARDAY",1,4) As year

from PRTRPT_XRE as a
JOIN ZAT_SKU As mat On a."RAW" = mat."SKU"

where a."ORGANIZATION" = 'M20'
and a."COLORNUM" is not null
and substring(a."CALENDARDAY",1,4) = '2019'

Group BY
a."RAW",
a."STYLENUM",
mat."BASENUM", 
a."ZCOLORCD",
a."SIZE",
a."STORENUM",
substring(a."CALENDARDAY",1,4)
)

Answer

Below condition from one of our CALENDAR table also worked same way as ADD_MONTHS mentioned in above response

select distinct CALENDARDAY
from
(
select FISCALWEEK, CALENDARDAY, CNST, row_number()over(partition by CNST order by FISCALWEEK desc) as rnum
from
(
select distinct FISCALWEEK, CALENDARDAY, 'A' as CNST
from CALENDARTABLE
where CALENDARDAY < current_date
order by 1,2
)
) where rnum < 366

Advertisement

Answer