How to make relation between tables to calculate cost of flight and transfer and hotel and excursion per every day ?
-
firstly if possible are relation between package and flight
and package and hotel
and package and transfer
and package and excursion is correct or wrong this is main question i ask -
because if design is correct i can clcultae correctly
-
is this a 3rd party application that is supplied by an external vendor?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
Here's the issue, ahmed_elbarbary.2010. You have a query there, but you need to explain why it doesn't work. I don't want to dig through a long result set and try to determine what's wrong, or the issue you see. If your query doesn't work, what values are incorrect? Or what values are you expecting to see?
Help is us trying to get you to understand what doesnt' work and why, but that requires you to provide some reason of what you don't understand. Right now you are saying "do my work" with the question: Can you help me to do calculate for flight and transfer and excursion and hotel? That doesn't tell us anything. The image of an applicationd doesn't help because we don't know the meaning of the fields or what's shown. We are blind as to requirements. What you need to do is explain in detail what you need, which means words, not results.
Your cost result set is wrong because you have a date at the end of the results (always use headers), but no date in the query, so you are cut and pasting from different times.
The calculation for the transfers for a day will require a SUM() of the fields, and a GROUP BY of the non-summed fields.
In your data, you have this:
INSERT [dbo].[Program] ([ProgramID], [ProgramName], [ProgramTypeID]) VALUES (1, N'alexa', 1)
Those fields don't exist in your DDL. So you've done something wrong there.If I needed the total sum per day for hotel, I'd do:
SELECT
DateDuration,
sum( dbo.DaysDetailsCost.HotelCost) as 'HotelCost',
FROM DaysDetailsCost
group by DateDurationHowever, you've got a design that is strange. I don't quite understand why you have DaysDetails and DaysDetailsCost unless you expect multiple Hotel or Flight costs on a day to be stored here. We can't tell if you expected DaysDetailsCost.FlightCost to be a sum of flight costs or one of many flight costs for a day. I typically wouldn't break out the costs like this unless I am tracking many.
When you get to HotelPrice, is the HotelPrice.HotelPrice the cost / day? Or the trip?
These are the details that don't make sense.
Here's what I suggest. First, make a new database and produce a script that will build the tables and insert data. Put all that as a single script here that works. Test this multiple times.
Then define what the cost per day means in terms of the fields. It is the sum of (tablex.Columny) using (TableZ.columnA) for the date. Explain what you are looking to calculate from where. Then we can help you do this. Right now it's unclear what data you are storing.
- ahmed_elbarbary.2010 - Thursday, July 6, 2017 9:52 AM
given what tables and data you have given us....this is my best guess for the above
SELECT p.PackageName,
p.Duration,
pd.NightCounts,
durd.Days,
dayd.DayDetailsID,
dayd.DetailsDurationID,
dayd.FlightTypeID,
dayd.HotelID,
dayd.ExcursionID,
dayd.TransferTypeID,
fd.FlighID,
fd.FlightNo,
fd.FlightDate
FROM PackageDuration AS pd
INNER JOIN Package AS p ON pd.PackageID = p.PackageID
INNER JOIN DurationDetails AS durd ON pd.PackageDurationsID = durd.PackageDurationsID
INNER JOIN DayDetails AS dayd
INNER JOIN FlightData AS fd ON dayd.FlightTypeID = fd.FlightTypeID ON durd.DetailsDurationID = dayd.DetailsDurationID;________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
Thank you for reply
this is my resultHiltonPackage 4 3 day1 1 1 1 1 1 1 1 ms750 2017-07-01 00:00:00.000
HiltonPackage 4 3 day4 4 4 2 1 NULL NULL 2 ms800 2017-08-02 00:00:00.000
- ahmed_elbarbary.2010 - Thursday, July 6, 2017 11:31 AMThank you for reply
this is my resultHiltonPackage 4 3 day1 1 1 1 1 1 1 1 ms750 2017-07-01 00:00:00.000
HiltonPackage 4 3 day4 4 4 2 1 NULL NULL 2 ms800 2017-08-02 00:00:00.000.....and?
what are you expecting the results to be?________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
OK actually i face problem
in my design above i make relation between
hotel and package to assign period per hotel
and
hotel and day of package to get cost
are this is correct logic and if not correct how to fix that - J Livingston SQL - Thursday, July 6, 2017 11:37 AMahmed_elbarbary.2010 - Thursday, July 6, 2017 11:31 AMThank you for reply
this is my resultHiltonPackage 4 3 day1 1 1 1 1 1 1 1 ms750 2017-07-01 00:00:00.000
HiltonPackage 4 3 day4 4 4 2 1 NULL NULL 2 ms800 2017-08-02 00:00:00.000.....and?
what are you expecting the results to be?can you please tell us what results you want for this basic query?
you are not helping any of us at the moment________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
im sorry for late
this is actually what i need
thanks
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply