Post reply

Long, complicated problem (SQL Svr 7)

  • August 18, 2003 at 4:07 pm

    #470340

    "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'

Viewing post 16 (of 15 total)

You must be logged in to reply to this topic. Login to reply