| Author |
 Topic  |
|
mikeallenbrown
Starting Member
USA
13 Posts |
 Posted - 02/15/2013 : 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
India
47169 Posts |
Posted - 02/16/2013 : 02:25:42
|
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/
|
 |
|
|
mikeallenbrown
Starting Member
USA
13 Posts |
Posted - 02/16/2013 : 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 |
Edited by - mikeallenbrown on 02/16/2013 13:06:29 |
|
|
|
James K
Flowing Fount of Yak Knowledge
1515 Posts |
Posted - 02/16/2013 : 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
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47169 Posts |
Posted - 02/17/2013 : 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/
|
|
|
|
mikeallenbrown
Starting Member
USA
13 Posts |
Posted - 02/18/2013 : 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 |
|
|
|
mikeallenbrown
Starting Member
USA
13 Posts |
Posted - 02/18/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47169 Posts |
Posted - 02/18/2013 : 12:15:49
|
Which is field that contain first admit date?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
mikeallenbrown
Starting Member
USA
13 Posts |
Posted - 02/18/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47169 Posts |
Posted - 02/18/2013 : 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/
|
|
|
|
mikeallenbrown
Starting Member
USA
13 Posts |
Posted - 02/18/2013 : 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 |
|
|
|
mikeallenbrown
Starting Member
USA
13 Posts |
Posted - 02/18/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47169 Posts |
Posted - 02/18/2013 : 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/
|
|
|
|
mikeallenbrown
Starting Member
USA
13 Posts |
Posted - 02/18/2013 : 12:47:08
|
Now the result is 479 ...
Mike Brown |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47169 Posts |
Posted - 02/18/2013 : 12:56:27
|
Isnt that your expected count?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
mikeallenbrown
Starting Member
USA
13 Posts |
Posted - 02/18/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47169 Posts |
Posted - 02/18/2013 : 13:32:40
|
what should be the count it should display?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
mikeallenbrown
Starting Member
USA
13 Posts |
Posted - 02/18/2013 : 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 |
|
|
|
mikeallenbrown
Starting Member
USA
13 Posts |
Posted - 02/18/2013 : 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 |
Edited by - mikeallenbrown on 02/18/2013 17:59:19 |
|
|
|
mikeallenbrown
Starting Member
USA
13 Posts |
Posted - 02/18/2013 : 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47169 Posts |
Posted - 02/18/2013 : 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/
|
|
|
|
Topic |
|
Query and/or View Help
