How to get total cost of start package table between two periods by sql query?

ahmedba · July 15, 2017, 11:24pm

Problem:

how to get total cost of start package table between two periods with a SQL query?

alexaPackage from 28/06/2017 to 05/07/2017 8days cost ?

Details

I need to get total cost in start package table between two dates, start date and end date. This cost between two

periods represent cost of hotels found on every packages depend on period per every hotel price.

Database script
USE [NileTravel3]
GO
/****** Object:  Table [dbo].[DayDetails]    Script Date: 14/07/2017 11:16:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DayDetails](
	[DayDetailsID] [nvarchar](50) NOT NULL,
	[DetailsDurationID] [nvarchar](50) NULL,
	[HotelID] [int] 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: 14/07/2017 11:17:00 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: 14/07/2017 11:17:00 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: 14/07/2017 11:17:00 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: 14/07/2017 11:17:00 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,
 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: 14/07/2017 11:17:00 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: 14/07/2017 11:17:00 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:  View [dbo].[View_1]    Script Date: 14/07/2017 11:17:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_1]
AS
SELECT        dbo.Package.PackageName, dbo.Package.Duration, dbo.PackageDuration.PackageDuration, dbo.PackageDuration.NightCounts, dbo.DurationDetails.Days, 
                         dbo.Hotel.HotelName, dbo.HotelPrice.FromDate, dbo.HotelPrice.ToDate, dbo.HotelPrice.HotelPrice
FROM            dbo.Package INNER JOIN
                         dbo.PackageDuration ON dbo.Package.PackageID = dbo.PackageDuration.PackageID INNER JOIN
                         dbo.DurationDetails ON dbo.PackageDuration.PackageDurationsID = dbo.DurationDetails.PackageDurationsID INNER JOIN
                         dbo.DayDetails ON dbo.DurationDetails.DetailsDurationID = dbo.DayDetails.DetailsDurationID INNER JOIN
                         dbo.Hotel ON dbo.DayDetails.HotelID = dbo.Hotel.HotelID INNER JOIN
                         dbo.HotelPrice ON dbo.Hotel.HotelID = dbo.HotelPrice.HotelID

GO
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', 2)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD05', N'DD05', 2)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD06', N'DD06', 2)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD07', N'DD07', 2)
INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD08', N'DD08', 1)
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].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (1, N'Hilton', N'***')
INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (2, N'Movenpick', 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].[Package] ([PackageID], [PackageName], [Duration]) VALUES (N'P02', N'AlexaPackage', N'8,15')
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].[StartPackage] ([StartID], [PackageID], [StartDate], [EndDate], [TotalCost]) VALUES (N'SD01', N'P02', CAST(0x0000A7A000000000 AS DateTime), CAST(0x0000A7A700000000 AS DateTime), CAST(250 AS Decimal(18, 0)))
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].[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
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[42] 4[1] 2[17] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "Package"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 118
               Right = 208
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "PackageDuration"
            Begin Extent = 
               Top = 6
               Left = 246
               Bottom = 135
               Right = 441
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "DurationDetails"
            Begin Extent = 
               Top = 0
               Left = 469
               Bottom = 112
               Right = 664
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "DayDetails"
            Begin Extent = 
               Top = 120
               Left = 38
               Bottom = 232
               Right = 219
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Hotel"
            Begin Extent = 
               Top = 138
               Left = 257
               Bottom = 250
               Right = 427
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "HotelPrice"
            Begin Extent = 
               Top = 118
               Left = 478
               Bottom = 247
               Right = 648
            End
            DisplayFlags = 280
            TopColumn = 1
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 12
         Width = 284
         Width = 1500
         W' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'idth = 1080
         Width = 1500
         Width = 1500
         Width = 780
         Width = 1290
         Width = 1275
         Width = 1500
         Width = 1155
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 570
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1'
GO
Explain for diagram

Package table:

    PackageID   PackageName   Duration
        p1         sfinx         8
        p2         alexa         8,15
PackageDuration table:

PackageDurationID  PackageID  Duration NightCount
PD01                   p2        8        7
PD02                   p2        15       14

DurationDetails table:

    DetailDurationID   PackageDurationID   Days
    DD01                     PD01          DAY1
    DD02                     PD01          DAY2
    DD03                     PD01          DAY3
    DD04                     PD01          DAY4
    DD05                     PD01          DAY5
    DD06                     PD01          DAY6
    DD07                     PD01          DAY7
    DD08                     PD01          DAY8
DayDetails table:

DayDetailID  DetailDurationID  HotelID
DayD01        DD01                01
DayD02        DD02                01
DayD03        DD03                01
DayD04        DD04                02
DayD05        DD05                02
DayD06        DD06                02
DayD07        DD07                02
DayD08        DD08                01
Hotel table:

HotelID  HotelName
01        Hilton
02        Movenpick
HotelPrice table:

HotelPriceID    FromDate   ToDate     HotelPrice HotelID
HP01            01/01/2017 30/06/2017 20          01
HP02            01/07/2017 31/12/2017 30          01
HP03            01/01/2017 30/06/2017 30          02
HP04            01/07/2017 31/12/2017 40          02
StartPackage table:

StartID   PackageID  StartDate   EndDate     TotalCost
SD01       p2        28/06/2017  05/07/2017   250
Calculate cost for total cost column:

date          cost
28/06/2017     20 
29/06/2017     20
30/06/2017     20
01/07/2017     40
02/07/2017     40
03/07/2017     40
04/07/2017     40
05/07/2017     30 
totalpackage  250

bitsmed · July 16, 2017, 12:21pm

I'm not exactly sure, what it is you're asking, but I think this might get you started:

declare @package nvarchar(100)=N'AlexaPackage';
declare @start_dt date=cast('2017-06-28' as date);
declare @end_dt date=cast('2017-07-05' as date);

with cte_days
  as (select dateadd(day,row_number() over(order by (select null))-1,@start_dt) as dt
            ,'DAY'+ltrim(str(row_number() over(order by (select null)))) as days
        from             (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as cte_days1(dt)
             cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as cte_days2(dt)
             cross apply (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) as cte_days3(dt)
     )
    ,cte_packageduration1
  as (select packagedurationsid
            ,packageid
            ,packageduration
            ,nightcounts
            ,row_number() over(partition by packageid order by packageduration) as rn
        from dbo.packageduration
     )
    ,cte_packageduration2
  as (select a.packagedurationsid
            ,a.packageid
            ,a.packageduration
            ,a.nightcounts
            ,isnull(b.packageduration,0) as packageduration_prev
        from cte_packageduration1 as a
             left outer join cte_packageduration1 as b
                          on b.packageid=a.packageid
                         and b.rn=a.rn-1
     )
select sum(h.hotelprice) as calculated_total_cost
      ,avg(c.totalcost) as total_cost
  from dbo.package as a
       inner join cte_days as b
	           on b.dt<dateadd(day,1,@end_dt)
       inner join dbo.startpackage as c
               on c.packageid=a.packageid
              and c.startdate<=b.dt
              and c.enddate>=b.dt
       inner join cte_packageduration2 as d
               on d.packageid=a.packageid
              and d.packageduration_prev<datediff(day,@start_dt,@end_dt)+1
              and d.packageduration>=datediff(day,@start_dt,@end_dt)+1
       inner join dbo.durationdetails as e
               on e.packagedurationsid=d.packagedurationsid
              and e.days=b.days
       inner join dbo.daydetails as f
               on f.detailsdurationid=e.detailsdurationid
       inner join dbo.hotel as g
               on g.hotelid=f.hotelid
       inner join dbo.hotelprice as h
               on h.hotelid=g.hotelid
              and h.fromdate<=b.dt
              and h.todate>=b.dt
 where a.packagename=@package
;

ahmedba · July 17, 2017, 2:45pm

Thank you for reply
I solved by this query
DECLARE @DateFrom DATE = '20170628';DECLARE @DateTo DATE = '20170705';
DECLARE @StartID NVARCHAR(255) = 'SD01';
WITH DurationDetailsNumeric AS ( SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY DD.PackageDurationsID ORDER BY DD.Days ) AS RN
FROM DurationDetails DD ),[Data]
AS ( SELECT SP.StartID ,P.PackageID ,SP.TotalCost ,SUM(HP.HotelPrice) AS TotalCostCalculated
FROM StartPackage SP INNER JOIN Package P ON P.PackageID = SP.PackageID
INNER JOIN PackageDuration PD ON PD.PackageID = P.PackageID
INNER JOIN DurationDetailsNumeric DDN ON DDN.PackageDurationsID = PD.PackageDurationsID
INNER JOIN DayDetails DyD ON DyD.DetailsDurationID = DDN.DetailsDurationID
INNER JOIN Hotel H ON H.HotelID = DyD.HotelID
INNER JOIN HotelPrice HP ON HP.HotelID = H.HotelID
WHERE DATEADD(DAY, DDN.RN - 1, SP.StartDate) BETWEEN HP.FromDate AND HP.ToDate
AND SP.StartID = @StartID
AND DATEADD(DAY, DDN.RN - 1, SP.StartDate) BETWEEN @DateFrom AND @DateTo
GROUP BY SP.StartID ,
P.PackageID ,
SP.TotalCost
)
UPDATE SP
SET SP.TotalCost = D.TotalCostCalculated
FROM StartPackage SP
INNER JOIN [Data] D ON D.StartID = SP.StartID;
SELECT *
FROM StartPackage SP;
I solved but remaining problem if any one can help me please

ahmedba · July 17, 2017, 2:49pm

my remaining problem is how to get packages related to hotel
my result i need to get
HotelID HotelName package startdate enddate totalcost
see screen shoot below