Comparison between Series dates and Film dates, the Series dates need to be within the Film Dates
I have this requirement, I have individual film date records, but there are multiple transactions per record.
I have joined
LEFT JOIN (VLFactTable) LOAD
VLPolicyKey VLPolicyNumber, VLFilmPolicies, VLBBNIPolicies, Min(Date(Floor(VLFIlmEffectiveDate),'DD/MM/YYYY')) as VLFEffectiveDateMin, Max(Date(Floor(VLFilmExpiryDate),'DD/MM/YYYY')) as VLFExpiryDateMax, Resident VLFactTable Group By VLPolicyNumber,VLLTAPolicies,VLBBNIPolicies;
LEFT JOIN (VLFactTable) LOAD
VLPolicyKey,
VLPolicyNumber, Date(SeriesStartDate, 'DD/MM/YYYY') as VLSStartDate, Date(SeriesEndDate, 'DD/MM/YYYY') as VLSEndDate, FROM [lib://QVD/DTL\LongTermAgreement.QVD] (qvd)
I am just struggling with the flag on how to get this requirement correct. I currently have :
If(Date(VLFEffectiveDateMin,'DD/MM/YYYY') <= Date(VLSStartDate,'DD/MM/YYYY') AND Date(VLExpiryDateMax,'DD/MM/YYYY') >= Date(VLSEndDate,'DD/MM/YYYY'),1,0) AS VL03Flag,
Is there a different way to achieve what I am trying to achieve? It does not seem to work correctly
After your 2nd left join do you see all the 4 dates you need for your calculation, do you see them in single row? Whats your date format, can you share some sample data?
