How to output accept items for repair


Joined
Nov 18, 2021
Messages
1
Reaction score
0
Can you help me to solve this :

To bring out all accepted items for repair, the deadline for issuance of which is 2 days
Thats the code:

CREATE TABLE Employee (
ID INT IDENTITY NOT NULL PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
);

CREATE TABLE Orders (
ID INT IDENTITY NOT NULL PRIMARY KEY,
Date_of_acceptance DATE,
Date_of_deadline DATE,
Status_order NVARCHAR(50) NOT NULL,
Type_of_repair NVARCHAR (50) NOT NULL,
Price DECIMAL (20),
EmployeeID INT,
CustomerID INT,
ItemsID INT
);


CREATE TABLE Customer (
ID INT IDENTITY NOT NULL PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
PhoneNumber VARCHAR(20)

);


CREATE TABLE Items (
ID INT IDENTITY NOT NULL PRIMARY KEY,
ItemsName NVARCHAR(50) NOT NULL,
Status_items NVARCHAR(50) NOT NULL
);

CREATE TABLE CompServises (
ID INT IDENTITY NOT NULL PRIMARY KEY,
ServisesName NVARCHAR(50) NOT NULL,
PriceOfServise DECIMAL(30) NOT NULL
);


INSERT INTO CompServises( ServisesName,PriceOfServise)
VALUES('Change of battery', 55);
INSERT INTO CompServises( ServisesName,PriceOfServise)
VALUES('Upgrade motherboard', 550);
INSERT INTO CompServises( ServisesName,PriceOfServise)
VALUES('Repair printers', 45);


ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Employee FOREIGN KEY (EmployeeID)
REFERENCES Employee(ID)

ON DELETE CASCADE
ON UPDATE CASCADE
;

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customer FOREIGN KEY (CustomerID)
REFERENCES Customer(ID)

ON DELETE CASCADE
ON UPDATE CASCADE
;

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Items FOREIGN KEY (ItemsID)
REFERENCES Items(ID)

ON DELETE CASCADE
ON UPDATE CASCADE
;


INSERT INTO Employee( FirstName,LastName)
VALUES('Yavor', 'Dimitrov');
INSERT INTO Employee( FirstName,LastName)
VALUES('Aleko', 'Ivanov');
INSERT INTO Employee( FirstName,LastName)
VALUES('Boris', 'Petrov');
INSERT INTO Employee( FirstName,LastName)
VALUES('Alex', 'Georgiev');


INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ('Nikolai', 'Georgiev','0889-125-521' );
INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ('Viktoriya', 'Dimitrova', '0899-187-784' );
INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ('Aneliya', 'Petkova', '0885-748-748' );
INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ( 'Petyr', 'Stoyanov', '0887-878-777');
INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ('Elena', 'Dimitrova', '0895-741-547' );
INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ( 'Dimityr', 'Angelov', '0897-546-345');
INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ('Alexandra', 'Dimova', '0885-321-764');
INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ('Viktor', 'Hristov' , '0874-436-987');
INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ( 'Petya', 'Dimitrova', '0889-765-743');
INSERT INTO Customer( FirstName, LastName, PhoneNumber)
VALUES ( 'Atanas', 'Petkov', '0888-463-675');


INSERT INTO Items( ItemsName, Status_items )
VALUES ( 'Printer', 'Accept for the repair ');
INSERT INTO Items( ItemsName, Status_items )
VALUES ( 'Computer', 'Unclaimed');
INSERT INTO Items( ItemsName, Status_items )
VALUES ('Laptop', 'Accept for the repair');
INSERT INTO Items( ItemsName, Status_items )
VALUES ( 'Monitor', 'Unclaimed');
INSERT INTO Items( ItemsName, Status_items )
VALUES ('Battery' , 'Unclaimed' );
INSERT INTO Items( ItemsName, Status_items )
VALUES ('Computer' , 'Accept for repair' );


INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price, EmployeeID, CustomerID, ItemsID)
VALUES('2021-11-16', '2021-11-18','Accept','Broken screen of laptop', 100, 1,1,1);
INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
VALUES ('2021-10-9', '2021-10-15','Submitted','Broken printer',90,1,1,1);
INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
VALUES ('2021-9-3', '2021-9-23','Waiting for delivery of part','Rеplacing the motherboard',500,1,1,1);
INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
VALUES ('2021-8-2', '2021-8-26','Accept','Repair a broken computer',600, 2,2,2);
INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
VALUES('2021-7-12', '2021-7-14','Accept','Change the laptop battery', 120,2,2,2);
INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
VALUES ('2021-6-10', '2021-6-18','Submitted','Update windows',30,2,2,2);
INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
VALUES ('2021-2-6', '2021-2-21','Waiting for delivery of part',' Rеplacing the motherboard ',500,3,3,3);
INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
VALUES ('2021-4-3', '2021-4-25','Waiting for delivery of part',' Virus scan',25,3,3,3);
INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
VALUES ('2021-5-16', '2021-5-30','Accept',' Change the laptop battery',120,3,3,3);
INSERT INTO Orders( Date_of_acceptance,Date_of_deadline,Status_order,Type_of_repair,Price,EmployeeID, CustomerID, ItemsID)
VALUES ('2021-3-5', '2021-3-5','Submitted','Update windows',30,4,4,4);
 
Ad

Advertisements

Joined
Mar 3, 2021
Messages
243
Reaction score
29
Hello and welcome! Sorry I'm so late. Are you still working on this? What SQL server are you using? It looks like MS SQL, so I'll try that. I'm also a little unclear on "deadline for issuance?" I'll take some shots the dark, though. This is untested, but is probably not too far off. It looks like you're just trying to SELECT from Orders. If you need extra information, like the name of the item, employee, or customer, you'll need to do some JOINs.

If you're trying to get ones that have an upcoming deadline within two days, you'd have something like the following. (BETWEEN is exclusive, not inclusive, so the bounds are ugly.)

SQL:
SELECT * FROM Orders WHERE DATEDIFF(day, Date_of_deadline, GETDATE()) BETWEEN -1 AND 3;

If you're trying to get the ones that have a deadline two days from being accepted, you'd use something like this:

SQL:
SELECT * FROM Orders WHERE DATEDIFF(day, Date_of_deadline, Date_of_acceptance) BETWEEN -1 AND 3;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top