i have fact table with lots of data...I have claim value ,policy number ,policy expiry date , policy date like columns in the table.
i need to take max of claim value where file should be latest file (have fike_master_ids in file_master tables )
and policy date >'date' (date should be first day of the date based on the file upload date )..ie..if file is upload on feb ...
policy date >'2/1/2022'
and policy expiry date >='date' (date should be last day of the previous month+last year based on the file upload date )..ie..if file is upload on feb ...policy expiry date >='1/31/2021'...Based on these above condition i need to create a report
1)max of claim value....
2)for latest file uploaded....eg:if file is upload on feb
3)policy date >'2/1/2022'
4)policy expiry date >='1/31/2021