CODEWITHSUNDEEP
sqlsql-servert-sql
bmsqldev
bmsqldev

Reputation: 2735

Convert Procedural Approach into Set Based Approach in Sql-Server

We are using procedural approach (while loop) for inserting records into a particular table. the insert syntax is like below,

DECLARE @CNT INT = 0,
@WEEK DATE = '2015-11-01',
@FLAG INT

CREATE TABLE #Tmpdata (officeId int,id smallint, weekDate date,startsOn varchar(10),endsOn varchar(10),flag bit);

WHILE (@CNT <7)
BEGIN
    SET @WEEK = DATEADD(D,@CNT,@WEEK )

    IF EXISTS
    (SELECT 1
    FROM YEARRANGE
    WHERE @WEEK BETWEEN CONVERT(DATE,taxseasonbegin) 
    AND CONVERT (DATE,taxSeasonEnd)
    )
    BEGIN
      SET @FLAG =1 
    END
    ELSE
    BEGIN
  SET @FLAG = 0
    END

   INSERT INTO #Tmpdata 
       (
       officeId,id,weekDate,startsOn,endsOn,flag
       )
   VALUES
       (
       5134,@lvCounter,@week,'09:00 AM','05:00 PM',@flag
       );

   SET @cnt=@cnt+1;
end

(NOTE : TaxSeason is from january to august).

Is it possible to re-write the above logic in set based approach?

Upvotes: 0

Views: 59

Answers (3)

CPMunich
CPMunich

Reputation: 725

You could try some Kind of this one. This gives you the data I think you Need for your insert. I do not have a table named YEARRANGE so I couldn't test it completely

 DECLARE @CNT INT = 0, @WEEK DATE = '2015-11-01', @FLAG INT;

 CREATE TABLE #Tmpdata (officeId int,id smallint, weekDate date,startsOn varchar(10),endsOn varchar(10),flag bit);

 WITH CTE AS
 (
 SELECT  num AS cnt,
         DATEADD(D, SUM(num) OVER(ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
            , @WEEK) AS [week]
 FROM
 (
 SELECT ROW_NUMBER() OVER (ORDER BY nl) -1 AS num
 FROM
 (SELECT NULL AS nl UNION ALL SELECT NULL AS nl UNION ALL SELECT NULL AS nl      UNION ALL SELECT NULL AS nl
  UNION ALL SELECT NULL AS nl  UNION ALL SELECT NULL AS nl  UNION ALL    SELECT NULL AS nl
 ) AS ni
 ) AS no
 )
 INSERT INTO #Tmpdata (officeId,id,weekDate,startsOn,endsOn,flag)
 SELECT 5134 AS officeID, cnt AS id, [week],'09:00 AM' AS startsOn,'05:00     PM' AS endsOn, COALESCE(A1.flag,0) AS flag
 FROM CTE
 OUTER APPLY (SELECT 1
              FROM YEARRANGE
              WHERE [week] BETWEEN CONVERT(DATE,taxseasonbegin) 
                           AND CONVERT (DATE,taxSeasonEnd)
             ) AS A1(flag);

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

This is making a number of assumption because you didn't post ddl or any consumable sample data. Also, there is a variable @lvCounter not defined in your code. This is perfect opportunity to use a tally or numbers table instead of a loop.

declare @lvCounter int = 42;

DECLARE @CNT INT = 0,
@WEEK DATE = '2015-11-01',
@FLAG INT;

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n))
    , cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E1
    )

select 5134 as officeId
    , @lvCounter as Id
    , DATEADD(DAY, N - 1, @WEEK) as weekDate
    , '09:00 AM' as startsOn
    , '05:00 PM' as EndOn
    , Flag
from cteTally t
cross apply 
(
    select CAST(count(*) as bit) as Flag
    from YearRange
    where DATEADD(Day, t.N, @WEEK) > CONVERT(DATE,taxseasonbegin) 
        AND DATEADD(Day, t.N, @WEEK) <= CONVERT (DATE,taxSeasonEnd)
) y
where t.N <= 7;

Upvotes: 1

A. Greensmith
A. Greensmith

Reputation: 375

Please can you provide sample data?

You can do something like:

SELECT DateIncrement = SUM(DATEADD(D,@CNT,@WEEK)) OVER (ORDER BY officeID)
FROM...

This gets an incremented date value for each record which you can then check against your start and end dates.

Upvotes: 1

Related Questions