0

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.

enter image description here

I keep getting this error: enter image description here

4
  • 1970 isn't a date, '1970-01-01' is a date. Commented Oct 12, 2021 at 23:40
  • Yeah I just identified that. I adjust those dates but still get the error on line 6 plus a new one on line 11. ~~~~ Msg 206, Level 16, State 2, Line 6 Operand type clash: date is incompatible with int Msg 402, Level 16, State 1, Line 11 The data types nvarchar and date are incompatible in the add operator. Commented Oct 12, 2021 at 23:42
  • Your WHILE expression is equally faulty. 2050-01-01 is a numeric expression resulting in an integer value - which you attempt to compare to a date. Posting an image for something you could easily paste into your question also deserves a downvote. Commented Oct 12, 2021 at 23:47
  • SQL is optimized to work on sets. Don't think "I need to loop to <perform x> to every row" think "I need to <perform x> to all the rows." Commented Oct 13, 2021 at 0:06

2 Answers 2

1

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)
)
...

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

Sign up to request clarification or add additional context in comments.

Comments

0

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.

1 Comment

So the issues I have are (a) Why not just DATEFROMPARTS? The dateadd/datediff logic works, but it is tribal knowledge IMHO. (b) Performing those date calculations against the column means no index can be used. Shouldn't be an issue with this kind of table, but, Microsoft has over 100K employees I think. (c) This leaves out any decades not represented in the table (again, that's rarely going to be an issue when we're spanning decades, but if you want to change the range to year or month, you're probably going to want a different approach).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.