Unable to retrieve TypeId from DocuRef table for SSRS report

[Ventainais]

Цитата:

I see in your code if startDateTime contains current 2019 year then endDateTime should contain the date of 2018 year:

Цитата:

Here you substract the value of 1 year from startDateTime and assign result to endDateTime. Such conditions do not allow you to get something else than nothing.

Not really sure what You mean by this. The two dateTime variables are used to fetch DocuRef records by field CreatedDateTime on one year period from current date.

Цитата:

No, it doesn't look correct. Due to your current conditions in a case when docuRef.TypeId != #SPP you should pull the records for any company (RefCompanyId) and any table (RefTableId). I'm sure you pulled the records with empty TypeId field right for this case. Enclose following code in additional braces and you will see the difference.

This got me thinking and I rewrote the while select statement as follows:

while select createdBy, createdDateTime, TypeId from docuRef
        where docuRef.RefTableId    == tableNum(CustTable)  &&
             ((docuRef.createdDateTime >= endDateTime   &&
              docuRef.createdDateTime <= startDateTime     &&
              docuRef.TypeId == #SPP) || docuRef.TypeId != #SPP)
        join AccountNum, Party from custTable
            where custTable.RecId       == docuRef.RefRecId     &&
                  custTable.dataAreaId  == docuRef.RefCompanyId &&
                  custTable.Blocked     == CustVendorBlocked::No
        exists join partyLocation
            where partyLocation.IsPrimary   == true   &&
                  partyLocation.Party       == custTable.Party
        exists join postalAddress
            where postalAddress.Location == partyLocation.Location
        exists join addressCountryRegion
            where addressCountryRegion.CountryRegionId      == postalAddress.CountryRegionId    &&
                  addressCountryRegion.SanctionedCountry_mb == NoYes::Yes
    {
        tmpCustVendListTable.clear();
        
        if (custVendAcc != custTable.AccountNum)
        {
            custVendAcc = custTable.AccountNum;
        }
        else
        {
            if (docuRef.TypeId != #SPP)
                continue;
        }

        tmpCustVendListTable.BPAccountNum       = custVendAcc;
        tmpCustVendListTable.BPName             = custTable.name();

        if (docuRef.TypeId == #SPP)
        {
            tmpCustVendListTable.ResponsibleUser    = XUserInfo::find(false, docuRef.createdBy).name;
            tmpCustVendListTable.ScreeningDateTime  = docuRef.createdDateTime;
        }

        tmpCustVendListTable.insert();
    }

Now when I execute the report I get the results I need.
Thank you dech.