What relations between package and flights and transfer and excursion and hotel

Databases
42

I apologize I misunderstood you. You talked about daydetails cost, and I saw daydetails, not daydetailscost (which is the actual name of the table)

The reason it’s not needed is because those values can be calculated, and a general rule of database normalization is not to store calculated values unless you need to.

The second query I gave you returns the per day costs, so there’s no real need to have a table just for costs, but if you REALLY want to save that data, then incorporate it into the DaysDetails tablle.

A pretty simple rule of thumb for database design is if you’re going to always have a 1:1 relationship between tables, then one of the two tables probably doesn’t need to exist,

1 Like
43

when ask company i do booking analysis for it
i got more information
I think i must make relation between hotel and hotel price direct
so that what i must change in module above
see this and tell me if possible

1011×566 120 KB

44

Why? If anything, you’d want to base it on the room type, not the hotel. The only way you put the price on the hotel is if EVERY room on EVERY floor was EXACTLY the same each and every day.

What you COULD do is set a base price in the room type table, with an additional fee added to the room itself to allow yourself some flexibility.

45

Can i get packages for every hotels from diagram above if possible

46

meaning i need to get final result as
HotelID HotelName package startdate end date totalcost
can i make query for doing that if possible

47

Yes. I’ve given you several examples of queries that provide the template to work with.

Start with one table, join it to the next and keep going until you get to all of the fields you want. Filter it down by the WHERE clause

It might behoove you to take your diagram, actually create the tables and fill them with some data, then start querying to see if it meets your needs…

48

i do as following

SELECT        dbo.Hotel.HotelID, dbo.Hotel.HotelName, dbo.DurationDetails.Days, dbo.PackageDuration.PackageDuration, dbo.Package.PackageName, dbo.Package.Duration, 
                         dbo.StartPackage.StartDate, dbo.StartPackage.EndDate
FROM            dbo.Hotel INNER JOIN
                         dbo.DayDetails ON dbo.Hotel.HotelID = dbo.DayDetails.HotelID INNER JOIN
                         dbo.DurationDetails ON dbo.DayDetails.DetailsDurationID = dbo.DurationDetails.DetailsDurationID INNER JOIN
                         dbo.PackageDuration ON dbo.DurationDetails.PackageDurationsID = dbo.PackageDuration.PackageDurationsID INNER JOIN
                         dbo.Package ON dbo.PackageDuration.PackageID = dbo.Package.PackageID INNER JOIN
                         dbo.StartPackage ON dbo.Package.PackageID = dbo.StartPackage.PackageID

1	Hilton	DAY1	8	AlexaPackage	8,15	2017-06-28 00:00:00.000	2017-07-05 00:00:00.000
1	Hilton	DAY2	8	AlexaPackage	8,15	2017-06-28 00:00:00.000	2017-07-05 00:00:00.000
1	Hilton	DAY3	8	AlexaPackage	8,15	2017-06-28 00:00:00.000	2017-07-05 00:00:00.000
1	Hilton	DAY4	8	AlexaPackage	8,15	2017-06-28 00:00:00.000	2017-07-05 00:00:00.000
1	Hilton	DAY5	8	AlexaPackage	8,15	2017-06-28 00:00:00.000	2017-07-05 00:00:00.000
1	Hilton	DAY6	8	AlexaPackage	8,15	2017-06-28 00:00:00.000	2017-07-05 00:00:00.000
1	Hilton	DAY7	8	AlexaPackage	8,15	2017-06-28 00:00:00.000	2017-07-05 00:00:00.000
1	Hilton	DAY8	8	AlexaPackage	8,15	2017-06-28 00:00:00.000	2017-07-05 00:00:00.000

but it give me details
i need summarizinf for it

`HotelID HotelName package startdate end date
1 Hilton AlexaPackage 28-06-2017 05-07-2017

49

Are the fields you selected the ones you want? I would say not…

Your query

  dbo.Hotel.HotelID
, dbo.Hotel.HotelName
, dbo.DurationDetails.Days
, dbo.PackageDuration.PackageDuration
, dbo.Package.PackageName
, dbo.Package.Duration
, dbo.StartPackage.StartDate
, dbo.StartPackage.EndDate

What you say you want (I’ll even break it down to make it easier to see…)

HotelID 
HotelName 
package 
startdate 
end date

How many columns do you have in your query? How many are in your expected results?

50 
HotelID 
HotelName 
package 
startdate 
end date

this is columns what i need actually

expected result
HotelID HotelName package startdate end date 
1 Hilton AlexaPackage 28-06-2017 05-07-2017
51

the columns 5 columns as i determine above
and one row result expected

52

So why aren’t those the fields being returned in the query?

53

which fields you mean

54

I provided a breakdown in post #49 of this thread. Look at the fields you selected and the fields you say you want. Are they the same? No.

SQL is like any other computerized operation. It will only give you what you ask for. If you don’t ask for the right things, you’re not going to get the right answers.

1 Like
55

I solve it this what i do and get the result

SELECT h.HotelID, h.HotelName, p.PackageName, sp.StartDate, sp.EndDate, SUM(hp.HotelPrice) AS cost
FROM package AS p
INNER JOIN packageduration AS pd
	ON p.packageID = pd.packageID
INNER JOIN (
	SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY PackageDurationsId ORDER BY Days) 
	FROM DurationDetails AS dd
) AS dd
	ON pd.PackageDurationsID = dd.PackageDurationsID
INNER JOIN DayDetails AS dyd
	ON dd.DetailsDurationID = dyd.DetailsDurationID
INNER JOIN Hotel AS h
	ON dyd.HotelID = h.HotelID
INNER JOIN StartPackage AS sp
	ON sp.PackageId = p.PackageId
INNER JOIN HotelPrice AS hp
	ON h.HotelID = hp.HotelID
WHERE DATEADD(day, dd.RN - 1, sp.StartDate) BETWEEN hp.FromDate AND hp.ToDate
GROUP BY h.HotelID, h.HotelName, p.PackageName, sp.StartDate, sp.EndDate

this give me expected result as below

HotelID HotelName package startdate end date 
1 Hilton AlexaPackage 28-06-2017 05-07-2017

my problem is some packages may be contain one or more hotels
I need only to display packages have one hotel only
how to do that please from sql query
How to show packages contain to only one hotel related from sql query ?

56

Are you looking for only packages that are available in single hotels, or are you looking for a specific hotel?

If you’re looking for a specific hotel, how can you filter that out?

If you’re looking for only packages that are available in single hotels, then GROUP BY and HAVING are your friends (hint HAVING COUNT(*) = 1)

57

Thank you for reply

what i mean is get only packages have only one hotel

SAMPLE DATA

USE [NileTravel3]
GO
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration]) VALUES (N'P02', N'AlexaPackage', N'8,15')
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration]) VALUES (N'P03', N'Amon', N'8')
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (N'PD01', N'P02', 8, 7)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (N'PD02', N'P02', 15, 14)
INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (N'PD03', N'P03', 8, 7)
INSERT [dbo].[StartPackage] ([StartID], [PackageID], [StartDate], [EndDate], [TotalCost]) VALUES (N'SD01', N'P02', CAST(0x0000A7A000000000 AS DateTime), CAST(0x0000A7A700000000 AS DateTime), CAST(210 AS Decimal(18, 0)))
INSERT [dbo].[StartPackage] ([StartID], [PackageID], [StartDate], [EndDate], [TotalCost]) VALUES (N'SD02', N'P03', CAST(0x0000A7A300000000 AS DateTime), CAST(0x0000A7AA00000000 AS DateTime), NULL)
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD01', N'PD01', N'DAY1')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD02', N'PD01', N'DAY2')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD03', N'PD01', N'DAY3')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD04', N'PD01', N'DAY4')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD05', N'PD01', N'DAY5')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD06', N'PD01', N'DAY6')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD07', N'PD01', N'DAY7')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD08', N'PD01', N'DAY8')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD09', N'PD03', N'DAY1')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD10', N'PD03', N'DAY2')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD11', N'PD03', N'DAY3')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD12', N'PD03', N'DAY4')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD13', N'PD03', N'DAY5')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD14', N'PD03', N'DAY6')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD15', N'PD03', N'DAY7')
INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD16', N'PD03', N'DAY8')
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (1, N'Hilton', N'***')
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (2, N'Movenpick', N'**')
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD01', N'DD01', 1)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD02', N'DD02', 1)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD03', N'DD03', 1)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD04', N'DD04', 1)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD05', N'DD05', 1)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD06', N'DD06', 1)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD07', N'DD07', 1)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD08', N'DD08', 1)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD09', N'DD09', 2)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD10', N'DD10', 2)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD11', N'DD11', 2)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD12', N'DD12', 2)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD13', N'DD13', 2)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD14', N'DD14', 2)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD15', N'DD15', 1)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD16', N'DD16', 1)
INSERT [dbo].[HotelPrice] ([HotelPriceID], [FromDate], [ToDate], [HotelPrice], [HotelID]) VALUES (N'HP01', CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A7A200000000 AS DateTime), CAST(20 AS Decimal(18, 0)), 1)
INSERT [dbo].[HotelPrice] ([HotelPriceID], [FromDate], [ToDate], [HotelPrice], [HotelID]) VALUES (N'HP02', CAST(0x0000A7A300000000 AS DateTime), CAST(0x0000A85A00000000 AS DateTime), CAST(30 AS Decimal(18, 0)), 1)
INSERT [dbo].[HotelPrice] ([HotelPriceID], [FromDate], [ToDate], [HotelPrice], [HotelID]) VALUES (N'HP03', CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A7A200000000 AS DateTime), CAST(30 AS Decimal(18, 0)), 2)
INSERT [dbo].[HotelPrice] ([HotelPriceID], [FromDate], [ToDate], [HotelPrice], [HotelID]) VALUES (N'HP04', CAST(0x0000A7A300000000 AS DateTime), CAST(0x0000A85A00000000 AS DateTime), CAST(40 AS Decimal(18, 0)), 2)

my sample data i use at above

the result i need is

alexa package only

HotelID HotelName Packages   startdate                     end date         cost                         cost
1	Hilton	AlexaPackage 2017-06-28 00:00:00.000	2017-07-05 00:00:00.000	210

1024×869 157 KB

58

to clear also more
I need to select or display
packageA hotel A correct

Not

Package B   hotel b wrong
Package B hotel c   wrong

only packages related or contain one hotel only
in my data above will be alexa package i need to show or display

59

I do as following
having count(*)=1 but not work
it return null records
no records return

SELECT h.HotelID, h.HotelName, p.PackageName, sp.StartDate, sp.EndDate, SUM(hp.HotelPrice) AS cost
FROM package AS p
INNER JOIN packageduration AS pd
	ON p.packageID = pd.packageID
INNER JOIN (
	SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY PackageDurationsId ORDER BY Days) 
	FROM DurationDetails AS dd
) AS dd
	ON pd.PackageDurationsID = dd.PackageDurationsID
INNER JOIN DayDetails AS dyd
	ON dd.DetailsDurationID = dyd.DetailsDurationID
INNER JOIN Hotel AS h
	ON dyd.HotelID = h.HotelID
INNER JOIN StartPackage AS sp
	ON sp.PackageId = p.PackageId
INNER JOIN HotelPrice AS hp
	ON h.HotelID = hp.HotelID
WHERE DATEADD(day, dd.RN - 1, sp.StartDate) BETWEEN hp.FromDate AND hp.ToDate 
GROUP BY h.HotelID, h.HotelName, p.PackageName, sp.StartDate, sp.EndDate having count(*)=1
60

OK, so you only want the package named AlexaPackage. How do you place restrictions on the result set out of a SELECT statement?

Perhaps the size of that query is messing you up. Break it down…how would you select just the package named AlexaPackage out of the package table?

The answer is the same for the full query - you may just need to delimit the field depending on if you have the sane field names on multiple tables In fact, I find it good habit to get into in general as it makes understanding queries easier when you have to return to them later…

61

actually i need to modify my query above to get only packages contain or related only one hotel