• 1.6k
• 530
• 121.6k

# How to get total cost of start package table between two per

Jul 14 2017 6:53 PM

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.

Image of diagram and view all data

1. Package table:
2.
3.     PackageID   PackageName   Duration
4.         p1         sfinx         8
5.         p2         alexa         8,15
6. PackageDuration table:
7.
8. PackageDurationID  PackageID  Duration NightCount
9. PD01                   p2        8        7
10. PD02                   p2        15       14
11.
12. DurationDetails table:
13.
14.     DetailDurationID   PackageDurationID   Days
15.     DD01                     PD01          DAY1
16.     DD02                     PD01          DAY2
17.     DD03                     PD01          DAY3
18.     DD04                     PD01          DAY4
19.     DD05                     PD01          DAY5
20.     DD06                     PD01          DAY6
21.     DD07                     PD01          DAY7
22.     DD08                     PD01          DAY8
23. DayDetails table:
24.
25. DayDetailID  DetailDurationID  HotelID
26. DayD01        DD01                01
27. DayD02        DD02                01
28. DayD03        DD03                01
29. DayD04        DD04                02
30. DayD05        DD05                02
31. DayD06        DD06                02
32. DayD07        DD07                02
33. DayD08        DD08                01
34. Hotel table:
35.
36. HotelID  HotelName
37. 01        Hilton
38. 02        Movenpick
39. HotelPrice table:
40.
41. HotelPriceID    FromDate   ToDate     HotelPrice HotelID
42. HP01            01/01/2017 30/06/2017 20          01
43. HP02            01/07/2017 31/12/2017 30          01
44. HP03            01/01/2017 30/06/2017 30          02
45. HP04            01/07/2017 31/12/2017 40          02
46. StartPackage table:
47.
48. StartID   PackageID  StartDate   EndDate     TotalCost
49. SD01       p2        28/06/2017  05/07/2017   250
50. Calculate cost for total cost column:
51.
52. date          cost
53. 28/06/2017     20
54. 29/06/2017     20
55. 30/06/2017     20
56. 01/07/2017     40
57. 02/07/2017     40
58. 03/07/2017     40
59. 04/07/2017     40
60. 05/07/2017     30
61. totalpackage  250
1. USE [NileTravel3]
2. GO
3. /****** Object:  Table [dbo].[DayDetails]    Script Date: 14/07/2017 11:16:59 PM ******/
4. SET ANSI_NULLS ON
5. GO
6. SET QUOTED_IDENTIFIER ON
7. GO
8. CREATE TABLE [dbo].[DayDetails](
9.     [DayDetailsID] [nvarchar](50) NOT NULL,
10.     [DetailsDurationID] [nvarchar](50) NULL,
11.     [HotelID] [intNULL,
12.  CONSTRAINT [PK_DayDetails] PRIMARY KEY CLUSTERED
13. (
14.     [DayDetailsID] ASC
15. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]
16. ON [PRIMARY]
17.
18. GO
19. /****** Object:  Table [dbo].[DurationDetails]    Script Date: 14/07/2017 11:17:00 PM ******/
20. SET ANSI_NULLS ON
21. GO
22. SET QUOTED_IDENTIFIER ON
23. GO
24. CREATE TABLE [dbo].[DurationDetails](
25.     [DetailsDurationID] [nvarchar](50) NOT NULL,
26.     [PackageDurationsID] [nvarchar](50) NULL,
27.     [Days] [nvarchar](50) NULL,
28.  CONSTRAINT [PK_DurationDetails] PRIMARY KEY CLUSTERED
29. (
30.     [DetailsDurationID] ASC
31. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]
32. ON [PRIMARY]
33.
34. GO
35. /****** Object:  Table [dbo].[Hotel]    Script Date: 14/07/2017 11:17:00 PM ******/
36. SET ANSI_NULLS ON
37. GO
38. SET QUOTED_IDENTIFIER ON
39. GO
40. CREATE TABLE [dbo].[Hotel](
41.     [HotelID] [intNOT NULL,
42.     [HotelName] [nvarchar](50) NULL,
43.     [Rating] [nvarchar](10) NULL,
44.  CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
45. (
46.     [HotelID] ASC
47. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]
48. ON [PRIMARY]
49.
50. GO
51. /****** Object:  Table [dbo].[HotelPrice]    Script Date: 14/07/2017 11:17:00 PM ******/
52. SET ANSI_NULLS ON
53. GO
54. SET QUOTED_IDENTIFIER ON
55. GO
56. CREATE TABLE [dbo].[HotelPrice](
57.     [HotelPriceID] [nvarchar](50) NOT NULL,
58.     [FromDate] [datetime] NULL,
59.     [ToDate] [datetime] NULL,
60.     [HotelPrice] [decimal](18, 0) NULL,
61.     [HotelID] [intNULL,
62.  CONSTRAINT [PK_ProductPrice] PRIMARY KEY CLUSTERED
63. (
64.     [HotelPriceID] ASC
65. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]
66. ON [PRIMARY]
67.
68. GO
69. /****** Object:  Table [dbo].[Package]    Script Date: 14/07/2017 11:17:00 PM ******/
70. SET ANSI_NULLS ON
71. GO
72. SET QUOTED_IDENTIFIER ON
73. GO
74. CREATE TABLE [dbo].[Package](
75.     [PackageID] [nvarchar](50) NOT NULL,
76.     [PackageName] [nvarchar](100) NULL,
77.     [Duration] [nvarchar](50) NULL,
78.  CONSTRAINT [PK_Package] PRIMARY KEY CLUSTERED
79. (
80.     [PackageID] ASC
81. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]
82. ON [PRIMARY]
83.
84. GO
85. /****** Object:  Table [dbo].[PackageDuration]    Script Date: 14/07/2017 11:17:00 PM ******/
86. SET ANSI_NULLS ON
87. GO
88. SET QUOTED_IDENTIFIER ON
89. GO
90. CREATE TABLE [dbo].[PackageDuration](
91.     [PackageDurationsID] [nvarchar](50) NOT NULL,
92.     [PackageID] [nvarchar](50) NULL,
93.     [PackageDuration] [intNULL,
94.     [NightCounts] [intNULL,
95.  CONSTRAINT [PK_PackageDuration] PRIMARY KEY CLUSTERED
96. (
97.     [PackageDurationsID] ASC
98. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]
99. ON [PRIMARY]
100.
101. GO
102. /****** Object:  Table [dbo].[StartPackage]    Script Date: 14/07/2017 11:17:00 PM ******/
103. SET ANSI_NULLS ON
104. GO
105. SET QUOTED_IDENTIFIER ON
106. GO
107. CREATE TABLE [dbo].[StartPackage](
108.     [StartID] [nvarchar](50) NOT NULL,
109.     [PackageID] [nvarchar](50) NULL,
110.     [StartDate] [datetime] NULL,
111.     [EndDate] [datetime] NULL,
112.     [TotalCost] [decimal](18, 0) NULL,
113.  CONSTRAINT [PK_StartPackage] PRIMARY KEY CLUSTERED
114. (
115.     [StartID] ASC
116. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]
117. ON [PRIMARY]
118.
119. GO
120. /****** Object:  View [dbo].[View_1]    Script Date: 14/07/2017 11:17:00 PM ******/
121. SET ANSI_NULLS ON
122. GO
123. SET QUOTED_IDENTIFIER ON
124. GO
125. CREATE VIEW [dbo].[View_1]
126. AS
127. SELECT        dbo.Package.PackageName, dbo.Package.Duration, dbo.PackageDuration.PackageDuration, dbo.PackageDuration.NightCounts, dbo.DurationDetails.Days,
128.                          dbo.Hotel.HotelName, dbo.HotelPrice.FromDate, dbo.HotelPrice.ToDate, dbo.HotelPrice.HotelPrice
129. FROM            dbo.Package INNER JOIN
130.                          dbo.PackageDuration ON dbo.Package.PackageID = dbo.PackageDuration.PackageID INNER JOIN
131.                          dbo.DurationDetails ON dbo.PackageDuration.PackageDurationsID = dbo.DurationDetails.PackageDurationsID INNER JOIN
132.                          dbo.DayDetails ON dbo.DurationDetails.DetailsDurationID = dbo.DayDetails.DetailsDurationID INNER JOIN
133.                          dbo.Hotel ON dbo.DayDetails.HotelID = dbo.Hotel.HotelID INNER JOIN
134.                          dbo.HotelPrice ON dbo.Hotel.HotelID = dbo.HotelPrice.HotelID
135.
136. GO
137. INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD01', N'DD01', 1)
138. INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD02', N'DD02', 1)
139. INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD03', N'DD03', 1)
140. INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD04', N'DD04', 2)
141. INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD05', N'DD05', 2)
142. INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD06', N'DD06', 2)
143. INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD07', N'DD07', 2)
144. INSERT [dbo].[DayDetails] ([DayDetailsID], [DetailsDurationID], [HotelID]) VALUES (N'DDD08', N'DD08', 1)
145. INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD01', N'PD01', N'DAY1')
146. INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD02', N'PD01', N'DAY2')
147. INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD03', N'PD01', N'DAY3')
148. INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD04', N'PD01', N'DAY4')
149. INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD05', N'PD01', N'DAY5')
150. INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD06', N'PD01', N'DAY6')
151. INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD07', N'PD01', N'DAY7')
152. INSERT [dbo].[DurationDetails] ([DetailsDurationID], [PackageDurationsID], [Days]) VALUES (N'DD08', N'PD01', N'DAY8')
153. INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (1, N'Hilton', N'***')
154. INSERT [dbo].[Hotel] ([HotelID], [HotelName], [Rating]) VALUES (2, N'Movenpick', N'**')
155. 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)
156. 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)
157. 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)
158. 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)
159. INSERT [dbo].[Package] ([PackageID], [PackageName], [Duration]) VALUES (N'P02', N'AlexaPackage', N'8,15')
160. INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (N'PD01', N'P02', 8, 7)
161. INSERT [dbo].[PackageDuration] ([PackageDurationsID], [PackageID], [PackageDuration], [NightCounts]) VALUES (N'PD02', N'P02', 15, 14)
162. 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)))
163. ALTER TABLE [dbo].[DayDetails]  WITH CHECK ADD  CONSTRAINT [FK_DayDetails_DurationDetails] FOREIGN KEY([DetailsDurationID])
164. REFERENCES [dbo].[DurationDetails] ([DetailsDurationID])
165. GO
166. ALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_DurationDetails]
167. GO
168. ALTER TABLE [dbo].[DayDetails]  WITH CHECK ADD  CONSTRAINT [FK_DayDetails_Hotel] FOREIGN KEY([HotelID])
169. REFERENCES [dbo].[Hotel] ([HotelID])
170. GO
171. ALTER TABLE [dbo].[DayDetails] CHECK CONSTRAINT [FK_DayDetails_Hotel]
172. GO
173. ALTER TABLE [dbo].[DurationDetails]  WITH CHECK ADD  CONSTRAINT [FK_DurationDetails_ProgramDuration] FOREIGN KEY([PackageDurationsID])
174. REFERENCES [dbo].[PackageDuration] ([PackageDurationsID])
175. GO
176. ALTER TABLE [dbo].[DurationDetails] CHECK CONSTRAINT [FK_DurationDetails_ProgramDuration]
177. GO
178. ALTER TABLE [dbo].[HotelPrice]  WITH CHECK ADD  CONSTRAINT [FK_HotelPrice_Hotel] FOREIGN KEY([HotelID])
179. REFERENCES [dbo].[Hotel] ([HotelID])
180. GO
181. ALTER TABLE [dbo].[HotelPrice] CHECK CONSTRAINT [FK_HotelPrice_Hotel]
182. GO
183. ALTER TABLE [dbo].[PackageDuration]  WITH CHECK ADD  CONSTRAINT [FK_PackageDuration_Package] FOREIGN KEY([PackageID])
184. REFERENCES [dbo].[Package] ([PackageID])
185. GO
186. ALTER TABLE [dbo].[PackageDuration] CHECK CONSTRAINT [FK_PackageDuration_Package]
187. GO
188. ALTER TABLE [dbo].[StartPackage]  WITH CHECK ADD  CONSTRAINT [FK_StartPackage_Package] FOREIGN KEY([PackageID])
189. REFERENCES [dbo].[Package] ([PackageID])
190. GO
191. ALTER TABLE [dbo].[StartPackage] CHECK CONSTRAINT [FK_StartPackage_Package]
192. GO