Sachin 80451 (1/11/2012) - via Private Message

---

does this help:

CREATE TABLE dbo.account

( ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

accountid Varchar(50),

name varchar(50),

Tpl_Traderred INT,

ownerid uniqueidentifier

)

CREATE TABLE dbo.contact

( ownerid uniqueidentifier,

Owneridname nvarchar(160)

)

CREATE TABLE dbo.zoneGWP

( tpl_companyid uniqueidentifier,

TPL_Month nvarchar(6),

TPL_NBGWP money,

TPL_RenewalGWP Money,

TPL_TotalGWP Money,

TPL_Zone Nvarchar(100)

)

INSERT INTO dbo.account (AccountID, name, tpl_traderred, ownerid)

SELECT '87b21301-e0c8-459b-b8f7-735222e87cd9','Capital Insurance Brokers (CI) Ltd','576574','b92e0266-5e8e-df11-83f6-0022194f7115' UNION ALL

SELECT '7c27e7e3-f0eb-4b2d-ade8-73bda850b766','Swinton Group Ltd (218)','233456','3a03a65e-5f8e-df11-83f6-0022194f7115' UNION ALL

SELECT '32c74a77-38da-473a-a3fc-740a35450317','Towergate Risk Solutions Berwick Upon Tweed','563224','b92e0266-5e8e-df11-83f6-0022194f7115' UNION ALL

SELECT '0ecc5258-9f64-4637-a3a9-7415d3c3abb9','Swinton Group Ltd (646)','986778','d61f3458-5f8e-df11-83f6-0022194f7115' UNION ALL

SELECT '817a5592-cb6b-4d89-9321-74259bcc35cd','G A Puttick (Insurance)','677322','3a03a65e-5f8e-df11-83f6-0022194f7115' UNION ALL

SELECT 'acdbf2c1-a79a-4eb7-a8bb-742b6a265e72','Box Insurance Services and Newton General Insurance Services','564644','a66099f6-5e8e-df11-83f6-0022194f7115' UNION ALL

SELECT '0762dd07-c48e-43ac-a319-747834600013','Swinton Group Ltd (724)','932578','a54f9c46-5f8e-df11-83f6-0022194f7115' UNION ALL

SELECT '56f63932-89fb-4d82-b447-747d1836446b','Farmer Insurance Agency Ltd','897778','b92e0266-5e8e-df11-83f6-0022194f7115' UNION ALL

SELECT '03b7dfbb-28c3-4c84-86bd-74a9dc839789','Blackfriars Insurance Brokers Ltd','122223','d61f3458-5f8e-df11-83f6-0022194f7115' UNION ALL

SELECT '5acc7b0c-9abb-4c54-9ef9-74bbfa862811','Brunsdon LLP','253463','a66099f6-5e8e-df11-83f6-0022194f7115' UNION ALL

SELECT 'aa92b72d-14d5-4057-b334-74d887729f7c','Swinton Group Ltd (573)','797979','a54f9c46-5f8e-df11-83f6-0022194f7115' UNION ALL

SELECT '5665a424-3788-4b90-936d-7518ae849e31','Caleb Roberts Insurance Services Ltd','334543','3a03a65e-5f8e-df11-83f6-0022194f7115' UNION ALL

SELECT '13608d88-1753-48b9-9dba-7536dc5f3d53','Peter Best Insurances Services Ltd','776543','a66099f6-5e8e-df11-83f6-0022194f7115' UNION ALL

SELECT '0c24414a-23e5-4e07-a054-755cc806fa4a','Barnett Jones and Cooke Ltd','334647','d61f3458-5f8e-df11-83f6-0022194f7115'

INSERT INTO dbo.contact (ownerID, owneridname)

SELECT 'a66099f6-5e8e-df11-83f6-0022194f7115','Lisa Walton' UNION ALL

SELECT 'd61f3458-5f8e-df11-83f6-0022194f7115','Wendy Crisp' UNION ALL

SELECT '3a03a65e-5f8e-df11-83f6-0022194f7115','Amy Stapenhill' UNION ALL

SELECT 'a54f9c46-5f8e-df11-83f6-0022194f7115', 'Victoria Martin' UNION ALL

