Window function not correctly validation on lookup fields

Question

Hi, I created a field StorageReportIndicator in a table to rank the records in the table.
This rank is a SQL window function RANK which combines table fields initially loaded from the data source (StorageUnit and ReferenceDateTime below) and lookup fields via table joins (DSA.Datum table, fields WeekKey and DayOfWeek).

Goal is to have a rank to determine the first record of each week for each storageUnit. The outcome afer execution is that all records have rank 1 which is incorrect. I verified this with the same SQL statement in Management Studio.

Could it be that this window function does not have the right result since the lookup is done after the rank is calculated? Note, the table is not historical and incremental.
Anyone with experience with this case and is there a proper solution?

icon

Best answer by Christian Hauggaard 16 January 2024, 04:45

View original

Christian Hauggaard · Accepted Answer

Hi @erwin.pasIt seems that the StorageReportIndicator currently has a bit data type, and as such accepts 0, 1 and NULL as output. Is this intentional?

erik.van.mastrigt · Answer

Could it be that this window function does not have the right result since the lookup is done after the rank is calculated?

Hi @erwin.pas , the lookup is done first. From a technical perspective that statement is not 100% correct :) but as long as [StorageReportIndicator] is just a field with a transformation that is not being used in relations, conditions etc. it should work fine.

As @Christian Hauggaard suggests, I think you should change the datatype. RANK() returns a bigint datatype. If you’re not expecting any big numbers, changing the field to int or smallint will be fine too.

Window function not correctly validation on lookup fields

3 replies

Reply

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded