Post reply

SQL MAX() date Query in SSRS

  • July 9, 2019 at 9:07 am

    #3661038

    Hi,

    I have two tables Systems (v_R_System SYS) and Warranty (WARRANTY_INFO_DATA). Systems table contains computer machine information and the Warranty table contains the warranty information of the machines in the Systems table. A machine can have multiple warranty entries in the warranty table.

    I would like to create a query to display the longest warranty associated with a machine.

    The issue I'm having is when a machine with a warranty appears the machine name is repeated.

    System table

    ResourceID | Name 

    Warranty table

    MachineID | Name | Warranty Start | Warranty End | Warranty Description

    SELECT DISTINCT

    SYS.Name0 AS 'Machine Name',
    WAR.StartDate00 AS 'Warranty Start',
    WAR.EndDate00 AS 'Warranty End',
    WAR.ServiceLevelDescription00 AS 'Warranty Description'

    FROM
    v_R_System SYS

    LEFT JOIN (SELECT WARRANTY_INFO_DATA.MachineID, MAX(WARRANTY_INFO_DATA.EndDate00)AS MaxEndDate FROM WARRANTY_INFO_DATA GROUP BY WARRANTY_INFO_DATA.MachineID) AS NewW ON SYS.ResourceID = NewW.MachineID

    LEFT JOIN WARRANTY_INFO_DATA WAR ON NewW.MachineID = sys.ResourceID

    ORDER BY sys.Name0

  • July 9, 2019 at 4:32 pm

    #3661251

    Sounds like a job for CROSS APPLY.

    SELECT e.EquipmentName
     , lw.WarrDescription
     , lw.StartDate
     , lw.EndDate
    FROM Equipment e
    CROSS APPLY (SELECT TOP 1 StartDate, EndDate, WarrDescription
                 FROM Warranty w
                 WHERE w.OnEquipment = e.EquipmentID
                 ORDER BY DATEDIFF(d,StartDate,EndDate) DESC) lw

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply