Hello SQL query team ,
I need to create a dimension in my database ( to create later a report in Power BI) and I am struggling with the stored procedure. In fact, I need to have the following table dim_table as a result.
| passport number | passport_expiry_date | is_valid_expiry_date | consolidated_expiry_date |
| ALJDLJZ | 7/10/2020 | 1 | 7/10/2020 |
| ALJDLJZ | 7/10/2020 | 1 | 7/10/2020 |
| ALJDLJZ | NULL | 1 | 7/10/2020 |
| ALJDLJZ | 8/10/2021 | 1 | 7/10/2020 |
DONE: I have already the passport numbers and passport expiry dates in the table and and I have updated the columns 'is_valid_expiry_date' to consider NULL and dates in the future as valid..
TO BE DONE: I need to update the consolidated column based on these rules:
- we consider just records with is_valid_expiry_date=1
-if count( distinct(passport_expiry_date))=1 (just one valid date)then, this latter should be in each line in the consolitaed column.
- if we have different dates ( as in our example), we need to take the most frequent one (7/10/2020 (repeated twice)) and put it in each line as a consolidated value.
--------------------------
update t
[consolidated_passport_expiry_date]=
CASE WHEN count(distinct [passport_exipiry_date])=1 OR count(distinct [passport_exipiry_date])=0 THEN t.passport_exipiry_date
WHEN.........................
WHEN..................
ELSE
END
FROM dim_table t
where t.passport_expiry_dateis_valid_expiry_date=1
GROUP BY t.[passport_number], [passport_exipiry_date]
------------------------
Thanks in advance for any help or tips to complete this update section.
Sabrina
