Obtenir le prix de la chambre des dates qui se chevauchent

En supposant qu'aucun intervalle de chevauchement n'est défini, et en supposant que toutes les fourchettes données sont censées être inclusives, nous pouvons get datatables en utilisant un CTE puis une simple question d'agrégation:

 declare @t table (date_from date,date_to date, price_per_room int) insert into @t (date_from,date_to,price_per_room) values ('20170901','20170910',70.00 ), ('20170911','20170920',100.00) declare @Start date declare @End date select @Start = '20170907',@End = '20170915' ;With IncludedPeriods as ( select CASE WHEN @Start > date_from THEN @Start ELSE date_from END as fromDT, CASE WHEN @End < date_to THEN @End ELSE date_to END as ToDT, price_per_room from @t where date_from <= @End and @Start <= date_to ) select SUM(price_per_room * (1 + DATEDIFF(day,fromDT,ToDT))) from IncludedPeriods 

Notez que nous en ajoutons un au résultat DATEDIFF car il count les transitions , mais je suppose qu'une période de '20170911' à '20170911' devrait countr comme un jour et des périodes plus longues de la même manière.

Contrairement à certaines autres réponses qui tentent d'énumérer divers "cas" de chevauchements, cela utilise la règle simple – deux périodes se chevauchent si la première commence avant la seconde et si la seconde commence avant la première – c'est la logique appliquée dans le where clause à l'intérieur du CTE. Pour déterminer l' étendue du chevauchement, nous prenons la dernière des deux dates de début et la plus hâtive des deux dates de fin – c'est ce que font les expressions CASE . Si nous avions des fonctions scalaires MIN et MAX fonctionnant à des dates que je préférerais utiliser, mais pas de telles fonctions embeddedes à SQL Server.

Dans SQL Server, vous pouvez faire ceci:

 select dateadd(d, number, '20170801') from master.dbo.spt_values where type='P' and (number <= datediff(d, '20170801', '20170810')) 

Vous calculez le nombre total de jours (dans la clause where) et vous select les nombres master.dbo.spt_values entre 0 et le nombre total de jours ( master.dbo.spt_values est une table utile dans SQL Server). Sinon, vous pouvez créer une table avec des nombres ascendants de 0 à?). Et puis vous ajoutez au startdate le numéro. Donc, vous obtenez tous les jours de la réservation:

 2017-08-01 2017-08-02 2017-08-03 2017-08-04 2017-08-05 2017-08-06 2017-08-07 2017-08-08 2017-08-09 2017-08-10 

Ensuite, vous pouvez join votre price_table donnée le date_from et date_to et vous pouvez calculer votre sum.

EDIT: Juste pour le terminer … 🙂

 SELECT SUM(price_per_room) FROM master.dbo.spt_values LEFT OUTER JOIN room_prices AS rp ON (date_from <= DATEADD(d, number, my_start_date) AND DATEADD(d, number, my_start_date) <= date_to) WHERE type='P' AND (number <= DATEDIFF(d, my_start_date, my_end_date)) 

Je suggère d'utiliser une table de dates (table contenant toutes les dates) ou une table dérivée pour peupler ces dates (vous pouvez le find en ligne facilement) comme ceci:

 SELECT p.id,p.room_type_id,sum(p.price_per_room) FROM( SELECT t.date,s.price_per_room,s.id,s.room_type_id FROM (DATES QUERY \ DATE_TABLE) t LEFT JOIN s.yourTable ON(t.date between s.date_from and s.date_to)) p WHERE p.id = YourID and p.room_type_id = YourTypeId AND p.date between yourStartDate and yourEndDate GROUP BY p.id,p.room_type_id 

Cela remplit une ligne pour chaque date dans la plage de dates pour le prix, de sorte qu'il saura combien est le prix par jour, puis juste additionner le prix entre vos dates souhaitées.

Vous pouvez utiliser un calendar ou une table de dates pour ce genre de chose.

Sans prendre l'étape actuelle de la création d'une table, vous pouvez utiliser une table ad hoc de dates avec une expression de table commune comme ceci:

 declare @fromdate date = '20170907'; declare @thrudate date = '20170915'; ;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)) , dates as ( select top (datediff(day, @fromdate, @thrudate)+1) [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate)) from n as deka cross join n as hecto cross join n as kilo cross join n as tenK cross join n as hundredK order by [Date] ) select total_price = sum(price_per_room) from dates d inner join t on d.date >= t.date_from and d.date <= t.date_to 

rextester demo: http://rextester.com/MCF52261

résultats:

 +-------------+ | total_price | +-------------+ | 780 | +-------------+ 

Pour une répartition plus détaillée du prix et des dates, vous pouvez échanger le select ci-dessus pour celui-ci:

 select fromdate = min(date) , thrudate = max(date) , days = count(*) , price_per_room = avg(price_per_room) , total = sum(price_per_room) from dates d inner join t on d.date >= t.date_from and d.date <= t.date_to group by grouping sets ((price_per_room),()) 

rextester demo: http://rextester.com/NKZD1468

résultats:

 +------------+------------+------+----------------+-------+ | fromdate | thrudate | days | price_per_room | total | +------------+------------+------+----------------+-------+ | 2017-09-07 | 2017-09-10 | 4 | 70 | 280 | | 2017-09-11 | 2017-09-15 | 5 | 100 | 500 | | 2017-09-07 | 2017-09-15 | 9 | 86 | 780 | +------------+------------+------+----------------+-------+ 

Référence de table Number et Calendar:

• Générer un set ou une séquence sans loops – 2 – Aaron Bertrand
• Le tableau "Numbers" ou "Tally": Qu'est-ce que c'est et comment il remplace une boucle – Jeff Moden
• Création d'une table / dimension de date dans sql Server 2008 – David Stein
• Tableaux du calendar – Pourquoi en avez-vous besoin? – David Stein
• Création d'une dimension de date ou d'une table de calendar dans sql Server – Aaron Bertrand

Utiliser UNION ALL pour tous les quatre cas possibles peut faire le travail aussi bien

 SELECT SUM(xp) FROM ( SELECT (DATEDIFF(DAY, @from, date_to)+1)* price p FROM tab WHERE date_to BETWEEN @from AND @to AND NOT date_from BETWEEN @from AND @to UNION ALL SELECT ISNULL(SUM((DATEDIFF(DAY, date_from, date_to)+1)* price), 0) p FROM tab WHERE date_to BETWEEN @from AND @to AND date_from BETWEEN @from AND @to UNION ALL SELECT (DATEDIFF(DAY, date_from, @to)+1)* price p FROM tab WHERE date_from BETWEEN @from AND @to AND NOT date_to BETWEEN @from AND @to UNION ALL -- in the case that the (@from, @to) interval is completely in one row interval SELECT (DATEDIFF(DAY, @from, @to)+1)* price p FROM tab WHERE date_from <= @from AND date_to >= @to )x; 

Démo sqlfiddle