How to write JPA Criteria API Query corresponding to this native sql?

247 Views Asked by At

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];
0

There are 0 best solutions below