"Is there any reason you need to check each company_id separately?"
Actually, I will be checking their company_name instead, as there's no direct link at the company level between the CRM & WEB systems! (It is possible to have products in one company in CRM, but another in WEB!! A mess, really.)
About the creation of the view, can anyone help me try to combine the "additional" products into one?
if object_ID('tempdb..#CRM_table') is not null drop table #CRM_table
CREATE TABLE #CRM_table (company_id INT, row_id INT, prod_type varchar(50), number_of_ad INT, end_date datetime, related_row_id INT NULL)
INSERT INTO #CRM_table Values (001, 5001, 'Single Advertiser', 5, '2003-12-31', NULL)
INSERT INTO #CRM_table Values (001, 5002, 'Additional Advertiser', 10, '2003-12-31', 5001)
INSERT INTO #CRM_table Values (002, 5003, 'Single Advertiser', 10, '2004-03-31', NULL)
INSERT INTO #CRM_table Values (003, 5004, 'Single Advertiser', 8, '2003-11-30', NULL)
INSERT INTO #CRM_table Values (001, 5005, 'Additional Advertiser', 5, '2003-12-31', 5001)
The CRM_view should be like:
001, 5001, 'Premium Advertiser', 20, '2003-12-31' (combined 3 into 1)
002, 5003, 'Single Advertiser', 10, '2004-03-31'
003, 5004, 'Single Advertiser', 8, '2003-11-30'