How do I count the no of patients?

Question

Post reply

How do I count the no of patients?

Nuts

SSCrazy

Points: 2547
More actions
November 12, 2007 at 6:59 pm

#180175

Hi guys
I have got a huge table which contains patients data.
I need to calculate the the no of patients for each clinical service.
Here is the sample data
Patient no Startdate EndDate ClinicalService cost
1qq 12/04/03 15/04/03 restHome $12
2qq 1/1/07 1/2/07 dementia $4234.00
3ww 30/01/04 12/01/06 hospital $1244
1qq 1/1/2004 12/1/2004 dementia $400
Assumption: If a person has been in two services at a different period of time, he will be treated as a different case each time in every different service.
I have got a table of dates called 'static_tab' which contains all the possible dates.
here is the script for the 'static_tab' table
USE [LOSDSS1]
GO
/****** Object: Table [dbo].[static_tab] Script Date: 11/13/2007 14:51:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[static_tab](
[cnt] [int] NULL,
[dates] [datetime] NULL,
[Quarter] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
I have been using a query for calculating total cost for each patient for each catagory . Its as follows
Select Year(x.dates) As [Year], Month (x.dates) As [Month],x.clinicalservice
Sum(dailyvol) As cost
From c.dates,f.clinicalService,
(f.cost / (DateDiff(dd, f.[Start date], f.[End date]) + 1)) As DailyVol
From tablePatients f
Inner Join static_tab c On c.dates Between f.[Start date]
And f.[End date]) as x
Group By Year(x.dates), Month (x.dates),x.clinicalservice
Order By Year(x.dates), Month (x.dates),x.clinicalservice
And the output table is as follows
clinical serviceYearMonthcost
Dementia 200771016842.852
Dementia 200781005945.664
Dementia 20079976899.9008
Hospital Care2003102714517.879
Hospital Care2003112724111.029
I want the similar output but insteasd of cost, I need the patient count.
I tried doing it but it gives me a conversion error
Please help guys
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 1

Ummmm.... you know what SUM is... do you know what COUNT is?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johannes Fourie Default port Points: 1470 More actions · Answer 2

Yes use COUNT instead of SUM

You may also want to use DISTINCT in your if you don't want to count the same patient twice if the patient used the same service within the grouped time period.

just add COUNT(DISTINCT PatientNo) to your existing query

Nuts SSCrazy Points: 2547 More actions · Answer 3

I know what sum is and what count is....:)

I mentioned earlier as well I tried to put a count on instead of sum but I get an error especially in the below part of the query:

'f.cost / (DateDiff(dd, f.[Start date], f.[End date]) + 1)) As DailyVol'

As my counts are in nvarchar type format, i get a conversion failed error

Thanks

Adam Haines SSC-Insane Points: 23217 More actions · Answer 4

You could try to do a simple convert, like such:

SELECT count(CONVERT(smallmoney,cost))

My blog: http://jahaines.blogspot.com

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

When doing count, either count the specific field (in this case, you want to count patients, so count the primary key of the patient), or count(*) (which will count each virtual/composite record in the recordset. Keep in mind that COUNT only counts NON-NULL values in a field, so COUNT(fieldname) would only count those rows where fieldname is not null.

What error do you get with your division? overflow?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Nuts SSCrazy Points: 2547 More actions · Answer 6

I get this error

'Conversion failed when converting the nvarchar value to data type int.'

Adam Haines SSC-Insane Points: 23217 More actions · Answer 7

Are you trying to convert PatientNo to an Int? It has letters in it, so you cant convert it to an integer.

This produces the same error:

declare @var1 nvarchar(10)

set @var1 = '1qq'

select count(convert(int,@var1))

You can follow Matt's advice and do something like:

select count(*)

--OR

select count(PatientNo)

My blog: http://jahaines.blogspot.com

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

what are the data types of cost, startdate and enddate in the patients table?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Nuts SSCrazy Points: 2547 More actions · Answer 9

start date and end date are of 'datetime' format and cost is float

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

I just noticed - some part of your aggregate query is missing, because that syntax should NOT be getting past the syntax checker. There's no way that's running. Actually - I'm wondering if the error you're getting is based on some syntax error above, and it's only "figuring it out" on that line.

Try re-pasting the query that works. Alternatively - paste your table sturcture, so that we can build the thing from scratch for you.

Otherwise - it's time to start getting back to basics. Break that predicate into smaller pieces (i.e. separate the numerator and denominator, check that the denominator, cast the numerator and denominator to int and see which one bombs.... do those one at a time, so that you know what's failing.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Nuts SSCrazy Points: 2547 More actions · Answer 11

Hi

Thanks for replying

This is the query which i use to calculate the cost :

Select Year(x.dates) As [Year], Month (x.dates) As [Month],x.clinicalservice

Sum(dailyvol) As cost

From c.dates,f.clinicalService,

(f.cost / (DateDiff(dd, f.[Start date], f.[End date]) + 1)) As DailyVol

From tablePatients f

Inner Join static_tab c On c.dates Between f.[Start date]

And f.[End date]) as x

Group By Year(x.dates), Month (x.dates),x.clinicalservice

Order By Year(x.dates), Month (x.dates),x.clinicalservice

But not working for the patient counts.

Here is the sample data

Patient no Startdate EndDate ClinicalService cost

1qq 12/04/03 15/04/03 restHome $12

2qq 1/1/07 1/2/07 dementia $4234.00

3ww 30/01/04 12/01/06 hospital $1244

1qq 1/1/2004 12/1/2004 dementia $400

The problem is I need the patient counts for each clinical service grouped by year and month.

Now there are some rows in the table where start dates and end dates differ by more than one year as well.

So I am not able to solve that part.

Thanks

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 12

This part here isn't correct SQL - you have 2 FROM statements, and expressions in the FROM statements.

Nuts (11/13/2007)
Hi
Select
...
From c.dates,f.clinicalService,
(f.cost / (DateDiff(dd, f.[Start date], f.[End date]) + 1)) As DailyVol
From tablePatients f
Inner Join static_tab c On c.dates Between f.[Start date]
And f.[End date]) as x
...

The thing I bolded is an expression which should either be in a subquery, or in the SELECT statement.

Either the editor on here is "eating your SQL code" or that's not valid SQL.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?