Good morning,
I got a list of days going 365 days that I generates from a query I've found on Jamie's excellent blog SSIS junkie which looks as follow:
datelist
with mycte as
(
select convert(smalldatetime, '14/06/2009',103) DateValue union all
select DateValue + 1
from mycte
where DateValue + 1 < '2010-06-14 00:00:00'
)
select DateValue
from mycte
OPTION (MAXRECURSION 0)
I use it in an OLE DB source component and it produces a one column datevalue starting 2009/06/14 all the way to 2010/06/14.
Then I got another query:
Hits
SELECT a.search_id,date_from,date_until,isnull(e.city_id,0)as city_id,a.hotel_id,isnull([hit-id],1)as [hit-id],isnull(available,0)as available
FROM Search.dbo.search_summary a
LEFT JOIN Search.dbo.hit_count b
ON (b.search_id=a.search_id and hotel_id=a.hotel_id and b.searchdate=@searchdate)
LEFT JOIN main.dbo.hotels e ON (e.hotel_id=a.hotel_id)
WHERE a.searchdate=@searchdate and a.hotel_id>0
I need to be able to inner join those 2 sources so that I get data as follow:
SELECT a.city_id,a.hotel_id,c.day_date,count(*)as hits,
count(CASE WHEN available=1 THEN [hit-id] ELSE null END)as hit_available
FROM Hits a
INNER JOIN datelist c ON (c.day_date>=a.date_from and c.day_date<a.date_until)
GROUP BY a.city_id,a.hotel_id,c.day_date
So basically, if someone book a room in a hotel for 5 days, I want to have a row for everyday in datelist which is between date_from and date_until.
That procedure exists already but I'm trying to switch it to SSIS because the aggregated tables will soon be sitting on a different server. So I want to grab raw data, aggregate on the fly and stick it into the data warehouse server.
Is there a way to do that in SSIS without having to write a huge query combining it all in an OLE DB source?
inner join date >= start_date and date <= end_date in SSIS
