June 7, 2021 at 1:39 pm
Hello!
I have to create a view called Customer_Age. I need to pick distinct birth year values from Customer table. Then using that I need to calculate Age as per today. And based on Age, I need to calculate Age group, Group Name etc. The data should look like:
How do I create a view which can generate this information?
June 7, 2021 at 2:05 pm
As you have not provided any DDL or sample data, and your sample results do not appear to include a single reference to your customer data, I had to guess:
WITH Tally (n)
AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
(
VALUES
(0)
,(0)
,(0)
,(0)
,(0)
,(0)
,(0)
,(0)
,(0)
,(0)
) a (n)
CROSS JOIN
(
VALUES
(0)
,(0)
,(0)
,(0)
,(0)
,(0)
,(0)
,(0)
,(0)
,(0)
) b (n))
,GroupInfo (StartAge, EndAge, AgeGroup, GroupName)
AS (SELECT *
FROM
(
VALUES
(0, 10, '0-10 years', 'Kids')
,(11, 12, '11-20 years', 'Kids')
,(13, 19, '11-20 years', 'Teenager')
,(20, 20, '11-20 years', 'Young Adult')
) GroupInfo (StartAge, EndAge, AgeGroup, GroupName) )
SELECT TOP (21)
calcs.BirthYear
,Age = YEAR(GETDATE()) - calcs.BirthYear
,g.AgeGroup
,g.GroupName
FROM Tally
CROSS APPLY
(SELECT BirthYear = n + 1921, Age = YEAR(GETDATE()) - n - 1921) calcs
JOIN GroupInfo g
ON calcs.Age
BETWEEN g.StartAge AND g.EndAge
ORDER BY Tally.n DESC;
June 7, 2021 at 2:38 pm
Hi @SSC Guru
I Just get a list of Distinct birth years from Customer table. So then based on that list I need to further calculate the other values
Select distinct Birth_year from Company.Customer
June 7, 2021 at 2:48 pm
Hi @SSC Guru
I Just get a list of Distinct birth years from Customer table. So then based on that list I need to further calculate the other values
Select distinct Birth_year from Company.Customer
OK, you should easily be able to modify the query I provided to do that.
June 7, 2021 at 3:38 pm
You need more than just year to accurately calculate age. Some people born in 2011 are 10 years old, but some are only 9 years old.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 7, 2021 at 4:07 pm
@Phil Parkin>
hi tried including it but then Calcs.Age cant be calculated.
CROSS APPLY(Select distinct Birth_year from Company.Customer) calcs
JOIN GroupInfo g
ON calcs.Age
BETWEEN g.StartAge AND g.EndAge
ORDER BY Tally.n DESC;
June 7, 2021 at 4:25 pm
Try changing this part
(SELECT BirthYear = n + 1921, Age = YEAR(GETDATE()) - n - 1921) calcs
JOIN GroupInfo g
ON calcs.Age
BETWEEN g.StartAge AND g.EndAge
ORDER BY Tally.n DESC;
to this
(SELECT BirthYear = n + 1921, Age = YEAR(GETDATE()) - n - 1921) calcs
JOIN GroupInfo g
ON calcs.Age
BETWEEN g.StartAge AND g.EndAge
WHERE calcs.BirthYear in (Select distinct Birth_year from Company.Customer)
ORDER BY Tally.n DESC;
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy