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. -MikeMike 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,ClientIDFROM(SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfDate) AS Seq,*FROM Patient)tWHERE Seq=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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, PrimDiagCountFROM(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)tWHERE 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 PrimDiagCountFROM(SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfDate) AS Seq,*FROM Patient)tWHERE Seq=1-----SELECT SUM(CASE WHEN PrimDiag BETWEEN 100 AND 400 THEN 1 ELSE 0 END) AS PrimDiagCountFROM(SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfDate) AS Seq,*FROM Patient)tWHERE Seq=1 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-17 : 01:58:45
|
I think it should be thisSELECT ID,MRNum,FirstName,LastName,SSN,StartOfDate,PrimDiag,ClientID, COUNT(CASE WHEN PrimDiag BETWEEN 100 AND 400 THEN PrimDiag ELSE NULL END) OVER() AS PrimDiagCountFROM(SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfDate) AS Seq,*FROM Patient)tWHERE Seq=1 or sum equivalent that James posted as you need count only over first visits------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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 |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 MVPhttp://visakhm.blogspot.com/
Startofcare is the fieldMike Brown |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 |
|
|
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-400Mike Brown |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-18 : 12:40:53
|
it should beSELECT ID,MRNum,FirstName,LastName,SSN,StartOfDate,PrimDiag,ClientID, COUNT(CASE WHEN PrimDiag BETWEEN 100 AND 400 THEN PrimDiag ELSE NULL END) OVER() AS PrimDiagCountFROM(SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfDate) AS Seq,*FROM PatientWHERE RptYear = '2012')tWHERE Seq=1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
mikeallenbrown
Yak Posting Veteran
72 Posts |
Posted - 2013-02-18 : 12:47:08
|
Now the result is 479 ...Mike Brown |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-18 : 12:56:27
|
Isnt that your expected count?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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-400I'm sorry ... I must not be explaining myself very wellMike Brown |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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 2012Mike Brown |
|
|
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,ClientIDFROM(SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfcare) AS Seq,*FROM PatientWHERE RptYear = '2012' and PrimDiag <> 'NULL' and PrimDiag <> '' and Status <> 'Pending' and Status <> 'Non-Admit' and SSN <> '')tWHERE Seq=1Order by ClientIDI 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 |
|
|
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.ClientIDWHERE t.Seq = 1Order By t.ClientIDMike Brown |
|
|
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.ClientIDWHERE t.Seq = 1Order By t.ClientIDMike Brown
can be simplified asSELECT 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.ClientIDWHERE t.Seq = 1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Next Page
|