Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 13th, 2007, 06:15 AM
Authorized User
 
Join Date: Nov 2005
Posts: 41
Thanks: 6
Thanked 1 Time in 1 Post
Send a message via MSN to RobCarter
Default "almost" a running sum I guess

Hi

I have to get a result set that basically counts the number of records from one column that corresponds with a value in another e.g.

idCount contact_no
------- ----------
1 15648
2 15648
1 15649
2 15649
3 15649
1 15650

etc

so for contact_no 15649 there are 3 different entries

I can't think how to go about getting it.

Can someone help please.

Rob

Rob Carter
__________________
Rob Carter
 
Old March 13th, 2007, 06:38 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

What distingushes one row with a given contact_no from another with the same contact_no value?

That is, how did you number your counts as you did, rather than, say, this way:

idCount contact_no
------- ----------
2 15648
1 15648
2 15649
3 15649
1 15649
1 15650

I ask because there must be some way to order these rows to get what you want...

Jeff Mason
Custom Apps, Inc.
[email protected]
 
Old March 13th, 2007, 06:50 AM
Authorized User
 
Join Date: Nov 2005
Posts: 41
Thanks: 6
Thanked 1 Time in 1 Post
Send a message via MSN to RobCarter
Default

I missed out a column I guess.

idCount contact_no idNo start_date
------- ---------- ------- ----------
1 15648 d56789 20061212
2 15648 d56789 20061215
1 15649 d78945 20061212
2 15649 d78945 20061218
3 15649 d78945 20061226
1 15650 d12345 20061202

I can't post any actual data as it is healthcare related but this is as close as I can get. The analogy goes: patient d78945 had a hospital stay (15649) and while they did they had 3 ITU stays starting on those dates (there is an ITU stay ID in the data set too if that is more useful than dates (what isn't more useful than dates? :) ))
 
Old March 13th, 2007, 06:52 AM
Authorized User
 
Join Date: Nov 2005
Posts: 41
Thanks: 6
Thanked 1 Time in 1 Post
Send a message via MSN to RobCarter
Default

As an addendum, the counts are what I want to derive from my dataset. They dont currently exist anywhere, I don't know if I was clear enough on my original request.

Rob Carter
 
Old March 13th, 2007, 10:16 AM
Authorized User
 
Join Date: Nov 2005
Posts: 41
Thanks: 6
Thanked 1 Time in 1 Post
Send a message via MSN to RobCarter
Default

table:

stayID idNo start_date CC_localID
------ ------- ---------- ----------
15648 d56789 20061212 4567
15648 d56789 20061215 7854
15649 d78945 20061212 1246
15649 d78945 20061218 6589
15649 d78945 20061226 1235
15650 d12345 20061202 4687
15651 d78945 20070121 4258

structure (important fields)

stayID char Overall Hospital stay
idNo char Patient Identifier
StartDate char Date of ITU spell Start
CC_LocalID char ID of ITU Spell

I need to derive the idCount column shown in previous entries in this thread to show a running count of the number of ITU spells each patient has during their hospital stay. It is important that there is a row for each ITU spell in each patient's hospital stay (so no grouping by and coming up with 1 row per patient saying the total number of ITU spells). Also, if only to complicate matters - the final row in my sample data contains a new hospital stay ID for a patient that has already had a spell. As this is a new hospital stay the count of ITU spells per hospital stay would begin again at 1.

I hope this clarifies my requirements a little (or, more to the point, I hope it hasn't confused you utterly.)

Rob Carter
 
Old March 14th, 2007, 07:24 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

The way to solve this is to partition your data into subsets, where each subset contains all the rows for a given idNO and contact_no combination whose start_date value is less than the start_date value for each given idNO and contact_no row in turn. You then count the rows in each subset.

We'll use a correlated subquery to do that:

Code:
SELECT (SELECT COUNT(*) + 1 FROM yourtable T2
           WHERE T2.idNo = T1.idNo
             AND T2.contact_no = T1.contact_no
             AND T2.start_date < T1.start_date) as idCount, contact_no, idNo, start_date
  FROM yourtable T1
 ORDER BY idNo, contact_no, idCount;
Jeff Mason
Custom Apps, Inc.
[email protected]
 
Old March 14th, 2007, 09:20 AM
Authorized User
 
Join Date: Nov 2005
Posts: 41
Thanks: 6
Thanked 1 Time in 1 Post
Send a message via MSN to RobCarter
Default

Thanks for the answer you gave. It works really well until we get to patients where one start_date is the same as the other, then the count doesn't work. e.g if we have

stayID idNo start_date CC_localID
------ ------- ---------- ----------
15648 d56789 20061212 4567
15648 d56789 20061215 7854
15649 d78945 20061212 1246
15649 d78945 20061218 6589
15649 d78945 20061218 1235
15650 d12345 20061202 4687
15651 d78945 20070121 4258

The column becomes

ITUCount | | | |
WithinStay |stayID |idNo |start_date|CC_localID
-----------|-------|--------|----------|----------
1 | 15648 | d56789 | 20061212 | 4567
2 | 15648 | d56789 | 20061215 | 7854
1 | 15649 | d78945 | 20061212 | 1246
3 | 15649 | d78945 | 20061218 | 6589
3 | 15649 | d78945 | 20061218 | 1235
1 | 15650 | d12345 | 20061202 | 4687
1 | 15651 | d78945 | 20070121 | 4258

Are there any suggestions as to how I can overcome this?
 
Old March 14th, 2007, 10:05 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Well then, back to my original question.

You must have something that unambiguously distingushes one row from another and allows you to order them in some way. This ordering is what allows you to compute, in effect, a row number. Apparently the start_date does not do that.

The subquery uses the start_date to construct subsets of rows "less than" (an ordering) of the row currently being processed. You have a tie on start_date there, so, though the count is correct (there ARE that many rows less than the current row) if fails as a row number.

You'll need to add further qualification to the less than test in the subquery so as to guarantee that each subset contains unique, distingushable rows which you can count.

Jeff Mason
Custom Apps, Inc.
[email protected]
 
Old March 16th, 2007, 02:00 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Solved here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80508







Similar Threads
Thread Thread Starter Forum Replies Last Post
Trying to guess around ActiveX=COM Components asgarcymed VBScript 2 December 3rd, 2007 02:08 PM
Issue with a Running Sum MArgente Access 1 June 11th, 2007 06:45 AM
Running Sum in Subform Odeh Naber Access 2 May 14th, 2007 06:45 AM
Help: Running Sum (or Cumulative Sum) timdasa VB Databases Basics 1 August 22nd, 2006 03:12 PM
Running Sum semooth Access 2 July 21st, 2004 09:17 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.