Avatar of geoffsweb
geoffsweb asked on

SQL between ages groups

I need to query some data and spit out the results into a data grid in VB.NET.  I have the query working that calculates the ages: SELECT Count(FLOOR(DATEDIFF(DAY, MomDOB, RecordCreateDate) / 365.25)) as ClientAge
FROM ConsentForm
.... this query might not be exact, but it works good enough....  where I'm stuck is that I need the data grouped (COUNT) in the datagrid.... Ages 10 -14, 15-19, > 20
Any help would be appreciated... thanks!
Microsoft SQL Server.NET ProgrammingASP.NET

Avatar of undefined
Last Comment
geoffsweb

8/22/2022 - Mon
kaufmed

Are you looking for something like this?
select FLOOR(DATEDIFF(DAY, MomDOB, RecordCreateDate) / 365.25)  as ClientAge, Count(1)
from ConsentForm 
group by FLOOR(DATEDIFF(DAY, MomDOB, RecordCreateDate) / 365.25)

Open in new window

kraiven

I would suggest a nice easy soltuion uitlising UNIONS

e.g. pseudo code-ish

SELECT Count(FLOOR(DATEDIFF(DAY, MomDOB, RecordCreateDate) / 365.25)) as ClientAge_10To14
FROM ConsentForm
WHERE MomDoB > (Today - 10 years) AND MomDoB <= (Today - 14 years)
UNION
SELECT Count(FLOOR(DATEDIFF(DAY, MomDOB, RecordCreateDate) / 365.25)) as ClientAge_15To19
FROM ConsentForm
WHERE MomDoB > (Today - 15 years) AND MomDoB <= (Today - 19 years)
UNION
SELECT Count(FLOOR(DATEDIFF(DAY, MomDOB, RecordCreateDate) / 365.25)) as ClientAge_Over20
FROM ConsentForm
WHERE MomDoB > (Today - 20 years)

you might be able to use some of the ranking functions but this seems the easiest method given your example.
Scott Pletcher

Add an outer query above/around the one that computes age:

SELECT
    SUM(CASE WHEN ClientAge BETWEEN 10 AND 14 THEN 1 ELSE 0 END) AS [10-14],
    SUM(CASE WHEN ClientAge BETWEEN 15 AND 19 THEN 1 ELSE 0 END) AS [15-19],
    SUM(CASE WHEN ClientAge >= 20 THEN 1 ELSE 0 END) AS [>=20]
FROM
(
SELECT Count(FLOOR(DATEDIFF(DAY, MomDOB, RecordCreateDate) / 365.25)) as ClientAge
FROM ConsentForm
) AS derivedTable
Your help has saved me hundreds of hours of internet surfing.
fblack61
Patrick Matthews

I would create a separate table that defines the age buckets.  In that table, BucketStart is the lower bound, inclusive, and BucketEnd is the upper bound, exclusive:

tblBuckets

BucketID  BucketStart  BucketEnd  Label
---------------------------------------
1         0            5          0 To 4
2         5            10         5 To 9
3         10           15         10 To 14
4         15           20         15 To 19

etc.

Open in new window


Then, write your query like so:

SELECT b.Label, COUNT(b.Label)
FROM tblBuckets b LEFT JOIN
    SomeTable s ON (DATEDIFF(DAY, s.MomDOB, s.RecordCreateDate) / 365.25) >= b.BucketStart AND
        (DATEDIFF(DAY, s.MomDOB, s.RecordCreateDate) / 365.25) < b.BucketEnd
GROUP BY b.Label

Open in new window

Cluskitt

SELECT COUNT(CASE WHEN Ages Between 10 AND 14 THEN 1 ELSE 0 END) '10-14',
       COUNT(CASE WHEN Ages Between 15 AND 19 THEN 1 ELSE 0 END) '15-19',
       COUNT(CASE WHEN Ages>20 THEN 1 ELSE 0 END) '>20'
FROM ........
etc.
However, note that age 20 won't be counted in this case, as per your example. To be counted, use >=
ASKER
geoffsweb

kaufmed: I need to have it grouped by the age groups: Ages 10 -14, 15-19, > 20
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Patrick Matthews

>>kaufmed: I need to have it grouped by the age groups: Ages 10 -14, 15-19, > 20

Adapting my approach above...

tblBuckets

BucketID  BucketStart  BucketEnd  Label
---------------------------------------
1         10           15         10 To 14
2         15           20         15 To 19
3         20           99         20+

etc.

Open in new window


SQL statement stays the same.
raulggonzalez

Hi,

Can you post the current result of your query?

Cheers
ASKER
geoffsweb

matthewspatrick: I went with your approach.  The SQL executes, but I don't get the results I'm looking for.  It shows a count of 1 in each of the 3 age groups.  And there should only be 1 in one of the groups and about 12 in another group. SQL output
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Patrick Matthews

Seems to be working for me.  I used this to generate some test data:

CREATE TABLE tblBuckets (BucketID int IDENTITY, BucketStart decimal (10, 6), BucketEnd decimal (10, 6), Label varchar(20))
INSERT INTO tblBuckets (BucketStart, BucketEnd, Label) VALUES (10, 15, '10 to 15')
INSERT INTO tblBuckets (BucketStart, BucketEnd, Label) VALUES (15, 20, '15 to 20')
INSERT INTO tblBuckets (BucketStart, BucketEnd, Label) VALUES (20, 99, '20+')

CREATE TABLE SomeTable (MomDOB datetime, RecordCreatedDate datetime)
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1952-01-05', '1969-02-04')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1969-07-26', '2003-06-17')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1969-03-29', '1977-04-29')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1967-01-31', '1985-01-31')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1950-01-09', '1989-04-30')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1961-07-21', '1981-04-16')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1970-08-25', '2003-04-27')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1953-03-04', '1989-08-23')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1968-07-17', '2003-06-27')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1969-03-01', '1986-09-23')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1970-06-12', '1997-04-15')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1962-05-22', '1992-04-16')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1959-09-16', '1984-01-17')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1950-11-17', '1989-02-02')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1952-11-07', '1960-09-23')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1955-04-15', '1985-03-02')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1961-10-27', '1972-05-01')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1951-04-04', '1985-03-20')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1952-06-28', '1976-02-25')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1956-03-06', '1989-05-29')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1966-02-26', '1980-07-07')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1969-03-11', '1985-05-06')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1955-05-29', '1972-12-15')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1956-10-24', '1981-05-28')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1951-01-26', '1980-02-27')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1965-03-22', '1987-10-19')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1966-12-03', '1989-12-15')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1958-02-04', '1993-04-22')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1968-03-25', '1995-10-23')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1957-08-09', '1978-11-14')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1962-03-01', '1992-11-17')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1953-05-10', '1992-07-07')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1964-03-17', '1986-10-11')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1957-04-05', '1987-10-07')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1969-10-19', '1978-03-17')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1967-07-02', '2004-11-29')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1963-09-05', '1982-09-05')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1956-05-03', '1984-11-08')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1956-08-09', '1972-10-26')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1953-12-14', '1967-05-16')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1956-01-10', '1993-02-17')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1952-10-31', '1968-11-18')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1963-03-31', '1990-05-27')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1964-09-28', '1989-04-16')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1959-06-17', '1972-09-13')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1959-01-20', '1996-01-12')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1954-01-24', '1983-07-05')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1958-11-30', '1995-07-11')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1967-01-14', '1981-01-26')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1960-01-31', '1983-12-14')

Open in new window




I then ran this query (same as before):



SELECT b.Label, COUNT(b.Label) AS [Count]
FROM tblBuckets b LEFT JOIN
    SomeTable s ON (DATEDIFF(DAY, s.MomDOB, s.RecordCreatedDate) / 365.25) >= b.BucketStart AND
        (DATEDIFF(DAY, s.MomDOB, s.RecordCreatedDate) / 365.25) < b.BucketEnd
GROUP BY b.Label

Open in new window




I got the following results, as expected:



Label         Count
-------------------
10 to 15       5
15 to 20       9
20+           33

Open in new window

Patrick Matthews

Note that in those results above, I deliberately set up three of the rows to have a result < 10.  That is why the counts sum to 47 and not 50...
Scott Pletcher

That's confusing to me.  Is age 15 in the 10-15 or the 15-20??
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Pletcher

Well, I know *looking at the code* that it's in the latter, but looking at only the output itself, how is one to know?
Patrick Matthews

Excellent point, Scott.  Amending...


Sample data:



CREATE TABLE tblBuckets (BucketID int IDENTITY, BucketStart decimal (10, 6), BucketEnd decimal (10, 6), Label varchar(20))
INSERT INTO tblBuckets (BucketStart, BucketEnd, Label) VALUES (10, 15, '10 to 14')
INSERT INTO tblBuckets (BucketStart, BucketEnd, Label) VALUES (15, 20, '15 to 19')
INSERT INTO tblBuckets (BucketStart, BucketEnd, Label) VALUES (20, 99, '20+')

CREATE TABLE SomeTable (MomDOB datetime, RecordCreatedDate datetime)
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1952-01-05', '1969-02-04')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1969-07-26', '2003-06-17')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1969-03-29', '1977-04-29')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1967-01-31', '1985-01-31')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1950-01-09', '1989-04-30')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1961-07-21', '1981-04-16')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1970-08-25', '2003-04-27')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1953-03-04', '1989-08-23')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1968-07-17', '2003-06-27')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1969-03-01', '1986-09-23')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1970-06-12', '1997-04-15')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1962-05-22', '1992-04-16')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1959-09-16', '1984-01-17')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1950-11-17', '1989-02-02')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1952-11-07', '1960-09-23')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1955-04-15', '1985-03-02')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1961-10-27', '1972-05-01')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1951-04-04', '1985-03-20')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1952-06-28', '1976-02-25')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1956-03-06', '1989-05-29')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1966-02-26', '1980-07-07')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1969-03-11', '1985-05-06')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1955-05-29', '1972-12-15')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1956-10-24', '1981-05-28')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1951-01-26', '1980-02-27')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1965-03-22', '1987-10-19')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1966-12-03', '1989-12-15')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1958-02-04', '1993-04-22')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1968-03-25', '1995-10-23')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1957-08-09', '1978-11-14')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1962-03-01', '1992-11-17')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1953-05-10', '1992-07-07')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1964-03-17', '1986-10-11')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1957-04-05', '1987-10-07')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1969-10-19', '1978-03-17')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1967-07-02', '2004-11-29')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1963-09-05', '1982-09-05')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1956-05-03', '1984-11-08')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1956-08-09', '1972-10-26')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1953-12-14', '1967-05-16')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1956-01-10', '1993-02-17')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1952-10-31', '1968-11-18')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1963-03-31', '1990-05-27')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1964-09-28', '1989-04-16')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1959-06-17', '1972-09-13')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1959-01-20', '1996-01-12')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1954-01-24', '1983-07-05')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1958-11-30', '1995-07-11')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1967-01-14', '1981-01-26')
INSERT INTO SomeTable (MomDOB, RecordCreatedDate) VALUES ('1960-01-31', '1983-12-14')

Open in new window





Query stays the same:



SELECT b.Label, COUNT(b.Label) AS [Count]
FROM tblBuckets b LEFT JOIN
    SomeTable s ON (DATEDIFF(DAY, s.MomDOB, s.RecordCreatedDate) / 365.25) >= b.BucketStart AND
        (DATEDIFF(DAY, s.MomDOB, s.RecordCreatedDate) / 365.25) < b.BucketEnd
GROUP BY b.Label

Open in new window




Output:



Label         Count
-------------------
10 to 14       5
15 to 19       9
20+           33

Open in new window

ASKER
geoffsweb

matthewspatrick: I really appreciate the help.  I think I have figured out why my output isn't working.  My table has the MomDOB set us as a varchar().  I didn't set this up and at this point I can't change it.  Can a conversion be done within the query to allow the DateDiff function to work correctly?  I took your query code and ran it to produce the tables exactly as you did it and everything works perfect.  But then if I change the MomDOB from datetime to varchar in the table to look like mine, it doesn't produce the correct output.  Hopefully there is some way around this. Thanks!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Patrick Matthews

>>My table has the MomDOB set us as a varchar().

Yet another lesson on the importance of using the right data types :)

Use the CONVERT function to convert the varchar value to a datetime value.  The exact syntax will depend on the format being used.  For example, if the values in MomDOB use the mm/dd/yyyy convention, try:

SELECT b.Label, COUNT(b.Label) AS [Count]
FROM tblBuckets b LEFT JOIN
    SomeTable s ON (DATEDIFF(DAY, CONVERT(datetime, s.MomDOB, 101), s.RecordCreatedDate) / 365.25) >= b.BucketStart AND
        (DATEDIFF(DAY, CONVERT(datetime, s.MomDOB, 101), s.RecordCreatedDate) / 365.25) < b.BucketEnd
GROUP BY b.Label

Open in new window


If that column has some other convention in use, then look up the entry in Books Online for the appropriate formatting code (that's the third argument in CONVERT).

Now, you will get into trouble if there is a mix of conventions in use :)

If RecordCreatedDate is also [n][var]char, then you would want to do a similar conversion there (although I am somewhat surprised that there was no implicit conversion already...).

If there is any chance that MomDOB will contain entries that are not convertible to datetime, then you may want to try:

SELECT b.Label, COUNT(b.Label) AS [Count]
FROM tblBuckets b LEFT JOIN
    SomeTable s ON (DATEDIFF(DAY, CONVERT(datetime, s.MomDOB, 101), s.RecordCreatedDate) / 365.25) >= b.BucketStart AND
        (DATEDIFF(DAY, CONVERT(datetime, s.MomDOB, 101), s.RecordCreatedDate) / 365.25) < b.BucketEnd
WHERE ISDATE(s.MomDOB) = 1
GROUP BY b.Label

Open in new window



Anthony,

Am I missing anything?  :)

Patrick
ASKER
geoffsweb

The conversion works great, thanks!  One last thing, I think..... I've noticed that if there's not a record that fits into one of the age groups, it still puts a 1 for the count. ???
Patrick Matthews

Please explain, I am not sure I'm following you...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
geoffsweb

I took out all but 3 records in your SomeTable table.  And all 3 records have a DOB that make them 20+ years from the RecordCreateDate.  As you can see from the screenshot, the results show the 3 records, but they still put 1 record in for the other two age groups.  I played around with it and it always shows 1 in an age group that doesn't have any. displaying counts.... displaying counts....
Patrick Matthews

OK, let me see if I can replicate this...
ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
geoffsweb

PERFECT!!  Thanks for all the help... have a friend buy you a drink from me!  :)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck