Query slow. Help improve?

• SQLStud 74876

  SSC Veteran

  Points: 277

  More actions

  October 12, 2018 at 5:08 pm

  #408921

  Hey all.  I have a contract building a database for a company that bills other companies, and I wrote what I thought was an awesome query.  It runs a bit slow and I have time to get coffee when it runs.  Any help would be great.  I'm pretty good with SQL, so no newbs please.

  
USE studly
GO

  DECLARE    @ID_Number int
DECLARE @Last_name char(25)
DECLARE @First_name char(20)
DECLARE @MI Char(2)
DECLARE @Address1 char(40)
DECLARE @Address2 char(10)
DECLARE @City char(20)
DECLARE @State char(2)
DECLARE @Zip char(5)
DECLARE @Home_phone char(10)
DECLARE @Gender char(1)
DECLARE @Marital_Status char(1)
DECLARE @DOB char(8)
DECLARE @Hire char(8)
DECLARE @Job_class char(10)
DECLARE @PayrollHomeOrg char(10)
DECLARE @Appt_end char(8)
DECLARE @Employ_status char(1)
DECLARE @Status_effect char(8)
DECLARE @Benefit_eligible char(1)
DECLARE @change_reason varchar(200)
DECLARE @gp_pd_status varchar(1)
DECLARE @inZip int
DECLARE @rateID int
DECLARE @life money
DECLARE @vision money
DECLARE @medhmo money
DECLARE @medppo money
DECLARE @denhmo money
DECLARE @univMedPrem money
DECLARE @univDentPrem money
DECLARE @univVisPrem money
DECLARE @univLifePrem money
DECLARE @univContribution money
DECLARE @intMonth int
DECLARE @intJCMonth int
DECLARE @intThisMonth int

  DECLARE @1a smalldatetime = '12/16/2018'
DECLARE @1b smalldatetime = '1/20/2019'
DECLARE @1c smalldatetime = '1/1/2019'
DECLARE @2a smalldatetime
DECLARE @2b smalldatetime
DECLARE @2c smalldatetime = '2/1/2018'
DECLARE @3a smalldatetime
DECLARE @3b smalldatetime
DECLARE @3c smalldatetime = '3/1/2018'
DECLARE @4a smalldatetime = '3/18/2018'
DECLARE @4b smalldatetime = '4/21/2018'
DECLARE @4c smalldatetime = '4/1/2018'
DECLARE @5a smalldatetime = '4/22/2018'
DECLARE @5b smalldatetime = '5/19/2018'
DECLARE @5c smalldatetime = '5/1/2018'
DECLARE @6a smalldatetime = '5/20/2018'
DECLARE @6b smalldatetime = '6/16/2018'
DECLARE @6c smalldatetime = '6/1/2018'
DECLARE @7a smalldatetime = '6/17/2018'
DECLARE @7b smalldatetime = '7/21/2018'
DECLARE @7c smalldatetime = '7/1/2018'
DECLARE @8a smalldatetime = '7/22/2018'
DECLARE @8b smalldatetime = '8/18/2018'
DECLARE @8c smalldatetime = '8/1/2018'
DECLARE @9a smalldatetime = '8/19/2018'
DECLARE @9b smalldatetime = '9/22/2018'
DECLARE @9c smalldatetime = '9/1/2018'
DECLARE @10a smalldatetime = '9/23/2018'
DECLARE @10b smalldatetime = '10/20/2018'
DECLARE @10c smalldatetime = '10/1/2018'
DECLARE @11a smalldatetime = '10/21/2018'
DECLARE @11b smalldatetime = '11/17/2018'
DECLARE @11c smalldatetime = '11/1/2018'
DECLARE @12a smalldatetime = '11/13/2018'
DECLARE @12B smalldatetime = '12/10/2018'
DECLARE @12c smalldatetime = '12/1/2018'

  SET @rateID = 9

  --Check for any record that has a change reason
--Move copy of records with change reason to history table then clear change reason value in UnivEnrollMain08
DECLARE univ_clear_change_reason CURSOR FOR
SELECT ID_Number, change_reason FROM studly.univEnrollMain08 WHERE len(isnull(change_reason,'')) > 0

  OPEN univ_clear_change_reason

  FETCH NEXT FROM univ_clear_change_reason
INTO @ID_Number, @change_reason

  -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE (@@FETCH_STATUS = 0)
    BEGIN
        INSERT INTO studly.univEnrollMainHistory08
        SELECT *, getDate() FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number

          UPDATE studly.univEnrollMain08 SET change_reason = '' WHERE ID_Number = @ID_Number
        print 'Cleared change reason for ' + convert(varchar,@ID_Number)    
        -- This is executed as long as the previous fetch succeeds.
        FETCH NEXT FROM univ_clear_change_reason
        INTO @ID_Number, @change_reason
    END

  CLOSE univ_clear_change_reason
DEALLOCATE univ_clear_change_reason

  DECLARE univ_rates CURSOR FOR
SELECT [life], [vision], [medhmo], [medppo], [denhmo] FROM [studly].[univRates] WHERE rateID = @rateID

  OPEN univ_rates

  FETCH NEXT FROM univ_rates
INTO @life, @vision, @medhmo, @medppo, @denhmo

  --Check import file to see if record currently exists
--If exists then update record and provide change reasons
--If not exists then add to database
DECLARE univ_import CURSOR FOR
SELECT ID_Number, Last_name, First_name, MI, Address1, Address2, City, State, Zip, Home_phone, Gender, Marital_Status, DOB, Hire, Job_class, PayrollHomeOrg, Appt_end, Employ_status, Status_effect, Benefit_eligible FROM studly.univEnrollMainImport

  OPEN univ_import

  FETCH NEXT FROM univ_import
INTO @ID_Number, @Last_name, @First_name, @MI, @Address1, @Address2, @City, @State, @Zip, @Home_phone, @Gender, @Marital_Status, @DOB, @Hire, @Job_class, @PayrollHomeOrg, @Appt_end, @Employ_status, @Status_effect, @Benefit_eligible

  -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE (@@FETCH_STATUS = 0)
    BEGIN
        IF (SELECT ID_Number FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number) = @ID_Number
        BEGIN
            Set @change_reason = ''
        
            IF (SELECT Last_name FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number) <> @Last_Name
            BEGIN
                set @change_reason = @change_reason + 'last name different, '
            END
            IF (SELECT Job_class FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number) <> @Job_class
            BEGIN

                  set @change_reason = @change_reason + 'job class different, '
            END
            IF (SELECT Employ_status FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number) <> @Employ_status
            BEGIN

                  set @change_reason = @change_reason + 'employ status different, '
            END
            IF (SELECT Status_effect FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number) <> @Status_effect
            BEGIN

                  set @change_reason = @change_reason + 'status effect different, '
            END
            IF (SELECT Benefit_eligible FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number) <> @Benefit_eligible
            BEGIN

                  set @change_reason = @change_reason + 'Benefit eligible different, '
            END
            IF (SELECT Address1 FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number) <> @Address1
            BEGIN

                  set @change_reason = @change_reason + 'address different'
            END

              IF @change_reason <> ''
            BEGIN
                --Check to see if record has already been approved prior to weekly import
                IF (SELECT gp_pd_status FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number) = 'a'
                BEGIN
                    SET @gp_pd_status = 'd'
                END
                ELSE
                BEGIN
                    SET @gp_pd_status = 'c'
                END

                  INSERT INTO studly.univEnrollMainHistory08
                SELECT *, getDate() FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number

                  UPDATE studly.univEnrollMain08 SET Last_name = @Last_Name, First_name = @First_name, MI = @MI, Address1 = @Address1, Address2 = @Address2, City = @City, State = @State, Zip = @Zip, Home_phone = @Home_phone, Gender = @Gender, Marital_Status = @Marital_Status, DOB = @DOB, Hire = @Hire, Job_class = @Job_class, PayrollHomeOrg = @PayrollHomeOrg, Appt_end = @Appt_end, Employ_status = @Employ_status, Status_effect = @Status_effect, Benefit_eligible = @Benefit_eligible, gp_pd_status = @gp_pd_status, change_reason = @change_reason WHERE ID_Number = @ID_Number
                print @change_reason

              END

              ELSE
            BEGIN
                
                UPDATE studly.univEnrollMain08 SET Last_name = @Last_Name, First_name = @First_name, MI = @MI, Address1 = @Address1, Address2 = @Address2, City = @City, State = @State, Zip = @Zip, Home_phone = @Home_phone, Gender = @Gender, Marital_Status = @Marital_Status, DOB = @DOB, Hire = @Hire, Job_class = @Job_class, PayrollHomeOrg = @PayrollHomeOrg, Appt_end = @Appt_end, Employ_status = @Employ_status, Status_effect = @Status_effect, Benefit_eligible = @Benefit_eligible WHERE ID_Number = @ID_Number
                print getDate()

              END
                        
--CONTINUE

          END
        --BREAK
        ELSE
            
            
         --IF (1=1)
         BEGIN
            SET @inZip = 0
            SET @intMonth = 0
            SET @intJCMonth = 0
            SET @univMedPrem = 0
            SET @univDentPrem = 0
            SET @univVisPrem = 0
            SET @univLifePrem = 0
            SET @univContribution = 0
            SET @intThisMonth = month(getDate())

              

              IF (SELECT Zip FROM studly.univZips10 WHERE Zip = (SELECT Zip FROM studly.univEnrollMainImport WHERE ID_Number = @ID_Number)) = (SELECT Zip FROM studly.univEnrollMainImport WHERE ID_Number = @ID_Number)
            BEGIN
                SET @inZip = 1
            END

              --Determine Job Code Month
            --January
            --12/16/2018 - 1/20/2019
            IF CONVERT(smalldatetime,@Job_class,110) >= @1a AND CONVERT(smalldatetime,@Job_class,110) <= @1b
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @1a AND CONVERT(smalldatetime,@Job_class,110) <= @1c
                BEGIN
                    SET @intJCMonth = 1
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 2
                END
            END
            --April
            --3/18/2018 - 4/21/2018
            IF CONVERT(smalldatetime,@Job_class,110) >= @4a AND CONVERT(smalldatetime,@Job_class,110) <= @4b
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @4a AND CONVERT(smalldatetime,@Job_class,110) <= @4c
                BEGIN
                    SET @intJCMonth = 4
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 5
                END
            END
            --May
            --4/22/2018 - 5/19/2018
            IF CONVERT(smalldatetime,@Job_class,110) >= @5a AND CONVERT(smalldatetime,@Job_class,110) <= @5b
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @5a AND CONVERT(smalldatetime,@Job_class,110) <= @5c
                BEGIN
                    SET @intJCMonth = 5
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 6
                END
            END
            --June
            --5/20/2018 - 6/16/2018
            IF CONVERT(smalldatetime,@Job_class,110) >= @6a AND CONVERT(smalldatetime,@Job_class,110) <= @6b
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @6a AND CONVERT(smalldatetime,@Job_class,110) <= @6c
                BEGIN
                    SET @intJCMonth = 6
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 7
                END
            END
            --July
            --6/17/2018 - 7/21/2018
            IF CONVERT(smalldatetime,@Job_class,110) >= @7a AND CONVERT(smalldatetime,@Job_class,110) <= @7b
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @7a AND CONVERT(smalldatetime,@Job_class,110) <= @7c
                BEGIN
                    SET @intJCMonth = 7
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 8
                END
            END
            --Aug
            --7/22/2018 - 8/18/2018
            IF CONVERT(smalldatetime,@Job_class,110) >= @8a AND CONVERT(smalldatetime,@Job_class,110) <= @8b
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @8a AND CONVERT(smalldatetime,@Job_class,110) <= @8c
                BEGIN
                    SET @intJCMonth = 8
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 9
                END
            END
            --Sep
            --8/19/2018 - 9/22/2018
            IF CONVERT(smalldatetime,@Job_class,110) >= @9a AND CONVERT(smalldatetime,@Job_class,110) <= @9b
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @9a AND CONVERT(smalldatetime,@Job_class,110) <= @9c
                BEGIN
                    SET @intJCMonth = 9
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 10
                END
            END
            --Oct
            --9/23/2018 - 10/20/2018
            IF CONVERT(smalldatetime,@Job_class,110) >= @10a AND CONVERT(smalldatetime,@Job_class,110) <= @10b
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @10a AND CONVERT(smalldatetime,@Job_class,110) <= @10c
                BEGIN
                    SET @intJCMonth = 10
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 11
                END
            END
            --Nov
            --10/21/2018 - 11/17/2018
            IF CONVERT(smalldatetime,@Job_class,110) >= @11a AND CONVERT(smalldatetime,@Job_class,110) <= @11b
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @11a AND CONVERT(smalldatetime,@Job_class,110) <= @11C
                BEGIN
                    SET @intJCMonth = 11
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 12
                END
            END
            --Dec
            --11/18/2018 - 12/8/2018
            IF CONVERT(smalldatetime,@Job_class,110) >= @12a AND CONVERT(smalldatetime,@Job_class,110) <= @12B
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @12a AND CONVERT(smalldatetime,@Job_class,110) <= @12c
                BEGIN
                    SET @intJCMonth = 12
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 1
                END
            END

              --Determine Our Current Month
            --April
            --4/17/2018 - 5/21/2018
            IF CONVERT(smalldatetime,getDate(),110) >= @4a AND CONVERT(smalldatetime,getDate(),110) <= @4b
            BEGIN
                IF CONVERT(smalldatetime,getDate(),110) >= @4a AND CONVERT(smalldatetime,getDate(),110) <= @4c
                BEGIN
                    SET @intMonth = 4
                END
                ELSE
                BEGIN
                    SET @intMonth = 5
                END
            END
            --May
            --4/17/2018 - 5/21/2018
            IF CONVERT(smalldatetime,getDate(),110) >= @5a AND CONVERT(smalldatetime,getDate(),110) <= @5b
            BEGIN
                IF CONVERT(smalldatetime,getDate(),110) >= @5a AND CONVERT(smalldatetime,getDate(),110) <= @5c
                BEGIN
                    SET @intMonth = 5
                END
                ELSE
                BEGIN
                    SET @intMonth = 6
                END
            END
            --June
            --5/22/2018 - 6/18/2018
            IF CONVERT(smalldatetime,getDate(),110) >= @6a AND CONVERT(smalldatetime,getDate(),110) <= @6b
            BEGIN
                IF CONVERT(smalldatetime,getDate(),110) >= @6a AND CONVERT(smalldatetime,getDate(),110) <= @6c
                BEGIN
                    SET @intMonth = 6
                END
                ELSE
                BEGIN
                    SET @intMonth = 7
                END
            END
            --July
            --6/19/2018 - 7/23/2018
            IF CONVERT(smalldatetime,getDate(),110) >= @7a AND CONVERT(smalldatetime,getDate(),110) <= @7b
            BEGIN
                IF CONVERT(smalldatetime,getDate(),110) >= @7a AND CONVERT(smalldatetime,getDate(),110) <= @7c
                BEGIN
                    SET @intMonth = 7
                END
                ELSE
                BEGIN
                    SET @intMonth = 8
                END
            END
            --Aug
            --7/24/2018 - 8/20/2018
            IF CONVERT(smalldatetime,getDate(),110) >= @8a AND CONVERT(smalldatetime,getDate(),110) <= @8b
            BEGIN
                IF CONVERT(smalldatetime,getDate(),110) >= @8a AND CONVERT(smalldatetime,getDate(),110) <= @8c
                BEGIN
                    SET @intMonth = 8
                END
                ELSE
                BEGIN
                    SET @intMonth = 9
                END
            END
            --Sep
            --8/21/2018 - 9/17/2018
            IF CONVERT(smalldatetime,getDate(),110) >= @9a AND CONVERT(smalldatetime,getDate(),110) <= @9b
            BEGIN
                IF CONVERT(smalldatetime,getDate(),110) >= @9a AND CONVERT(smalldatetime,getDate(),110) <= @9c
                BEGIN
                    SET @intMonth = 9
                END
                ELSE
                BEGIN
                    SET @intMonth = 10
                END
            END
            --Oct
            --9/18/2018 - 10/22/2018
            IF CONVERT(smalldatetime,getDate(),110) >= @10a AND CONVERT(smalldatetime,getDate(),110) <= @10b
            BEGIN
                IF CONVERT(smalldatetime,getDate(),110) >= @10a AND CONVERT(smalldatetime,getDate(),110) <= @10c
                BEGIN
                    SET @intMonth = 10
                END
                ELSE
                BEGIN
                    SET @intMonth = 11
                END
            END
            --Nov
            --10/23/2018 - 11/12/2018
            IF CONVERT(smalldatetime,getDate(),110) >= @11a AND CONVERT(smalldatetime,getDate(),110) <= @11b
            BEGIN
                IF CONVERT(smalldatetime,getDate(),110) >= @11a AND CONVERT(smalldatetime,getDate(),110) <= @11c
                BEGIN
                    SET @intMonth = 11
                END
                ELSE
                BEGIN
                    SET @intMonth = 12
                END
            END
            --Dec
            --11/13/2018 - 12/10/2018
            IF CONVERT(smalldatetime,getDate(),110) >= @12a AND CONVERT(smalldatetime,getDate(),110) <= @12B
            BEGIN
                IF CONVERT(smalldatetime,getDate(),110) >= @12a AND CONVERT(smalldatetime,getDate(),110) <= @12c
                BEGIN
                    SET @intMonth = 12
                END
                ELSE
                BEGIN
                    SET @intMonth = 1
                END
            END
            Print @intThisMonth
            Print @intJCMonth
            Print @intMonth

              IF @intJCMonth = @intMonth --See what today's billing period is and see if it lines up with Job Code builling period
            BEGIN
                IF @intMonth > @intThisMonth OR (@intThisMonth > 9 AND @intMonth < 4)--Future Bill
                BEGIN
                    print 'future bill' --leave zero
                    Set @intMonth = 0
                END
                ELSE IF @intMonth < @intThisMonth OR (@intThisMonth < 4 AND @intMonth > 9)--Bill Multiple Months
                BEGIN
                    --Need to code for months when jan - march have months oct - dec to bill for
                    SET @intMonth = @intThisMonth - @intMonth + 1
                END
                ELSE --Bill One Month
                BEGIN
                    SET @intMonth = 1
                END
            END
            ELSE IF @intJCMonth > @intThisMonth --Job Code is a Future Bill
            BEGIN
                print 'future bill' --leave zero
                Set @intMonth = 0
            END
            ELSE IF @intJCMonth = @intThisMonth --Bill One Month
            BEGIN
                SET @intMonth = 1
            END
            ELSE IF @intJCMonth < @intMonth
            BEGIN
                SET @intMonth = @intThisMonth - @intJCMonth + 1
            END
            ELSE
            BEGIN
                SET @intMonth = 0
            END

              print @intMonth

              IF @inZip = 1
            BEGIN
                SET @univMedPrem = @medhmo * @intMonth
            END
            ELSE
            BEGIN
                SET @univMedPrem = @medppo * @intMonth
            END
            SET @univDentPrem = @denhmo * @intMonth
            SET @univVisPrem = @vision * @intMonth
            SET @univLifePrem = @life * @intMonth

              SET @univContribution = @univMedPrem + @univDentPrem + @univVisPrem + @univLifePrem
            
            print @univContribution

              IF @intMonth = 0
            BEGIN
                INSERT INTO studly.univEnrollMain08
                SELECT *,null,null,null,null,null,null,null,null,null,null,'z',null,null,'N','P',null,null,1,@Job_class,'Auto Enroll From Import',null FROM studly.univEnrollMainImport WHERE ID_Number = @ID_Number
                
                INSERT INTO [studly].[univEnrollMainAutoReport] (univ_id,Last_name,First_name,Job_class,Medical,isFutureChange) VALUES (@ID_Number,@Last_name,@First_name,@Job_class,CASE WHEN @inZip=1 THEN 'HMO' WHEN @inZip=0 THEN 'PPO' END,'Y')
            END
            ELSE
            BEGIN
                INSERT INTO studly.univEnrollMain08
                SELECT *,null,null,null,null,null,null,null,null,null,null,'a',null,null,CASE WHEN @intMonth=1 THEN 'N' WHEN @intMonth>1 THEN 'Y' END,'P',null,null,0,null,null,null FROM studly.univEnrollMainImport WHERE ID_Number = @ID_Number
            
                INSERT INTO [studly].[univEnrollMainAutoReport] (univ_id,Last_name,First_name,Job_class,Medical,isFutureChange) VALUES (@ID_Number,@Last_name,@First_name,@Job_class,CASE WHEN @inZip=1 THEN 'HMO' WHEN @inZip=0 THEN 'PPO' END,'N')

              END

              INSERT INTO studly.univCoverage08 (univ_id,Enroll_med,Enroll_dent,Enroll_vis,Enroll_self,self_med,self_dent,self_vis,COBRA,Ins_privacy,pdMedPrem,univMedPrem,pdDentPrem,univDentPrem,pdVisPrem,univVisPrem,pdLifePrem,univLifePrem,pdContribution,univContribution,edit_by,isDWC) VALUES (@ID_Number,CASE WHEN @inZip=1 THEN 'HMO_E' WHEN @inZip=0 THEN 'PPO_E' END,'DMO_E','E','E','Y','Y','Y','YES','YES',0,@univMedPrem,0,@univDentPrem,0,@univVisPrem,0,@univLifePrem,0,@univContribution,'NUMBER',0)
            
            Print 'new'         
        END
         --ELSE
         --CONTINUE

          -- This is executed as long as the previous fetch succeeds.
        FETCH NEXT FROM univ_import
        INTO @ID_Number, @Last_name, @First_name, @MI, @Address1, @Address2, @City, @State, @Zip, @Home_phone, @Gender, @Marital_Status, @DOB, @Hire, @Job_class, @PayrollHomeOrg, @Appt_end, @Employ_status, @Status_effect, @Benefit_eligible
    END

  CLOSE univ_import
