UPgUT04d

1To check a date in one table does NOT exist in reservation period in 2nd table
2333, 2011-12-20, 66.00
3333, 2011-12-21, 66.00
4333, 2011-12-22, 66.00
5333, 2011-12-23, 66.00
6333, 2011-12-24, 66.00
7333, 2011-12-25, 66.00
8333, 2011-12-26, 66.00
9333, 2011-12-27, 66.00
10333, 2011-12-28, 66.00
11333, 2011-12-29, 66.00
12333, 2011-12-30, 66.00
13	
14333, 2011-12-20, 2011-12-22
15333, 2011-12-24, 2011-12-26
16333, 2011-12-28, 2011-12-30
17	
18333, 2011-12-23, 66.00
19333, 2011-12-27, 66.00
20333, 2011-12-31, 66.00
21	
22SELECT p1.* FROM dates_prices p1 
23WHERE (p1.id, p1.date) NOT IN
24(
25  SELECT p2.id, p2.date
26  FROM dates_prices p2
27  JOIN reservations r
28  ON  (
29     r.id = p2.id 
30     and r.startdate <= p2.date 
31     and p2.date <= r.enddate
32  )
33)
34	
35select dp.id, dp.date, dp.price
36  from dates_prices dp
37   left join 
38     (select dp.id, dp.date
39       from dates_prices dp
40       join reservations res 
41        on res.id = dp.id 
42         and dp.date between res.startdate and res.enddate) as inner_table
43     on inner_table.id = dp.id and inner_table.date = dp.date
44   where inner_table.id is null
45	
46SELECT p1.* FROM dates_prices p1 
47WHERE NOT EXISTS
48(
49  SELECT * FROM reservations r
50  WHERE r.id = p1.id 
51  AND r.startdate <= p1.date 
52  AND p1.date <= r.enddate  
53)
54	
55create table  table12 (id int, datefor date, price int)
56
57insert  into  table12
58select 333, '2011-12-20', 66.00
59union all
60select 333, '2011-12-21', 66.00
61union all
62 select 333, '2011-12-22', 66.00
63 union all
64 select 333, '2011-12-23', 66.00
65 union all
66select 333, '2011-12-24', 66.00
67union all
68 select 333, '2011-12-25', 66.00
69 union all
70 select 333, '2011-12-26', 66.00
71 union all
72 select 333, '2011-12-27', 66.00
73 union all
74 select 333, '2011-12-28', 66.00
75 union all
76 select 333, '2011-12-29', 66.00
77 union all
78 select 333, '2011-12-30', 66.00 
79
80 create table  table2 (id int, startdate date, enddate date)
81
82insert  into  table2
83select 333, '2011-12-20', '2011-12-22'
84 union all
85 select 333, '2011-12-24', '2011-12-26'
86 union all
87 select 333, '2011-12-28', '2011-12-30'
88	
89create proc solution( @id int) 
90  as
91  begin
92
93declare @count int
94    declare @i int
95    declare @query varchar(800)
96    set @query=''
97 declare @startdate varchar(80)
98    set @startdate=''
99     declare @enddate varchar(80)
100    set @enddate=''
101
102 declare @table table( row int ,startdate date,enddate date,id int)
103insert into @table select row_number() over(order by (select 1)) as rownumber  ,startdate,enddate,id  from  table2  where table2.id=@id
104 set  @count=(select COUNT (*)  from  @table )
105 set   @i=1
106 set @query += 'select * from  table12 where '
107while @count>=@i
108begin
109 set @startdate=( select startdate  from  @table  where row=@i) 
110 set  @enddate =(select enddate  from  @table  where row=@i)
111 set @query +=  ' datefor not between '+''''+@startdate+''''+ ' and '+'''' +@enddate+''''
112 if   @count>@i
113 begin
114 set @query +=' and  '
115 end
116 if @count=@i
117 begin
118 set @query += ' and  table12.id='+cast (@id as varchar(50)) +''
119 end 
120
121set @i+=1
122 end
123 if(@i=1)
124 set @query+='table12.id='+cast (@id as varchar(50)) +''
125
126
127  exec (@query)
128end
129
130exec solution 333