How do I select the most current date

• kawi6rr

  SSC-Addicted

  Points: 403

  More actions

  February 27, 2017 at 1:00 pm

  #151082

  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.

• Henrico Bekker

  One Orange Chip

  Points: 27652

  More actions

  February 27, 2017 at 1:06 pm

  #1930532

  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

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  February 27, 2017 at 1:06 pm

  #1930533

  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

• drew.allen

  SSC Guru

  Points: 76989

  More actions

  February 27, 2017 at 2:25 pm

  #1930554

  /**** 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

• kawi6rr

  SSC-Addicted

  Points: 403

  More actions

  February 28, 2017 at 5:25 pm

  #1930863

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