Sum Columns in sql

user_12036885 0
Viewed 36

I need a little help, Basically, I am creating a database for Hotel Management, Please take a look at my Queries. So now, in the end, I want to take a total for availed Services that's where I'm stuck. Can anyone here help me out with this and please let me know if I'm wrong somewhere because I'm still learning Microsoft SQL. Thanks in Advance

Create Database CustRoomBooking
use CustRoomBooking

drop table RoomsTypes
CREATE TABLE RoomTypes
(
ID int Not NULL,
type varchar(50) not NULL,
PRIMARY KEY (ID)
)

CREATE TABLE Pricing
(
service_ID int not NULL,
price int not null,
PRIMARY KEY(Service_ID)
)


drop table Rooms
create Table Rooms
(
ID int Not NULL,
floor int not NULL,
bed int not NULL,
roomType int not NULL,
roomPrice int not NULL, 
PRIMARY KEY (ID),
FOREIGN KEY (roomType) REFERENCES RoomTypes(ID),
FOREIGN KEY (roomPrice) REFERENCES Pricing(Service_ID)
)


Drop Table Customers
create Table Customers
(
ID int Not NULL,
name varchar(100) not NULL,
cnic varchar (13) not null,
age int not null,
phone varchar(11) not null,
gender varchar(6) not null,
PRIMARY KEY (ID)
)


drop table Booking
Create Table Booking
(
ID int not NULL,
room_ID int not NULL,
cust_ID int not NULL,
checkIN date not NULL,
checkOUT date not NULL,
booking_status varchar(100) not NULL,
booking_date date not NULL,
PRIMARY KEY (ID),
FOREIGN KEY (room_ID) REFERENCES Rooms(ID),
FOREIGN KEY (cust_ID) REFERENCES Customers(ID)

)

drop table Billing
Create Table Billing 
(
ID int NOT NULL,
room_ID int not NULL,
service_price int not NULL,
cust_ID int not NULL,
credit_card varchar(20) not NULL,
payment_date Date not NULL,
status varchar(50) not NULL, 

FOREIGN KEY (room_ID) REFERENCES Rooms(ID),
FOREIGN KEY (service_price) REFERENCES Pricing(service_ID),
FOREIGN KEY (cust_ID) REFERENCES Customers(ID)
)

CREATE TABLE Services
(
ID int not null,
service varchar (100),
price int not NULL,
PRIMARY KEY(ID),

FOREIGN KEY(price) REFERENCES Pricing(service_ID)

)

CREATE TABLE ServicesAvailed
(
ID int not null,
cust_ID int not NULL,
service int not NULL,
price int not NULL,

PRIMARY KEY(ID),
FOREIGN KEY(cust_ID) REFERENCES Customers(ID),
FOREIGN KEY(service) REFERENCES Services(ID),
FOREIGN KEY(price) REFERENCES Pricing(service_ID)
)

insert into ServicesAvailed
values
(1,1,1,1),
(2,1,2,2)


insert into RoomTypes
Values
(1,'Standard'),
(2,'Moderate '),
(3,'Deluxe ')

insert into Services
Values
(1,'Laundry',1),
(2,'Airport Transport',2),
(3,'Standard Room',3),
(4,'Moderate Room',4),
(5,'Deluxe Room',5)


insert into Pricing
Values
(1,1000),
(2,2000),
(3,2000),
(4,4000),
(5,6000)

insert into Rooms
Values
(1,0,2,1,3),
(2,0,2,1,3),
(3,1,3,2,4),
(4,1,3,2,4),
(5,2,4,3,5),
(6,2,4,3,5)

insert into Customers
Values
(1,'Hamza','12345',20,'98765','Male'),
(2,'Ahmed','12345',20,'98765','Male'),
(3,'Irfan','12345',20,'98765','Male')

Insert Into Booking
Values
(1,1,1,'2020-12-31','2021-1-1','Booked','2020-12-31'),
(2,2,2,'2020-12-31','2021-1-1','Booked','2020-12-31')

Insert Into Billing 
Values
(1,1,1,1,'5657446546','2020-12-31','Paid'),
(2,2,2,2,'5657446546','2020-12-31','Paid')

select * from Billing
select * from Booking
select * from Customers
select * from RoomTypes
select * from Rooms
select * from RoomPrices
select * from Booking
select * from Billing




select r.ID as RoomNumber,r.floor,r.bed,rt.*,p.price from Rooms r
inner join RoomTypes rt on r.roomType=rt.ID
inner join Pricing p on r.roomPrice=p.service_ID





select b.ID as BookingID,c.ID as CustomerID,c.name,b.booking_date,b.booking_status,b.checkIN,b.checkOUT, r.ID as roomID,r.floor,r.bed,rt.type,rp.price from Booking b
inner join Customers c on b.cust_ID=c.ID
inner join Rooms r on b.room_ID=r.ID
inner join RoomTypes rt ON r.roomType=rt.ID
inner join Pricing rp ON r.roomPrice= rp.service_ID 
where c.name='Hamza'


select * from Billing


select c.ID as CustomerID,c.name,r.ID as roomID,r.floor,r.bed,rt.type,rp.price from Billing b
inner join Customers c on b.cust_ID=c.ID
inner join Rooms r on b.room_ID=r.ID
inner join RoomTypes rt ON r.roomType=rt.ID
inner join Pricing rp ON r.roomPrice= rp.service_ID 

select sa.ID as NumberOFService,c.ID as CustomerID,c.name,s.service,p.price from ServicesAvailed sa
inner join Customers c on sa.cust_ID=c.ID
inner join Services s on sa.service=s.ID
inner join Pricing p on sa.price=p.service_ID
sql-server database
1 Answers

I am guessing your problem is that your last query does not include the price of the rooms themselves. The steps I would do in that case:

  1. Add a [charged_date] column to table [ServicesAvailed], and include it in the primary key, because a customer might buy the same type of service multiple times

  2. Add triggers to [Booking], which use the [checkIN] and [checkOUT] columns to create entries in [ServicesAvailed] which are actually room prices. Then, your final query will include the room prices.

If you don't want to have one row for every day of the same booing in [ServicesAvailed], you could consider an additional column [multiplier] that would multiply the base service price by a factor (in case of the rooms this would be a date). If you wanted, you could use similar logic to implements discounts and so on.

user_58259630
Related