October 24, 2016 at 9:01 am
Hi,
I'm trying to "remove" a selected portion of data that does NOT have a 'POS' value in the series. could some one help me out?
The picture shows the result set... The dRank section 2 is the portion that needs to be removed because there is NO value of 'POS'
Select
r.CaseFileIdentifier
, DateAdd(hour,-7,r.ReferredOn) as ReferralDate
, sv.Name
, sv.Value
, Cast(DateAdd(hour,-7,s.DrawnOn) as Date) as CollectedOnDate
, right(convert(varchar(32),DateAdd(hour,-7,s.DrawnOn),100),8) as CollectedOnTime
, Dense_Rank () OVER(PARTITION BY r.CaseFileIdentifier ORDER BY s.DrawnOn ASC) AS dRank
, IsNull(s.DataCategory,'Organ') DataCategory
, p.UnosId
From dbo.Serology s
Inner Join dbo.Patient p ON p.Id = s.PatientId
Inner Join dbo.Referral r ON r.PatientId = p.Id
Inner Join dbo.SerologyValue sv ON sv.SerologyId = s.Id
Inner Join dbo.DonorReferral dr ON dr.PatientId = r.PatientId
--AND sv.Name LIKE '%cru%'
--AND sv.Value LIKE 'PO%'
WHERE s.DrawnOn is not null ----as multiple dates
AND Value is not null
--AND (
----This gives me only two records but I need to show the "non-POS" as well
--sv.Name = 'HBsAg' and sv.Value = 'POS'
-- OR sv.Name = 'HBsAb' and sv.Value = 'POS'
-- OR sv.Name = 'Syphilis' and sv.Value = 'POS'
-- OR sv.Name = 'HIV NAT' and sv.Value = 'POS'
-- OR sv.Name = 'HCV NAT' and sv.Value = 'POS'
-- OR sv.Name = 'HCV NAT' and sv.Value = 'POS'
-- OR sv.Name = 'RPR/VDRL' and sv.Value = 'POS'
-- OR sv.Name = 'NAT HBV' and sv.Value = 'POS'
-- OR sv.Name ='NAT HCV' and sv.Value = 'POS'
-- OR sv.Name = 'NAT HIV' and sv.Value = 'POS'
-- )
----We only want to "see" these Names
AND sv.Name IN ('HBsAg', 'HBsAb', 'Syphilis', 'HIV NAT', 'HCV NAT', 'HBV NAT', 'RPR/VDRL', 'NAT HBV', 'NAT HCV', 'NAT HIV')
Thank you in advance for you help!
Larry
October 24, 2016 at 10:14 am
Since we don't have sample data to work with, it's difficult to come up with a query. Try this approach.
WITH CTE AS (
SELECT
r.CaseFileIdentifier
, DateAdd(hour,-7,r.ReferredOn) as ReferralDate
, sv.Name
, sv.Value
, Cast(DateAdd(hour,-7,s.DrawnOn) as Date) as CollectedOnDate
, right(convert(varchar(32),DateAdd(hour,-7,s.DrawnOn),100),8) as CollectedOnTime
, MAX(CASE WHEN sv.Value = 'POS' THEN 1 ELSE 0 END) OVER(PARTITION BY r.CaseFileIdentifier, s.DrawnOn) AS pos_indicator
, IsNull(s.DataCategory,'Organ') DataCategory
, p.UnosId
From dbo.Serology s
Inner Join dbo.Patient p ON p.Id = s.PatientId
Inner Join dbo.Referral r ON r.PatientId = p.Id
Inner Join dbo.SerologyValue sv ON sv.SerologyId = s.Id
Inner Join dbo.DonorReferral dr ON dr.PatientId = r.PatientId
--AND sv.Name LIKE '%cru%'
--AND sv.Value LIKE 'PO%'
WHERE s.DrawnOn is not null ----as multiple dates
AND Value is not NULL
)
SELECT *
FROM CTE
WHERE CTE.pos_indicator = 1
I do think that the partition should probably just be the serology id, but, again, it's hard to say without sample data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 24, 2016 at 10:29 am
Thank you Drew! I'll give that a shot and if it doesn't work, I'll put up some sample data.
October 24, 2016 at 11:31 am
Hi Drew,
That didn't work... 🙁
Here is some sample data, when you have time to check it out.
Create Table tempSero
(
CaseId varchar(20)
, rDate datetime
, Name varchar(50)
, Value varchar(20)
, CollectedOnDate Date
, CollectedOnTime Time
, dRank int
)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBsAb','2016-05-31','6:00PM',1)
Insert Into tempSero Values (1, '2016-05-26','NEG','HBsAg','2016-05-31','6:00PM',1)
Insert Into tempSero Values (1, '2016-05-26','NEG','HBV NAT','2016-05-31','6:00PM',1)
Insert Into tempSero Values (1, '2016-05-26','NEG','HCV NAT','2016-05-31','6:00PM',1)
Insert Into tempSero Values (1, '2016-05-26','NEG','HIV NAT','2016-05-31','6:00PM',1)
Insert Into tempSero Values (1, '2016-05-26','POS','Syphilis','2016-05-31','6:00PM',1)
--Need to "weed out" this section
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBsAb','2016-06-01','5:35PM',2)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBsAg','2016-06-01','5:35PM',2)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBV NAT','2016-06-01','5:35PM',2)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HCV NAT','2016-06-01','5:35PM',2)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HIV NAT','2016-06-01','5:35PM',2)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','Syphilis','2016-06-01','5:35PM',2)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBsAb','2016-06-03','5:00AM',3)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBsAg','2016-06-03','5:00AM',3)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBV NAT','2016-06-03','5:00AM',3)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HCV NAT','2016-06-03','5:00AM',3)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HIV NAT','2016-06-03','5:00AM',3)
Insert Into tempSero Values (1, '2016-05-26','POS','Syphilis','2016-06-03','5:00AM',3)
Select
CaseId
,rDate
, Name
, Value
,CollectedOnDate
,CollectedOnTime
, dRank
From tempSero
October 24, 2016 at 11:36 am
I tried this too...
select
CaseFileIdentifier
, ReferralDate
, CollectedOnDate
, CollectedOnTime
, dRank as CollectionSequence
,Isnull([HBsAb],'---') as [HBsAb]
,Isnull([HBsAg], '---') as [HBsAg]
,Isnull([Syphilis],'---') as [Syphilis]
,[HIV NAT], [HCV NAT], [HBV NAT]
,Isnull([RPR/VDRL], '---') as [RPR/VDRL]
,Isnull([NAT HBV], '---') as [NAT HBV]
,Isnull([NAT HCV],'---') as [NAT HCV]
,Isnull([NAT HIV], '---') as [NAT HIV]
from
(
select
CaseFileIdentifier
, ReferralDate
, CollectedOnDate
, CollectedOnTime
, Name
, dRank
, isnull(Value,'---') as Value
From vwSerology
) DataTable
PIVOT
(
Max(Value)
FOR Name
IN (
[HBsAg], [HBsAb], [Syphilis], [HIV NAT], [HCV NAT], [HBV NAT], [RPR/VDRL], [NAT HBV], [NAT HCV], [NAT HIV]
)) PivotTable
October 24, 2016 at 11:46 am
larrycook74 (10/24/2016)
Hi Drew,That didn't work... 🙁
Here is some sample data, when you have time to check it out.
Create Table tempSero
(
CaseId varchar(20)
, rDate datetime
, Name varchar(50)
, Value varchar(20)
, CollectedOnDate Date
, CollectedOnTime Time
, dRank int
)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBsAb','2016-05-31','6:00PM',1)
Insert Into tempSero Values (1, '2016-05-26','NEG','HBsAg','2016-05-31','6:00PM',1)
Insert Into tempSero Values (1, '2016-05-26','NEG','HBV NAT','2016-05-31','6:00PM',1)
Insert Into tempSero Values (1, '2016-05-26','NEG','HCV NAT','2016-05-31','6:00PM',1)
Insert Into tempSero Values (1, '2016-05-26','NEG','HIV NAT','2016-05-31','6:00PM',1)
Insert Into tempSero Values (1, '2016-05-26','POS','Syphilis','2016-05-31','6:00PM',1)
--Need to "weed out" this section
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBsAb','2016-06-01','5:35PM',2)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBsAg','2016-06-01','5:35PM',2)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBV NAT','2016-06-01','5:35PM',2)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HCV NAT','2016-06-01','5:35PM',2)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HIV NAT','2016-06-01','5:35PM',2)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','Syphilis','2016-06-01','5:35PM',2)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBsAb','2016-06-03','5:00AM',3)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBsAg','2016-06-03','5:00AM',3)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBV NAT','2016-06-03','5:00AM',3)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HCV NAT','2016-06-03','5:00AM',3)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HIV NAT','2016-06-03','5:00AM',3)
Insert Into tempSero Values (1, '2016-05-26','POS','Syphilis','2016-06-03','5:00AM',3)
Select
CaseId
,rDate
, Name
, Value
,CollectedOnDate
,CollectedOnTime
, dRank
From tempSero
What exactly do you mean by "it didn't work"? Did you get an error message? Did you not get any results? Did you get results, but not what you were looking for?
When providing sample data, you should ALWAYS provide expected results based on the sample data. Otherwise, people have to guess at what you want.
Also, it looks like you have the Name and Value columns switched in your sample data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 24, 2016 at 11:56 am
it gave me all three "dRank"...
The specific "Names" needed are in the WHERE clause. if you take that there may be an "other" name in the table that is 'POS' but she doesn't need that. Only the specific names.
I'm transposing the data in SSRS using a matrix.
I thought I did that in the Original post...I apologize.
The picture is the expected look... less dRank 2.
updated the columns.
Create Table tempSero
(
CaseId varchar(20)
, rDate datetime
, Value varchar(50)
, Name varchar(20)
, CollectedOnDate Date
, CollectedOnTime Time
, dRank int
)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBsAb','2016-05-31','6:00PM',1)
Insert Into tempSero Values (1, '2016-05-26','NEG','HBsAg','2016-05-31','6:00PM',1)
Insert Into tempSero Values (1, '2016-05-26','NEG','HBV NAT','2016-05-31','6:00PM',1)
Insert Into tempSero Values (1, '2016-05-26','NEG','HCV NAT','2016-05-31','6:00PM',1)
Insert Into tempSero Values (1, '2016-05-26','NEG','HIV NAT','2016-05-31','6:00PM',1)
Insert Into tempSero Values (1, '2016-05-26','POS','Syphilis','2016-05-31','6:00PM',1)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBsAb','2016-06-01','5:35PM',2)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBsAg','2016-06-01','5:35PM',2)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBV NAT','2016-06-01','5:35PM',2)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HCV NAT','2016-06-01','5:35PM',2)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HIV NAT','2016-06-01','5:35PM',2)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','Syphilis','2016-06-01','5:35PM',2)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBsAb','2016-06-03','5:00AM',3)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBsAg','2016-06-03','5:00AM',3)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HBV NAT','2016-06-03','5:00AM',3)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HCV NAT','2016-06-03','5:00AM',3)
Insert Into tempSero Values (1, '2016-05-26','NOTDONE','HIV NAT','2016-06-03','5:00AM',3)
Insert Into tempSero Values (1, '2016-05-26','POS','Syphilis','2016-06-03','5:00AM',3)
Select
CaseId
,rDate
, Name
, Value
,CollectedOnDate
,CollectedOnTime
, dRank
From tempSero
October 24, 2016 at 1:00 pm
It does work, your partition is wrong.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 24, 2016 at 1:08 pm
larrycook74 (10/24/2016)
it gave me all three "dRank"...
There is no dRank in my query. I specifically removed it because it was unnecessary.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 24, 2016 at 1:12 pm
Thank you! I'll review again.
How would you recommend getting "all" the Names on the query? (even though there is NO value or CaseId associated)
October 24, 2016 at 1:34 pm
You would need another table containing all the names, and then outer join it.
SELECT n.FirstName
, tS.CaseID
, rDate
...
FROM Names n LEFT JOIN TempSero ts ON n.Name = ts.Name;
October 24, 2016 at 1:37 pm
Thank you sir!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply