Query Syntax Help
-
I am using this query:
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 is equal to Patient.ClientID ...just not sure how to get the syntax right.
Thanks in advance!
-Mike
-
Hi
Not sure if this is what you want
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, * -- probably best to specify the columns required
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
This could also be done
SELECT t.SSN, t.StartOfCare, t.PrimDiag, t.ClientID,
t.[required columns]
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfCare) AS Seq, [required columns ...]
FROM Patient p
INNER JOIN Client c ON c.ID = p.ClientID
WHERE RptYear = '2012' and PrimDiag <> 'NULL' and PrimDiag <> '' and Status <> 'Pending' and Status <> 'Non-Admit' and SSN <> ''
) t
WHERE t.Seq = 1
Order By t.ClientID
I notice that you have PrimDiag <> 'NULL'. Is this stored as a characters in your table? If not then you will need to use PrimDiag is not NULL
-
Great! ...thank you. That worked.
Yes, PrimDiag is a char field ...probably should be dec(18,2) but a few clients have letters in this field so changing would be problematic.
-
mikeallenbrown (2/18/2013)
Great! ...thank you. That worked.Yes, PrimDiag is a char field ...probably should be dec(18,2) but a few clients have letters in this field so changing would be problematic.
No problem.
It wasn't so much the datatype of PrimDiag, but how NULLs are stored in it. If it is a system NULL rather than 'NULL' spelled out as characters, you may need to filter it differently.
It probably won't affect what you are trying to achieve, but it is something to be aware of.
As a quick example
;WITH testdata AS (SELECT *
FROM (VALUES (1,'Value'), (2,null), (3,'Null'), (4,'')) AS T(someID, PrimDiag)
)
-- Query 1, Handle character nulls
SELECT *, 'Query One' WhichQuery
FROM testData
WHERE PrimDiag <> 'NULL' and PrimDiag <> ''
UNION ALL
-- Query 2, Handle system null
SELECT *, 'Query Two' WhichQuery
FROM testData
WHERE PrimDiag is not null and PrimDiag <> ''
-
Ahhh I see ... ya, it isn't a system NULL. At one time the program that is using this database inputted a 'NULL' if the user didn't make a selection ...then sometime later that was changed it no selection was just blank ...lol.
-Mike
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply