Table de calendar!

Un autre candidat parfait pour une table de calendar. Si vous ne pouvez pas être dérangé pour en chercher un, voici un que j'ai fait plus tôt .

Configurer datatables

 DECLARE @t_venues table ( venue_id int , start_date date , end_date date ); INSERT INTO @t_venues (venue_id, start_date, end_date) VALUES (1, '2014-01-01', '2014-01-02') , (2, '2014-01-05', '2014-01-05') , (3, '2014-01-09', '2014-01-15') , (4, '2014-01-20', '2014-01-30') ; DECLARE @t_venueuser table ( venue_id int , start_date date , end_date date ); INSERT INTO @t_venueuser (venue_id, start_date, end_date) VALUES (1, '2014-01-02', '2014-01-02') , (2, '2014-01-05', '2014-01-05') , (3, '2014-01-09', '2014-01-10') , (4, '2014-01-23', '2014-01-25') ; 

La requête

 SELECT t_venues.venue_id , calendar.the_date , CASE WHEN t_venueuser.venue_id IS NULL THEN 1 ELSE 0 END As is_available FROM dbo.calendar /* see: http://gvee.co.uk/files/sql/dbo.numbers%20&%20dbo.calendar.sql for an example */ INNER JOIN @t_venues As t_venues ON t_venues.start_date <= calendar.the_date AND t_venues.end_date >= calendar.the_date LEFT JOIN @t_venueuser As t_venueuser ON t_venueuser.venue_id = t_venues.venue_id AND t_venueuser.start_date <= calendar.the_date AND t_venueuser.end_date >= calendar.the_date ORDER BY t_venues.venue_id , calendar.the_date ; 

Le résultat

 venue_id the_date is_available ----------- ----------------------- ------------ 1 2014-01-01 00:00:00.000 1 1 2014-01-02 00:00:00.000 0 2 2014-01-05 00:00:00.000 0 3 2014-01-09 00:00:00.000 0 3 2014-01-10 00:00:00.000 0 3 2014-01-11 00:00:00.000 1 3 2014-01-12 00:00:00.000 1 3 2014-01-13 00:00:00.000 1 3 2014-01-14 00:00:00.000 1 3 2014-01-15 00:00:00.000 1 4 2014-01-20 00:00:00.000 1 4 2014-01-21 00:00:00.000 1 4 2014-01-22 00:00:00.000 1 4 2014-01-23 00:00:00.000 0 4 2014-01-24 00:00:00.000 0 4 2014-01-25 00:00:00.000 0 4 2014-01-26 00:00:00.000 1 4 2014-01-27 00:00:00.000 1 4 2014-01-28 00:00:00.000 1 4 2014-01-29 00:00:00.000 1 4 2014-01-30 00:00:00.000 1 (21 row(s) affected) 

L'explication

Nos tables de calendar contient une input pour chaque date.

Nous t_venues nos t_venues (en aparté, si vous avez le choix, perdez le préfixe t_ !) Pour returnner chaque jour entre notre start_date et end_date . Exemple de sortie pour venue_id=4 pour cette jointure:

 venue_id the_date ----------- ----------------------- 4 2014-01-20 00:00:00.000 4 2014-01-21 00:00:00.000 4 2014-01-22 00:00:00.000 4 2014-01-23 00:00:00.000 4 2014-01-24 00:00:00.000 4 2014-01-25 00:00:00.000 4 2014-01-26 00:00:00.000 4 2014-01-27 00:00:00.000 4 2014-01-28 00:00:00.000 4 2014-01-29 00:00:00.000 4 2014-01-30 00:00:00.000 (11 row(s) affected) 

Maintenant nous avons une ligne par jour, nous [outer] t_venueuser notre table t_venueuser . Nous nous associons à cela de la même manière qu'avant, mais avec une touche supplémentaire: nous devons nous joindre en fonction du lieu- venue_id !

