Insert data using view

Question

Insert data using view

piu.scorpio

Old Hand

Points: 353
More actions
June 7, 2021 at 1:39 pm

#3892970

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?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

piu.scorpio Old Hand Points: 353 More actions · Answer 2

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

piu.scorpio wrote:

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101095 More actions · Answer 4

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".

piu.scorpio Old Hand Points: 353 More actions · Answer 5

@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;

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

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;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin