Query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query help

Author

Topic

Baz-23
Starting Member

3 Posts

Posted - 2010-09-27 : 07:07:05

Hey guys,

I'm a newbie to here and to SQL and am after some help. I've been going through some exercises to get a grip on SQL and thought i was on top of things, however I've hit a wall and am stuck. Below is a sample database i uploaded to let you play around with and to understand what I'm trying to do.

http://uploading.com/files/9ca8c58f/SQL.txt/ (change the file extension to mdb)

Ok onto the question, I need to list admission_id, admission_date, first and last name as well as the number of visits and average temp of each admission. Also, For patients without observations i have to list the number as 0, and the average as '-'. The question also mentions a union is needed. The Union and the last part of the question is where I'm stuck

here is the code i have-

SELECT DISTINCT admission.admission_id, admission.admission_date, person.first_name, person.Surname, COUNT (*) AS Temp_observations, AVG (observ_value) AS Average_temp
FROM observation, admission, person
WHERE observ_type = 'Temp' 
AND observation.admission_id = admission.admission_id 
AND person.person_id = admission.patient_id
GROUP BY admission.admission_id, admission_date, person.first_name, person.Surname, observ_value;
UNION
SELECT Temp_observations WHERE NULL = '0'
FROM observation

This code doesn't work though and whilst i know why, i'm unsure how to approach it.

Any help would be great! :)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-27 : 09:27:30

I'm just guessing here since I don't click on links in forums


SELECT  a.admission_id
      , a.admission_date
      , p.first_name
      , p.Surname
      , ISNULL(COUNT (o.admission_id),0)           AS Temp_observations
      , ISNULL(AVG (observ_value),0)  AS Average_temp
FROM observation o 
LEFT JOIN admission a  ON o.admission_id   = a.admission_id 
LEFT JOIN  person p    ON a.patient_id     = p.person_id                             
WHERE observ_type = 'Temp'  
GROUP BY a.admission_id, admission_date, p.first_name, p.Surname, observ_value;

Jim

Everyday I learn something that somebody else already knew

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-27 : 09:33:20

Baz-23.

It's not good form to post stuff for people to download unless asked for.........

It's better to simply post the DDL of the tables involved and some sample data-scripts.

Regards,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Baz-23
Starting Member

3 Posts

Posted - 2010-09-27 : 09:39:22

Thanks for that but I'm still not having any luck. I'm wanting to use a Union in the query. Also that query doesn't appear to work. I'm using Access for the SQL query if that makes any difference.

I understand you not wanting to click the link but i can assure you its virus free.

Baz-23
Starting Member

3 Posts

Posted - 2010-09-27 : 09:42:40

Yea, sorry. Was in a rush and thought this was easier. Didn't mean to scare people or seem rude. I apologise for that.

Tables im using are

Admission_id	Patient_id	Admission_date	Expected_op	Admitted_by	Ward_code	Discharge_date
205	101	2/02/1992	HB	114	P	16/02/1992
274	109	1/09/1994	AP	114	P	9/09/1994
275	101	1/09/1994	HB	115	L	
276	106	24/08/1994	LA	114	P	5/09/1994
277	114	2/09/1994	AP	115	P	10/09/1994
278	105	1/01/1995	HB			
279	113	3/09/1994	TS	115	F	6/09/1994
280	117	3/09/1994	AP	115	F	
281	104	5/09/1994	LA	115	F	6/09/1994
282	104	7/09/1994	AP	114	P	


Admission_id	Observ_date	Observ_time	Observ_type	Observ_value	Staff_id
205	2/02/1992	1500	Temp	38	114
274	4/09/1994	0601	Temp	39	116
275	1/09/1994	1400	Pulse	74	115
275	1/09/1994	1400	Temp	38	115
275	3/09/1994	1800	Temp	40	116
275	3/09/1994	2200	Temp	40	116
275	4/09/1994	0610	Pulse	82	116
275	4/09/1994	0610	Temp	38	116
277	4/09/1994	0600	Temp	39	115


Person_id	Surname	First_name	Sex	Birth_date	Street	Town	Postcode	Next_of_kin
101	Black	Barry	M	31/12/1959	11 High St.	Cooma	2630	102
102	Black	Mary	F	1/01/1963	11 High St.	Cooma	2630	
103	Strathclyde	Albert	M	5/05/1955	3 The Mews	Hawthorn	4171	104
104	Strathclyde	Alice	F	7/07/1955	3 The Mews	Hawthorn	4171	103
105	Green	Gill	F	6/06/1966	124 Main St.	Young	2594	106
106	Green	Graham	M	4/04/1967	124 Main St.	Young	2594	105
107	Gray	Lesley	F	2/02/1972	130 Main St.	Young	2594	109
109	Gray	John	M	4/04/1972	130 Main St.	Young	2594	107
110	Samuelson	Thomas	M	1/01/1964	17 The Mews	Hawthorn	4171	
111	Abrahams	Mary	F	5/05/1967	21 The Esplanade	Ivanhoe	2878	
112	Aumann	Monica	F	5/05/1955	29 The Esplanade	Ivanhoe	2878	
113	Brown	Melissa	F	8/08/1984	11 East St.	Cooma	2630	
114	Napier	Mary	F	1/01/1971	163 New Rd.	Henty	2658	
115	Nelson	Nigel	M	2/02/1972	165 Young Rd.	Temora	2666	
116	Newman	Olive	F	3/03/1973	21 Olympic Way	Henty	2658	
117	Gray	Lesley	M	31/12/1989	130 Main St.	Young	2594	107

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-27 : 10:09:18

This is what I'd try as a first stab


SELECT
	ad.[Admission_id] AS [Admission ID]
	, ad.[Admission_date] AS [Admission Date]
	, pat.[First_name] AS [First name]
	, pat.[Surname] AS [Last Name]
	, COUNT(vis.[Admission_id]) AS [Visits]
	, ISNULL(CAST(
		AVG(CASE vis.[Observ_type]
			WHEN 'Temp' THEN vis.[Observ_value]
			ELSE NULL
		END)
		AS VARCHAR(20)
		, '-'
		) AS [Average Temp]
FROM
	Admission AS ad
	JOIN Patient AS pat ON pat.[Person_Id] = ad.[Person_Id]
	LEFT JOIN Visits AS vis ON vis.[Admission_id] = ad.[Admission_id]
GROUP BY
	ad.[Admission_id]
	, ad.[Admission_date]
	, pat.[First_name]
	, pat.[Surname]

I've made some assumptions:

The first table posted is called Admission
The second table posted is called Patient
The third table posted is called Visits

-- IF you are going to post tables and data please post them as DDL -- so instead of dumping that output you posted before do something like


CREATE TABLE #Admission (
    [Admission_id] INT
    , ......

AND


INSERT Admission
SELECT .....

Makes it a lot easier for us to help. Especially as we know what datatypes we are dealing with.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Britt
Starting Member

1 Post

Posted - 2011-09-18 : 19:57:51

Hi just wondering what result you got for the question about using the same data for :

For each admission list admission_id, admission_date, surname, first_name, the number of the Temp observations and the average Temp value taken durinf the admission. For patients without observations list the number as 0, and the average as '-'".

Im currently doing a question similar and am struggling a tad.
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources