Re: You might need to scratch your head

Whoa Imran!

Atif is right. It seems like your query did skip out one year! But an interesting attempt, really.

Atif,

It seems like techies weren't able to crack it. Here is your solution;

SELECT A.*
FROM(SELECT ((SELECT MAX(Hire_t) FROM ABC) + 1) - ROW_NUMBER() OVER
(ORDER BY GETDATE() DESC) AS DT
FROM sysobjects WITH (NOLOCK)) A
WHERE A.DT BETWEEN
(SELECT MIN(Hire_t) FROM ABC)
AND (SELECT MAX(Hire_t) FROM ABC)
AND A.DT NOT IN (SELECT HIRE_T FROM ABC)

It ain't as simple as it sound!

Re: You might need to scratch your head

Atif, it's seems like Imran has cracked this one!

Here we are with the solution;

SELECT Hire_T + 1
FROM ABC AS AC
WHERE NOT EXISTS
(
SELECT *
FROM ABC AS H
WHERE H.Hire_T = (AC.Hire_T + 1)
AND H.Number_Of_Employees_Hired > 0
)
AND Hire_T + 1 < (SELECT MAX(Hire_T) from ABC)


Can anyone else achieve the desired output in an alternate way?

You might need to scratch your head!

We have Table ABC;

Year_T         Number_Of_Employees_Hired
1995              7
1997              9
1999              2
2000              14
2003              5
2005              6

This table contains data of years and the number of employees hired in a company in the respective year.


Problem is to find out in which years the company did not hire any one.
(Means; we need years, 1996, 1998,2001,2002 and 2004).

Conditions;

We have to use a in-line SQL query only. No stored procedures are allowed. Also, we cannot alter this table or view. FYI, both column types are integers

Submitted by Atif Fasihi