In SQL Server, How to Create While Loop in Select

In SQL Server, how to create while loop in select

1. Create function that parses incoming string (say "AABBCC") as a table of strings (in particular "AA", "BB", "CC").
2. Select IDs from your table and use CROSS APPLY the function with data as argument so you'll have as many rows as values contained in the current row's data. No need of cursors or stored procs.

how to create while loop in select?

This is usually handled with a recursive subquery. I derived a row order in case of duplicate names, however, that is best served using a unique key.

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE Names(name NVARCHAR(50), total INT)
INSERT Names VALUES('ahmed',3),('mahmoud',2),('ahmed',5)

Query 1:

;WITH Normalized AS
(
    SELECT *, RowNumber = ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Names
)
,ReplicateAmount AS
 (
    SELECT  name, running_total=total, total, RowNumber
    FROM Normalized

    UNION ALL

    SELECT R.name, running_total=(R.running_total - 1), R.total , R.RowNumber
    FROM ReplicateAmount R INNER JOIN Normalized N ON R.RowNumber = N.RowNumber
    WHERE R.running_total > 1
)   

SELECT 
    name,instance=RowNumber,total=1,OriginalTotal=total,running_total 
FROM 
    ReplicateAmount
ORDER BY
    RowNumber,name,total,running_total
OPTION (MAXRECURSION 0)     

Results:

|    name | instance | total | OriginalTotal | running_total |
|---------|----------|-------|---------------|---------------|
|   ahmed |        1 |     1 |             3 |             1 |
|   ahmed |        1 |     1 |             3 |             2 |
|   ahmed |        1 |     1 |             3 |             3 |
| mahmoud |        2 |     1 |             2 |             1 |
| mahmoud |        2 |     1 |             2 |             2 |
|   ahmed |        3 |     1 |             5 |             1 |
|   ahmed |        3 |     1 |             5 |             2 |
|   ahmed |        3 |     1 |             5 |             3 |
|   ahmed |        3 |     1 |             5 |             4 |
|   ahmed |        3 |     1 |             5 |             5 |

while loop select in SQL Server

First of all, doing:

select @serial = select serial from T1

while @serial

…

Doesn't mean that it will start to magically loop for every value of serial that T1 has, it will just assign one value of serial to your variable (if it was correctly written anyway select @serial = serial from T1).

What you want doesn't really make sense to do it in a loop; sql works in sets, and you should try to write your code accordingly. In this case, a simple JOIN should do:

SELECT T1.Serial,
       ISNULL(COUNT(T2.Serial),0) N
FROM T1
LEFT JOIN T2
    ON T1.Serial = T2.Serial
GROUP BY T1.Serial
;

How to get one output table from select in while-loop

You need an Hours table to join and group against. You can create this using a VALUES constructor:

SELECT
    Count(*) AS OrdersAmount
FROM (VALUES 
    (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23)
) AS v(Hour)

LEFT JOIN [FLX].[dbo].[NDA_SAP_Bestillinger]
 ON v.Hour = DATEPART(hour, PromisedPickupTime)
   AND PromisedPickupDate >= CAST(CAST(GETDATE() AS date) AS datetime)
   AND PromisedPickupDate < CAST(DATEADD(day, 1, CAST(GETDATE() AS date)) AS datetime)
GROUP BY v.Hour;

What is going on here is that we start with a filter by the current date.

Then we join a constructed Hours table against the hour-part of the date. Because it is on the left-side of a LEFT JOIN, we now have all the hours whether or not there is any value in that hour. We can now group by it.

---

Note the correct method to compare to a date range: a half-open interval >= AND <

Do not use YEAR\MONTH etc in join and filter predicates, as indexes cannot be used for this.

Select from another table in WHILE loop based on results

The ROTA table is very readable for us humans, but not so much for a DBMS that doesn't know that a week's Sunday is followed by the next week's Monday (or that we consider the values in the table adjacent values in the order mon-tue-wed-thu-fri-sat-sun at all).

You can transform the table into a readable form for the machine, giving the days numbers 1,2,3,4,5,6,7 for week #1, then 8,9,10,11,12,13,14 for week #2, etc. The formula to calculate a day number is then: day_number = day_of_week + (7 * (week - 1)).

The query:

with days as
(
  select 1 + (7 * (week - 1)) as daynum, mon as shift from rota
  union all
  select 2 + (7 * (week - 1)) as daynum, tue as shift from rota
  union all
  select 3 + (7 * (week - 1)) as daynum, wed as shift from rota
  union all
  select 4 + (7 * (week - 1)) as daynum, thu as shift from rota
  union all
  select 5 + (7 * (week - 1)) as daynum, fri as shift from rota
  union all
  select 6 + (7 * (week - 1)) as daynum, sat as shift from rota
  union all
  select 7 + (7 * (week - 1)) as daynum, sun as shift from rota
)
select sum(s.hours)
from days d
join shifts s on s.shift = d.shift
where d.daynum between @dayOfWeek + (7 * (@rotaWeek - 1))
                   and @dayOfWeek + (7 * (@rotaWeek - 1)) + @totalDays - 1;

Of course, if you changed your data model to match my ad-hoc days view, then the query would reduce to the mere last five lines of above query.

UPDATE:

In the request comments you say you want to continue week #5 with week #1 again. You can use a modulo operation to get from day #35 to day #1 (next_daynum = daynum % 35 + 1). But thus this becomes an iterative process where a ROTA week can even be used more than once in the calculation. Iterations are done with recursive queries in SQL:

with days as
(
  select 1 + (7 * (week - 1)) as daynum, mon as shift from rota
  union all
  select 2 + (7 * (week - 1)) as daynum, tue as shift from rota
  union all
  select 3 + (7 * (week - 1)) as daynum, wed as shift from rota
  union all
  select 4 + (7 * (week - 1)) as daynum, thu as shift from rota
  union all
  select 5 + (7 * (week - 1)) as daynum, fri as shift from rota
  union all
  select 6 + (7 * (week - 1)) as daynum, sat as shift from rota
  union all
  select 7 + (7 * (week - 1)) as daynum, sun as shift from rota
)
, cte (daynum, remaining, hours) as
(
  select d.daynum, @totalDays - 1, s.hours
  from days d
  join shifts s on s.shift = d.shift
  where d.daynum = @dayOfWeek + (7 * (@rotaWeek - 1))
  union all                     
  select d.daynum, cte.remaining - 1, cast(cte.hours + s.hours as decimal(5,1))
  from cte
  join days d on d.daynum = cte.daynum % 35 + 1
  join shifts s on s.shift = d.shift
  where cte.remaining >= 1
)
select max(hours)
from cte;

(Unfortunately, SQL Server requires the cast in the recursive CTE to match the column's exact data type.)

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=351ef091ddb80acf27e209595e2d3f9e

Is it possible to run a while loop to create dynamic select columns

You could do a PIVOT statement, but it can also be done with conditional aggregation using CASE statements.

SELECT fac.NAME AS FACILITY
     , SUM(fac.GROUP1 + fac.GROUP2) AS FAC_COUNT
     , SUM(CASE WHEN PROGRAM_ID = 1 THEN 1 ELSE 0 END) AS [PROGRAM 1]
     , SUM(CASE WHEN PROGRAM_ID = 2 THEN 1 ELSE 0 END) AS [PROGRAM 2]
     , SUM(CASE WHEN PROGRAM_ID = 4 THEN 1 ELSE 0 END) AS [PROGRAM 4]
FROM FACILITY fac
INNER JOIN PROGRAMS pgms ON fac.ID = pgms.FACILITY_ID
WHERE fac.SUBTYPE IN (3,4,5)

Clearly, this isn't going to flex if you have Programs other than 1, 2, or 4 without having to manually change the code. To accommodate that you should look at a dynamic PIVOT query discussed here.

While Loop in MySQL to select data with join and subquery

You're thinking procedurally. But SQL is, unlike most other programming languages, declarative. You describe the set of things you want and the server gives them to you.

I believe this is what you want. You don't need to muck around with a stored procedure to get this.

SELECT ms.ID, ms.`first_name`,ms.`middle_name`, ms.`last_name`, 
       mwd.exam_id, mwd.ww_score
  FROM mais_students ms
  LEFT JOIN mais_writtenworks_detail mwd
          ON mwd.student_id = ms.ID
 ORDER BY ms.ID, mwd.exam_id

You'll get a result set with one row per student per exam.

If you want to have two separate columns for two different exams, you must do a so-called pivot operation on the result set. Like this.

SELECT ID, first_name, middle_name, last_name,
       MAX(IF(exam_id = 1, ww_score, null)) exam_1,
       MAX(IF(exam_id = 2, ww_score, null)) exam_2
  FROM (
         SELECT ms.ID, ms.`first_name`,ms.`middle_name`, ms.`last_name`, 
                mwd.exam_id, mwd.ww_score
           FROM mais_students ms
           LEFT JOIN mais_writtenworks_detail mwd
                    ON mwd.student_id = ms.ID
      ) detail
 GROUP BY ID, first_name, middle_name, last_name
 ORDER BY ID

Pivoting is a notorious pain in the xxx neck in SQL.

---

Related Topics