DEALLOCATE univ_import

  CLOSE univ_rates
DEALLOCATE univ_rates

  GO

  USE studly
GO

  DECLARE    @ID_Number int
DECLARE @Home_phone char(10)

  DECLARE univ_phone_change CURSOR FOR
SELECT ID_Number, Home_phone FROM studly.univEnrollMain WHERE (Home_phone = '') OR (Home_phone IS NULL)

  OPEN univ_phone_change

  FETCH NEXT FROM univ_phone_change
INTO @ID_Number, @Home_phone

  -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE (@@FETCH_STATUS = 0)
    BEGIN
        UPDATE studly.univEnrollMain SET Home_phone = CONVERT(varchar, @ID_Number, 101) + '00' WHERE ID_Number = @ID_Number
        FETCH NEXT FROM univ_phone_change
        INTO @ID_Number, @Home_phone
    END

  CLOSE univ_phone_change
DEALLOCATE univ_phone_change

• frederico_fonseca

  SSCoach

  Points: 16155

  More actions

  October 12, 2018 at 6:30 pm

  #2009453

  SQLStud 74876 - Friday, October 12, 2018 5:08 PM

  Hey all.  I have a contract building a database for a company that bills other companies, and I wrote what I thought was an awesome query.  It runs a bit slow and I have time to get coffee when it runs.  Any help would be great.  I'm pretty good with SQL, so no newbs please.

  
USE studly
GO

  DECLARE    @ID_Number int
DECLARE @Last_name char(25)
DECLARE @First_name char(20)
DECLARE @MI Char(2)
DECLARE @Address1 char(40)
DECLARE @Address2 char(10)
DECLARE @City char(20)
DECLARE @State char(2)
DECLARE @Zip char(5)
DECLARE @Home_phone char(10)
DECLARE @Gender char(1)
DECLARE @Marital_Status char(1)
DECLARE @DOB char(8)
DECLARE @Hire char(8)
DECLARE @Job_class char(10)
DECLARE @PayrollHomeOrg char(10)
DECLARE @Appt_end char(8)
DECLARE @Employ_status char(1)
DECLARE @Status_effect char(8)
DECLARE @Benefit_eligible char(1)
DECLARE @change_reason varchar(200)
DECLARE @gp_pd_status varchar(1)
DECLARE @inZip int
DECLARE @rateID int
DECLARE @life money
DECLARE @vision money
DECLARE @medhmo money
DECLARE @medppo money
DECLARE @denhmo money
DECLARE @univMedPrem money
DECLARE @univDentPrem money
DECLARE @univVisPrem money
DECLARE @univLifePrem money
DECLARE @univContribution money
DECLARE @intMonth int
DECLARE @intJCMonth int
DECLARE @intThisMonth int

  DECLARE @1a smalldatetime = '12/16/2018'
DECLARE @1b smalldatetime = '1/20/2019'
DECLARE @1c smalldatetime = '1/1/2019'
DECLARE @2a smalldatetime
DECLARE @2b smalldatetime
DECLARE @2c smalldatetime = '2/1/2018'
DECLARE @3a smalldatetime
DECLARE @3b smalldatetime
DECLARE @3c smalldatetime = '3/1/2018'
DECLARE @4a smalldatetime = '3/18/2018'
DECLARE @4b smalldatetime = '4/21/2018'
DECLARE @4c smalldatetime = '4/1/2018'
DECLARE @5a smalldatetime = '4/22/2018'
DECLARE @5b smalldatetime = '5/19/2018'
DECLARE @5c smalldatetime = '5/1/2018'
DECLARE @6a smalldatetime = '5/20/2018'
DECLARE @6b smalldatetime = '6/16/2018'
DECLARE @6c smalldatetime = '6/1/2018'
DECLARE @7a smalldatetime = '6/17/2018'
DECLARE @7b smalldatetime = '7/21/2018'
DECLARE @7c smalldatetime = '7/1/2018'
DECLARE @8a smalldatetime = '7/22/2018'
DECLARE @8b smalldatetime = '8/18/2018'
DECLARE @8c smalldatetime = '8/1/2018'
DECLARE @9a smalldatetime = '8/19/2018'
DECLARE @9b smalldatetime = '9/22/2018'
DECLARE @9c smalldatetime = '9/1/2018'
DECLARE @10a smalldatetime = '9/23/2018'
DECLARE @10b smalldatetime = '10/20/2018'
DECLARE @10c smalldatetime = '10/1/2018'
DECLARE @11a smalldatetime = '10/21/2018'
DECLARE @11b smalldatetime = '11/17/2018'
DECLARE @11c smalldatetime = '11/1/2018'
DECLARE @12a smalldatetime = '11/13/2018'
DECLARE @12B smalldatetime = '12/10/2018'
DECLARE @12c smalldatetime = '12/1/2018'

  SET @rateID = 9

  --Check for any record that has a change reason
--Move copy of records with change reason to history table then clear change reason value in UnivEnrollMain08
DECLARE univ_clear_change_reason CURSOR FOR
SELECT ID_Number, change_reason FROM studly.univEnrollMain08 WHERE len(isnull(change_reason,'')) > 0

  OPEN univ_clear_change_reason

  FETCH NEXT FROM univ_clear_change_reason
INTO @ID_Number, @change_reason

  -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE (@@FETCH_STATUS = 0)
    BEGIN
        INSERT INTO studly.univEnrollMainHistory08
        SELECT *, getDate() FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number

          UPDATE studly.univEnrollMain08 SET change_reason = '' WHERE ID_Number = @ID_Number
        print 'Cleared change reason for ' + convert(varchar,@ID_Number)    
        -- This is executed as long as the previous fetch succeeds.
        FETCH NEXT FROM univ_clear_change_reason
        INTO @ID_Number, @change_reason
    END

  CLOSE univ_clear_change_reason
DEALLOCATE univ_clear_change_reason

  DECLARE univ_rates CURSOR FOR
SELECT [life], [vision], [medhmo], [medppo], [denhmo] FROM [studly].[univRates] WHERE rateID = @rateID

  OPEN univ_rates

  FETCH NEXT FROM univ_rates
INTO @life, @vision, @medhmo, @medppo, @denhmo

  --Check import file to see if record currently exists
--If exists then update record and provide change reasons
--If not exists then add to database
DECLARE univ_import CURSOR FOR
SELECT ID_Number, Last_name, First_name, MI, Address1, Address2, City, State, Zip, Home_phone, Gender, Marital_Status, DOB, Hire, Job_class, PayrollHomeOrg, Appt_end, Employ_status, Status_effect, Benefit_eligible FROM studly.univEnrollMainImport

  OPEN univ_import

  FETCH NEXT FROM univ_import
INTO @ID_Number, @Last_name, @First_name, @MI, @Address1, @Address2, @City, @State, @Zip, @Home_phone, @Gender, @Marital_Status, @DOB, @Hire, @Job_class, @PayrollHomeOrg, @Appt_end, @Employ_status, @Status_effect, @Benefit_eligible

  -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE (@@FETCH_STATUS = 0)
    BEGIN
        IF (SELECT ID_Number FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number) = @ID_Number
        BEGIN
            Set @change_reason = ''
        
            IF (SELECT Last_name FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number) <> @Last_Name
            BEGIN
                set @change_reason = @change_reason + 'last name different, '
            END
            IF (SELECT Job_class FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number) <> @Job_class
            BEGIN

                  set @change_reason = @change_reason + 'job class different, '
            END
            IF (SELECT Employ_status FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number) <> @Employ_status
            BEGIN

                  set @change_reason = @change_reason + 'employ status different, '
            END
            IF (SELECT Status_effect FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number) <> @Status_effect
            BEGIN

                  set @change_reason = @change_reason + 'status effect different, '
            END
            IF (SELECT Benefit_eligible FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number) <> @Benefit_eligible
            BEGIN

                  set @change_reason = @change_reason + 'Benefit eligible different, '
            END
            IF (SELECT Address1 FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number) <> @Address1
            BEGIN

                  set @change_reason = @change_reason + 'address different'
            END

              IF @change_reason <> ''
            BEGIN
                --Check to see if record has already been approved prior to weekly import
                IF (SELECT gp_pd_status FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number) = 'a'
                BEGIN
                    SET @gp_pd_status = 'd'
                END
                ELSE
                BEGIN
                    SET @gp_pd_status = 'c'
                END

                  INSERT INTO studly.univEnrollMainHistory08
                SELECT *, getDate() FROM studly.univEnrollMain08 WHERE ID_Number = @ID_Number

                  UPDATE studly.univEnrollMain08 SET Last_name = @Last_Name, First_name = @First_name, MI = @MI, Address1 = @Address1, Address2 = @Address2, City = @City, State = @State, Zip = @Zip, Home_phone = @Home_phone, Gender = @Gender, Marital_Status = @Marital_Status, DOB = @DOB, Hire = @Hire, Job_class = @Job_class, PayrollHomeOrg = @PayrollHomeOrg, Appt_end = @Appt_end, Employ_status = @Employ_status, Status_effect = @Status_effect, Benefit_eligible = @Benefit_eligible, gp_pd_status = @gp_pd_status, change_reason = @change_reason WHERE ID_Number = @ID_Number
                print @change_reason

              END

              ELSE
            BEGIN
                
                UPDATE studly.univEnrollMain08 SET Last_name = @Last_Name, First_name = @First_name, MI = @MI, Address1 = @Address1, Address2 = @Address2, City = @City, State = @State, Zip = @Zip, Home_phone = @Home_phone, Gender = @Gender, Marital_Status = @Marital_Status, DOB = @DOB, Hire = @Hire, Job_class = @Job_class, PayrollHomeOrg = @PayrollHomeOrg, Appt_end = @Appt_end, Employ_status = @Employ_status, Status_effect = @Status_effect, Benefit_eligible = @Benefit_eligible WHERE ID_Number = @ID_Number
                print getDate()

              END
                        
--CONTINUE

          END
        --BREAK
        ELSE
            
            
         --IF (1=1)
         BEGIN
            SET @inZip = 0
            SET @intMonth = 0
            SET @intJCMonth = 0
            SET @univMedPrem = 0
            SET @univDentPrem = 0
            SET @univVisPrem = 0
            SET @univLifePrem = 0
            SET @univContribution = 0
            SET @intThisMonth = month(getDate())

              

              IF (SELECT Zip FROM studly.univZips10 WHERE Zip = (SELECT Zip FROM studly.univEnrollMainImport WHERE ID_Number = @ID_Number)) = (SELECT Zip FROM studly.univEnrollMainImport WHERE ID_Number = @ID_Number)
            BEGIN
                SET @inZip = 1
            END

              --Determine Job Code Month
            --January
            --12/16/2018 - 1/20/2019
            IF CONVERT(smalldatetime,@Job_class,110) >= @1a AND CONVERT(smalldatetime,@Job_class,110) <= @1b
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @1a AND CONVERT(smalldatetime,@Job_class,110) <= @1c
                BEGIN
                    SET @intJCMonth = 1
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 2
                END
            END
            --April
            --3/18/2018 - 4/21/2018
            IF CONVERT(smalldatetime,@Job_class,110) >= @4a AND CONVERT(smalldatetime,@Job_class,110) <= @4b
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @4a AND CONVERT(smalldatetime,@Job_class,110) <= @4c
                BEGIN
                    SET @intJCMonth = 4
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 5
                END
            END
            --May
            --4/22/2018 - 5/19/2018
            IF CONVERT(smalldatetime,@Job_class,110) >= @5a AND CONVERT(smalldatetime,@Job_class,110) <= @5b
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @5a AND CONVERT(smalldatetime,@Job_class,110) <= @5c
                BEGIN
                    SET @intJCMonth = 5
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 6
                END
            END
            --June
            --5/20/2018 - 6/16/2018
            IF CONVERT(smalldatetime,@Job_class,110) >= @6a AND CONVERT(smalldatetime,@Job_class,110) <= @6b
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @6a AND CONVERT(smalldatetime,@Job_class,110) <= @6c
                BEGIN
                    SET @intJCMonth = 6
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 7
                END
            END
            --July
            --6/17/2018 - 7/21/2018
            IF CONVERT(smalldatetime,@Job_class,110) >= @7a AND CONVERT(smalldatetime,@Job_class,110) <= @7b
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @7a AND CONVERT(smalldatetime,@Job_class,110) <= @7c
                BEGIN
                    SET @intJCMonth = 7
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 8
                END
            END
            --Aug
            --7/22/2018 - 8/18/2018
            IF CONVERT(smalldatetime,@Job_class,110) >= @8a AND CONVERT(smalldatetime,@Job_class,110) <= @8b
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @8a AND CONVERT(smalldatetime,@Job_class,110) <= @8c
                BEGIN
                    SET @intJCMonth = 8
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 9
                END
            END
            --Sep
            --8/19/2018 - 9/22/2018
            IF CONVERT(smalldatetime,@Job_class,110) >= @9a AND CONVERT(smalldatetime,@Job_class,110) <= @9b
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @9a AND CONVERT(smalldatetime,@Job_class,110) <= @9c
                BEGIN
                    SET @intJCMonth = 9
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 10
                END
            END
            --Oct
            --9/23/2018 - 10/20/2018
            IF CONVERT(smalldatetime,@Job_class,110) >= @10a AND CONVERT(smalldatetime,@Job_class,110) <= @10b
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @10a AND CONVERT(smalldatetime,@Job_class,110) <= @10c
                BEGIN
                    SET @intJCMonth = 10
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 11
                END
            END
            --Nov
            --10/21/2018 - 11/17/2018
            IF CONVERT(smalldatetime,@Job_class,110) >= @11a AND CONVERT(smalldatetime,@Job_class,110) <= @11b
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @11a AND CONVERT(smalldatetime,@Job_class,110) <= @11C
                BEGIN
                    SET @intJCMonth = 11
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 12
                END
            END
            --Dec
            --11/18/2018 - 12/8/2018
            IF CONVERT(smalldatetime,@Job_class,110) >= @12a AND CONVERT(smalldatetime,@Job_class,110) <= @12B
            BEGIN
                IF CONVERT(smalldatetime,@Job_class,110) >= @12a AND CONVERT(smalldatetime,@Job_class,110) <= @12c
                BEGIN
                    SET @intJCMonth = 12
                END
                ELSE
                BEGIN
                    SET @intJCMonth = 1
                END
            END

              --Determine Our Current Month
            --April
            --4/17/2018 - 5/21/2018
            IF CONVERT(smalldatetime,getDate(),110) >= @4a AND CONVERT(smalldatetime,getDate(),110) <= @4b
            BEGIN
                IF CONVERT(smalldatetime,getDate(),110) >= @4a AND CONVERT(smalldatetime,getDate(),110) <= @4c
                BEGIN
                    SET @intMonth = 4
                END
                ELSE
                BEGIN
                    SET @intMonth = 5
                END
            END
            --May
            --4/17/2018 - 5/21/2018
            IF CONVERT(smalldatetime,getDate(),110) >= @5a AND CONVERT(smalldatetime,getDate(),110) <= @5b
            BEGIN
                IF CONVERT(smalldatetime,getDate(),110) >= @5a AND CONVERT(smalldatetime,getDate(),110) <= @5c
                BEGIN
                    SET @intMonth = 5
                END
                ELSE
                BEGIN
                    SET @intMonth = 6
                END
            END
            --June
            --5/22/2018 - 6/18/2018
            IF CONVERT(smalldatetime,getDate(),110) >= @6a AND CONVERT(smalldatetime,getDate(),110) <= @6b
            BEGIN
                IF CONVERT(smalldatetime,getDate(),110) >= @6a AND CONVERT(smalldatetime,getDate(),110) <= @6c
                BEGIN
                    SET @intMonth = 6
                END
                ELSE
                BEGIN
                    SET @intMonth = 7
                END
            END
            --July
            --6/19/2018 - 7/23/2018
            IF CONVERT(smalldatetime,getDate(),110) >= @7a AND CONVERT(smalldatetime,getDate(),110) <= @7b
            BEGIN
                IF CONVERT(smalldatetime,getDate(),110) >= @7a AND CONVERT(smalldatetime,getDate(),110) <= @7c
                BEGIN
                    SET @intMonth = 7
                END
                ELSE
                BEGIN
                    SET @intMonth = 8
                END
            END
            --Aug
            --7/24/2018 - 8/20/2018
            IF CONVERT(smalldatetime,getDate(),110) >= @8a AND CONVERT(smalldatetime,getDate(),110) <= @8b
            BEGIN
                IF CONVERT(smalldatetime,getDate(),110) >= @8a AND CONVERT(smalldatetime,getDate(),110) <= @8c
                BEGIN
                    SET @intMonth = 8
                END
                ELSE
                BEGIN
                    SET @intMonth = 9
                END
            END
            --Sep
            --8/21/2018 - 9/17/2018
            IF CONVERT(smalldatetime,getDate(),110) >= @9a AND CONVERT(smalldatetime,getDate(),110) <= @9b
            BEGIN
                IF CONVERT(smalldatetime,getDate(),110) >= @9a AND CONVERT(smalldatetime,getDate(),110) <= @9c
                BEGIN
                    SET @intMonth = 9
                END
                ELSE
                BEGIN
                    SET @intMonth = 10
                END
            END
            --Oct
            --9/18/2018 - 10/22/2018
            IF CONVERT(smalldatetime,getDate(),110) >= @10a AND CONVERT(smalldatetime,getDate(),110) <= @10b
            BEGIN
                IF CONVERT(smalldatetime,getDate(),110) >= @10a AND CONVERT(smalldatetime,getDate(),110) <= @10c
                BEGIN
                    SET @intMonth = 10
                END
                ELSE
                BEGIN
                    SET @intMonth = 11
                END
            END
            --Nov
            --10/23/2018 - 11/12/2018
            IF CONVERT(smalldatetime,getDate(),110) >= @11a AND CONVERT(smalldatetime,getDate(),110) <= @11b
            BEGIN
                IF CONVERT(smalldatetime,getDate(),110) >= @11a AND CONVERT(smalldatetime,getDate(),110) <= @11c
                BEGIN
                    SET @intMonth = 11
                END
                ELSE
                BEGIN
                    SET @intMonth = 12
                END
            END
            --Dec
            --11/13/2018 - 12/10/2018
            IF CONVERT(smalldatetime,getDate(),110) >= @12a AND CONVERT(smalldatetime,getDate(),110) <= @12B
            BEGIN
                IF CONVERT(smalldatetime,getDate(),110) >= @12a AND CONVERT(smalldatetime,getDate(),110) <= @12c
                BEGIN
                    SET @intMonth = 12
                END
                ELSE
                BEGIN
                    SET @intMonth = 1
                END
            END
            Print @intThisMonth
            Print @intJCMonth
            Print @intMonth

              IF @intJCMonth = @intMonth --See what today's billing period is and see if it lines up with Job Code builling period
            BEGIN
                IF @intMonth > @intThisMonth OR (@intThisMonth > 9 AND @intMonth < 4)--Future Bill
                BEGIN
                    print 'future bill' --leave zero
                    Set @intMonth = 0
                END
                ELSE IF @intMonth < @intThisMonth OR (@intThisMonth < 4 AND @intMonth > 9)--Bill Multiple Months
                BEGIN
                    --Need to code for months when jan - march have months oct - dec to bill for
                    SET @intMonth = @intThisMonth - @intMonth + 1
                END
                ELSE --Bill One Month
                BEGIN
                    SET @intMonth = 1
                END
            END
            ELSE IF @intJCMonth > @intThisMonth --Job Code is a Future Bill
            BEGIN
                print 'future bill' --leave zero
                Set @intMonth = 0
            END
            ELSE IF @intJCMonth = @intThisMonth --Bill One Month
            BEGIN
                SET @intMonth = 1
            END
            ELSE IF @intJCMonth < @intMonth
            BEGIN
                SET @intMonth = @intThisMonth - @intJCMonth + 1
            END
            ELSE
            BEGIN
                SET @intMonth = 0
            END

              print @intMonth

              IF @inZip = 1
            BEGIN
                SET @univMedPrem = @medhmo * @intMonth
            END
            ELSE
            BEGIN
                SET @univMedPrem = @medppo * @intMonth
            END
            SET @univDentPrem = @denhmo * @intMonth
            SET @univVisPrem = @vision * @intMonth
            SET @univLifePrem = @life * @intMonth

              SET @univContribution = @univMedPrem + @univDentPrem + @univVisPrem + @univLifePrem
            
            print @univContribution

              IF @intMonth = 0
            BEGIN
                INSERT INTO studly.univEnrollMain08
                SELECT *,null,null,null,null,null,null,null,null,null,null,'z',null,null,'N','P',null,null,1,@Job_class,'Auto Enroll From Import',null FROM studly.univEnrollMainImport WHERE ID_Number = @ID_Number
                
                INSERT INTO [studly].[univEnrollMainAutoReport] (univ_id,Last_name,First_name,Job_class,Medical,isFutureChange) VALUES (@ID_Number,@Last_name,@First_name,@Job_class,CASE WHEN @inZip=1 THEN 'HMO' WHEN @inZip=0 THEN 'PPO' END,'Y')
            END
            ELSE
            BEGIN
                INSERT INTO studly.univEnrollMain08
                SELECT *,null,null,null,null,null,null,null,null,null,null,'a',null,null,CASE WHEN @intMonth=1 THEN 'N' WHEN @intMonth>1 THEN 'Y' END,'P',null,null,0,null,null,null FROM studly.univEnrollMainImport WHERE ID_Number = @ID_Number
            
                INSERT INTO [studly].[univEnrollMainAutoReport] (univ_id,Last_name,First_name,Job_class,Medical,isFutureChange) VALUES (@ID_Number,@Last_name,@First_name,@Job_class,CASE WHEN @inZip=1 THEN 'HMO' WHEN @inZip=0 THEN 'PPO' END,'N')

              END

              INSERT INTO studly.univCoverage08 (univ_id,Enroll_med,Enroll_dent,Enroll_vis,Enroll_self,self_med,self_dent,self_vis,COBRA,Ins_privacy,pdMedPrem,univMedPrem,pdDentPrem,univDentPrem,pdVisPrem,univVisPrem,pdLifePrem,univLifePrem,pdContribution,univContribution,edit_by,isDWC) VALUES (@ID_Number,CASE WHEN @inZip=1 THEN 'HMO_E' WHEN @inZip=0 THEN 'PPO_E' END,'DMO_E','E','E','Y','Y','Y','YES','YES',0,@univMedPrem,0,@univDentPrem,0,@univVisPrem,0,@univLifePrem,0,@univContribution,'NUMBER',0)
            
            Print 'new'         
        END
         --ELSE
         --CONTINUE

          -- This is executed as long as the previous fetch succeeds.
        FETCH NEXT FROM univ_import
        INTO @ID_Number, @Last_name, @First_name, @MI, @Address1, @Address2, @City, @State, @Zip, @Home_phone, @Gender, @Marital_Status, @DOB, @Hire, @Job_class, @PayrollHomeOrg, @Appt_end, @Employ_status, @Status_effect, @Benefit_eligible
    END

  CLOSE univ_import
DEALLOCATE univ_import

  CLOSE univ_rates
DEALLOCATE univ_rates

  GO

  USE studly
GO

  DECLARE    @ID_Number int
DECLARE @Home_phone char(10)

  DECLARE univ_phone_change CURSOR FOR
SELECT ID_Number, Home_phone FROM studly.univEnrollMain WHERE (Home_phone = '') OR (Home_phone IS NULL)

  OPEN univ_phone_change

  FETCH NEXT FROM univ_phone_change
INTO @ID_Number, @Home_phone

  -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE (@@FETCH_STATUS = 0)
    BEGIN
        UPDATE studly.univEnrollMain SET Home_phone = CONVERT(varchar, @ID_Number, 101) + '00' WHERE ID_Number = @ID_Number
        FETCH NEXT FROM univ_phone_change
        INTO @ID_Number, @Home_phone
    END

  CLOSE univ_phone_change
DEALLOCATE univ_phone_change

  Where to start!!..
  I know. please forget that there is such a thing as a CURSOR !!!!!
  you need to think set based, not record based.

  Just as an example from your own code.
  

  declare @Id_Number int
declare @Home_Phone char(10)

  declare univ_phone_change cursor for
  select ID_Number
   , Home_phone
   from studly.univEnrollMain
   where (Home_phone = '')
    or (Home_phone is null)

  open univ_phone_change

  fetch next from univ_phone_change
into @Id_Number, @Home_Phone

  -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
while (@@Fetch_Status = 0)
begin
  update studly.univEnrollMain
   set Home_phone = convert(varchar, @Id_Number, 101) + '00'
  where ID_Number = @Id_Number
  fetch next from univ_phone_change
  into @Id_Number, @Home_Phone
end

  close univ_phone_change
deallocate univ_phone_change

  can be written as
  update studly.univEnrollMain
   set Home_phone = convert(varchar(30), ID_Number, 101) + '00'
where (Home_phone = '')
    or (Home_phone is null)

  and also 
  
declare univ_clear_change_reason cursor for
  select ID_Number
   , change_reason
   from studly.univEnrollMain08
   where len(isnull(change_reason, '')) > 0

  open univ_clear_change_reason

  fetch next from univ_clear_change_reason
into @Id_Number, @Change_Reason

  -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
while (@@Fetch_Status = 0)
begin
  insert into studly.univEnrollMainHistory08
   select *
     , getdate()
   from studly.univEnrollMain08
   where ID_Number = @Id_Number

    update studly.univEnrollMain08
   set change_reason = ''
  where ID_Number = @Id_Number
  print 'Cleared change reason for ' + convert(varchar, @Id_Number)
  -- This is executed as long as the previous fetch succeeds.
  fetch next from univ_clear_change_reason
  into @Id_Number, @Change_Reason
end

  close univ_clear_change_reason
deallocate univ_clear_change_reason


  can be written as
  
insert into studly.univEnrollMainHistory08
select *
  , getdate()
from studly.univEnrollMain08
where len(isnull(change_reason, '')) > 0

  update studly.univEnrollMain08
   set change_reason = ''
where len(isnull(change_reason, '')) > 0
/*
If you really need to print the ID's changed
you can do something based on the following query - if it is really "print" then a cursor can be used, otherwise just a select would do
*/
update studly.univEnrollMain08
   set change_reason = ''
output 'Cleared change reason for ' + convert(varchar(20), inserted.ID_Number)
where len(isnull(change_reason, '')) > 0


  Edited: to change some varchar to varchar(xx) and typo on the very last update (was updating history instead of main)

• Brandie Tarvin

  SSC Guru

  Points: 173061

  More actions

  October 13, 2018 at 4:09 am

  #2009462

  SQLStud 74876 - Friday, October 12, 2018 5:08 PM

  Hey all.  I have a contract building a database for a company that bills other companies, and I wrote what I thought was an awesome query.  It runs a bit slow and I have time to get coffee when it runs.  Any help would be great.  I'm pretty good with SQL, so no newbs please.

  Don't be offended, but if your query is so awesome and you're pretty good with SQL, maybe you should be telling us how to improve its performance?

  Just an FYI, you'll get more people willing to assist you if you don't say things like this when you start off a thread.

  Looking at your query, I agree with the need to get rid of your cursors, but I also wonder why you have so many variables. Do you really need them?

  It would help if you give us DDL for tables with a couple of queries to load them with sample data. Then tell us what results you're looking for (just so we can verify what you've coded for is what you're actually getting). Then we can run all sorts of tests to verify the performance of said query.

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

• DesNorton

  SSC-Insane

  Points: 24387

  More actions

  October 13, 2018 at 5:16 am

  #2009465

  frederico_fonseca - Friday, October 12, 2018 6:30 PM

  
  insert into studly.univEnrollMainHistory08
  select *
    , getdate()
  from studly.univEnrollMain08
  where len(isnull(change_reason, '')) > 0

  update studly.univEnrollMain08
   set change_reason = ''
output 'Cleared change reason for ' + convert(varchar(20), inserted.ID_Number)
where len(isnull(change_reason, '')) > 0


  In fact, the above code can be written as a single statement
  UPDATE studly.univEnrollMain08
SET change_reason = ''
OUTPUT deleted.ID_Number
   , deleted.change_reason
   /*, ..... */ -- Add a full list of field names here. DO NOT USE *
   , GETDATE()
  INTO studly.univEnrollMainHistory08 (ID_Number, change_reason, /* ... */, [YOUR DATETIME FIELD]) -- Add a full list of field names here.
OUTPUT 'Cleared change reason for ' + convert(varchar(10), deleted.ID_Number)
WHERE LEN(ISNULL(change_reason, '')) > 0;


  ---

  How to post data/code on a forum to get the best help.

• frederico_fonseca

  SSCoach

  Points: 16155

  More actions

  October 13, 2018 at 5:22 am

  #2009466

  DesNorton - Saturday, October 13, 2018 5:16 AM

  frederico_fonseca - Friday, October 12, 2018 6:30 PM

  
  insert into studly.univEnrollMainHistory08
  select *
    , getdate()
  from studly.univEnrollMain08
  where len(isnull(change_reason, '')) > 0

  update studly.univEnrollMain08
   set change_reason = ''
output 'Cleared change reason for ' + convert(varchar(20), inserted.ID_Number)
where len(isnull(change_reason, '')) > 0


  In fact, the above code can be written as a single statement
  UPDATE studly.univEnrollMain08
SET change_reason = ''
OUTPUT deleted.ID_Number
   , deleted.change_reason
   /*, ..... */ -- Add a full list of field names here. DO NOT USE *
   , GETDATE()
  INTO studly.univEnrollMainHistory08 (ID_Number, change_reason, /* ... */, [YOUR DATETIME FIELD]) -- Add a full list of field names here.
OUTPUT 'Cleared change reason for ' + convert(varchar(10), deleted.ID_Number)
WHERE LEN(ISNULL(change_reason, '')) > 0;


  Hehe... bit too advanced for the OP 😀

• DesNorton

  SSC-Insane

  Points: 24387

  More actions

  October 13, 2018 at 5:40 am

  #2009467

  frederico_fonseca - Saturday, October 13, 2018 5:22 AM

  Hehe... bit too advanced for the OP 😀

  Nah.  He's pretty good with SQL ....

  ---

  How to post data/code on a forum to get the best help.

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  October 13, 2018 at 9:33 am

  #2009481

  SQLStud 74876 - Friday, October 12, 2018 5:08 PM

  I'm pretty good with SQL, so no newbs please.

  Do you always walk into a lion's den while wearing a pork chop necklace and gravy for cologne?

  [EDIT]:  Considering a bit of revelation on my part, it may be intentional as a sign of someone truly frustrated by others... please see my next post below.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  October 13, 2018 at 10:25 am

  #2009490

  Heh... I just saw your other post on the monster wide table that violated every form of normalization there is and where every update was preserved without the benefit of proper historical auditing structures (audit tables, etc) not to mention such problems as what if there is more than 5 children (or less than 5 children) and that the SSN columns don't appear to be encrypted, just to dog-ear a couple of the multitude of problems with that table.  Few could be both that ignorant and arrogant about T-SQL, PII, and databases in general, especially all at once.  So, switching gears and giving you the benefit of the doubt, could it be that maybe, just maybe, you're a whole lot smarter than you're currently letting on to?

  Could the following scenario actually be true?

  Could it be that you're actually VERY smart about SQL Server and T-SQL and, much more importantly, about the nature of people (especially in a "hostile to the DBA" environment) and that you're stuck with a whole herd of "developers" (lower case very intentional , in this case or, perhaps, an idiot savant contractor that you need to flush out to your management?) that write the kind of awful code that you posted on all three posts and you cannot convince them to do otherwise and that you're actually getting feedback from them about how good they think they are as their self-proclaimed line of defense?  And could it be that you actually made a deal with the developers that if you posted the code even biased in their "I'm pretty good at this" direction that folks on this forum would trash the original code and then show the right way to do it, which would absolutely win you the bet and compel the "developers" to start to listen to you?

  If so, then you're a very clever and wise person.  If not, then you're actually someone that needs some severe help with what a database actually is and how it should be used.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 15, 2018 at 4:28 am

  #2009552

  /*
The block of code which assigns a value to the variable @intJCMonth
is insanely long and overcomplicated, and quite possibly wrong too -
check 11th - 15th  December 2018.
I'd suggest using something like this instead. Not only will it be
tons faster, it's easier to modify and could lead you to a solution
with no hardcoding at all of dates.
The same applies to populating @intMonth. You could have the values
in the same table as this one.
*/

  DECLARE @Job_Class_SDT smalldatetime = CONVERT(smalldatetime,@Job_class,110)

  ;WITH DateRange AS (
   SELECT
    RangeStart = CONVERT(DATE,RangeStart,103),
    RangeEnd = CONVERT(DATE,RangeEnd,103),
    intJCMonth
   FROM (VALUES
    ('16/12/2018', '01/01/2019', 1),
    ('02/01/2019', '20/01/2019', 2),
    ('18/03/2018', '01/04/2018', 4),
    ('02/04/2018', '01/05/2018', 5),
    ('02/05/2018', '01/06/2018', 6),
    ('02/06/2018', '01/07/2018', 7),
    ('02/07/2018', '01/08/2018', 8),
    ('02/08/2018', '01/09/2018', 9),
    ('02/09/2018', '01/10/2018', 10),
    ('02/10/2018', '01/11/2018', 11),
    ('02/11/2018', '01/12/2018', 12),
    ('02/12/2018', '10/12/2018', 1)
   ) d (RangeStart, RangeEnd, intJCMonth)
  )
  SELECT @intJCMonth = intJCMonth
  FROM DateRange
  WHERE @Job_Class_SDT BETWEEN RangeStart AND RangeEnd

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• SQLStud 74876

  SSC Veteran

  Points: 277

  More actions

  October 15, 2018 at 12:04 pm

  #2009684

  Jeff Moden - Saturday, October 13, 2018 10:25 AM

  Heh... I just saw your other post on the monster wide table that violated every form of normalization there is and where every update was preserved without the benefit of proper historical auditing structures (audit tables, etc) not to mention such problems as what if there is more than 5 children (or less than 5 children) and that the SSN columns don't appear to be encrypted, just to dog-ear a couple of the multitude of problems with that table.  Few could be both that ignorant and arrogant about T-SQL, PII, and databases in general, especially all at once.  So, switching gears and giving you the benefit of the doubt, could it be that maybe, just maybe, you're a whole lot smarter than you're currently letting on to?

  Could the following scenario actually be true?

  Could it be that you're actually VERY smart about SQL Server and T-SQL and, much more importantly, about the nature of people (especially in a "hostile to the DBA" environment) and that you're stuck with a whole herd of "developers" (lower case very intentional , in this case or, perhaps, an idiot savant contractor that you need to flush out to your management?) that write the kind of awful code that you posted on all three posts and you cannot convince them to do otherwise and that you're actually getting feedback from them about how good they think they are as their self-proclaimed line of defense?  And could it be that you actually made a deal with the developers that if you posted the code even biased in their "I'm pretty good at this" direction that folks on this forum would trash the original code and then show the right way to do it, which would absolutely win you the bet and compel the "developers" to start to listen to you?

  If so, then you're a very clever and wise person.  If not, then you're actually someone that needs some severe help with what a database actually is and how it should be used.

  Winner!  Well, not VERY smart, but smarter than the "idiot savant" who crapped this out.  The poor people who own the company had no idea what was under the hood.  They trusted a friend who "knows a great programmer." I have my work cut out for me, but luckily we now have some sharp SQL developers to help out.  Thank you for the posts, sorry for the way I went about it.

• Brandie Tarvin

  SSC Guru

  Points: 173061

  More actions

  October 15, 2018 at 12:19 pm

  #2009691

  SQLStud 74876 - Monday, October 15, 2018 12:04 PM

  Jeff Moden - Saturday, October 13, 2018 10:25 AM

  Heh... I just saw your other post on the monster wide table that violated every form of normalization there is and where every update was preserved without the benefit of proper historical auditing structures (audit tables, etc) not to mention such problems as what if there is more than 5 children (or less than 5 children) and that the SSN columns don't appear to be encrypted, just to dog-ear a couple of the multitude of problems with that table.  Few could be both that ignorant and arrogant about T-SQL, PII, and databases in general, especially all at once.  So, switching gears and giving you the benefit of the doubt, could it be that maybe, just maybe, you're a whole lot smarter than you're currently letting on to?

  Could the following scenario actually be true?

  Could it be that you're actually VERY smart about SQL Server and T-SQL and, much more importantly, about the nature of people (especially in a "hostile to the DBA" environment) and that you're stuck with a whole herd of "developers" (lower case very intentional , in this case or, perhaps, an idiot savant contractor that you need to flush out to your management?) that write the kind of awful code that you posted on all three posts and you cannot convince them to do otherwise and that you're actually getting feedback from them about how good they think they are as their self-proclaimed line of defense?  And could it be that you actually made a deal with the developers that if you posted the code even biased in their "I'm pretty good at this" direction that folks on this forum would trash the original code and then show the right way to do it, which would absolutely win you the bet and compel the "developers" to start to listen to you?

  If so, then you're a very clever and wise person.  If not, then you're actually someone that needs some severe help with what a database actually is and how it should be used.

  Winner!  Well, not VERY smart, but smarter than the "idiot savant" who crapped this out.  The poor people who own the company had no idea what was under the hood.  They trusted a friend who "knows a great programmer." I have my work cut out for me, but luckily we now have some sharp SQL developers to help out.  Thank you for the posts, sorry for the way I went about it.

  Oh, wow. So I thought you really did code that. Yikes. Apologies for the assumption.

  Yeah, that code needs to be shredded. As does any table underlying it.

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

• SQLStud 74876

  SSC Veteran

  Points: 277

  More actions

  October 15, 2018 at 12:32 pm

  #2009700

  When I asked him why he did it this way, the guy who created this mastery told me that he was "just using the technology of the time".  He did this from 2010 to present.  I think RDBMS has been around since the 70's.

• frederico_fonseca

  SSCoach

  Points: 16155

  More actions

  October 15, 2018 at 12:37 pm

  #2009702

  SQLStud 74876 - Monday, October 15, 2018 12:32 PM

  When I asked him why he did it this way, the guy who created this mastery told me that he was "just using the technology of the time".  He did this from 2010 to present.  I think RDBMS has been around since the 70's.

  Nah - basically someone that didn't know what he was doing back then. Maybe he was learning then (incorrectly) and didn't want to go through the hassle of changing it after he learned how to do it "better" at a later stage.

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  October 15, 2018 at 12:43 pm

  #2009706

  SQLStud 74876 - Monday, October 15, 2018 12:04 PM

  Jeff Moden - Saturday, October 13, 2018 10:25 AM

  Heh... I just saw your other post on the monster wide table that violated every form of normalization there is and where every update was preserved without the benefit of proper historical auditing structures (audit tables, etc) not to mention such problems as what if there is more than 5 children (or less than 5 children) and that the SSN columns don't appear to be encrypted, just to dog-ear a couple of the multitude of problems with that table.  Few could be both that ignorant and arrogant about T-SQL, PII, and databases in general, especially all at once.  So, switching gears and giving you the benefit of the doubt, could it be that maybe, just maybe, you're a whole lot smarter than you're currently letting on to?

  Could the following scenario actually be true?

  Could it be that you're actually VERY smart about SQL Server and T-SQL and, much more importantly, about the nature of people (especially in a "hostile to the DBA" environment) and that you're stuck with a whole herd of "developers" (lower case very intentional , in this case or, perhaps, an idiot savant contractor that you need to flush out to your management?) that write the kind of awful code that you posted on all three posts and you cannot convince them to do otherwise and that you're actually getting feedback from them about how good they think they are as their self-proclaimed line of defense?  And could it be that you actually made a deal with the developers that if you posted the code even biased in their "I'm pretty good at this" direction that folks on this forum would trash the original code and then show the right way to do it, which would absolutely win you the bet and compel the "developers" to start to listen to you?

  If so, then you're a very clever and wise person.  If not, then you're actually someone that needs some severe help with what a database actually is and how it should be used.

  Winner!  Well, not VERY smart, but smarter than the "idiot savant" who crapped this out.  The poor people who own the company had no idea what was under the hood.  They trusted a friend who "knows a great programmer." I have my work cut out for me, but luckily we now have some sharp SQL developers to help out.  Thank you for the posts, sorry for the way I went about it.

  Heh.... your role reversal was nearly impeccable.  One of the giveaways was that I could feel the sarcasm dripping with your "Easy, right?" comment.  Your humility about "not VERY smart" and your comment about the "poor people who own the company" make you someone that I'd love to work with.
  Well played, good Sir!

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• frederico_fonseca

  SSCoach

  Points: 16155

  More actions

  October 15, 2018 at 12:53 pm

  #2009710

  Not fair!!! I feel deceived!!! 
  was hopping for a healthy discussion on the benefits of good coding and database design!!!