# How to get total cost per hotel and transfer per 8 days by sql query?

#1

Problem

How to get total cost per hotel and transfer per 8 days ?

Details

8 days = 7 nights

meaning 8 days = 7 days accommodation per hotel

because last day he take flight not stay in hotel .

Result i need to get it

why null display in fiddle i need result in one line as above

the database and query found in the following fiddle

http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=0096a903948a93c1269e931328648be2
this sql query used
;with cte_HotelPrice
as
(
select
T6.HotelPrice,
T4.HotelID,
T5.HotelName,
T3.DetailsDurationID from package T
inner join StartPackage T1 on T.PackageId=T1.PackageId
inner join packageduration T2 on T.PackageId=T2.PackageId
inner join (SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY PackageDurationsId ORDER BY Days)
FROM DurationDetails) T3 on T2.PackageDurationsId=T3.PackageDurationsID
inner join DayDetails T4 on T3.DetailsDurationID=T4.DetailsDurationID
left join Hotel T5 on T4.HotelID=T5.HotelID
cross apply (select HotelPrice from HotelPrice where HotelID=T4.HotelID and FromDate<=DATEADD(day, T3.RN - 1, T1.StartDate) and ToDate>=DATEADD(day, T3.RN - 1, T1.StartDate)) T6
)
,TransferPrice as
(
select
ttd.Price,
dds.DetailsDurationID
from package p
inner join StartPackage s on p.PackageId=s.PackageId
inner join packageduration pd on p.PackageId=pd.PackageId
inner join (SELECT *, RN = ROW_NUMBER() OVER (PARTITION BY PackageDurationsId ORDER BY Days)
FROM DurationDetails) dd on pd.PackageDurationsId=dd.PackageDurationsID
inner join DayDetails dds on dd.DetailsDurationID=dds.DetailsDurationID
left join TransferType tt on dds.TransferTypeID=tt.TransferID
cross apply (select Price from TransferPeriod where TransferTypeID=dds.TransferTypeID and FromDate<=DATEADD(day, dd.RN - 1, s.StartDate) and Todate>=DATEADD(day, dd.RN - 1, s.StartDate)) ttd
)
select
S4.HotelID,S4.HotelName, S.PackageName, S1.StartDate, S1.EndDate,
sum(S4.HotelPrice) AS cost,
sum(S5.Price) as transfercost
from package S
inner join StartPackage S1 on S.PackageId=S1.PackageId
inner join packageduration S2 on S.PackageId=S2.PackageId
inner join DurationDetails S3 on S2.PackageDurationsId=S3.PackageDurationsID
left join cte_HotelPrice S4 on S3.DetailsDurationID=S4.DetailsDurationID
left join TransferPrice S5 on S3.DetailsDurationID=S5.DetailsDurationID
GROUP BY S4.HotelID, S4.HotelName,S.PackageName, S1.StartDate, S1.EndDate

#2

The query you posted doesn't "compile" with the tables/data you provided.

You're getting the null row because day 8 in daydetails referes to null hotel.

Replace:

``````S4.HotelID,S4.HotelName, S.PackageName, S1.StartDate, S1.EndDate,
``````

with

``````max(S4.HotelID) as hotelid,max(S4.HotelName) as hotelname, S.PackageName, S1.StartDate, S1.EndDate,
``````

and replace:

``````GROUP BY S4.HotelID, S4.HotelName,S.PackageName, S1.StartDate, S1.EndDate
``````

with

``````GROUP BY S.PackageName, S1.StartDate, S1.EndDate
``````

ps.: your cost and transfercost are not as the result you'd expect.

#3

I got the correct result i need
`1 Hilton AlexaPackage 2017-06-28 00:00:00.000 2017-07-05 00:00:00.000 180 700`
but suppose i have package contain two hotels
it not display two hotels it display only one hotel
see following sample data
USE [NileTravel3]
GO
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [Resident]) VALUES (N'P02', N'AlexaPackage', N'8,15', 0)
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [Resident]) VALUES (N'P03', N'Amon', N'8', 1)
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], [DestinationID]) VALUES (1, N'Hilton', N'*', NULL)
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating], [DestinationID]) VALUES (2, N'Movenpick', N'
', NULL)
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating], [DestinationID]) VALUES (3, N'BasmaHotel', N'***', NULL)
INSERT [dbo].[TransferType] ([TransferID], [TransferType]) VALUES (N'T01', N'from airport to hotel')
INSERT [dbo].[TransferType] ([TransferID], [TransferType]) VALUES (N'T02', N'From Hotel to parking')
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID], [ExcursionID], [RouteID]) VALUES (N'DDD01', N'DD01', 1, N'T01', NULL, N'R01')
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID], [ExcursionID], [RouteID]) VALUES (N'DDD02', N'DD02', 1, N'T01', NULL, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID], [ExcursionID], [RouteID]) VALUES (N'DDD03', N'DD03', 1, NULL, N'E01', NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID], [ExcursionID], [RouteID]) VALUES (N'DDD04', N'DD04', 1, NULL, NULL, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID], [ExcursionID], [RouteID]) VALUES (N'DDD05', N'DD05', 1, NULL, N'E02', NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID], [ExcursionID], [RouteID]) VALUES (N'DDD06', N'DD06', 1, NULL, NULL, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID], [ExcursionID], [RouteID]) VALUES (N'DDD07', N'DD07', 1, NULL, NULL, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID], [ExcursionID], [RouteID]) VALUES (N'DDD08', N'DD08', NULL, N'T02', NULL, N'R02')
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID], [ExcursionID], [RouteID]) VALUES (N'DDD09', N'DD09', 2, N'T01', NULL, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID], [ExcursionID], [RouteID]) VALUES (N'DDD10', N'DD10', 2, NULL, NULL, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID], [ExcursionID], [RouteID]) VALUES (N'DDD11', N'DD11', 2, NULL, N'E01', NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID], [ExcursionID], [RouteID]) VALUES (N'DDD12', N'DD12', 2, NULL, NULL, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID], [ExcursionID], [RouteID]) VALUES (N'DDD13', N'DD13', 2, NULL, NULL, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID], [ExcursionID], [RouteID]) VALUES (N'DDD14', N'DD14', 2, NULL, N'E02', NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID], [ExcursionID], [RouteID]) VALUES (N'DDD15', N'DD15', 3, NULL, NULL, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID], [ExcursionID], [RouteID]) VALUES (N'DDD16', N'DD16', 3, N'T02', NULL, NULL)
INSERT [dbo].[TransferPeriod] ([TransferDataID], [FromDate], [Todate], [Price], [TransferTypeID]) VALUES (N'TD01', CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A7A200000000 AS DateTime), CAST(200 AS Decimal(18, 0)), N'T01')
INSERT [dbo].[TransferPeriod] ([TransferDataID], [FromDate], [Todate], [Price], [TransferTypeID]) VALUES (N'TD02', CAST(0x0000A7A300000000 AS DateTime), CAST(0x0000A85A00000000 AS DateTime), CAST(300 AS Decimal(18, 0)), N'T01')
INSERT [dbo].[TransferPeriod] ([TransferDataID], [FromDate], [Todate], [Price], [TransferTypeID]) VALUES (N'TD03', CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A7A200000000 AS DateTime), CAST(200 AS Decimal(18, 0)), N'T02')
INSERT [dbo].[TransferPeriod] ([TransferDataID], [FromDate], [Todate], [Price], [TransferTypeID]) VALUES (N'TD04', CAST(0x0000A7A300000000 AS DateTime), CAST(0x0000A85A00000000 AS DateTime), CAST(300 AS Decimal(18, 0)), N'T02')
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)
INSERT [dbo].[HotelPrice] ([HotelPriceID], [FromDate], [ToDate], [HotelPrice], [HotelID]) VALUES (N'HP05', CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A7A200000000 AS DateTime), CAST(50 AS Decimal(18, 0)), 3)
INSERT [dbo].[HotelPrice] ([HotelPriceID], [FromDate], [ToDate], [HotelPrice], [HotelID]) VALUES (N'HP06', CAST(0x0000A7A300000000 AS DateTime), CAST(0x0000A85A00000000 AS DateTime), CAST(60 AS Decimal(18, 0)), 3)

#4

the modification you give me is for packages have only one hotel
suppose i have two hotels in package
how to display two hotel
if i have package contain two hotels according to your modification
it will show only one hotel not more

#5

Like I wrote:

Now if you "connect" day 8 to a hotel (just like you did with your latest sample data on day 16), you wouldn't have this problem, and you could revert the changes I proposed earlier.

#6

what i do as following

`WHERE (S4.HotelId IS NOT NULL )` to first query i write in first post as folowing
select
S4.HotelID,S4.HotelName, S.PackageName, S1.StartDate, S1.EndDate,
sum(S4.HotelPrice) AS cost,
sum(S5.Price) AS transfercost
from package S
inner join StartPackage S1 on S.PackageId=S1.PackageId
inner join packageduration S2 on S.PackageId=S2.PackageId
inner join DurationDetails S3 on S2.PackageDurationsId=S3.PackageDurationsID
left join cte_HotelPrice S4 on S3.DetailsDurationID=S4.DetailsDurationID
left join TransferPrice S5 on S3.DetailsDurationID=S5.DetailsDurationID WHERE (S4.HotelId IS NOT NULL )
GROUP BY S4.HotelID, S4.HotelName,S.PackageName, S1.StartDate, S1.EndDate
and i get following
wrong in transfer only
but hotels is correct and no null display
How to make correct to transfer values

as example 400 must be 700 to be correct for transfer cost for alexa package

#7

This is why you get the null row:

If you replace the null in hotelid on this row with 1, your sums will be correct (if you remove the where statement you just added).

#8

8days = 7day
if tourisits booking hotel for 8 days
it will equal 7 night meaning 7 days accommodation on hotel
and last days must be null
because last day it will take flight to his country
this is my system work for booking
null in day 8 not wrong
so that how to get transfer cost correct

#9

Well how come day 16 then links to hotelid 3?

You have to define, how transfercost should be "assigned" to a hotel, in case of hotelid is null.

#10

yes you are correct this is wrong from me
im sorry
but in my system 8days = night accommodation in hotel
because last day for flight
until now i remove null and get hotel cost correct but remaining
transfer cost is wrong
How to get correct transfer cost

#11

Does this work for you:

``````declare @start_dt date=cast('2017-06-28' as date);
declare @end_dt date=cast('2017-07-05' as date);

with cte_durationdetails1
as (select detailsdurationid
,packagedurationsid
,row_number() over(partition by packagedurationsid order by detailsdurationid)-1 as rn
from dbo.durationdetails
)
,cte_durationdetails2
as (select a.detailsdurationid
,a.packagedurationsid
from cte_durationdetails1 as a
left outer join cte_durationdetails1 as b
on b.packagedurationsid=a.packagedurationsid
and b.rn=a.rn-1
)
,cte_daydetails1
as (select daydetailsid
,detailsdurationid
,hotelid
,transfertypeid
,row_number() over(order by daydetailsid) as rn
from dbo.daydetails
)
,cte_daydetails2
as (select a.daydetailsid
,a.detailsdurationid
,isnull(a.hotelid,b.hotelid) as hotelid
,a.transfertypeid
from cte_daydetails1 as a
left outer join cte_daydetails1 as b
on b.rn=a.rn-1
)
select e.hotelid
,f.hotelname
,a.packagename
,min(d.thedate) as startdate
,max(d.thedate) as enddate
,sum(isnull(g.hotelprice,0)) as hotelcost
,sum(isnull(h.price,0)) as transfercost
from dbo.package as a
inner join dbo.packageduration as b
on b.packageid=a.packageid
inner join dbo.startpackage as c
on c.packageid=a.packageid
and datediff(day,c.startdate,c.enddate)=b.nightcounts
inner join cte_durationdetails2 as d
on d.packagedurationsid=b.packagedurationsid
inner join cte_daydetails2 as e
on e.detailsdurationid=d.detailsdurationid
left outer join dbo.hotel as f
on f.hotelid=e.hotelid
left outer join dbo.hotelprice as g
on g.hotelid=f.hotelid
and g.fromdate<=e.thedate
and g.todate>=e.thedate
left outer join dbo.transferperiod as h
on h.transfertypeid=e.transfertypeid
and h.fromdate<=e.thedate
and h.todate>=e.thedate
group by e.hotelid
,f.hotelname
,a.packagename
order by packagename
,startdate
;
``````

#12

according to my sample data
final result must be
`1 Hilton alexaPackage 28/06/2017 05-07-2017 180 500`
but after i run query above i get following result

there are two wrong in image
1- extra record per movenpick number 2
2-per hilton hotel hotel cost must be 180 not 220

#13

So which sample data are we using this time?

#14

when run the following query
declare @start_dt date=cast('2017-06-28' as date);
declare @end_dt date=cast('2017-07-05' as date);

``````with cte_durationdetails1
as (select detailsdurationid
,packagedurationsid
,row_number() over(partition by packagedurationsid order by detailsdurationid)-1 as rn
from dbo.durationdetails
)
,cte_durationdetails2
as (select a.detailsdurationid
,a.packagedurationsid
from cte_durationdetails1 as a
left outer join cte_durationdetails1 as b
on b.packagedurationsid=a.packagedurationsid
and b.rn=a.rn-1
)
,cte_daydetails1
as (select daydetailsid
,detailsdurationid
,hotelid
,transfertypeid
,row_number() over(order by daydetailsid) as rn
from dbo.daydetails
)
,cte_daydetails2
as (select a.daydetailsid
,a.detailsdurationid
,isnull(a.hotelid,b.hotelid) as hotelid
,a.transfertypeid
from cte_daydetails1 as a
left outer join cte_daydetails1 as b
on b.rn=a.rn-1
)
select e.hotelid
,f.hotelname
,a.packagename
,min(d.thedate) as startdate
,max(d.thedate) as enddate
,sum(isnull(g.hotelprice,0)) as hotelcost
,sum(isnull(h.price,0)) as transfercost
from dbo.package as a
inner join dbo.packageduration as b
on b.packageid=a.packageid
inner join dbo.startpackage as c
on c.packageid=a.packageid
and datediff(day,c.startdate,c.enddate)=b.nightcounts
inner join cte_durationdetails2 as d
on d.packagedurationsid=b.packagedurationsid
inner join cte_daydetails2 as e
on e.detailsdurationid=d.detailsdurationid
left outer join dbo.hotel as f
on f.hotelid=e.hotelid
left outer join dbo.hotelprice as g
on g.hotelid=f.hotelid
and g.fromdate<=e.thedate
and g.todate>=e.thedate
left outer join dbo.transferperiod as h
on h.transfertypeid=e.transfertypeid
and h.fromdate<=e.thedate
and h.todate>=e.thedate
group by e.hotelid
,f.hotelname
,a.packagename
order by packagename
,startdate
;
``````

to this sample data

``````CREATE TABLE [dbo].[DayDetails](
[DayDetailsID] [nvarchar](50) NOT NULL,
[DetailsDurationID] [nvarchar](50) NULL,
[HotelID] [int] NULL,
[TransferTypeID] [nvarchar](50) NULL,
CONSTRAINT [PK_DayDetails] PRIMARY KEY CLUSTERED
(
[DayDetailsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[DurationDetails]    Script Date: 24/07/2017 7:20:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DurationDetails](
[DetailsDurationID] [nvarchar](50) NOT NULL,
[PackageDurationsID] [nvarchar](50) NULL,
[Days] [nvarchar](50) NULL,
CONSTRAINT [PK_DurationDetails] PRIMARY KEY CLUSTERED
(
[DetailsDurationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Hotel]    Script Date: 24/07/2017 7:20:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Hotel](
[HotelID] [int] NOT NULL,
[HotelName] [nvarchar](50) NULL,
[Rating] [nvarchar](10) NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[HotelID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[HotelPrice]    Script Date: 24/07/2017 7:20:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HotelPrice](
[HotelPriceID] [nvarchar](50) NOT NULL,
[FromDate] [datetime] NULL,
[ToDate] [datetime] NULL,
[HotelPrice] [decimal](18, 0) NULL,
[HotelID] [int] NULL,
CONSTRAINT [PK_ProductPrice] PRIMARY KEY CLUSTERED
(
[HotelPriceID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Package]    Script Date: 24/07/2017 7:20:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Package](
[PackageID] [nvarchar](50) NOT NULL,
[PackageName] [nvarchar](100) NULL,
[Duration] [nvarchar](50) NULL,
[Resident] [tinyint] NULL,
CONSTRAINT [PK_Package] PRIMARY KEY CLUSTERED
(
[PackageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[PackageDuration]    Script Date: 24/07/2017 7:20:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PackageDuration](
[PackageDurationsID] [nvarchar](50) NOT NULL,
[PackageID] [nvarchar](50) NULL,
[PackageDuration] [int] NULL,
[NightCounts] [int] NULL,
CONSTRAINT [PK_PackageDuration] PRIMARY KEY CLUSTERED
(
[PackageDurationsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[StartPackage]    Script Date: 24/07/2017 7:20:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StartPackage](
[StartID] [nvarchar](50) NOT NULL,
[PackageID] [nvarchar](50) NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[TotalCost] [decimal](18, 0) NULL,
CONSTRAINT [PK_StartPackage] PRIMARY KEY CLUSTERED
(
[StartID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[TransferPeriod]    Script Date: 24/07/2017 7:20:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TransferPeriod](
[TransferDataID] [nvarchar](50) NOT NULL,
[FromDate] [datetime] NULL,
[Todate] [datetime] NULL,
[Price] [decimal](18, 0) NULL,
[TransferTypeID] [nvarchar](50) NULL,
CONSTRAINT [PK_TransferPeriod] PRIMARY KEY CLUSTERED
(
[TransferDataID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[TransferType]    Script Date: 24/07/2017 7:20:44 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TransferType](
[TransferID] [nvarchar](50) NOT NULL,
[TransferType] [nvarchar](50) NULL,
CONSTRAINT [PK_TransferType] PRIMARY KEY CLUSTERED
(
[TransferID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD01', N'DD01', 1, N'T01')
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD02', N'DD02', 1, N'T01')
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD03', N'DD03', 1, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD04', N'DD04', 1, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD05', N'DD05', 1, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD06', N'DD06', 1, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD07', N'DD07', 1, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD08', N'DD08', NULL, N'T02')
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD09', N'DD09', 2, N'T01')
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD10', N'DD10', 2, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD11', N'DD11', 2, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD12', N'DD12', 2, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD13', N'DD13', 2, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD14', N'DD14', 2, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD15', N'DD15', 3, NULL)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID], [TransferTypeID]) VALUES (N'DDD16', N'DD16', NULL, N'T02')
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].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (3, N'BasmaHotel', N'***')
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)
INSERT [dbo].[HotelPrice] ([HotelPriceID], [FromDate], [ToDate], [HotelPrice], [HotelID]) VALUES (N'HP05', CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A7A200000000 AS DateTime), CAST(50 AS Decimal(18, 0)), 3)
INSERT [dbo].[HotelPrice] ([HotelPriceID], [FromDate], [ToDate], [HotelPrice], [HotelID]) VALUES (N'HP06', CAST(0x0000A7A300000000 AS DateTime), CAST(0x0000A85A00000000 AS DateTime), CAST(60 AS Decimal(18, 0)), 3)
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [Resident]) VALUES (N'P02', N'AlexaPackage', N'8,15', 0)
INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration], [Resident]) VALUES (N'P03', N'Amon', N'8', 1)
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(0x0000A91000000000 AS DateTime), CAST(0x0000A7AA00000000 AS DateTime), NULL)
INSERT [dbo].[TransferPeriod] ([TransferDataID], [FromDate], [Todate], [Price], [TransferTypeID]) VALUES (N'TD01', CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A7A200000000 AS DateTime), CAST(200 AS Decimal(18, 0)), N'T01')
INSERT [dbo].[TransferPeriod] ([TransferDataID], [FromDate], [Todate], [Price], [TransferTypeID]) VALUES (N'TD02', CAST(0x0000A7A300000000 AS DateTime), CAST(0x0000A85A00000000 AS DateTime), CAST(300 AS Decimal(18, 0)), N'T01')
INSERT [dbo].[TransferPeriod] ([TransferDataID], [FromDate], [Todate], [Price], [TransferTypeID]) VALUES (N'TD03', CAST(0x0000A6EE00000000 AS DateTime), CAST(0x0000A7A200000000 AS DateTime), CAST(200 AS Decimal(18, 0)), N'T02')
INSERT [dbo].[TransferPeriod] ([TransferDataID], [FromDate], [Todate], [Price], [TransferTypeID]) VALUES (N'TD04', CAST(0x0000A7A300000000 AS DateTime), CAST(0x0000A85A00000000 AS DateTime), CAST(300 AS Decimal(18, 0)), N'T02')
INSERT [dbo].[TransferType] ([TransferID], [TransferType]) VALUES (N'T01', N'from airport to hotel')
INSERT [dbo].[TransferType] ([TransferID], [TransferType]) VALUES (N'T02', N'From Hotel to parking')
ALTER TABLE [dbo].[DayDetails]  WITH CHECK ADD  CONSTRAINT [FK_DayDetails_DurationDetails] FOREIGN KEY([DetailsDurationID])
REFERENCES [dbo].[DurationDetails] ([DetailsDurationID])
GO
ALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_DurationDetails]
GO
ALTER TABLE [dbo].[DayDetails]  WITH CHECK ADD  CONSTRAINT [FK_DayDetails_Hotel] FOREIGN KEY([HotelID])
REFERENCES [dbo].[Hotel] ([HotelID])
GO
ALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_Hotel]
GO
ALTER TABLE [dbo].[DayDetails]  WITH CHECK ADD  CONSTRAINT [FK_DayDetails_TransferType] FOREIGN KEY([TransferTypeID])
REFERENCES [dbo].[TransferType] ([TransferID])
GO
ALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_TransferType]
GO
ALTER TABLE [dbo].[DurationDetails]  WITH CHECK ADD  CONSTRAINT [FK_DurationDetails_ProgramDuration] FOREIGN KEY([PackageDurationsID])
REFERENCES [dbo].[PackageDuration] ([PackageDurationsID])
GO
ALTER TABLE [dbo].[DurationDetails] CHECK CONSTRAINT [FK_DurationDetails_ProgramDuration]
GO
ALTER TABLE [dbo].[HotelPrice]  WITH CHECK ADD  CONSTRAINT [FK_HotelPrice_Hotel] FOREIGN KEY([HotelID])
REFERENCES [dbo].[Hotel] ([HotelID])
GO
ALTER TABLE [dbo].[HotelPrice] CHECK CONSTRAINT [FK_HotelPrice_Hotel]
GO
ALTER TABLE [dbo].[PackageDuration]  WITH CHECK ADD  CONSTRAINT [FK_PackageDuration_Package] FOREIGN KEY([PackageID])
REFERENCES [dbo].[Package] ([PackageID])
GO
ALTER TABLE [dbo].[PackageDuration] CHECK CONSTRAINT [FK_PackageDuration_Package]
GO
ALTER TABLE [dbo].[StartPackage]  WITH CHECK ADD  CONSTRAINT [FK_StartPackage_Package] FOREIGN KEY([PackageID])
REFERENCES [dbo].[Package] ([PackageID])
GO
ALTER TABLE [dbo].[StartPackage] CHECK CONSTRAINT [FK_StartPackage_Package]
GO
ALTER TABLE [dbo].[TransferPeriod]  WITH CHECK ADD  CONSTRAINT [FK_TransferPeriod_TransferType] FOREIGN KEY([TransferTypeID])
REFERENCES [dbo].[TransferType] ([TransferID])
GO
ALTER TABLE [dbo].[TransferPeriod] CHECK CONSTRAINT [FK_TransferPeriod_TransferType]
GO
``````

I get the following result

transfer cost is correct
hotel cost is wrong

#15

This should do the trick:

``````declare @start_dt date=cast('2017-06-28' as date);
declare @end_dt date=cast('2017-07-05' as date);

with cte_durationdetails1
as (select detailsdurationid
,packagedurationsid
,row_number() over(partition by packagedurationsid order by detailsdurationid)-1 as rn
from dbo.durationdetails
)
,cte_durationdetails2
as (select a.detailsdurationid
,a.packagedurationsid
from cte_durationdetails1 as a
left outer join cte_durationdetails1 as b
on b.packagedurationsid=a.packagedurationsid
and b.rn=a.rn-1
)
,cte_daydetails1
as (select daydetailsid
,detailsdurationid
,hotelid
,transfertypeid
,row_number() over(order by daydetailsid)-1 as rn
from dbo.daydetails
)
,cte_daydetails2
as (select a.daydetailsid
,a.detailsdurationid
,a.hotelid as hotelid1
,isnull(a.hotelid,b.hotelid) as hotelid2
,a.transfertypeid
from cte_daydetails1 as a
left outer join cte_daydetails1 as b
on b.rn=a.rn-1
)
select e.hotelid2
,f.hotelname
,a.packagename
,min(d.thedate) as startdate
,max(d.thedate) as enddate
,sum(isnull(g.hotelprice,0)) as hotelcost
,sum(isnull(h.price,0)) as transfercost
from dbo.package as a
inner join dbo.packageduration as b
on b.packageid=a.packageid
inner join dbo.startpackage as c
on c.packageid=a.packageid
and datediff(day,c.startdate,c.enddate)=b.nightcounts
inner join cte_durationdetails2 as d
on d.packagedurationsid=b.packagedurationsid
inner join cte_daydetails2 as e
on e.detailsdurationid=d.detailsdurationid
left outer join dbo.hotel as f
on f.hotelid=e.hotelid2
left outer join dbo.hotelprice as g
on g.hotelid=e.hotelid1
and g.fromdate<=e.thedate
and g.todate>=e.thedate
left outer join dbo.transferperiod as h
on h.transfertypeid=e.transfertypeid
and h.fromdate<=e.thedate
and h.todate>=e.thedate
group by e.hotelid2
,f.hotelname
,a.packagename
order by packagename
,startdate
;
``````

#16

yes it work
but if i need to get all packages in table start packages
without variable @startdate and @endizhupanii need it dynamic
how to do that