L'exécution de this pour venue_id=4 donne ce résultat:

 venue_id the_date t_venueuser_venue_id ----------- ----------------------- -------------------- 4 2014-01-20 00:00:00.000 NULL 4 2014-01-21 00:00:00.000 NULL 4 2014-01-22 00:00:00.000 NULL 4 2014-01-23 00:00:00.000 4 4 2014-01-24 00:00:00.000 4 4 2014-01-25 00:00:00.000 4 4 2014-01-26 00:00:00.000 NULL 4 2014-01-27 00:00:00.000 NULL 4 2014-01-28 00:00:00.000 NULL 4 2014-01-29 00:00:00.000 NULL 4 2014-01-30 00:00:00.000 NULL (11 row(s) affected) 

Voyez comment nous avons une valeur NULL pour les lignes où il n'y a pas d'logging t_venueuser . Génie, non? ;-)

Donc, dans ma première requête, je vous ai donné une rapide déclaration CASE qui montre la disponibilité (1 = disponible, 0 = non disponible). Ceci est à titre d'illustration seulement, mais pourrait vous être utile.

Vous pouvez ensuite placer la requête en attente, puis appliquer un filter supplémentaire sur cette colonne calculée ou simplement append une clause where dans: WHERE t_venueuser.venue_id IS NULL et cela fera le même tour.

Ceci est un hack complet, mais il donne les résultats dont vous avez besoin, je l'ai seulement testé sur datatables que vous avez fournies afin qu'il puisse y avoir des getchas avec de plus grands sets.

En général, ce que vous cherchez à résoudre ici est une variation des lacunes et des problèmes d'îles, c'est (brièvement) une séquence où certains éléments sont manquants. Les éléments manquants sont référencés comme des espaces et les éléments existants sont référencés comme des îlots. Si vous souhaitez comprendre ce problème en général, consultez quelques articles:

• Article de discussion simple
• article blogs.MSDN
• SO répond à des tags-et-îles marqués

Code:

 ;with dates as ( SELECT vdates.venue_id, vdates.vdate FROM ( SELECT DATEADD(d,sv.number,v.start_date) vdate , v.venue_id FROM t_venues v INNER JOIN master..spt_values sv ON sv.type='P' AND sv.number BETWEEN 0 AND datediff(d, v.start_date, v.end_date)) vdates LEFT JOIN t_venueuser vu ON vdates.vdate >= vu.start_date AND vdates.vdate <= vu.end_date AND vdates.venue_id = vu.venue_id WHERE ISNULL(vu.venue_id,-1) = -1 ) SELECT venue_id, ISNULL([1],[2]) StartDate, [2] EndDate FROM (SELECT venue_id, rDate, ROW_NUMBER() OVER (PARTITION BY venue_id, DateType ORDER BY rDate) AS rType, DateType as dType FROM( SELECT d1.venue_id ,d1.vdate AS rDate ,'1' AS DateType FROM dates AS d1 LEFT JOIN dates AS d0 ON DATEADD(d,-1,d1.vdate) = d0.vdate LEFT JOIN dates AS d2 ON DATEADD(d,1,d1.vdate) = d2.vdate WHERE CASE ISNULL(d2.vdate, '01 Jan 1753') WHEN '01 Jan 1753' THEN '2' ELSE '1' END = 1 AND ISNULL(d0.vdate, '01 Jan 1753') = '01 Jan 1753' UNION SELECT d1.venue_id ,ISNULL(d2.vdate,d1.vdate) ,'2' FROM dates AS d1 LEFT JOIN dates AS d2 ON DATEADD(d,1,d1.vdate) = d2.vdate WHERE CASE ISNULL(d2.vdate, '01 Jan 1753') WHEN '01 Jan 1753' THEN '2' ELSE '1' END = 2 ) res ) src PIVOT (MIN (rDate) FOR dType IN ( [1], [2] ) ) AS pvt 

Résultats:

 venue_id StartDate EndDate 1 2014-01-01 2014-01-01 3 2014-01-11 2014-01-15 4 2014-01-20 2014-01-22 4 2014-01-26 2014-01-30