How to fetch the records from the SQL Server Database using loop

Question

I have an employee table, I need to fetch the details of employees based on course 1 joining date for the last 12 months from the specified month.

Table Design

+-----------+-------------+
| Field     | Type        |   
+-----------+-------------+    
| emp_name  | varchar(30) | 
| join_date | date        | 
| emp_id    | int(5)      | 
| c1_date   | date        | 
+-----------+-------------+

I want to show the output below. c1_date joining date for the last 12 months' employee details.

For example, today date is 7/1/2023

first month:

emp_id , emp_name , joining_date , c1_date
1         xxx        21-7-7         12-12-2022
2         yyy        22-2-1         1-12-2022

second month:

emp_id , emp_name , joining_date , c1_date
11         abc        21-7-7         12-11-2022
21         cde        22-2-1         1-11-2022

Like above last 12 months i want to generate, i have created a procedure for this requirement, but i am getting all records without c1_date -1, -2, -3..

CREATE PROCEDURE [dbo].[StudentReport] 
    @FromDate datetime =null
AS 

DECLARE @count INT;
SET @count = -1;

WHILE @count>= -12
BEGIN
   SELECT *
    FROM [Student]
    WHERE [Group Type] ='AIS-140' AND
    [c1_date] BETWEEN DATEADD(MONTH, @count, GETDATE()) AND GETDATE()
   SET @count = @count - 1;
END;

Help me figure out where I am committing the mistake.

Pet Peeve ... ambiguous dates I suspect the loop is not required and should probably be avoided. A simple join to a calendar table or even an ad-hoc numbers table — John Cappelletti, Jan 07 '23 at 14:59

score 0 · Answer 1 · answered Jan 07 '23 at 14:59

This does not work, because if e.g. GETDATE()='2023/01/07' then DATEADD(MONTH, -1, GETDATE()) is '2022/12/07'. So, you will not get the full months. See How can I select the first day of a month in SQL?. And also, looping in SQL is generally not a good idea. See; Optimizing SQL: Removing queries inside loops.

How to fetch the records from the SQL Server Database using loop

1 Answers1