SQL Server 2000 query conversion

Question

I have a query written in SQL Server 2000 and I want to re-write that to use in SQL Server 2005 and newer versions.

CREATE TABLE #TEMP(AffinityGroupName VarChar(100), MostPopularVehicle VarChar(100), LeadCount Int, CNTL_ID int, CNTT_ID int, DayTime datetime, MemberFirstName varchar(50), 
 MemberLastName varchar(50), MemberAddress1 varchar(150), MemberAddress2 varchar(150),
 MemberCity varchar(50), MemberState varchar(2), MemberZip varchar(10), MemberPhone varchar(50), 
 MemberEmail varchar(200), LeadSource varchar(200), MemRqType varchar(200), MemberRequest varchar(200),
 VehicleYear varchar(20), VehicleMake varchar(200), VehicleModel varchar(200), StatusType int, 
 Status varchar (200), TempMake varchar(200), EmailMessage varchar(2000), Consultant varchar(200), 
 Office int, Region varchar(200), ReferredBy VarChar(200))

Select @Sql = 'INSERT INTO #TEMP
  select AffinityGroupName = NULL,
   MostPopularVehicle = NULL,
   LeadCount  = NULL,
   CNTL_ID   = C.CNTL_ID,
   CNTT_ID   = C.CNTL_Type,
   DayTime   = C.CNTL_DateTime,
   MemberFirstName  = C.CNTL_FirstName,
   MemberLastName  = C.CNTL_LastName,
   MemberAddress1  = C.CNTL_Address1,
   MemberAddress2  = C.CNTL_Address2,
   MemberCity  = C.CNTL_City,
   MemberState  = C.STAT_ID, 
   MemberZIP  = C.ZIPC_ID,
   MemberPhone  = C.CNTL_Phone,
   MemberEmail  = C.CNTL_Email,
   LeadSource  = null,
   MMemRqType  = T.CNTT_ID,
   MemberRequest  = T.CNTT_Description,
   VehicleYear  = null,
   VehicleMake  = null,
   VehicleModel  = null,
   StatusType  = C.CNTC_ID,
   Status   = SC.CNTC_Description,
   TempMake  = null,
   EmailMessage  = convert(varchar(200), C.CNTL_Message),
   Consultant  = C.USER_ConsultantID,
   Office   = U.CMEO_ID,
   Region   = null,
            ReferredBy      = ISNULL((SELECT TOP 1 WORK_CreditUnionEmployeeName FROM CARS.DBO.WORK_Worksheets [WORK], CARS.DBO.WORS_WorksheetsLeads WORS, CARS.DBO.LEAD_Leads LEAD WHERE [WORK].WORK_ID = WORS.WORK_ID AND WORS.LEAD_ID = LEAD.LEAD_ID AND LEAD.LEAD_OldCode = C.CNTL_ID), '''')

  FROM CNTL_ContactCenterLeads  C,
   CNTC_ContactCenterStatusCodes SC,
   CARS.DBO.EMPL_Employees  U,
   CNTT_ContactCenterleadTypes T
  WHERE C.CNTC_ID = SC.CNTC_ID
  and  C.USER_ConsultantID = U.USER_ID_Employee
  and C.CNTL_Type *= T.CNTT_ID
  and (C.CNTC_ID <> 7 or C.CNTC_ID <> 11 or C.CNTC_ID <> 100)
  AND C.CNTL_DateTime BETWEEN ''' + CONVERT(VarChar(50), @StartDate) + ''' AND ''' + CONVERT(VarChar(50), @EndDate) + ''' '

IF @AgrpID > 0 SELECT @Sql = @Sql + ' AND C.AGRP_ID = ' + CONVERT(VarChar(10), @AgrpID)

EXEC(@SQL)

Since this query has been written with sql server 2000 I need to upgrade it and use JOINs instead of = or *=. This is what I did:

INSERT INTO #TEMP
  SELECT
   AffinityGroupName = NULL,
   MostPopularVehicle = NULL,
   LeadCount  = NULL,
   CNTL_ID   = C.CNTL_ID,
   CNTT_ID   = C.CNTL_Type,
   DayTime   = C.CNTL_DateTime,
   MemberFirstName  = C.CNTL_FirstName,
   MemberLastName  = C.CNTL_LastName,
   MemberAddress1  = C.CNTL_Address1,
   MemberAddress2  = C.CNTL_Address2,
   MemberCity  = C.CNTL_City,
   MemberState  = C.STAT_ID, 
   MemberZIP  = C.ZIPC_ID,
   MemberPhone  = C.CNTL_Phone,
   MemberEmail  = C.CNTL_Email,
   LeadSource  = null,
   MMemRqType  = T.CNTT_ID,
   MemberRequest  = T.CNTT_Description,
   VehicleYear  = null,
   VehicleMake  = null,
   VehicleModel  = null,
   StatusType  = C.CNTC_ID,
   Status   = SC.CNTC_Description,
   TempMake  = null,
   EmailMessage  = convert(varchar(200), C.CNTL_Message),
   Consultant  = C.USER_ConsultantID,
   Office   = U.CMEO_ID,
   Region   = null,
            ReferredBy      = ISNULL((SELECT TOP 1 WORK_CreditUnionEmployeeName FROM CARS.DBO.WORK_Worksheets [WORK], CARS.DBO.WORS_WorksheetsLeads WORS, CARS.DBO.LEAD_Leads LEAD WHERE [WORK].WORK_ID = WORS.WORK_ID AND WORS.LEAD_ID = LEAD.LEAD_ID AND LEAD.LEAD_OldCode = C.CNTL_ID), '''')

  FROM COM.DBO.CNTL_ContactCenterLeads  C
  LEFT JOIN COM.DBO.CNTC_ContactCenterStatusCodes SC ON C.CNTC_ID = SC.CNTC_ID
  LEFT JOIN CARS.DBO.EMPL_Employees  U ON C.USER_ConsultantID = U.USER_ID_Employee
  LEFT OUTER JOIN COM.DBO.CNTT_ContactCenterleadTypes T ON C.CNTL_Type = T.CNTT_ID
  WHERE  (C.CNTC_ID <> 7 or C.CNTC_ID <> 11 or C.CNTC_ID <> 100)
  AND C.CNTL_DateTime BETWEEN '' + CONVERT(VarChar(50), @StartDate) + '' AND '' + CONVERT(VarChar(50), @EndDate) + ''

  IF @AgrpID > 0 SELECT @Sql = @Sql + ' AND C.AGRP_ID = ' + CONVERT(VarChar(10), @AgrpID)

But the problem is I dont know what is the last line does:

IF @AgrpID > 0 SELECT @Sql = @Sql + ' AND C.AGRP_ID = ' + CONVERT(VarChar(10), @AgrpID)

and if I want to change this line and keep the logic, how can I do that?

Answer 1

This is what you want. I am assumming that you have correctly refactored the JOINs. Look at the very last line to see how the IF statement logic has been incorporated into your query. The second last line dispenses with converting dates. There is also an edit in the subquery for "ReferredBy" to get rid of the doubled-up quotes and to modernize the JOINs.

INSERT  INTO #TEMP
        SELECT
          AffinityGroupName = NULL,
          MostPopularVehicle = NULL,
          LeadCount = NULL,
          CNTL_ID = C.CNTL_ID,
          CNTT_ID = C.CNTL_Type,
          DayTime = C.CNTL_DateTime,
          MemberFirstName = C.CNTL_FirstName,
          MemberLastName = C.CNTL_LastName,
          MemberAddress1 = C.CNTL_Address1,
          MemberAddress2 = C.CNTL_Address2,
          MemberCity = C.CNTL_City,
          MemberState = C.STAT_ID,
          MemberZIP = C.ZIPC_ID,
          MemberPhone = C.CNTL_Phone,
          MemberEmail = C.CNTL_Email,
          LeadSource = NULL,
          MMemRqType = T.CNTT_ID,
          MemberRequest = T.CNTT_Description,
          VehicleYear = NULL,
          VehicleMake = NULL,
          VehicleModel = NULL,
          StatusType = C.CNTC_ID,
          Status = SC.CNTC_Description,
          TempMake = NULL,
          EmailMessage = CONVERT(VARCHAR(200), C.CNTL_Message),
          Consultant = C.USER_ConsultantID,
          Office = U.CMEO_ID,
          Region = NULL,
          ReferredBy = ISNULL((
                   SELECT TOP 1
                     WORK_CreditUnionEmployeeName
                   FROM
                     CARS.DBO.WORK_Worksheets [WORK]
                     JOIN CARS.DBO.WORS_WorksheetsLeads WORS
                       ON [WORK].WORK_ID = WORS.WORK_ID
                     JOIN CARS.DBO.LEAD_Leads LEAD
                       ON WORS.LEAD_ID = LEAD.LEAD_ID
                         AND LEAD.LEAD_OldCode = C.CNTL_ID
                  ), '')
        FROM
          COM.DBO.CNTL_ContactCenterLeads C
          LEFT JOIN COM.DBO.CNTC_ContactCenterStatusCodes SC
            ON C.CNTC_ID = SC.CNTC_ID
          LEFT JOIN CARS.DBO.EMPL_Employees U
            ON C.USER_ConsultantID = U.USER_ID_Employee
          LEFT OUTER JOIN COM.DBO.CNTT_ContactCenterleadTypes T
            ON C.CNTL_Type = T.CNTT_ID
        WHERE
          (
            C.CNTC_ID <> 7
            OR C.CNTC_ID <> 11
            OR C.CNTC_ID <> 100
          )
          AND C.CNTL_DateTime BETWEEN @StartDate AND @EndDate
          AND C.AGRP_ID = CASE WHEN @AgrpID > 0 THEN @AgrpID ELSE C.AGRP_ID END