I have one birthdate field as date type if want to group all uses data by age range i have got follwing sql code to perform solution but i need criteria api query corresponding to this. How to write criteria query for the following code??
WITH AgeData as
(
SELECT [Username],
[Birthdate],
DATEDIFF(YEAR, [Birthdate], GETDATE()) AS [AGE]
FROM @table
),
GroupAge AS
(
SELECT [Username],
[Birthdate],
[Age],
CASE
WHEN AGE < 30 THEN 'Under 30'
WHEN AGE BETWEEN 31 AND 40 THEN '31 - 40'
WHEN AGE BETWEEN 41 AND 50 THEN '41 - 50'
WHEN AGE > 50 THEN 'Over 50'
ELSE 'Invalid Birthdate'
END AS [Age Groups]
FROM AgeData
)
SELECT COUNT(*) AS [AgeGrpCount],
[Age Groups]
FROM GroupAge
GROUP BY [Age Groups];