• Do you get different results with/without the DISTINCT?

    Here's a reformatted copy of the query. I've added table aliases to make it a little more readable and changed the order of the columns in the GROUP BY to see which ones are in the output list and which are not.

    SELECT DISTINCT

    h.RepairSheetNr,

    v.unitnr,

    v.groupid,

    v.brandId,

    v.modelId,

    v.version,

    v.Type_Fleet,

    h.close_date,

    -- you don't need this CASE because the condition already exists in a join: v.unitnr = h.UnitNr

    -- count(CASE WHEN h.unitnr = v.unitnr THEN v.modelId ELSE 0 END) AS TotalModels

    COUNT(v.modelId) AS TotalModels

    FROM Vehicle v

    INNER JOIN RepairSheetH h

    ON v.unitnr = h.UnitNr

    INNER JOIN RepairSheetL l

    ON h.RepairSheetNr = l.RepairSheetNr

    INNER JOIN CashFlow_Accounts cf

    ON h.Company = cf.Company

    AND l.cashflow = cf.CashFlow_AccID

    WHERE l.cashflow = '201708'

    AND v.Type_Fleet<>'FS'

    GROUP BY

    h.RepairSheetNr,

    v.unitnr,

    v.groupid,

    v.brandId,

    v.modelId,

    v.version,

    v.Type_Fleet,

    h.close_date,

    cf.CashFlow_AccID,

    h.Date,

    h.total_value,

    l.cashflow,

    h.UnitNr


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]