USE [Goldbook23]
GO
/****** Object:  StoredProcedure [dbo].[GetLatestListingUpdates]    Script Date: 12/23/2011 13:29:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[GetLatestListingUpdates]
  
AS

IF OBJECT_ID('tempdb..#tempListings') IS NOT NULL DROP TABLE #tempListings
create table #tempListings(
	BusinessID int not null, 
	Name nvarchar(256) not null, 
	CreateDate datetime, 
	IsMain bit not null, 
	StreetAddress varchar(256) not null, 
	City varchar(256) not null, 
	PostalCode varchar(10), 
	Province varchar(20),
	ProvinceCode varchar(5), 
	ActionDescription varchar(50),
	[Status] varchar(10) not null,
	UpdateDate datetime) 

	declare @c int=0
	declare  @date datetime = getdate()
	declare @found bit=0
while @found = 0  --repeat the loop untill at least 10 listings have been found
	begin 
            insert into #tempListings 
			select a.BusinessID, a.Name, a.CreateDate, b.IsMain, c.StreetAddress, d.Name as City, c.PostalCode,
			f.Name as Province, f.Code as PovinceCode, h.Name as 'ActionDescription', 
			Case when h.Name like '%update%' Then 'Update' 
			     when h.Name like '%added%' Then 'Update' 
			     when h.Name like '%deleted%' Then 'Update'
			     when h.Name like '%created%' Then 'Added'  
			     else 'Added' 
			End as [Status], 
			g.UpdateDate 
			from gb_business.Business a left join gb_business.BusinessLocation b on a.BusinessID = b.BusinessID
			left join gb_location.Address c on c.AddressID = b.AddressID
			left join gb_location.City d on c.CityID = d.CityID 
			left join gb_location.Region e on d.RegionID= e.RegionID
			left join gb_location.Province f on f.ProvinceID=e.ProvinceID
			left join gb_business.ActionLog g on g.ActionEntityPrimaryKeyID=a.BusinessID
			left Join gb_business.ActionType h on h.ActionTypeID = g.ActionTypeID
			where c.CityID is not Null -- must have value for City
			and c.PostalCode is not Null and c.PostalCode <>'' and c.PostalCode <>' ' -- must have value for PostalCode
			and c.StreetAddress<>'' and c.StreetAddress<>' ' -- must have value for StreetAddress
			and g.ActionEntityID=1 -- changes to Business entity
			and a.ShowAddress=1 --address visibility indicator - True
			and (h.Name like '%deal%' or h.Name like '%name%' or h.Name like '%address%' or h.Name like '%photo%' 
			  or h.Name like '%video%' or h.Name like '%created customer profile%') --must have changes to at least one of the following attributes
			and (g.UpdateDate > @date or a.CreateDate > @date); -- filtering the most recent updates starting from today
			--order by g.UpdateDate desc, a.Name; 
			set @c=@@rowcount; 
		    /*if the result set returned is less than 10 records, include one more day in the search criteria*/
			if @c<10 
		      begin		
			   set @date = @date -1 
			   delete from #tempListings
			  end
			else 
			  begin
			   select * from #tempListings
		       set @found=1;
	          end
end;

 insert into #tempListings 
select BusinessID, Name, CreateDate, IsMain, StreetAddress, City, PostalCode,
Province, PovinceCode, ActionDescription,[Status],UpdateDate
from
(
select *,dense_rank() over (order by g.UpdateDate desc, a.Name) as rnk
from
(
			select count(1) over () as rwcnt,
                        a.BusinessID, a.Name, a.CreateDate, b.IsMain, c.StreetAddress, d.Name as City, c.PostalCode,
			f.Name as Province, f.Code as PovinceCode, h.Name as 'ActionDescription', 
			Case when h.Name like '%update%' Then 'Update' 
			     when h.Name like '%added%' Then 'Update' 
			     when h.Name like '%deleted%' Then 'Update'
			     when h.Name like '%created%' Then 'Added'  
			     else 'Added' 
			End as [Status], 
			g.UpdateDate 
			from gb_business.Business a left join gb_business.BusinessLocation b on a.BusinessID = b.BusinessID
			left join gb_location.Address c on c.AddressID = b.AddressID
			left join gb_location.City d on c.CityID = d.CityID 
			left join gb_location.Region e on d.RegionID= e.RegionID
			left join gb_location.Province f on f.ProvinceID=e.ProvinceID
			left join gb_business.ActionLog g on g.ActionEntityPrimaryKeyID=a.BusinessID
			left Join gb_business.ActionType h on h.ActionTypeID = g.ActionTypeID
			where c.CityID is not Null -- must have value for City
			and c.PostalCode is not Null and c.PostalCode <>'' and c.PostalCode <>' ' -- must have value for PostalCode
			and c.StreetAddress<>'' and c.StreetAddress<>' ' -- must have value for StreetAddress
			and g.ActionEntityID=1 -- changes to Business entity
			and a.ShowAddress=1 --address visibility indicator - True
			and (h.Name like '%deal%' or h.Name like '%name%' or h.Name like '%address%' or h.Name like '%photo%' 
			  or h.Name like '%video%' or h.Name like '%created customer profile%') --must have changes to at least one of the following attributes
			and (g.UpdateDate > @date or a.CreateDate > @date); -- filtering the most recent updates starting from today
)t
where rwcnt >10
)r
where rnk=1