How do I select the most current date

  • I would like to select all records that have an expiration date in 2016 if the record does not have an expiration date in 2017.

    SELECT DISTINCT

    FAC.[Facility_Name],

    FAC.[Facility_Type],

    FAC.[Status],

    CASE

    WHEN LIC.License_Expiration_Date > '1/1/2017' THEN LIC.License_Expiration_Date

    ELSE LIC.License_Expiration_Date

    END,

    LIC.License_Type

    FROM [dbo].[FacilityLocations] FAC

    INNER JOIN [dbo].[License_Tracking] LIC ON FAC.FacilityID = LIC.FacilityID

    ;


     

    Any help is appreciated, thanks.

  • Edit: read the question wrong. 
    Difficult to test without sample data.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • Here's an option. Note that this is untested as I don't have anything to test on.

    SELECT
      FAC.[Facility_Name],
      FAC.[Facility_Type],
      FAC.[Status],
      LIC.License_Expiration_Date,
      LIC.License_Type
    FROM [dbo].[FacilityLocations] FAC
    JOIN [dbo].[License_Tracking] LIC ON FAC.FacilityID = LIC.FacilityID
    WHERE LIC.License_Expiration_Date >= '20170101'
    UNION ALL
    SELECT
      FAC.[Facility_Name],
      FAC.[Facility_Type],
      FAC.[Status],
      LIC.License_Expiration_Date,
      LIC.License_Type
    FROM [dbo].[FacilityLocations] FAC
    JOIN [dbo].[License_Tracking] LIC ON FAC.FacilityID = LIC.FacilityID
    WHERE LIC.License_Expiration_Date >= '20160101'
    AND LIC.License_Expiration_Date < '20170101'
    AND NOT EXISTS( SELECT *
            FROM [dbo].[License_Tracking] LIC
            WHERE FAC.FacilityID = LIC.FacilityID
             AND LIC.License_Expiration_Date > '20170101')

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • /**** CROSS APPLY: This will perform well if LIC is dense (many licenses for each facility)
        and there is an appropriate index on at least FacilityID and License_Expiration_Date. ****/
    SELECT
        FAC.Facility_Name,
        FAC.Facility_Type,
        FAC.[Status],
        License_Expiration_Date,
        LIC.License_Type
    FROM dbo.FacilityLocations FAC
    CROSS APPLY
    (
        SELECT TOP 1 *
        FROM dbo.License_Tracking LIC
        WHERE FAC.FacilityID = LIC.FacilityID
        ORDER BY LIC.License_Expiration_Date DESC
    ) LIC
    ;

    /**** PRE-AGGREGATE ****/
    WITH license_expirations AS
    (
        SELECT FacilityID, License_Type, MAX(License_Expiration_Date) AS License_Expiration_Date
        FROM dbo.License_Tracking
        GROUP BY FacilityID, License_Type
    )
    SELECT
        FAC.Facility_Name,
        FAC.Facility_Type,
        FAC.[Status],
        LIC.License_Expiration_Date,
        LIC.License_Type
    FROM FACILITY FAC
    INNER JOIN license_expirations LIC
        ON FAC.FacilityID = LIC.FacilityID

    This is UNTESTED code, because no sample data/expected results were provided.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • @SSChampion
    Looks like your code worked, thank you very much. I need to check the data but no far so good.

Viewing 5 posts - 1 through 4 (of 4 total)

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