June 16, 2020 at 7:21 pm
I have a web interface for managing tool rentals for our chapter members. I have a display page that will list all of the tools we have and to flag those tools that are currently being rented out to include the expected return date. The important thing is to know which are available and which are not but also ensuring that we maintain historical rental information. I need help with the query based on the following tables:
The table that Stores all the Tools we have to rent including some records to add
SELECT * FROM dbo.EAA_TOOLS
CREATE TABLE dbo.EAA_TOOLS (
toolID int NOT NULL IDENTITY(1,1),
[Tool Name] nvarchar(75) NOT NULL,
[Tool Description] text(16) NOT NULL,
[Tool Rental Price] smallmoney NOT NULL,
[Tool Max Rental Period] int NOT NULL,
[Tool Owner ID] int NOT NULL,
[Tool Location] nvarchar(100) NOT NULL,
[Tool Image] image NULL,
[Addtional Charge] smallmoney NULL DEFAULT (NULL),
[Additional Charge Description] text(16) NULL DEFAULT (NULL),
[Tool Availability] nchar(1) NOT NULL DEFAULT ('y'), --This is a flag that the web user interface will update with a "y" or "n" upon renting a tool
[Tool Display Status] nvarchar(8) NOT NULL DEFAULT ('ACTIVE') -- This only us used to determine the tool is avlable for diplaying on the website for renatl purposes.
);
GO
ALTER TABLE dbo.EAA_TOOLS ADD CONSTRAINT PK__EAA_TOOL__02F0FC1EE65D8A7E PRIMARY KEY (toolID);
GO
INSERT INTO dbo.EAA_TOOLS (toolID, [Tool Name], [Tool Description], [Tool Rental Price], [Tool Max Rental Period], [Tool Owner ID], [Tool Location], [Tool Image], [Addtional Charge], [Additional Charge Description], [Tool Availability], [Tool Display Status]) VALUES
(1, 'Air Breathing Mask', 'Air breathing mask and blower for painting', 25, 2, 130, 'Gastonia Hangar', NULL, NULL, NULL, 'y', 'ACTIVE'),
(2, 'Scales', 'Scales for performing weigh and balance CG refernce', 50, 2, 130, 'Gastonia Hangar', NULL, NULL, NULL, 'n', 'ACTIVE'),
(3, 'Dynavibe Propeller Balancer', 'The DynaVibe Classic is an economical, state-of-the-art dynamic propeller balancer designed specifically for the aircraft industry', 50, 2, 4, 'Gastonia Hangar', NULL, NULL, NULL, 'y', 'ACTIVE'),
(4, 'Chapter Event Trailer', 'Trailer includes 14 tables (5x8 and 9x6) chairs ($100 per weekend) & large grill (extra $50)', 100, 1, 130, 'Gastonia Hangar', NULL, 50, 'Large Grill', 'y', 'ACTIVE'),
(5, 'Soda/Sand Blaster', 'Use dry abrasives only, Hose length - 7ft.,Tank capacity - 50lbs.', 50, 1, 84, 'Gastonia Hangar', NULL, NULL, NULL, 'n', 'ACTIVE');
This is the table that keeps track of the current tools being rented and also historical rental information. Again this table will have all the current tools being rented out and those that have been returned for rental.
If the Above table column "Tool Availability" = N and the table below column "Actual Return Date" is Null then this would be a record to display on the web page among those records where the above table column "Tool Availability" also = Y. When someone returns the tool the web interface will update these two columns EAA_TOOLS "Tool Availability" = y and EAA_TOOL_RENTALS "Actual Return Date" to the current date-time the tool admin enters the date into the web interface. Here is the EAA_TOOLS_RENTALS table
CREATE TABLE dbo.EAA_TOOL_RENTALS (
rentalID int NOT NULL IDENTITY(1,1),
toolID int NOT NULL,
[Renter ID] int NOT NULL,
[Pickup Date] smalldatetime(4) NOT NULL,
[Expected Return Date] smalldatetime(4) NOT NULL,
[Actual Return Date] smalldatetime(4) NULL
);
GO
ALTER TABLE dbo.EAA_TOOL_RENTALS ADD CONSTRAINT PK__EAA_TOOL__016470CEE2422235 PRIMARY KEY (rentalID);
GO
INSERT INTO dbo.EAA_TOOL_RENTALS (toolID, [Renter ID], [Pickup Date], [Expected Return Date], [Actual Return Date]) VALUES (1, 82, '2020-06-18 12:00:00', '2020-06-23 04:00:00', '');
INSERT INTO dbo.EAA_TOOL_RENTALS (toolID, [Renter ID], [Pickup Date], [Expected Return Date], [Actual Return Date]) VALUES (1, 82, '2020-03-01 02:00:00', '2020-03-09 10:30:00', '2020-03-09 09:00:00');
INSERT INTO dbo.EAA_TOOL_RENTALS (toolID, [Renter ID], [Pickup Date], [Expected Return Date], [Actual Return Date]) VALUES (4, 53, '2020-06-01 09:00:00', '2020-06-20 05:00:00', '');
INSERT INTO dbo.EAA_TOOL_RENTALS (toolID, [Renter ID], [Pickup Date], [Expected Return Date], [Actual Return Date]) VALUES (3, 46, '2020-01-15 09:00:00', '2020-01-25 09:00:00', '');
The web interface minimally needs to pull in the following information into a table
Tool Availability | Tool Name | Expected Return Date
Here is a query however there are some records that are marked Tool Availability = y and also the MAX(Expected Return Date) If the Tool Availability = y and "Actual Return Date" = a date and time, then we do not need to display an Expected Return date on the interface page.
SELECT
A.[Tool Availability],
A.[toolID],
A.[Tool Name],
MAX(B.[Expected Return Date]) AS "Expected Return Date"
FROM dbo.EAA_TOOLS A
LEFT JOIN dbo.EAA_TOOL_RENTALS B
ON A.toolID = B.toolID
WHERE A.[Tool Display Status] = 'ACTIVE'
GROUP BY A.[Tool Availability],
A.[toolID],
A.[Tool Name]
I hope this all makes sense, and it may come down to redesigning the tables to make it easier to represent this information. At first, I thought of keeping it all in one table, BUT this doesn't allow us to maintain a historical log of the information.
June 16, 2020 at 10:09 pm
I think I may have figured an easy way to do this. Since the interface controls the availability of the tool then a case statement can be created to drive what we want the end-user to see in terms of the Expected Return Date based on the Yes or No value at the primary table EAA_TOOLS. I believe this should work for the simple display of what's available and what's not, and of those things that are not available use, MAX Date to only display the most recent record.
SELECT
A.[Tool Availability],
A.[toolID],
A.[Tool Name],
CASE
WHEN A.[Tool Availability] = 'n' THEN CONVERT(varchar,MAX(B.[Expected Return Date]),101)
--WHEN A.[Tool Availability] = 'y' THEN 'Tool Is Available'
ELSE ''
END AS 'Expected Return Date',
CASE
WHEN A.[Tool Availability] = 'n' THEN C.[Full Name]
ELSE ''
END AS 'Current Renter'
FROM dbo.EAA_TOOLS A
LEFT JOIN dbo.EAA_TOOL_RENTALS B
ON A.toolID = B.toolID
LEFT JOIN dbo.EAA_MEMBERS C
ON B.[Renter ID] = C.[Member ID]
WHERE A.[Tool Display Status] = 'ACTIVE'
GROUP BY A.[Tool Availability],
A.[toolID],
A.[Tool Name],
C.[Full Name]
June 23, 2020 at 5:16 pm
>> I need help with the query based on the following tables: <<
The table that Stores all the Tools we have to rent including some records [sic: rows are not records] ... Actually, you need a lot of help with your DDL which is a mess. You've mixed the tools (entities) and rentals (a relationship between a tool and a renter) in violation of a fundamental principle of data modeling and normalization. You have embedded spaces in data element names in violation of ISO rules. You're using the old proprietary MONEY datatypes from Sybase (are you doing everything you need to guarantee that it doesn't funk up the math? Remember, it doesn't do correct rounding). Why do you believe in the magical universal Kabbalah number called "identity"? Why do you use assembly language flags in SQL?
Let me answer my own questions; you do not know how to do a relational design and what you've done is copy a paper file into a table. You don't have a relational database at all. Start with this skeleton:
CREATE TABLE Tools;
CREATE TABLE Renters;
CREATE TABLE Rentals … REFERENCES Tools, REFERENCES Renters;
Add some flesh ...
CREATE TABLE Tools
(tool_id CHAR(15) NOT NULL,
tool_name CHAR (75) NOT NULL,
tool_description VARCHAR(35) NOT NULL,
tool_location NVARCHAR(100) NOT NULL,
tool_Image IMAGE, --- deprecated data type!!
tool_status VARCHAR(20) NOT NULL
CHECK(tool_status IN (‘available’, ‘retired’, ‘not available’, etc)),
status_start_date DEFAULT CURRENT_TIMESTAMP NOT NULL,
status_end_date DATE
CHECK (status_start_date <= status_end_date),
PRIMARY KEY (tool_id, status_start_date)
);
Status comes from the Latin for "a state of being" , so it has to have a start and end date. It is not a permanent feature of an entity, but it has a time element to it. If you do not need to track status, then leave off the status dates.
CREATE TABLE Rentals
(tool_id CHAR(15) NOT NULL REFERENCES Tools, ---important!
renter_id CHAR(15) NOT NULL REFERENCES Renters, ---important!
rental_start_date DEFAULT CURRENT_TIMESTAMP NOT NULL,
rental_return_date DATE NOT NULL, --- contract terms
CHECK (rental_start_date <= rental_return_date),
actual_return_date DATE, --- NULL means it was early or on time
CHECK (rental_start_date <= actual_return_date),
PRIMARY KEY (tool_id, rental_start_date)
);
>> If the Above table column "tool_availability" = N and the table below column "actual_return_date" is NULL then this would be a record [sic: rows are not records] to display on the web page among those records [sic: rows are not records] where the above table column "tool_availability" also = Y. When someone returns the tool the web interface will update these two columns… <<
If there is no actual return date, then you know that tool is not available. I don't know if you can rent out a tool immediately when it's returned, so we may need some more status codes. But we sure do not need any assembly language flags
You are demonstrating a design flaw called "attribute splitting" in which one fact is represented in two or more places in your schema.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply