Forum Discussion

bcharlto's avatar
bcharlto
Frequent Visitor
9 years ago
Solved

Rolling Regular Price Calculations

I am hitting RAM limitations when running a calculated DAX column on 92 million rows. The calculation works on smaller amounts of data. Any ideas on how to convert this to SQL? I can calculate when l...
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi bcharlto,

     

    You can modify the T-SQL query like that to return "Reg Price Column":

     

    select *
, 
(select max([Avg Price Column]) from  CBG_POS_Detail_TEMP b 
where  b.PRODUCT_NBR_PETAILER=a.PRODUCT_NBR_PETAILER and b.STORE_NUMBER=a.STORE_NUMBER and b.WEEK_END_DATE>=DATEADD(DAY,-84,a.WEEK_END_DATE) and b.WEEK_END_DATE<=a.WEEK_END_DATE) c
from CBG_POS_Detail_TEMP a

     

     

     

    Best Regards,

    Qiuyun Yu

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
9 years ago

Hi bcharlto,

 

You can modify the T-SQL query like that to return "Reg Price Column":

 

select *
, 
(select max([Avg Price Column]) from  CBG_POS_Detail_TEMP b 
where  b.PRODUCT_NBR_PETAILER=a.PRODUCT_NBR_PETAILER and b.STORE_NUMBER=a.STORE_NUMBER and b.WEEK_END_DATE>=DATEADD(DAY,-84,a.WEEK_END_DATE) and b.WEEK_END_DATE<=a.WEEK_END_DATE) c
from CBG_POS_Detail_TEMP a

 

 

 

Best Regards,

Qiuyun Yu