→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mvelez
Engaged Sweeper
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
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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

View solution in original post

2 REPLIES 2
mvelez
Engaged Sweeper
This is perfect. Thank you so much.
RCorbeil
Honored Sweeper II
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