INSERT INTO DBO.Contract

(

ContractID,

3rdPartyFK,

CustomerFK,

OfferID,

Revision,

SoldDate,

StartDate,

EndDate,

SalesPerson,

SalesManager

)

SELECT

CASE

WHEN COUNT(DISTINCT(tms.Contract_ID)) = '1' THEN MAX(tms.Contract_ID)

ELSE '-1'

END AS Contract_ID,

MIN(pb.ParticipantPK) AS ParticipantPK,

CASE

WHEN COUNT(DISTINCT(tms.CustomerID)) = '1' THEN MAX(pc.ParticipantPK)

ELSE '-1'

END AS CustomerID,

tms.OfferID,

tms.Revision,

MAX(tms.DateSold) AS DateSold,

MAX(tms.StartDate) AS StartDate,

MAX(tms.EndDate) AS EndDate,

MAX(tms.Sold_By) AS Sold_By,

MAX(tms.salesmanager) AS Salesmanager,

FROM #TmsContract TMS

LEFT JOIN dbo.Participant PB

ON tms.3rdPartyID = pb.TmsFK

AND pb.ParticipantType = '3rdParty'

LEFT JOIN dbo.Participant PC

ON tms.PeCustomerID = pc.TmsFK

AND pc.ParticipantType = 'Customer'

LEFT JOIN dbo.Contract C

ON tms.OfferID = C.OfferID

AND tms.Revision = C.Revision

WHERE

c.OfferID IS NULL

Group by

tms.OfferID,

tms.Revision

ORDER BY

tms.OfferID