SQL select items between LAG and LEAD using as range

Question

Is it possible to select and sum items from a table using Lag and lead from another table as range as below.

SELECT @Last  = MAX(ID) from [dbo].[#Temp] 
     select  opl.Name  as [Age Categories] ,  
     ( SELECT count([dbo].udfCalculateAge([BirthDate],GETDATE())) 
       FROM    [dbo].[tblEmployeeDetail] ed  
       inner join  [dbo].[tblEmployee] e 
       on   ed.EmployeeID = e.ID 
      where    convert(int,[dbo].udfCalculateAge(e.[BirthDate],GETDATE()))
             between LAG(opl.Name) OVER (ORDER BY opl.id)   
                 and (CASE opl.ID WHEN   @Last  THEN '100'  ELSE  opl.Name End ) 
     )  as Total
FROM [dbo].[#Temp] opl 

tblEmployee contains the employees and their dates of birth

  INSERT INTO @tblEmployees VALUES
(1, 'A', 'A1', 'A', '1983/01/02'),
(2, 'B', 'B1', 'BC', '1982/01/02'),
(3, 'C', 'C1', 'JR2', '1982/10/11'),
(4, 'V', 'V1', 'G', '1990/07/12'),
(5, 'VV', 'VV1', 'J', '1992/06/02'),
(6, 'R', 'A', 'D', '1982/05/15'),
(7, 'C', 'Ma', 'C', '1984/09/29')

Next table is a temp table which is created depending on the ages enter by user eg "20;30;50;60" generates a temp table below , using funtion split

 select * FROM  [dbo].[Split](';','20;30;50;60') 

Temp Table

pn  s
1   20
2   30
3   50
4   60

Desired output as below, though column Age Categories can be renamed in a data-table in C#. l need the total columns to be accurate on ranges.

Age Categories      Total
up to 20            0
21 - 30             2
31 - 50             5
51 - 60             0

Answer 1

Something along these lines should work for you:

declare @tblEmployees table(
ID int,    
FirstNames varchar(20),       
Surname varchar(20),     
Initial varchar(3),    
BirthDate date)

INSERT INTO @tblEmployees VALUES
(1, 'A', 'A1', 'A', '1983/01/02'),
(2, 'B', 'B1', 'BC', '1982/01/02'),
(3, 'C', 'C1', 'JR2', '1982/10/11'),
(4, 'V', 'V1', 'G', '1990/07/12'),
(5, 'VV', 'VV1', 'J', '1992/06/02'),
(6, 'R', 'A', 'D', '1982/05/15'),
(7, 'C', 'Ma', 'C', '1984/09/29')

declare @temp table
(id int identity,
age int)

INSERT INTO @temp 
SELECT cast(item as int) FROM dbo.fnSplit(';','20;30;50;60')

declare @today date = GetDate()
declare @minBirthCutOff date = (SELECT DATEADD(yy, -MAX(age), @today) FROM @temp)
declare @minBirth date = (SELECT Min(birthdate) from @tblEmployees)
IF @minBirth < @minBirthCutOff
    BEGIN
        INSERT INTO @temp VALUES (100)
    end
SELECT COALESCE(CAST((LAG(t.age) OVER(ORDER BY t.age) + 1) as varchar(3)) 
+ ' - ','Up to ') 
       + CAST(t.age AS varchar(3)) AS [Age Categories], 
       COUNT(e.id) AS [Total] FROM @temp t
LEFT JOIN
(SELECT te.id, 
       te.age, 
       (SELECT MIN(age) FROM @temp t WHERE t.age > te.age) AS agebucket
FROM (select id, 
      dbo.udfCalculateAge(birthdate,@today) age from @tblEmployees) te) e
ON e.agebucket = t.age
GROUP BY t.age ORDER BY t.age

Result set looks like this:

Age Categories  Total
Up to 20    0
21 - 30 2
31 - 50 5
51 - 60 0

For future reference, particularly when asking SQL questions, you will get far faster and better response, if you provide much of the work that I have done. Ie create statements for the tables concerned and insert statements to supply the sample data. It is much easier for you to do this than for us (we have to copy and paste and then re-format etc), whereas you should be able to do the same via a few choice SELECT statements!

Note also that I handled the case when a birthdate falls outside the given range rather differently. It is a bit more efficient to do a single check once via MAX than to complicate your SELECT statement. It also makes it much more readable.

Thanks to HABO for suggestion on GetDate()