Declare Loop SQL to count values between dates-CodePudding

I have a database of employees and I am trying to count the number of employees I have between specific dates in 10 year increments. I have my syntax incorrect but I am not very proficient at identifying why this will not run.

I need the query to produce the following:

12 employees were hired between 1970 and 1980

19 employees were hired between 1980 and 1990

etc.

I keep getting this error:

CodePudding user response：

First, you're using malformed date literals.

1970 isn't a date, '1970-01-01' is a date.
2050-01-01 isn't a date, '2050-01-01' is a date.

Second, you don't need a loop at all. Instead round the emp_hiredate down to the preceding decade start, and then group by it...

SELECT
  DATEADD(YEAR, (DATEDIFF(YEAR, '1900-01-01', emp_hiredate)/10)*10, '1900-01-01')  AS decade_start,
  COUNT(*)
FROM
  lgemployee
GROUP BY
  DATEADD(YEAR, (DATEDIFF(YEAR, '1900-01-01', emp_hiredate)/10)*10, '1900-01-01')

Or even just use (DATEDIFF(YEAR, '1900-01-01', emp_hiredate)/10)*10 to identify the decade.

CodePudding user response：

Why are you looping?

DECLARE @StartYear date = '19700101',
        @EndYear   date = '20500101';

;WITH Years(y) AS
( 
  SELECT @StartYear
  UNION ALL
  SELECT DATEADD(YEAR, 10, y) 
    FROM Years 
    WHERE y < @EndYear
)
SELECT Years.y, EmployeesHired = COUNT(e.emp_num)
  FROM Years 
  LEFT OUTER JOIN dbo.lgemployee AS e
     ON e.emp_hire_date >= Years.y
    AND e.emp_hire_date <  DATEADD(YEAR, 10, Years.y)
    GROUP BY Years.y;

If you really want to start with ints, you could say:

DECLARE @StartYear int = 1970,
        @EndYear   int = 2050;

;WITH Years(y) AS
(
  SELECT DATEFROMPARTS(@StartYear, 1, 1)
  UNION ALL
  SELECT DATEADD(YEAR, 10, y) 
    FROM Years
    WHERE y < DATEFROMPARTS(@EndYear, 1, 1)
)
...

Example db<>fiddle

Lots of general date tips here, especially why you want to avoid BETWEEN:

Dating Responsibly