Days remaining with licenses report

[mvelez]

Can anyone help me with a report to show the number of days left for a license from tblSublicensesOrders.OrderDate minus tblLicenses.LicenseExpiration in days?
Maybe add a column to the BuiltIn report of License: Expiration < XX days.

Thank you so much

[mvelez]

This is perfect. Thank you so much.

[RCorbeil · Accepted solution]

Try this as a starting point:

SELECT
  tblLicenses.softwareName,
  tblSublicensesOrders.Orderdate,
  tblLicenses.LicenseExpiration,
  DATEDIFF(d, tblSublicensesOrders.Orderdate, tblLicenses.LicenseExpiration) AS LicenseDuration,
  DATEDIFF(d, GETDATE(), tblLicenses.LicenseExpiration) AS [Days to Expiration],
  CASE
    WHEN DATEDIFF(d, GETDATE(), tblLicenses.LicenseExpiration) <= 0
    THEN '#ffcccc'
  END As backgroundcolor
FROM
  tblSublicensesOrders
  INNER JOIN tblLicenses ON tblLicenses.LicenseidID = tblSublicensesOrders.LicenseidID
ORDER BY
  tblLicenses.softwareName,
  tblSublicensesOrders.Orderdate