December 4, 2015 at 11:39 am
Hi,
I creating a database to manage license key usage within my organization. We reimage company computer often enough that I would like to keep track of the license keys in a database.
Here's what the database looks like:
https://i.imgur.com/ul2GjTR.png
Every product will have one or many license keys for it. For each license key there are a range of dates that it's reserved for.
There's a total number of seats available for one or a set of license keys. For example I have 10 seats available for an Office 2010 license key and an Office 2013 license key. I can give out 4 2010 license keys and 6 2013 license keys but I cannot go over the seats available for the allotment.
I working on several queries right now and I need a little guidance with them. Here's what I have so far.
--Insert a reservation for a key during specific dates
WITH Dates AS (
SELECT
[Date] = CONVERT(date,'20151127')
UNION ALL SELECT
[Date] = DATEADD(DAY, 1, [Date])
FROM
Dates
WHERE
Date < '20151202'
) INSERT RX.ReservationTB(LicenseKey, RentalOrderNum, ReservationDate)
VALUES('7WP6V-W2Y33-GKH2B-6YTD4-K2WPR', 'RO11', [Date])
OPTION (MAXRECURSION 45)
GO
I am getting an error where '[Date] could not be bounded'.
-- Query for first available license key for a given product where there's a seat available
SELECT p.Name, l.LicenseKey, count(1) as numOfReservation
FROM RX.SeatsAvailableTB s
JOIN RX.LicenseKeyTB l ON s.SeatID=l.SeatID
JOIN RX.ReservationTB r ON r.LicenseKey=l.LicenseKey
JOIN RX.ProductTB p ON p.ProductID=l.ProductID
WHERE r.LicenseKey = '6RCX3-H722D-8MQT6-8Y2VC-FY9FG'
AND CONVERT(date, '20151130')<=r.ReservationDate
AND r.ReservationDate<CONVERT(date, '20151201')
AND p.Name = 'Office 2010 SP2'
GROUP BY p.Name, l.LicenseKey;
I am not sure how to go about this one.
I'll be working on them and I'll update my post if I find a solution.
December 4, 2015 at 12:01 pm
First of all, you need to reference the CTE through a SELECT statement. Something like this:
--Insert a reservation for a key during specific dates
WITH Dates AS (
SELECT
[Date] = CONVERT(date,'20151127')
UNION ALL SELECT
[Date] = DATEADD(DAY, 1, [Date])
FROM
Dates
WHERE
Date < '20151202'
)
INSERT RX.ReservationTB(LicenseKey, RentalOrderNum, ReservationDate)
SELECT '7WP6V-W2Y33-GKH2B-6YTD4-K2WPR', 'RO11', [Date]
FROM Dates
OPTION (MAXRECURSION 45)
Second, you need to change that recursive query as it might give you performance problems. Check this for more information: http://www.sqlservercentral.com/articles/T-SQL/74118/
The alternative could be using cascade CTEs
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
Dates([Date]) AS(
SELECT TOP (DATEDIFF(dd, '20151127', '20151202') + 1 )
CONVERT(date,DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1, '20151127')) [Date]
FROM E4
)
INSERT RX.ReservationTB(LicenseKey, RentalOrderNum, ReservationDate)
SELECT '7WP6V-W2Y33-GKH2B-6YTD4-K2WPR', 'RO11', [Date]
FROM Dates
Last, your second query has nothing weird. Please read the article in my signature to give you a better idea on how to post so we can understand and help better and faster.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
