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 2008 Forums
 Transact-SQL (2008)
 Query and/or View Help

Author  Topic 

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2013-02-15 : 19:50:32
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

52326 Posts

Posted - 2013-02-16 : 02:25:42
[code]
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
[/code]

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

Go to Top of Page

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2013-02-16 : 13:04:52
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
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-16 : 15:08:17
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

52326 Posts

Posted - 2013-02-17 : 01:58:45
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

72 Posts

Posted - 2013-02-18 : 11:18:15
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

72 Posts

Posted - 2013-02-18 : 12:11:16
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

52326 Posts

Posted - 2013-02-18 : 12:15:49
Which is field that contain first admit date?

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

Go to Top of Page

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2013-02-18 : 12:26:31
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

52326 Posts

Posted - 2013-02-18 : 12:28:22
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

72 Posts

Posted - 2013-02-18 : 12:37:25
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

72 Posts

Posted - 2013-02-18 : 12:40:28
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

52326 Posts

Posted - 2013-02-18 : 12:40:53
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

72 Posts

Posted - 2013-02-18 : 12:47:08
Now the result is 479 ...

Mike Brown
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 12:56:27
Isnt that your expected count?

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

Go to Top of Page

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2013-02-18 : 13:09:11
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

52326 Posts

Posted - 2013-02-18 : 13:32:40
what should be the count it should display?

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

Go to Top of Page

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2013-02-18 : 13:43:35
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

72 Posts

Posted - 2013-02-18 : 16:17:32
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
Go to Top of Page

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2013-02-18 : 20:16:07
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

52326 Posts

Posted - 2013-02-18 : 23:52:02
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
  Previous Page&nsp;  Next Page

- Advertisement -