March 2, 2006 at 4:11 pm
use charts
select distinct patientno , lastname, firstname, sex dob, race, loc, docid, age, dos, count(patientno) as Visits
from charts
where Patientno in
(Select patientno
from charts
where DOS >= 2005-10-01 and age >=40)
group by patientno, lastname, firstname, sex, dob, race, loc, docid, age, dos
having count(patientno)>=3
Problems:
1) It returns duplicates
2) It displays any dates regardless of the one specified above
3) The information doesn't match the fieldname in the resultset even if it is in the DB correctly.
4) Is it supposed to count the number of time a patientno appears in the db, but it returns different numbers under VISITS. For Jill Rocker, she had 6 and 4 vists.
What is wrong?
Here is the recordset info
Patientno | lastname | firstname | dob | race | loc | docid | age | 9/27/2005 | visits | |||||||||
13801 | CAUSEY | SONNY | M | BL | ML | 26 | 61 | 1/25/2005 | 3 | |||||||||
30001 | MAY | F | BL | ML | 22 | 49 | 1/11/2005 | 6 | ||||||||||
30002 | ROCKER | JILL | F | BL | ML | 22 | 67 | 1/5/2005 | 6 | |||||||||
30002 | ROCKER | JILL | F | BL | ML | 58 | 67 | 11/7/2005 | 4 | |||||||||
30004 | COBB | TOM | M | WH | AB | 83 | 54 | 8/23/2005 | 4 | |||||||||
30006 | BROWN | HAROLD | M | BL | AB | 9 | 80 | 11/15/2005 | 5 | |||||||||
30006 | BROWN | HAROLD | M | BL | AB | 9 | 80 | 4/6/2005 | 4 | |||||||||
30025 | KING | JAQUITA | F | BL | MA | 13 | 60 | 3/21/2005 | 4 | |||||||||
30037 | BURROUGHS | BARBARA | F | BL | AB | 26 | 60 | 8/24/2005 | 3 | |||||||||
30077 | MAYES | PAM | F | WH | AB | 83 | 46 | 2/22/2005 | 4 | |||||||||
30080 | MCCLAIN | JOHN | M | BL | AB | 9 | 80 | 11/22/2005 | 3 | |||||||||
30080 | MCCLAIN | JOHN | M | BL | AB | 83 | 80 | 5/12/2005 | 4 | |||||||||
30093 | QUINNEY | MARIE | F | BL | AB | 26 | 74 | 6/15/2005 | 5 | |||||||||
30107 | SEALS | SALLY | F | BL | AB | 26 | 59 | 10/20/2005 | 4 | |||||||||
30107 | SEALS | SALLY | F | BL | MA | 77 | 59 | 11/10/2005 | 3 | |||||||||
30107 | SEALS | SALLY | F | BL | MA | 77 | 59 | 10/6/2005 | 4 | |||||||||
30124 | SNELL | MITCHEL | M | BL | AB | 83 | 62 | 8/1/2005 | 5 | |||||||||
30133 | SAMUEL | M | BL | AB | 83 | 64 | 11/17/2005 | 4 | ||||||||||
30134 | PORTIS | ROBERT | F | BL | AB | 83 | 70 | 12/8/2005 | 3 | |||||||||
30137 | HILL | MELVIN | M | BL | MA | 12 | 75 | 7/21/2005 | 7 | |||||||||
30142 | WILLIAMS | SONNY | M | BL | AB | 83 | 55 | 9/12/2005 | 4 | |||||||||
30147 | KIM | F | BL | ML | 22 | 61 | 1/3/2005 | 6 | ||||||||||
30147 | KIM | F | BL | ML | 30 | 61 | 2/9/2005 | 3 | ||||||||||
30156 | SEWER | HENRY | M | BL | MA | 12 | 76 | 4/20/2005 | 6 | |||||||||
30156 | SEWER | HENRY | M | BL | MA | 13 | 76 | 7/13/2005 | 3 | |||||||||
30156 | SEWER | HENRY | M | BL | MA | 13 | 76 | 7/20/2005 | 7 | |||||||||
30156 | SEWER | HENRY | M | BL | MA | 13 | 76 | 2/26/2005 | 3 | |||||||||
Patientno | lastname | firstname | dob | race | loc | docid | age | dos | visits |
| ||||||||
13801 | CAUSEY | SONNY | M | BL | ML | 26 | 61 | 00:00.0 | 3 |
| ||||||||
30001 | MAY | F | BL | ML | 22 | 49 | 00:00.0 | 6 |
| |||||||||
30002 | ROCKER | JILL | F | BL | ML | 22 | 67 | 00:00.0 | 6 |
| ||||||||
30002 | ROCKER | JILL | F | BL | ML | 58 | 67 | 00:00.0 | 4 |
| ||||||||
30004 | COBB | TOM | M | WH | AB | 83 | 54 | 00:00.0 | 4 |
| ||||||||
30006 | BROWN | HAROLD | M | BL | AB | 9 | 80 | 00:00.0 | 5 |
| ||||||||
30006 | BROWN | HAROLD | M | BL | AB | 9 | 80 | 00:00.0 | 4 |
| ||||||||
30025 | KING | JAQUITA | F | BL | MA | 13 | 60 | 00:00.0 | 4 |
| ||||||||
30037 | BURROUGHS | BARBARA | F | BL | AB | 26 | 60 | 00:00.0 | 3 |
| ||||||||
30077 | MAYES | PAM | F | WH | AB | 83 | 46 | 00:00.0 | 4 |
| ||||||||
30080 | MCCLAIN | JOHN | M | BL | AB | 9 | 80 | 00:00.0 | 3 |
| ||||||||
30080 | MCCLAIN | JOHN | M | BL | AB | 83 | 80 | 00:00.0 | 4 |
| ||||||||
30093 | QUINNEY | MARIE | F | BL | AB | 26 | 74 | 00:00.0 | 5 |
| ||||||||
30107 | SEALS | SALLY | F | BL | AB | 26 | 59 | 00:00.0 | 4 |
| ||||||||
30107 | SEALS | SALLY | F | BL | MA | 77 | 59 | 00:00.0 | 3 |
| ||||||||
30107 | SEALS | SALLY | F | BL | MA | 77 | 59 | 00:00.0 | 4 |
| ||||||||
30124 | SNELL | MITCHEL | M | BL | AB | 83 | 62 | 00:00.0 | 5 |
| ||||||||
30133 | SAMUEL | M | BL | AB | 83 | 64 | 00:00.0 | 4 |
| |||||||||
30134 | PORTIS | ROBERT | F | BL | AB | 83 | 70 | 00:00.0 | 3 |
| ||||||||
30137 | HILL | MELVIN | M | BL | MA | 12 | 75 | 00:00.0 | 7 |
| ||||||||
30142 | WILLIAMS | SONNY | M | BL | AB | 83 | 55 | 00:00.0 | 4 |
| ||||||||
30147 | KIM | F | BL | ML | 22 | 61 | 00:00.0 | 6 |
| |||||||||
30147 | KIM | F | BL | ML | 30 | 61 | 00:00.0 | 3 |
| |||||||||
30156 | SEWER | HENRY | M | BL | MA | 12 | 76 | 00:00.0 | 6 |
| ||||||||
30156 | SEWER | HENRY | M | BL | MA | 13 | 76 | 00:00.0 | 3 |
| ||||||||
30156 | SEWER | HENRY | M | BL | MA | 13 | 76 | 00:00.0 | 7 |
| ||||||||
30156 | SEWER | HENRY | M | BL | MA | 13 | 76 | 00:00.0 | 3 |
| ||||||||
March 2, 2006 at 4:42 pm
On first glance at the query and results the rows highlighted as duplicates are not duplicates as docid and dos are different. Also the results seem to be showing sex under the header dob.
March 2, 2006 at 6:30 pm
Two comments:
1. I agree with Keith, the rows are not duplicates, so DISTINCT won't eliminate one. If ANY column in a row is different, then it's a different row. DOCID is different for Rocker, so both rows will be returned.
2. This is wrong syntax:
DOS >= 2005-10-01
the date is a string, so enclose it in single quotes:
DOS >= '2005-10-01'
-SQLBill
March 2, 2006 at 6:32 pm
Also, is the Age column an integer or varchar/char column? If it is varchar or char (or nvarchar or nchar), you need to enclose that value in single quotes also.
-SQLBill
March 2, 2006 at 6:33 pm
Finally, if the top chart is what appears in the database....provide us a chart of how you WANT the data to be returned and maybe we can fix your query.
-SQLBill
March 3, 2006 at 11:47 am
Thanks guys! I got it to work based on the info you guys gave me.
Do you know where i can find online rules for creating queries that are not necessarily SQL server specific i guess? I have an SQL book that tells you what you can do and what you cannot like have multiple fields in a subquery or using order by, group by and having, but the book i have is lost and i can't find it.
I was trying to do this query but it did not like the having clause when i use order by. It is a variation of the above query which i did get to work.
use charts
select distinct patientno , lastname, firstname, sex, dob, race, age, count(patientno) as Visits
from charts
where patientno in
(select distinct patientno
from charts
where DOS >= '2005-10-01' and race = 'BL' and sex = 'F' and age in (40, 41, 42, 50, 51, 56, 57, 70) )
group by patientno, lastname, firstname, sex, dob, race, age
order by age, dob, patientno, lastname, firstname, sex, race
having count(patientno)>=3
Once i add the ORDER BY, I have problems with the having. the only alternative i can think of since i don't have my book that explains the rules is to do a UNION or diference or something that will get rid of the visits less than 3 because right now any number of visits display.
thanks!
March 3, 2006 at 4:29 pm
This works, with some changes to the query and criteria to test it:
declare @charts table (patientno int, lastname varchar(20), firstname varchar(20), sex char(1), race char(2), loc char(3), docid int, age int, DOS datetime, visits int)
insert @charts values('13801', 'CAUSEY', 'SONNY', 'M', 'BL', 'ML', '26', '61', '1/25/2005', '3')
insert @charts values('30001', 'TAYLOR', 'MAY', 'F', 'BL', 'ML', '22', '49', '1/11/2005', '6')
insert @charts values('30002', 'ROCKER', 'JILL', 'F', 'BL', 'ML', '22', '67', '1/5/2005', '6')
insert @charts values('30002', 'ROCKER', 'JILL', 'F', 'BL', 'ML', '58', '67', '11/7/2005', '4')
insert @charts values('30004', 'COBB', 'TOM', 'M', 'WH', 'AB', '83', '54', '8/23/2005', '4')
insert @charts values('30006', 'BROWN', 'HAROLD', 'M', 'BL', 'AB', '9', '80', '11/15/2005', '5')
insert @charts values('30006', 'BROWN', 'HAROLD', 'M', 'BL', 'AB', '9', '80', '4/6/2005', '4')
insert @charts values('30025', 'KING', 'JAQUITA', 'F', 'BL', 'MA', '13', '60', '3/21/2005', '4')
insert @charts values('30037', 'BURROUGHS', 'BARBARA', 'F', 'BL', 'AB', '26', '60', '8/24/2005', '3')
insert @charts values('30077', 'MAYES', 'PAM', 'F', 'WH', 'AB', '83', '46', '2/22/2005', '4')
insert @charts values('30080', 'MCCLAIN', 'JOHN', 'M', 'BL', 'AB', '9', '80', '11/22/2005', '3')
insert @charts values('30080', 'MCCLAIN', 'JOHN', 'M', 'BL', 'AB', '83', '80', '5/12/2005', '4')
insert @charts values('30093', 'QUINNEY', 'MARIE', 'F', 'BL', 'AB', '26', '74', '6/15/2005', '5')
insert @charts values('30107', 'SEALS', 'SALLY', 'F', 'BL', 'AB', '26', '59', '10/20/2005', '4')
insert @charts values('30107', 'SEALS', 'SALLY', 'F', 'BL', 'MA', '77', '59', '11/10/2005', '3')
insert @charts values('30107', 'SEALS', 'SALLY', 'F', 'BL', 'MA', '77', '59', '10/6/2005', '4')
insert @charts values('30124', 'SNELL', 'MITCHEL', 'M', 'BL', 'AB', '83', '62', '8/1/2005', '5')
insert @charts values('30133', 'ANDERSON', 'SAMUEL', 'M', 'BL', 'AB', '83', '64', '11/17/2005', '4')
insert @charts values('30134', 'PORTIS', 'ROBERT', 'F', 'BL', 'AB', '83', '70', '12/8/2005', '3')
insert @charts values('30137', 'HILL', 'MELVIN', 'M', 'BL', 'MA', '12', '75', '7/21/2005', '7')
insert @charts values('30142', 'WILLIAMS', 'SONNY', 'M', 'BL', 'AB', '83', '55', '9/12/2005', '4')
insert @charts values('30147', 'MOORE', 'KIM', 'F', 'BL', 'ML', '22', '61', '1/3/2005', '6')
insert @charts values('30147', 'MOORE', 'KIM', 'F', 'BL', 'ML', '30', '61', '2/9/2005', '3')
insert @charts values('30156', 'SEWER', 'HENRY', 'M', 'BL', 'MA', '12', '76', '4/20/2005', '6')
insert @charts values('30156', 'SEWER', 'HENRY', 'M', 'BL', 'MA', '13', '76', '7/13/2005', '3')
insert @charts values('30156', 'SEWER', 'HENRY', 'M', 'BL', 'MA', '13', '76', '7/20/2005', '7')
insert @charts values('30156', 'SEWER', 'HENRY', 'M', 'BL', 'MA', '13', '76', '2/26/2005', '3')
select patientno , lastname, firstname, sex, race, age, count(patientno) as Visits
from @charts
where DOS >= '2005-01-01' and race = 'BL' and sex = 'M' and age in (40, 41, 42, 50, 51, 56, 57, 70, 76, 80)
group by patientno, lastname, firstname, sex, race, age
having count(patientno)>=2
order by age, patientno, lastname, firstname, sex, race
March 6, 2006 at 8:26 am
Thanks SO VERY MUCH!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
It looks like it had to do with the order of the clauses. I needed to move the order by beneathe the having clause just as you have in your statement above!
Thanks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
March 6, 2006 at 8:30 am
Note that in my query, there is no subquery. This should help the performance.
March 6, 2006 at 10:54 am
Thanks I did notice that! thanks!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply