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

Transact-SQL
,
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

finalresult.jpg697×75 16.5 KB

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 :slight_smile:
;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

Thank you for reply
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

I add these sentence
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

Thank you for reply
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
            ,dateadd(day,a.rn,@start_dt) as thedate
        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
            ,dateadd(day,a.rn,@start_dt) as thedate
        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

Thank you for reply
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

Thank you for reply
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
            ,dateadd(day,a.rn,@start_dt) as thedate
        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
            ,dateadd(day,a.rn,@start_dt) as thedate
        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
            ,dateadd(day,a.rn,@start_dt) as thedate
        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
            ,dateadd(day,a.rn,@start_dt) as thedate
        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