Time related where clause

General
New Member

Time related where clause

Hi I have written a code to show me was prices of some products and I also have some current prices.

 

What I want to do is find out which date the current price equalled the was price

 

I have tried a where clause which says where was price equals current price, but I can't get it to bring back the date that the start and end dates matched. Does anyone know how to help, please?

 

select
a.pc_dt -- date,
a.tlu_efct_dt -- product start date,
a.tlu_end_dt -- product end date,
a.prod_cd -- product,
max(a.was_prc) -- was price,
max(a.prod_lst_prc) -- current price,

 

from BUS_UNIT_PRC a

join products b
on b.prod_cd = a.prod_cd

 

join chains c
on c.chn_cd = a.bsnu_cd

 

join CALENDAR g
on g.CLDR_DT= f.PC_DT

 

where
a.BSNU_ARA_CD = '01'
AND a.BSNU_TYP_CD = '01'
AND a.CURR_IND = '1'
--AND a.WAS_PRC > 0
--AND a.tlu_IND = 'N'
AND c.chn_cd = 06
AND prod_typ_cd = '01'
AND ( g.PC_DT_STS_CD = '1' )
AND g.RLT_FNCL_DAY_NUM IN (-7, -6, -5, -4, -3, -2, -1)
AND a.prod_lst_prc = a.was_prc


group by
a.tlu_efct_dt,
a.tlu_end_dt,
a.prod_cd,

 

 

 

 

 

 

 

 

 

 

 

 

1 REPLY
Junior Contributor

Re: Time related where clause

It's hard to tell what you're trying tom achieve, but its probably some Windowed Aggregate.

 

Can you show some example data after the join, but before the agregation and explain what you want as result and why?