Selecting distinct years from a dimension

Anonymous — Wed, 04 Apr 2018 15:36:23 GMT

i'm creating a qvd from a fact table that has multiple date dimensions associated with it.

I want to do a couple of things.

Pull only the dates I will be loading from the fact table which I can do with the following query.
Display only the distinct Years from those dates to the users.

The problem is that these are actually different dimension keys so I will get duplicate years.

Is there a way to extract just the unique years but still have the dim keys to associate with the model?

select distinct wpdd.plcy_dt_dim_key, PLCY_DT_YR_YY as "Policy Year",wpdd.*

From AWH.WR_MTD_COMB_DTL_F wmcdf, AWH.WR_VAL_DT_D, awh.wr_plcy_dt_d wpdd

where wmcdf.VAL_DT_DIM_KEY = WR_VAL_DT_D.VAL_DT_DIM_KEY

and wpdd.plcy_dt_dim_key = wmcdf.plcy_dt_dim_key

and AWH.WR_VAL_DT_D.VAL_DT_DAY_MDY >= '01-JAN-2013'

and AWH.WR_VAL_DT_D.VAL_DT_DAY_MDY <= '01-JAN-2018'

order by 2

topic Selecting distinct years from a dimension in Connectivity & Data Prep