SELECT 'b92e0266-5e8e-df11-83f6-0022194f7115', 'Fiona Auge'

INSERT INTO dbo.zoneGWP (Tpl_companyID, TPL_Month, TPL_NBGWP,TPL_RenewalGWP, TPL_TotalGWP,TPL_Zone)

SELECT '87b21301-e0c8-459b-b8f7-735222e87cd9','201201','354','275','650','AIUA' UNION ALL

SELECT '7c27e7e3-f0eb-4b2d-ade8-73bda850b766','201201','453','383','850','TCU' UNION ALL

SELECT '32c74a77-38da-473a-a3fc-740a35450317','201201','3346','2386','6500','TCU' UNION ALL

SELECT '0ecc5258-9f64-4637-a3a9-7415d3c3abb9','201201','3235','2285','6500','AIUA' UNION ALL

SELECT '817a5592-cb6b-4d89-9321-74259bcc35cd','201201','9732','8782','18000','AIUA' UNION ALL

SELECT 'acdbf2c1-a79a-4eb7-a8bb-742b6a265e72','201201','8834','7884','17000','TCU' UNION ALL

SELECT '0762dd07-c48e-43ac-a319-747834600013','201201','9762','8872','1950','AIUA' UNION ALL

SELECT '56f63932-89fb-4d82-b447-747d1836446b','201201','973','893','1700','TUE' UNION ALL

SELECT '03b7dfbb-28c3-4c84-86bd-74a9dc839789','201201','9783','8883','18500','TCU' UNION ALL

SELECT '5acc7b0c-9abb-4c54-9ef9-74bbfa862811','201201','3757','3757','7000','AIUA' UNION ALL

SELECT 'aa92b72d-14d5-4057-b334-74d887729f7c','201201','463','463','900','TUE' UNION ALL

SELECT '5665a424-3788-4b90-936d-7518ae849e31','201201','9768','9868','19500','AIUA' UNION ALL

SELECT '13608d88-1753-48b9-9dba-7536dc5f3d53','201201','343','343','700','TCU' UNION ALL

SELECT '0c24414a-23e5-4e07-a054-755cc806fa4a','201201','864','864','1900','TUE' UNION ALL

SELECT '87b21301-e0c8-459b-b8f7-735222e87cd9','201101','354','275','550','AIUA' UNION ALL

SELECT '7c27e7e3-f0eb-4b2d-ade8-73bda850b766','201101','453','383','750','TCU' UNION ALL

SELECT '32c74a77-38da-473a-a3fc-740a35450317','201101','4346','2386','5500','TCU' UNION ALL

SELECT '0ecc5258-9f64-4637-a3a9-7415d3c3abb9','201101','3235','2285','5500','AIUA' UNION ALL

SELECT '817a5592-cb6b-4d89-9321-74259bcc35cd','201101','8732','8782','17000','AIUA' UNION ALL

SELECT 'acdbf2c1-a79a-4eb7-a8bb-742b6a265e72','201101','7834','7884','16000','TCU' UNION ALL

SELECT '0762dd07-c48e-43ac-a319-747834600013','201101','8762','8872','1850','AIUA' UNION ALL

SELECT '56f63932-89fb-4d82-b447-747d1836446b','201101','873','893','1600','TUE' UNION ALL

SELECT '03b7dfbb-28c3-4c84-86bd-74a9dc839789','201101','8783','8883','17500','TCU' UNION ALL

SELECT '5acc7b0c-9abb-4c54-9ef9-74bbfa862811','201101','2757','3757','6000','AIUA' UNION ALL

SELECT 'aa92b72d-14d5-4057-b334-74d887729f7c','201101','363','463','800','TUE' UNION ALL

SELECT '5665a424-3788-4b90-936d-7518ae849e31','201101','9768','9868','19000','AIUA' UNION ALL

SELECT '13608d88-1753-48b9-9dba-7536dc5f3d53','201101','243','343','600','TCU' UNION ALL

SELECT '0c24414a-23e5-4e07-a054-755cc806fa4a','201101','764','864','1800','TUE'

expected results:

BSC - Fiona Ague

CompanyName - Capital Insurance Brokers (CI) Ltd

TotalGWP2012 - 650

TotalGWP2011 - 550

TraderRef - 576574