Search code examples
sqlsql-serverreporting-servicesssrs-2012

SSRS lookup with datediff Incorrect result error showing in fields

I have to write again this problem. I am not an expert in SSRS. I have been trying to resolve this issue. Even though the fields are all the same datatype am still getting error and incorrect time difference in the filed in some fields. I do not know what am doing wrong. This is the code. Please find below screen shot also. 

enter code =Datediff(DateInterval.Minute, Fields!health_start_date.Value, Lookup(Fields!flight_date.Value & Fields!register_number.Value &  DATEPART(DateInterval.Hour,Fields!health_start_date.Value) , Fields!FL_DATE.Value & Fields!REG.Value & DatePart(DateInterval.Hour, Fields!ATD.Value), Fields!ATD.Value, "DataSetAIMS") ) MOD 60 & " mins "

here

The scenario is that the two fields Health start date and ATD have two different dataset which shows time of flight differently but the time difference is always in minutes. What am trying to do is to compare the two table with their hour and date using lookup to find difference in minutes This is the first dataset table

This is DataSetAIMS table

Solution

  • Okay, I think I see the problem: you're really close and this stuff trips us all up on occasion. In the LOOKUP function, I think you are joining the two data sets based on:

    • flight_date = FL_DATE, and
    • register_number = REG, and
    • HOUR(health_start_date) = HOUR(ATD)

    Which is fine, but the ampersand (&) just "concatenates" strings together, and (a) your strings don't actually match, and (b) HOUR is a number, not a string.

    (a) The "date" in the first data set is DDMMYYYY, and in the 2nd it is DDMMYY. I.e., "05222018" is not the same as "052818", so your lookup will not work. It's possible that they are stored correctly as date fields in the database and so the comparison will work fine, but better to force them to match. For that I'd recommend formatting each of the date strings into a standard format, like "FormatDateTime(Fields!flight_date.Value, DateFormat.VBShortDate)".

    (b) Should be easy, just add ".ToString" after the parentheses, like "DATEPART(DateInterval.Hour,Fields!health_start_date.Value).ToString()".

    (c) I'm assuming the register_number and REG are formatted the same.

    Given all that, your new LOOKUP function might look something like this:

    =Datediff(DateInterval.Minute, 
    Fields!health_start_date.Value, 
    Lookup(
        FormatDateTime(Fields!flight_date.Value, DateFormat.VBShortDate) & Fields!register_number.Value &  DATEPART(DateInterval.Hour,Fields!health_start_date.Value).ToString() , 
        FormatDateTime(Fields!FL_DATE.Value, DateFormat.VBShortDate) & Fields!REG.Value & DatePart(DateInterval.Hour, Fields!ATD.Value).ToString()
        , Fields!ATD.Value
        , "DataSetAIMS") 
        ).ToString() & " minutes"

    However, I think you'd make your life easier by just doing that sort of calculation in the database layer, in the report query, something like:

    SELECT 
    d1.FlightDate
    , d1.RegisterNumber
    , d1.HealthStartDate
    , d2.ATD
    , DiffInMinutes = DATEDIFF(MINUTE, d1.HealthStartDate, d2.ATD)
FROM DataSet1 d1
    JOIN DataSetAIMS d2
        ON CAST(d1.FlightDate AS DATE) = CAST(d2.FL_DATE AS DATE)
        AND d1.register_number = d2.REG
        AND DATEPART(HOUR, d1.health_start_date) = DATEPART(HOUR, d2.ATD)

    This code certainly isn't exact, but hopefully it will get you pointed in the right direction!