Avoiding Cursor in SQL Server

Question

I am trying to avoid cursor to get the following desired output from current format. Currently I have to use a cursor and since the dataset is very huge it takes me around 2 hours to run it. Is there a way to avoid cursor

Here is my current Code

Declare @CustomerId CHAR(8)
Declare @StartDate DateTime
Declare @PlStartDate DateTime
Declare @ProviderNo CHAR(8)
Declare @Code CHAR(3)       

Declare @PreviousCustomerId CHAR(8)
Declare @PreviousStartDate DateTime
Declare @PreviousRealStartDate DateTime
Declare @PreviousProviderNo CHAR(8)
Declare @PreviousCode CHAR(3)           
Declare @RowNumber smallint 
Declare @providers CURSOR

SET @providers = CURSOR  FAST_FORWARD  FOR
Select CustomerId, StartDate, PlStartDate, ProviderNo, Code, ProviderSSN, RowNumber  
From dbo.[provider] ORDER by CustomerId, StartDate

OPEN @providers 
FETCH NEXT From @providers  INTO @CustomerId, @StartDate, @PlStartDate,@ProviderNo, @Code, @ProviderSSN, @RowNumber 

WHILE @@FETCH_STATUS = 0
BEGIN  

    If @RowNumber <>1 AND @CustomerId = @PreviousCustomerId AND @Code = @PreviousCode AND (@ProviderNo = @PreviousProviderNo)       
    BEGIN   
        Update dbo.provider
        SET StartDate = @PreviousRealStartDate
        Where CustomerId = @CustomerId AND  StartDate = @StartDate;     
    END
    ELSE
    BEGIN
        Set @StartDate = @StartDate;

        Update dbo.provider
        Set StartDate = @StartDate
        Where CustomerId = @CustomerId AND  StartDate = @StartDate
    END

    Set @PreviousCustomerId = @CustomerId
    Set @PreviousCode = @Code       
    Set @PreviousProviderNo =  @ProviderNo 
    if @StartDate IS NOT NULL
        Set @PreviousRealStartDate = @StartDate
    Set @PreviousStartDate = @StartDate

    FETCH NEXT From @providers INTO @CustomerId, @StartDate, @PlStartDate,@ProviderNo, @Code, @RowNumber        
END 

CLOSE @providers
DEALLOCATE @providers

Current Format

               
Cust ID Start Date      End Date    Code    Provider
7063903 2/11/2009   2/17/2009   DEF 485960
7063903 2/17/2009   2/24/2009   DEF 485960
7063903 2/24/2009   4/6/2009    LHF 479407
7063903 4/6/2009    9/11/2009   DEF 487398
7063903 8/31/2010   9/1/2010    DEF 487398
7063903 8/28/2011   11/25/2011  ABC 531428
7063903 3/1/2012    6/25/2012   DEF 487398
7063903 6/25/2012   3/22/2013   DEF 487398
7063903 3/22/2013   4/23/2014   DEF 487398
7063903 4/23/2014   5/1/2014    DEF 487398
7063903 5/1/2014    7/1/2015    DEF 487398
7063903 7/1/2015    8/28/2015   DEF 531428
7063903 8/28/2015   11/25/2015  ABC 531428
7063903 11/25/2015  9/21/2016   ABC 531428

Desired Output              

CustID  Start Date  End Date    Code    Provider
7063903 2/11/2009   2/24/2009   DEF 485960
7063903 2/24/2009   4/6/2009    LHF 479407
7063903 4/6/2009    9/1/2010    DEF 487398
7063903 8/28/2011   11/25/2011  ABC 531428
7063903 4/6/2009    7/1/2015    DEF 487398
7063903 7/1/2015    8/28/2015   DEF 531428
7063903 8/28/2015   9/21/2016   ABC 531428

if you provided those samples of data as text, then we would be able to use that data to test our proposed solutions. i.e. In future use text tables. — Paul Maxwell
– Paul Maxwell, Commented Dec 15, 2017 at 0:30

Brian Leach · Accepted Answer · 2017-12-16 01:10:41Z

  SELECT customer
       , code
       , provider
       , MIN (start_date) start_date
       , MAX (end_date) end_date
    FROM dbo.provider
GROUP BY customer, code, provider;

This solution fails when the provider is repeated like below. Look at the red arrows:

Tom,

I must apologize, I misunderstood the original problem. You want the answers grouped where customer/code/provider are the same, and start date = previous end date.

The SQL below does this by setting a value of 1 whenever customer/code/provider don't match, or the start date does not equal the previous end date. I then sum all of the 1's on the previous records to create a value I can group by. I then perform the min/max as in my original answer, this time including the group.

I get the same answer as you requested, with one exception. Your first arrow should result in two rows, not a single row. Please give this a try.

Thanks,

-Brian

WITH
    aset
    AS
        (SELECT customer
              , code
              , provider
              , start_date
              , end_date
              , CASE
                    WHEN LAG (customer)
                             OVER (
                                 PARTITION BY customer, code, provider ORDER BY end_date
                             ) = customer
                     AND LAG (provider)
                             OVER (
                                 PARTITION BY customer, code, provider ORDER BY end_date
                             ) = provider
                     AND LAG (code)
                             OVER (
                                 PARTITION BY customer, code, provider ORDER BY end_date
                             ) = code
                     AND LAG (end_date)
                             OVER (
                                 PARTITION BY customer, code, provider ORDER BY end_date
                             ) = start_date
                    THEN
                        0
                    ELSE
                        1
                END
                    flag
           FROM deleteme_tbl),
    bset
    AS
        (SELECT customer
              , code
              , provider
              , flag
              , start_date
              , end_date
              , SUM (flag)
                    OVER (
                        ORDER BY
                            customer
                          , code
                          , provider
                          , start_date
                        RANGE UNBOUNDED PRECEDING
                    )
                    grp
           FROM aset)
  SELECT customer
       , code
       , provider
       , MIN (start_date) start_date
       , MAX (end_date) end_date
    FROM bset
GROUP BY customer
       , code
       , provider
       , grp
ORDER BY customer
       , code
       , provider
       , start_date;

Paul Maxwell · Accepted Answer · 2017-12-15 00:24:16Z

0

Yes, use a window functions instead, consider using of LAG()

Select
       CustomerId
      , StartDate
      , PlStartDate
      , ProviderNo
      , Code
      , ProviderSSN
      , RowNumber 
      , LAG(StartDate) OVER(PARTITION BY CustomerId, Code, ProviderNo 
                            ORDER by StartDate) as lag_date
From dbo.[provider] 
WHERE StartDate IS NULL
ORDER by CustomerId, StartDate

If this is correct you can work this into a common table expression, and then update using that. Test it on something small first though. e.g.

with cte as ( query shown above )
update cte
set StartDate = lag_date

edited Dec 15, 2017 at 0:24

answered Dec 15, 2017 at 0:16

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Collectives™ on Stack Overflow

Avoiding Cursor in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related