Count records within a key value

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 2000 Forums

SQL Server Development (2000)

Count records within a key value

Author

Topic

RobCarter
Starting Member

10 Posts

Posted - 2007-03-13 : 07:40:24

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-13 : 07:53:06

sorry, don't quite understand what do you want. Can you post the required result ?

RobCarter
Starting Member

10 Posts

Posted - 2007-03-13 : 07:56:23

That IS the required result.

I missed out a column or two I guess.

idCount stayID 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.

I need to derive the data in the idCount column by counting each individual record with the appropriate stayID value

Rob

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-13 : 08:16:29

please also post your table structure and sample data. It don't have to be actual data. Just make sure the sample data required matches the required result.

RobCarter
Starting Member

10 Posts

Posted - 2007-03-13 : 09:23:47

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.)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-13 : 12:32:43

Try this thread:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79625[/url]

If that is not enough try searching for "SEQ" (sequence number). There are several topics on this subject. Also, if you have the option touse 2005, there are easier ways to do it in 2005.

Cheers,

-Ryan

RobCarter
Starting Member

10 Posts

Posted - 2007-03-14 : 06:23:43

Thanks for the suggestion but it is not a sequence collumn that I need. I need a running count, I don't know how better to describe it other than to show you:

I need to add a column to this 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

To make it look like this:

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

ITUCountwithinstay gives the number and order of a patient's ITU spells.

How can I go about achieving this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 06:32:31

[code]-- Prepare sample data
DECLARE @Sample TABLE (StayID INT, ID VARCHAR(6), StartDate DATETIME, LocalID INT)

INSERT @Sample
SELECT 15648, 'd56789', '20061212', 4567 UNION ALL
SELECT 15648, 'd56789', '20061215', 7854 UNION ALL
SELECT 15649, 'd78945', '20061212', 1246 UNION ALL
SELECT 15649, 'd78945', '20061218', 6589 UNION ALL
SELECT 15649, 'd78945', '20061226', 1235 UNION ALL
SELECT 15650, 'd12345', '20061202', 4687 UNION ALL
SELECT 15651, 'd78945', '20070121', 4258

-- Show the expected output
SELECT (SELECT COUNT(*) FROM @Sample AS s2 WHERE s2.StayID = s1.StayID AND s2.StartDate <= s1.StartDate) AS WithinStay,
s1.StayID,
s1.ID,
s1.StartDate,
s1.LocalID
FROM @Sample AS s1
ORDER BY s1.StayID,
s1.StartDate

SELECT ROW_NUMBER() OVER (PARTITION BY StayID ORDER BY StartDate) AS WithinStay,
StayID,
ID,
StartDate,
LocalID
FROM @Sample
ORDER BY StayID,
StartDate[/code]

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 06:34:05

Is this what you want ?


declare @table table
(
	stayID 		int,
	idNo 		varchar(10),
	start_date 	datetime,
	CC_localID	int
)
insert into @table
select	15648, 'd56789', '20061212', 4567	union all
select	15648, 'd56789', '20061215', 7854	union all
select	15649, 'd78945', '20061212', 1246	union all
select	15649, 'd78945', '20061218', 6589	union all
select	15649, 'd78945', '20061226', 1235	union all
select	15650, 'd12345', '20061202', 4687	union all
select	15651, 'd78945', '20070121', 4258

select	ITUCountWithinStay	
	= (select count(*) from @table x where x.stayID = t.stayID and x.start_date <= t.start_date),
	*
from	@table t

/*
ITUCountWithinStay stayID      idNo       start_date  CC_localID  
------------------ ----------- ---------- ----------- ----------- 
1                  15648       d56789     2006-12-12  4567
2                  15648       d56789     2006-12-15  7854
1                  15649       d78945     2006-12-12  1246
2                  15649       d78945     2006-12-18  6589
3                  15649       d78945     2006-12-26  1235
1                  15650       d12345     2006-12-02  4687
1                  15651       d78945     2007-01-21  4258
*/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 06:37:57

Soon

Peter Larsson
Helsingborg, Sweden

RobCarter
Starting Member

10 Posts

Posted - 2007-03-14 : 06:55:42

That's really great. It looks to have solved my problem. Now just to apply it to the actual table.

Thanks for bearing with me guys :)

Rob

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 06:57:40

I'll do it for you, khtan

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-14 : 07:14:22

Another way:

declare @table table
(
	stayID 		int,
	idNo 		varchar(10),
	start_date 	datetime,
	CC_localID	int
)
insert into @table
select	15648, 'd56789', '20061212', 4567	union all
select	15648, 'd56789', '20061215', 7854	union all
select	15649, 'd78945', '20061212', 1246	union all
select	15649, 'd78945', '20061218', 6589	union all
select	15649, 'd78945', '20061226', 1235	union all
select	15650, 'd12345', '20061202', 4687	union all
select	15651, 'd78945', '20070121', 4258

Select 
	count(t2.stayid) as ITUCountWithinStay, t1.*
from	
	@table t1 join @table t2 
on 	
	t1.stayID = t2.stayID and t2.Start_date <= t1.Start_Date
group by 
	t1.stayid, t1.idno, t1.start_date, t1.cc_localid

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 07:16:22

quote:
Originally posted by Peso

I'll do it for you, khtan

Peter Larsson
Helsingborg, Sweden

Arrrrrgh ! Didn't see that.

Thanks Peter

RobCarter
Starting Member

10 Posts

Posted - 2007-03-14 : 10:16:47

The answers you gave 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?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 10:37:40

Do you want them both to be 2 instead?

Peter Larsson
Helsingborg, Sweden

RobCarter
Starting Member

10 Posts

Posted - 2007-03-14 : 10:42:33

no. I need them to run in sequence until there is a new stay_ID so if there are 3 rows containing the same stay_ID the ITUCountWithinStay will show 1 for the first, 2 for the 2nd and 3 for the third. if there were 12 rows containing the same value in the stay ID column, I need, in sequence, 1 through 12 for each row in the ITUCountWithinStay column until a new stay ID value.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 10:55:15

Satisfied with this?

-- Prepare sample data
DECLARE	@Sample TABLE (StayID INT, ID VARCHAR(6), StartDate DATETIME, LocalID INT)

INSERT	@Sample
SELECT	15648, 'd56789', '20061212', 4567 UNION ALL
SELECT	15648, 'd56789', '20061215', 7854 UNION ALL
SELECT	15649, 'd78945', '20061212', 1246 UNION ALL
SELECT	15649, 'd78945', '20061218', 6589 UNION ALL
SELECT	15649, 'd78945', '20061218', 1235 UNION ALL
SELECT	15650, 'd12345', '20061202', 4687 UNION ALL
SELECT	15651, 'd78945', '20070121', 4258

-- Show the expected output
SELECT		(SELECT COUNT(*) FROM @Sample AS s2 WHERE s2.StayID = s1.StayID AND s2.StartDate <= s1.StartDate) -
		(SELECT COUNT(*) FROM @Sample AS s2 WHERE s2.StayID = s1.StayID AND s2.StartDate = s1.StartDate AND s2.LocalID > s1.LocalID) AS WithinStay,
		s1.StayID,
		s1.ID,
		s1.StartDate,
		s1.LocalID
FROM		@Sample AS s1
ORDER BY	s1.StayID,
		1

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 11:29:15

This performs much better on large datasets

-- Prepare sample data
DECLARE	@Sample TABLE (StayID INT, ID VARCHAR(6), StartDate DATETIME, LocalID INT)

INSERT	@Sample
SELECT	15648, 'd56789', '20061212', 4567 UNION ALL
SELECT	15648, 'd56789', '20061215', 7854 UNION ALL
SELECT	15649, 'd78945', '20061212', 1246 UNION ALL
SELECT	15649, 'd78945', '20061218', 6589 UNION ALL
SELECT	15649, 'd78945', '20061218', 1235 UNION ALL
SELECT	15650, 'd12345', '20061202', 4687 UNION ALL
SELECT	15651, 'd78945', '20070121', 4258

-- Stage the data
DECLARE	@Stage TABLE	(
				RecID INT IDENTITY(1, 1),
				StayID INT,
				ID VARCHAR(6),
				StartDate DATETIME,
				LocalID INT,
				Start INT
			)

INSERT		@Stage
		(
			StayID,
			ID,
			StartDate,
			LocalID
		)
SELECT		StayID,
		ID,
		StartDate,
		LocalID
FROM		@Sample
ORDER BY	StayID,
		StartDate

UPDATE		s
SET		s.Start = x.Start
FROM		@Stage AS s
INNER JOIN	(
			SELECT		StayID,
					MIN(RecID) AS Start
			FROM		@Stage
			GROUP BY	StayID
		) AS x ON x.StayID = s.StayID

-- Show the expected output
SELECT		1 + RecID - Start AS WithinStay,
		StayID,
		ID,
		StartDate,
		LocalID
FROM		@Stage
ORDER BY	StayID,
		1

Peter Larsson
Helsingborg, Sweden

RobCarter
Starting Member

10 Posts

Posted - 2007-03-21 : 10:59:46

Hi all who answered this forum with correlated query suggestions.

Peter, your penultimate response worked like a charm. Thanks a lot

Thanks all for your help

Rob Carter
Birmingham, UK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources