This is a tough one. NEED help quick.

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

SQL Server 2005 Forums

Transact-SQL (2005)

This is a tough one. NEED help quick.

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2007-06-27 : 08:59:10

The following table is counts of the patients that are currently in these statuses. For example, there are 4 people in Triage/Green
[url]http://www.helixpoint.com/sql/TriageStatusGrid.jpg[/url]

If you look at a screen shot of the tables below, Red/Yellow/Green... These are in the Priority table. Red Being "Immediate or ID of 1
[url]http://www.helixpoint.com/sql/db.gif[/url]

Now the grid and the db do not match...but here is a scenario from the db screen shot..

Patient 1, 2, 3, and 4 are currently in Sector 2 (transport)

Patient 1, 3, and 4 are currently in Priority 1(Immediate)

Here is a kicker. Patient 4 can not be counted because he has a dischargeDateTime in the Patient table.

So here are the counts I need to get:

So there are 3 patients in the transport column ( Patient 1, 2, 3) “4 has been discharged”
2 patients in the immediate/Red column ( Patient 1, 3) “4 has been discharged”

I would need to return a number 2 for the Transport/Red column

How can I do this sql? Would I do this in multiple sql calls? Can you give me an example?

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-27 : 09:30:35

What's the issue?

Just left join to the discharged patients and exclude those.
If you post what you have someone will give you a hint as to where it is having problems.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2007-06-27 : 09:38:39

I tried to write the sql, but I just can seem to write the sql that pulls back one number. The combination of 2 tables and multiple entries for those tables are throwing me

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-06-27 : 09:41:29

quote:
Originally posted by helixpoint

I tried to write the sql, but I just can seem to write the sql that pulls back one number. The combination of 2 tables and multiple entries for those tables are throwing me

Again, post what you have so far.

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2007-06-27 : 09:58:20

Sorry guys. I am a beginner. I am not even sure how to begin. Figures I would get a project like this:(

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2007-06-27 : 10:04:56

Here is my shot, but it does not work and does not give the count

SELECT Patient.PatientID, Patient.IncidentID, Patient.TagNum, Patient.Age, Patient.AgeUnitID, Patient.Sex, Patient.Contaminated, Patient.Decontaminated,
Patient.Assessor, Patient.DischargeDateTime, Patient.AuditDateTime, PriorityHistory.PriorityHistoryID, PriorityHistory.PatientID AS Expr2,
PriorityHistory.AuditDateTime AS Expr3
FROM Patient LEFT OUTER JOIN
SectorHistory ON Patient.PatientID = SectorHistory.PatientID LEFT OUTER JOIN
PriorityHistory ON Patient.PatientID = PriorityHistory.PatientID
WHERE (Patient.DischargeDateTime IS NULL) AND (PriorityHistory.PriorityID = 1) AND (SectorHistory.SectorID = 2)

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2007-06-27 : 10:10:39

Ok. It does not seem to use the current status

SELECT COUNT(*) AS Expr1
FROM Patient LEFT OUTER JOIN
SectorHistory ON Patient.PatientID = SectorHistory.PatientID LEFT OUTER JOIN
PriorityHistory ON Patient.PatientID = PriorityHistory.PatientID
WHERE (Patient.DischargeDateTime IS NULL) AND (PriorityHistory.PriorityID = 1) AND (SectorHistory.SectorID = 2)

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-27 : 10:33:45

from the output you will need to group by the colour ( is triage, transport, ... the status?

in the select
select colour ,
triage = sum case when status = 'triage' then 1 else 0 end) ,
transport = sum case when status = 'transport' then 1 else 0 end) ,
...
from

group by colour

now the from clause - you want the patient IDs, colour and status

(SELECT distnict PatientID, colour, status
FROM Patient LEFT OUTER JOIN
SectorHistory ON Patient.PatientID = SectorHistory.PatientID LEFT OUTER JOIN
PriorityHistory ON Patient.PatientID = PriorityHistory.PatientID
WHERE (Patient.DischargeDateTime IS NULL) AND (PriorityHistory.PriorityID = 1) AND (SectorHistory.SectorID = 2)
) a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2007-06-27 : 10:52:03

But I need the count?? Needs to be one number?

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-27 : 11:21:12

Don't you want the count of each status and colour?
On the result you seem to have counts for red triage, red transport, yellow triage, yellow transport, ...
and they look like they aer different i.e. many counts rather than one count.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2007-06-27 : 11:22:32

Yes I do, but I guess I do not follow your sql

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-06-27 : 11:26:46

quote:
Originally posted by helixpoint

Yes I do, but I guess I do not follow your sql

I'm not trying to be mean, but do you have other queries like this to write? You may be a little over your head if you can't get this, and may need to bring in additional help on the project.

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2007-06-27 : 11:32:54

I be all alone. :(:( need to rely on the newsgroups for this one

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-27 : 11:33:53

select colour ,
triage = sum(case when status = 'triage' then 1 else 0 end) ,
transport = sum(case when status = 'transport' then 1 else 0 end) ,
...
from
(SELECT distinct PatientID, colour, status
FROM Patient LEFT OUTER JOIN
SectorHistory ON Patient.PatientID = SectorHistory.PatientID LEFT OUTER JOIN
PriorityHistory ON Patient.PatientID = PriorityHistory.PatientID
WHERE (Patient.DischargeDateTime IS NULL) AND (PriorityHistory.PriorityID = 1) AND (SectorHistory.SectorID = 2)
) a
group by colour

now the from clause - you want the patient IDs, colour and status

the derived table a is all the patients that you want to count and their colour and status.
You want one row per colour so group by colour.
Now in the select you count all those in the group with the particular status.

triage = sum(case when status = 'triage' then 1 else 0 end)

this will sum 1 if it's a triage and 0 if it's not - giving you the count of triage for that colour.

You might find it easier to put the derived table into a temp table and use that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2007-06-27 : 13:14:42

does this take the most current location?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources