SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query and/or View Help
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 02/15/2013 :  19:50:32  Show Profile  Reply with Quote
Here is the data I am working with:





I need either a query or a view that would count first admissions (very first time a patient was entered into the database). A patient may have been admitted multiple times (like Kendra Johnson) but I only want the FIRST admission. So in Kendra's case I would want ONLY her 1-01-2012 record and not the others.

Kendra is the only multiple in my example here so naturally all the others would be counted too as they would all count as the "first" admission.

I hope I articulated what I want right so you all can understand.

I've been working with SQL for a while now but I wouldn't consider myself an "advanced" user.

Thank you in advance...and by the way, this isn't real patient data, in case anyone was wondering.

-Mike

Mike Brown

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/16/2013 :  02:25:42  Show Profile  Reply with Quote

SELECT ID,MRNum,FirstName,LastName,SSN,StartOfDate,PrimDiag,ClientID
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfDate) AS Seq,*
FROM Patient
)t
WHERE Seq=1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 02/16/2013 :  13:04:52  Show Profile  Reply with Quote
Thank you! ... how may I now get a count of these records grouped by 'PrimDiag'? ... more specifically I'd like to get a count of records where 'PrimDiag' is between 100 and 400 (while still only counting the first time a patient was entered into the database). I tried adding a 'count(startofcare) as expr1' and a 'between' in a couple different places but I only got errors back.





Mike Brown

Edited by - mikeallenbrown on 02/16/2013 13:06:29
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3652 Posts

Posted - 02/16/2013 :  15:08:17  Show Profile  Reply with Quote
What would be the expected output for the COUNT for the sample data that you posted? See if any of these give you the output you are looking for; if not post the desired output:
SELECT ID,MRNum,FirstName,LastName,SSN,StartOfDate,PrimDiag,ClientID, PrimDiagCount
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfDate) AS Seq,
	SUM(CASE WHEN PrimDiag BETWEEN 100 AND 400 THEN 1 ELSE 0 END) 
	OVER (PARTITION BY SSN) AS PrimDiagCount, *
FROM Patient
)t
WHERE Seq=1

-----
SELECT ID,MRNum,FirstName,LastName,SSN,StartOfDate,PrimDiag,ClientID,
	SUM(CASE WHEN PrimDiag BETWEEN 100 AND 400 THEN 1 ELSE 0 END) OVER() AS PrimDiagCount
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfDate) AS Seq,*
FROM Patient
)t
WHERE Seq=1

-----
SELECT SUM(CASE WHEN PrimDiag BETWEEN 100 AND 400 THEN 1 ELSE 0 END) AS PrimDiagCount
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfDate) AS Seq,*
FROM Patient
)t
WHERE Seq=1
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/17/2013 :  01:58:45  Show Profile  Reply with Quote
I think it should be this

SELECT ID,MRNum,FirstName,LastName,SSN,StartOfDate,PrimDiag,ClientID,
	COUNT(CASE WHEN PrimDiag BETWEEN 100 AND 400 THEN PrimDiag ELSE NULL END) OVER() AS PrimDiagCount
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfDate) AS Seq,*
FROM Patient
)t
WHERE Seq=1


or sum equivalent that James posted as you need count only over first visits

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 02/18/2013 :  11:18:15  Show Profile  Reply with Quote
I tried several of the queries above and didn't get the results I was looking for... I probably wasn't clear enough on what exactly I need.

Below is a view a created a few days ago:



I need something similar to this but only new admissions(a new admission being the first time a patient was entered into the database).

I'm sorry if this is too ambiguous .

Mike Brown
Go to Top of Page

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 02/18/2013 :  12:11:16  Show Profile  Reply with Quote
Here is a query I just wrote that is half way there to what I need:

'Expr1' should always be equal to 1 (because I only want to count the patients first new admission) ... I just don't know how to make my query do that.



Mike Brown
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  12:15:49  Show Profile  Reply with Quote
Which is field that contain first admit date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 02/18/2013 :  12:26:31  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

Which is field that contain first admit date?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





Startofcare is the field

Mike Brown
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  12:28:22  Show Profile  Reply with Quote
then my earlier suggestion should give you the intended result.
What happened when you used it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 02/18/2013 :  12:37:25  Show Profile  Reply with Quote
It gives me "451" as the count... Not sure what that is a count of.

I added rptyear='2012' to your query as I only need records for the year 2012 (rptyear is a date part (year) from 'startofcare ')



Mike Brown
Go to Top of Page

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 02/18/2013 :  12:40:28  Show Profile  Reply with Quote
Interestingly if I take away the rptyear='2012' then it goes to 521 ....also, the PrimDiag isn't between the values 100-400

Mike Brown
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  12:40:53  Show Profile  Reply with Quote
it should be

SELECT ID,MRNum,FirstName,LastName,SSN,StartOfDate,PrimDiag,ClientID,
	COUNT(CASE WHEN PrimDiag BETWEEN 100 AND 400 THEN PrimDiag ELSE NULL END) OVER() AS PrimDiagCount
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfDate) AS Seq,*
FROM Patient
WHERE RptYear = '2012'
)t
WHERE Seq=1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 02/18/2013 :  12:47:08  Show Profile  Reply with Quote
Now the result is 479 ...

Mike Brown
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  12:56:27  Show Profile  Reply with Quote
Isnt that your expected count?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 02/18/2013 :  13:09:11  Show Profile  Reply with Quote
I'm expecting a "1" in the 'PrimDiagCount' ... I don't know where 479 is coming from, what is that a count of? ...and the query didn't narrow down the range between 100-400

I'm sorry ... I must not be explaining myself very well



Mike Brown
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  13:32:40  Show Profile  Reply with Quote
what should be the count it should display?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 02/18/2013 :  13:43:35  Show Profile  Reply with Quote
It should be one, always one.... because it is a count of a single record that meets the specified criteria: being primdiag 100-400 & year 2012


Mike Brown
Go to Top of Page

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 02/18/2013 :  16:17:32  Show Profile  Reply with Quote
ok, after much thought I've settled on this query:

SELECT SSN,StartOfCare,PrimDiag,ClientID
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfcare) AS Seq,*
FROM Patient
WHERE RptYear = '2012' and PrimDiag <> 'NULL' and PrimDiag <> '' and Status <> 'Pending' and Status <> 'Non-Admit' and SSN <> ''
)t
WHERE Seq=1
Order by ClientID

I need to incorporate another table however. The table is called "client".

I need client.ID & client.ClientName on an inner join with the above query where client.ID equals Patient.clientID ...just not sure how to get the syntax right.

Mike Brown

Edited by - mikeallenbrown on 02/18/2013 17:59:19
Go to Top of Page

mikeallenbrown
Yak Posting Veteran

USA
53 Posts

Posted - 02/18/2013 :  20:16:07  Show Profile  Reply with Quote
Got the syntax right ...for those interested this is what it looks like:

SELECT t.SSN, t.StartOfCare, t.PrimDiag, t.ClientID,
c.[required columns]
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfCare) AS Seq, *
FROM Patient
WHERE RptYear = '2012' and PrimDiag <> 'NULL' and PrimDiag <> '' and Status <> 'Pending' and Status <> 'Non-Admit' and SSN <> ''
) t
INNER JOIN Client c ON c.ID = t.ClientID
WHERE t.Seq = 1
Order By t.ClientID

Mike Brown
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  23:52:02  Show Profile  Reply with Quote
quote:
Originally posted by mikeallenbrown

Got the syntax right ...for those interested this is what it looks like:

SELECT t.SSN, t.StartOfCare, t.PrimDiag, t.ClientID,
c.[required columns]
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfCare) AS Seq, *
FROM Patient
WHERE RptYear = '2012' and PrimDiag <> 'NULL' and PrimDiag <> '' and Status <> 'Pending' and Status <> 'Non-Admit' and SSN <> ''
) t
INNER JOIN Client c ON c.ID = t.ClientID
WHERE t.Seq = 1
Order By t.ClientID

Mike Brown



can be simplified as


SELECT t.SSN, t.StartOfCare, t.PrimDiag, t.ClientID, 
    c.[required columns]
FROM
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfCare) AS Seq, *
    FROM Patient
    WHERE RptYear = '2012' 
    and PrimDiag NOT IN('NULL','') 
    and Status NOT IN ('Pending','Non-Admit') 
    and SSN <> ''
    ) t
    INNER JOIN Client c ON c.ID = t.ClientID
WHERE t.Seq = 1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000