locked
Recommendation on speeding up the Procedure RRS feed

  • Question

  • Hi

     Below is the stored procedure that i am trying to use to populate a table. But it is taking more than few hours to run. This is not a feasible solution. Please let me know if there is a way to speed up the query or any recommendation of redesigning the the query to get the data. 

    The Key goal is to , take every single record in the Sourcetable1 and run it in the "sub-while" group and its results are to populate the final table.  The source table has 10,000 rows and using the "sub-while" loop, it has a max of 527 cycles, which results in a max of 527*10,000 = 5,270,000 rows in the final table. It can be less as the StartDate within the Itemdetail table for few records may have a start date in 2020. 

    Any suggestions please. 

    /*************** Procedure Begin *******************/
    DECLARE @StartDate datetime		-- Date when the query starts calculating
     , @EndDate datetime			-- Date when the query stops calculating
      , @Date datetime				-- Date that is used in the query. Every loop this data adds a day
      , @StartID int			-- Min ID of the Sourcetable1 table. This adds by 1 every loop
      , @LastID int				-- Max ID of the Sourcetable1 table. Using this to stop the loop
      , @MainID nvarchar(18)		-- Based on @StartID the corresponding MainID is brought in to the query
     
    
    SELECT @StartDate = '1/1/2019'  
     , @EndDate = getdate()
     , @StartID = '1'
     , @LastID = max(rnumber) from Sourcetable1
    
    
    SELECT @Date = @StartDate -- Assigning the startdate
    
    /*************** The First LOOP is the MainID for every row in the Sourcetable1. So for each MainID, run the below query for all the dates. 
    				The Second LOOP uses the MAINID and keeps looping for every single date and populates the Final table *******************/
    While @StartID  <= @LastID
    Begin
    	SELECT @Date = @StartDate
    	Select @MainID = MainID from Sourcetable1 where id = @StartID
    
    	WHILE @Date < @EndDate
    	BEGIN
    		 insert into Finaltable	( MainID, Region,  SubDept, Item, Trig_Event)
    
    		select a.id, a.Region, a.subdept , b.item , case when datediff(dd,max(c.pur_date), @Date) > 60 then 'NotTrig' else'Trig' end
    			from Region a, ItemDetail b
    			where a.id = b.regionID
    			and b.start_Date1 <= @Date
    			and b.itemfield in ('It2','IT3','IT6')
    			and a.Id = @MainID
    			group by  a.id, a.Region, a.subdept , b.item
    
    	 SELECT @Date = DATEADD(dd,1,@Date)
    
    	end
    	
    	set @StartID = @StartID +1
    
    END
    
    

    Thursday, June 11, 2020 5:57 PM

All replies

  • Rewrite your code with a set based solution to get rid of these two loops.

    If you want a working solution, please post your table DDL, sample data and sample result. (A small set to represent your result).

    If you have a calendar table, it will help as well.

    Thursday, June 11, 2020 6:17 PM
  • You don't need any loops in your code - you can do the select directly assuming you have a Calendar table. Do you need more information or you would be able to figure this out?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, June 11, 2020 6:18 PM
  • :) We posted very similar reply in exactly same time :)

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, June 11, 2020 6:27 PM
  • Not sure i follow the suggestion completely. Working on getting the sample dataset and the DDL . 
    Thursday, June 11, 2020 6:40 PM
  • Assuming you have a Calendar table in your database your query will become:

    insert into Finaltable ( MainID, Region, SubDept, Item, Trig_Event) select a.id, a.Region, a.subdept , b.item,

    case when datediff(dd,max(b.start_date1

    ), cal.theDate) > 60 then 'NotTrig' else'Trig' end from Region a inner join ItemDetail b on a.id = b.regionID inner join dbo.Calendar cal on cal.theDate between @StartDate and @EndDate -- essentially a cross join where b.start_Date1 <= cal.Date and b.itemfield in ('It2','IT3','IT6') and a.Id in (select MainID from SourceTable) group by a.id, a.Region, a.subdept , b.item


    This single statement will insert all rows as you need. Instead of using it as INSERT try first as SELECT to verify it does what your loop does.



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles






    • Edited by Naomi N Friday, June 12, 2020 1:20 PM
    Thursday, June 11, 2020 6:55 PM
  • This should provide the same results as a set, instead of a loop.  There is rarely any reason to loop in SQL Server.

    DECLARE @StartDate datetime		-- Date when the query starts calculating
     , @EndDate datetime			-- Date when the query stops calculating
      , @StartID int			-- Min ID of the Sourcetable1 table. This adds by 1 every loop
      , @LastID int				-- Max ID of the Sourcetable1 table. Using this to stop the loop
    
    
    
    select a.id, a.Region, a.subdept , b.item , case when datediff(dd,max(c.pur_date), @Date) > 60 then 'NotTrig' else'Trig' end
    			from Region a, ItemDetail b
    			where a.id = b.regionID
    			and b.start_Date1 BETWEEN @StartDate AND @EndDate
    			and b.itemfield in ('It2','IT3','IT6')
    			and a.Id BETWEEN @StartID AND @LastID
    			group by  a.id, a.Region, a.subdept , b.item

    Thursday, June 11, 2020 7:35 PM
  • @ Tom - In your solution, if i dont increment the @date, how will the query get results for each day. 

    Below is the DDL for the tables.  I removed some extra fields as they are not important to the issue. 

    create table Finaltable(id int identity(1,1) primary key,
    						MainID nvarchar(18),
    						Datefield date,
    						TrigEvent nvarchar(15))
    
    
    
    create table SourceTable1 (id int identity(1,1) primary key,
    						MainID nvarchar(18))
    
    
    
    create table RegionTable (id nvarchar(18),
    					Region nvarchar(2))
    
    
    
    create table Itemdetail (ID nvarchar(18),
    						Datefield date,
    
    						TrigEvent nvarchar(15))
    


    • Edited by doineedanid Thursday, June 11, 2020 7:56 PM with screenshots
    Thursday, June 11, 2020 7:54 PM
  • As everyone else said, this could be a single set-based query. I was starting to rewrite the query but I ran into a snag.

    In this query:

       select a.id, a.Region, a.subdept , b.item , case when datediff(dd,max(c.pur_date), @Date) > 60 then 'NotTrig' else'Trig' end
       from Region a, ItemDetail b
       where a.id = b.regionID
       and b.start_Date1 <= @Date
       and b.itemfield in ('It2','IT3','IT6')
       and a.Id = @MainID
       group by  a.id, a.Region, a.subdept , b.item

    there are two tables with the aliases a and b. But in the case expression, there appears a c.pur_date. Where does this alias c come from? It appears that the query is incomplete.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, June 11, 2020 9:47 PM
  • >> Tom - In your solution, if i dont increment the @date, how will the query get results for each day. <<

    SQL is a declarative language. Declarative languages do not have loops or increments! You take the entire set of dates and arrange and then you apply your function to each element of the set all at once. This is what we mean by set oriented.

    >>Below is the DDL for the tables. I removed some extra fields [sic] as they are not important to the issue. <<

    Please learn SQL. The term "field" refers to part of a column. Thus, a date is made up of three fields, year, month and day. But since you're still writing with the mindset of 1950s file system you're using the old term they had for their data. In fact, all of your code is very much 1950s tape systems! 

    If you will read a book on RDBMS, you'll find that the identity table property can never be used. The putting the word table in the name of the table is a design error so bad it's actually got a name; it's called a "tibble"!

    CREATE TABLE Final_Stuff 
    (main_id NVARCHAR(18)  NOT NULL,,
     foobar_date DATE  NOT NULL,
     trigger_event_flag NVARCHAR(15)  NOT NULL
      CHECK(trigger_event_flag IN ('trig', 'not trig')));

    I'd like to know what kind of attribute a "Main" is. We name attributes for what they are, not how they're used in identifier has to be the identifier of something in particular.

    The only display format for dates allowed in ANSI/ISO standard SQL is yyyy-mm-dd and not your local dialect.

    We don't write with flags in SQL, but that's what you're doing with your trigger_event column. And you didn't even bother to put a constraint on it.

    Your Final_Table looks like the way you would have named a tape file in the old days and write data to it. None of your other table names or declarations with those identity columns make any sense.

    CREATE TABLE Something_Sources  -- needs a valid column name
    (main_id VARCHAR(18) NOT NULL PRIMARY KEY);

    CREATE TABLE Something_Regions 
    (region_name CHAR(2) NOT NULL PRIMARY KEY);

    Etc. 

    Where are the references clauses? Why did you post pictures and not SQL code?  

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, June 11, 2020 10:54 PM
  • Yes, Erland, I noticed this as well. Looks like what we saw was not the actual code of the procedure.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, June 12, 2020 12:05 AM
  • Hello,

     Thank you for the response. 

    - I apologize for the incorrect code and the terminology being used (column vs field). I was juggling multiple applications and the terminology crossed over into while creating this question on the forum. 

    - While going through the pasted code, i did realize that I posted the incorrect code as I had multiple windows in SSMS in different code variations trying to solve the problem, so i must've pasted the wrong one. My apologies. 

    - Its correct, my knowledge of RDBMS is limited, but its still a work in progress. 

    - I'm going to try to restart this and hopefully, I am able to explain it clearly. 

    - I create a table with all the RegionID and use the auto incremented primary key number to sequentially add it so I can capture the next Region ID and pass it to the Loop. (This is the DailySource)

    - My original concept/logic was to take each RegionID and run it against every single day from the @startdate to @enddate within the Select logic and capture for every day, if it was  trig event or not Trig event. This data is to be inserted into the TrigEvent table. 

    - The TrigEvent table is to be used to for reporting, where I should be able to count for either a date or a date range, how many RegionID's had a Trig or NotTrig Event. 

    - A NotTrig event is defined as, if the StartDate for the item within the Region to a specific date is more or equal to 2, then its a NotTrig Event.

    - Below is the entire code with DDL. 

    create table DailySource (Rnumber int identity(1,1) primary key,
    							RegionID nvarchar(10));
    
    create table Region (Regionid nvarchar(10) primary key,
    						Region nvarchar(2));
    
    create table ItemDetail (ID nvarchar(10) primary key,
    						RegionID nvarchar(10),
    						Start_Date1 date,
    						ItemField nvarchar(5));
    
    Create table TrigEvent (id int identity(1,1) primary key,
    						RegionID nvarchar(10),
    						AsofDate date,
    						Trig_Event nvarchar(10))
    						
    
    insert into Region values ('BDe3487','CA');
    insert into Region values ('BDe3488','AK');
    
    insert into Itemdetail values ('IC124','BDe3487','01/15/2020','IT2');
    insert into Itemdetail values ('IC125','BDe3487','01/18/2020','IT2');
    insert into Itemdetail values ('IC126','BDe3487','01/19/2020','IT3');
    insert into Itemdetail values ('IC127','BDe3488','01/12/2020','IT6');
    
    insert into DailySource select regionid from region
    
    select * from Dailysource;
    select * from region;
    select * from itemdetail;
    
    /*************** Procedure Begin *******************/
    DECLARE @StartDate datetime		-- Date when the query starts calculating
     , @EndDate datetime			-- Date when the query stops calculating
      , @Date datetime			-- Date that is used in the query. Every loop this data adds a day
      , @StartID int			-- Min ID of the Dailysource table. This adds by 1 every loop
      , @LastID int				-- Max ID of the DailySource table. Using this to stop the loop
      , @UseRegionID nvarchar(10)		-- Based on @StartID the corresponding RegionID is brought in to the query
     
    
    SELECT @StartDate = '1/10/2020'  
     , @EndDate = '01/19/2020'
     , @StartID = '1'
     , @LastID = max(rnumber) from DailySource
    
    
    SELECT @Date = @StartDate -- Assigning the startdate
    
    While @StartID  <= @LastID
    Begin
    	SELECT @Date = @StartDate
    	Select @UseRegionID = RegionID from DailySource where rnumber = @StartID
    
    	WHILE @Date < @EndDate
    	BEGIN
    		 insert into TrigEvent	(RegionID, AsofDate, Trig_Event)
    
    		select a.RegionID, @Date,  case when datediff(dd,max(b.start_date1), @Date) >= 2 then 'NotTrig' else'Trig' end
    			from Region a, ItemDetail b
    			where a.regionid = b.regionID
    			and b.start_Date1 <= @Date
    			and a.regionId = @UseRegionID
    			group by  a.regionID
    
    	 SELECT @Date = DATEADD(dd,1,@Date)
    
    	end
    	
    	set @StartID = @StartID +1
    
    END
    
    

    Again, I apologize for my sloppiness and my RDBMS shortfall. 

    Friday, June 12, 2020 3:32 AM

  • create table DailySource (Rnumber int identity(1,1) primary key,
    							RegionID nvarchar(10));
    
    create table Region (Regionid nvarchar(10) primary key,
    						Region nvarchar(2));
    
    create table ItemDetail (ID nvarchar(10) primary key,
    						RegionID nvarchar(10),
    						Start_Date1 date,
    						ItemField nvarchar(5));
    
    Create table TrigEvent (id int identity(1,1) primary key,
    						RegionID nvarchar(10),
    						AsofDate date,
    						Trig_Event nvarchar(10))
    						
    
    insert into Region values ('BDe3487','CA');
    insert into Region values ('BDe3488','AK');
    
    insert into Itemdetail values ('IC124','BDe3487','01/15/2020','IT2');
    insert into Itemdetail values ('IC125','BDe3487','01/18/2020','IT2');
    insert into Itemdetail values ('IC126','BDe3487','01/19/2020','IT3');
    insert into Itemdetail values ('IC127','BDe3488','01/12/2020','IT6');
    
    insert into DailySource select regionid from region
    
    select * from Dailysource;
    select * from region;
    select * from itemdetail;
    
    /*************** Procedure Begin *******************/
    DECLARE @StartDate datetime		-- Date when the query starts calculating
     , @EndDate datetime			-- Date when the query stops calculating
      , @Date datetime			-- Date that is used in the query. Every loop this data adds a day
      , @StartID int			-- Min ID of the Dailysource table. This adds by 1 every loop
      , @LastID int				-- Max ID of the DailySource table. Using this to stop the loop
      , @UseRegionID nvarchar(10)		-- Based on @StartID the corresponding RegionID is brought in to the query
     
    
    SELECT @StartDate = '1/10/2020'  
     , @EndDate = '01/19/2020'
     , @StartID = '1'
     , @LastID = max(rnumber) from DailySource
    
    
    SELECT @Date = @StartDate -- Assigning the startdate
    
    While @StartID  <= @LastID
    Begin
    	SELECT @Date = @StartDate
    	Select @UseRegionID = RegionID from DailySource where rnumber = @StartID
    
    	WHILE @Date < @EndDate
    	BEGIN
    		 insert into TrigEvent	(RegionID, AsofDate, Trig_Event)
    
    		select a.RegionID, @Date,  case when datediff(dd,max(b.start_date1), @Date) >= 2 then 'NotTrig' else'Trig' end
    			from Region a, ItemDetail b
    			where a.regionid = b.regionID
    			and b.start_Date1 <= @Date
    			and a.regionId = @UseRegionID
    			group by  a.regionID
    
    	 SELECT @Date = DATEADD(dd,1,@Date)
    
    	end
    	
    	set @StartID = @StartID +1
    
    END

    Good day doineedanid,

    Before I start: I added one more table to your DDL+DML in order to have dates table. You can fill as many dated as you need in the Calendar table and don't forget to create index and create the calendar table fit your needs.

    create table Calendar(D date)
    GO
    
    INSERT Calendar (D) 
    	SELECT top 300 DATEADD (DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), CONVERT(DATE, '01/01/2020', 103))
    	FROM sys.all_objects t1
    	CROSS JOIN sys.all_objects t2
    GO

     

    I read the thread twice and Your description is not clear since you are not consistent with your own sample and description. Even the last queries does not returns the same as shown in the images above

    I can adjust the following query to any of the versions which you describe, but I will present the solution which fit exactly to your last message.

    Please check if this fit your need (as said it returns exactly the same set as your last DDL+DML)

    If you need any explanation on what I did, then please feel free to ask.

    Note: I kept some comments to help understand what I did and to adjust the query to a different versions of your previous request.

    DECLARE @StartDate DATE, @EndDate DATE
    SET @StartDate = CONVERT(DATE, '10/01/2020', 103)
    SET @EndDate = CONVERT(DATE, '19/01/2020', 103)
    
    ;With MyCTE as (
    	select C.D, R.Regionid, R.Region, I.Start_Date1
    	from Calendar C
    	LEFT JOIN Region R on 1=1
    	LEFT JOIN Itemdetail I on I.RegionID = R.Regionid and I.Start_Date1 = C.D
    	where C.D >= @StartDate and C.D < @EndDate
    	--ORDER BY R.Regionid, R.Region, C.D
    ),
    MyCTE1 as (
    	SELECT D, Regionid, Region
    		, Start_Date1
    		--, LAG (Start_Date1, 1) OVER (PARTITION BY Regionid ORDER BY D)
    		--, COALESCE(Start_Date1, LAG (Start_Date1, 1) OVER (PARTITION BY Regionid ORDER BY D))
    		, IsTrig = CASE
    			WHEN LAG (Start_Date1, 1) OVER (PARTITION BY Regionid ORDER BY D) is null and Start_Date1 is null then 'not trig'
    			else 'trig'
    		END
    		, MD = MIN(Start_Date1) OVER(PARTITION BY Regionid ORDER BY D)
    	FROM MyCTE
    )
    -- If you want to insert the result to the TrigEvent table then remove the comment from the next line
    --INSERT TrigEvent (RegionID,AsofDate,Trig_Event)
    SELECT  RegionID, D as AsofDate, IsTrig as Trig_Event
    FROM MyCTE1
    WHERE MD is not null
    ORDER BY Regionid, Region, D


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    • Edited by pituachMVP Friday, June 12, 2020 5:23 AM
    • Proposed as answer by Lily Lii Friday, June 12, 2020 5:59 AM
    Friday, June 12, 2020 5:17 AM
  • Hi doineedanid,

    --You could also use a simple function
    CREATE FUNCTION [dbo].[DateRange]
    (     
          @Increment              CHAR(1),
          @StartDate              DATE,
          @EndDate                DATE
    )
    RETURNS  
    @SelectedRange    TABLE 
    (IndividualDate DATE)
    AS 
    BEGIN
          ;WITH cteRange (DateRange) AS (
                SELECT @StartDate
                UNION ALL
                SELECT 
                      CASE
                            WHEN @Increment = 'd' THEN DATEADD(dd, 1, DateRange)
                            WHEN @Increment = 'm' THEN DATEADD(mm, 1, DateRange)
    						WHEN @Increment = 'y' THEN DATEADD(yy, -1, @EndDate)
                      END
                FROM cteRange
                WHERE DateRange <= 
                      CASE
                            WHEN @Increment = 'd' THEN DATEADD(dd, -1, @EndDate)
                            WHEN @Increment = 'm' THEN DATEADD(mm, -1, @EndDate)
    						WHEN @Increment = 'y' THEN DATEADD(yy, -1, @EndDate)
                      END)
          INSERT INTO @SelectedRange (IndividualDate)
          SELECT DateRange
          FROM cteRange
          OPTION (MAXRECURSION 3660);
          RETURN
    END
    GO
    
    DECLARE @StartDate DATE
    DECLARE @EndDate DATE
    SET @StartDate ='2020-01-10'
    SET @EndDate = '2020-01-19'
    
    ;with cte1 as(
    SELECT d.IndividualDate,R.Regionid, R.Region, I.Start_Date1
    FROM dbo.DateRange('d', @StartDate , @EndDate ) as d
    left join #Region R on 1=1
    LEFT JOIN #Itemdetail I on I.RegionID = R.Regionid and I.Start_Date1 = d.IndividualDate
    ),
    cte2 as(
    SELECT IndividualDate , Regionid, Region
    		, Start_Date1
    		, IsTrig = CASE
    			WHEN LAG (Start_Date1,1) OVER (PARTITION BY Regionid ORDER BY IndividualDate) is null and Start_Date1 is null then 'not trig' else 'trig' END 
    		, MD = MIN(Start_Date1) OVER(PARTITION BY Regionid ORDER BY IndividualDate)
    from cte1)
    SELECT  RegionID, IndividualDate  as AsofDate, IsTrig as Trig_Event
    FROM cte2
    WHERE MD is not null
    ORDER BY Regionid, Region, IndividualDate

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by pituachMVP Friday, June 12, 2020 11:24 AM
    • Unproposed as answer by Naomi N Friday, June 12, 2020 1:17 PM
    Friday, June 12, 2020 9:09 AM
  • Please re-try what I posted originally with the Calendar table in place (I use column named theDate in that table).

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, June 12, 2020 1:21 PM
  • Thank you all for your suggestions. 

    @Naomi- I added the calendar table and executed your code , but it isnt giving the right solution. 

    For RegionID = Bde3487,

     for calendar date 1/16, as the min(start_date1) prior or equal to 1/16 is not more than 2 days. = Trig

     for calendar date 1/17, as the min(start_date1) prior or equal to 1/17 is not more than 2 days. = Trig

     for calendar date 1/18, as the min(start_date1) prior or equal to 1/18 is more than 2 days. = NotTrig

     for calendar date 1/19,as the min(start_date1) prior or equal to 1/19 is not more than 2 days. = Trig

    create table Calendar(thedate date)
    GO
    
    INSERT Calendar (thedate) 
    	SELECT top 300 DATEADD (DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), CONVERT(DATE, '01/01/2020', 103))
    	FROM sys.all_objects t1
    	CROSS JOIN sys.all_objects t2
    	
    --insert into Finaltable	( MainID, Region,  SubDept, Item, Trig_Event)
    
    DECLARE @StartDate DATE, @EndDate DATE
    SET @StartDate = CONVERT(DATE, '10/01/2020', 103)
    SET @EndDate = CONVERT(DATE, '25/01/2020', 103)
    
    
    select a.regionid, a.Region, cal.thedate--, b.Start_Date1
    ,case when datediff(dd,max(b.start_date1), theDate) >= 2 then 'NotTrig' else 'Trig' end
    			from Region a 
    inner join ItemDetail b
    			on a.regionid = b.regionID
    inner join dbo.Calendar cal on cal.theDate between @StartDate and @EndDate -- essentially a cross join
    
    			where b.start_Date1 <= cal.theDate
    			and a.Regionid = 'BDe3487'
    			group by a.regionid, a.Region, cal.thedate, b.Start_Date1

    @ Ronen - Your query is giving the results as expected, but i will have to change the date range in different scenarios. When i do that, i get incorrect results, probably due to me not understanding your query completely. Please advise on (1) why you have a coalesce statement  and (2) I dont follow the logic behind IsTrig column. 

    Thank you. 

    Saturday, June 13, 2020 5:05 AM
  • Did you read my answer?!?

    It is working and returns the expected result according to your last explanation and expected result. It fit your DDL+DML so you just need to execute it as it is and check if this fit your need


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, June 13, 2020 9:51 AM
  • @Ronen. You are correct in that it is working as per the last explanation. In my testing, your query is working exactly when we have a lag(column,1). However as mentioned, when i change the date difference range from 1 to 10 or 30 etc, it is not working. 

    So when i have to look at a 30 day range, i do a lag (column,30) to look at the value 30 days  ago, but it is not giving me the results as preferred. 

    And I am fairly certain that it might be due to the data that i have. Also that i am sure I dont understand the logic in your query. So please help me understand your query so I can use it to build on top of it. 

    Thank you. 

    Saturday, June 13, 2020 6:11 PM
  • Please disregard @Ronen. i had to fix part of my data and using your logic it worked. Thank you @ronen , @naomi and all for helping me sort out this issue. 

    Sunday, June 14, 2020 4:56 AM
  • Hi
    
    1- Your first loop according to the field rnumber from Sourcetable1 table incriment by 1
    
    2-But if some recods are deleted between first and last Id some null value may inserted in the Finaltable table
    
    3- So use cursor for first loop and nested the inner loop in the cursor
    
    DECLARE @StartID int ;    
    set @StartDate = '2019/01/01'  ;
     set @EndDate = getdate();
    DECLARE source_cursor CURSOR FOR     
    SELECT rnumber from Sourcetable1 where rnumber > 0
    order by rnumber;    
      
    OPEN source_cursor    
      
    FETCH NEXT FROM source_cursor    
    INTO @StartID   
      
    
    WHILE @@FETCH_STATUS = 0    
    BEGIN    
       
    WHILE @Date < @EndDate
    	BEGIN
    		 insert into Finaltable	( MainID, Region,  SubDept, Item, Trig_Event)
    
    		
    		select a.id, a.Region, a.subdept , b.item , case when datediff(dd,max(c.pur_date), @Date) > 60 then 'NotTrig' else'Trig' end
    			from Region a inner join ItemDetail b on a.id = b.regionID 
    			where a.id = @MainID and b.start_Date1 = @Date and (b.itemfield = 'It2' or b.itemfield = 'IT3'or b.itemfield = 'IT6')
    			group by  a.id, a.Region, a.subdept , b.item
    	 		SELECT @Date = DATEADD(dd,1,@Date)
    
    	end
    
        FETCH NEXT FROM source_cursor   
    INTO @StartID    
       
    END     
    CLOSE source_cursor;    
    DEALLOCATE source_cursor;     
    

    Sunday, June 14, 2020 10:51 AM
  • Please disregard @Ronen. i had to fix part of my data and using your logic it worked. Thank you @ronen , @naomi and all for helping me sort out this issue. 

    Awesome 😀 I am glad to hear that your need was solved

    Please remember to close the thread by marking one or more of the threads which lead you to the solution, as answer, and on the way you can vote for responses as well.

    Closing the thread changes the icon near the thread and it prevents supporters to wast their time by entering the thread and re-read all the information, and in order to let search Engine focus on the main messages in the thread. In addition by marking messages as answer (partially or full) and/or voting to messages, the user who wrote the message get some virtual points (No... we cannot go to the store with these points, but it is a nice "credit" or "reputation points"


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, June 14, 2020 11:06 AM
  • Good day Laxmidhar,

    You should avoid using loops when you work with tabular databases like SQL Server, since the server is designed to work with SET of data (table's like - which means a set of rows). In order to improve performance the server uses algorithm which improve the work with SET of data but at the same time it reduce the performance of working with loops usually.

    Loops of any type should be your last resort only and usually when you see loops it usually implies a poor design/code.

    In the current scenario there is no need to use loops


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, June 14, 2020 11:15 AM
  • Hi doineedanid,

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue, in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, June 15, 2020 1:30 AM