October 16, 2008 at 4:02 pm
From the below data, I need to return the ID from the coApptResources table where
the Resource in ptAppointments matches the name in coApptResource, HOWEVER, I need
to also strip off the '-NORTH' and '-SOUTH' and if the Resource = 'CHRISTOPHER J TRAUTH'
return 'TRAUTH'. Any ideas?
coApptResources
ID Name
-- ----
1 AKS
2 CHEMO
3 COUMADIN CLINIC
4 DRSG CHANGE
5 EAKIN
6 LOCUM
7 HIRSCH
8 INJECTIONS
9 LAB
10 LANA
11 NURSE VISIT
12 OUTREACH-BRECK
13 OUTREACH-C CITY
14 OUTREACH-COLEMAN
15 OUTREACH-HASKELL
16 OUTREACH-SNYDER
17 OUTREACH-STAMFORD
18 OUTREACH-SWEETWATER
19 REEDY
20 TRAUTH
21 X-RAY
22 YIMER
23 WALK-IN
24 WALK-INS NORTH
25 KALLA-SOUTH
26 GOOD
27 FABRIZIO PAC, GINGER
ptAppointments (Distinct Resource column)
------------------
AKS-NORTH
AKS-SOUTH
CHEMO-NORTH
CHEMO-SOUTH
CHRISTOPHER J TRAUTH
COUMADIN CLINIC
DRSG CHANGE/FLUSH-NORTH
DRSG CHANGE/FLUSH-SOUTH
EAKIN-NORTH
EAKIN-SOUTH
FABRIZIO PAC, GINGER
GOOD
HIRSCH-NORTH
HIRSCH-SOUTH
INJECTIONS-NORTH
INJECTIONS-SOUTH
KALLA-NORTH
KALLA-SOUTH
LAB-NORTH
LAB-SOUTH
LANA-NORTH
LANA-SOUTH
NURSE VISIT
OUTREACH-BRECK
OUTREACH-C CITY
OUTREACH-COLEMAN
OUTREACH-HASKELL
OUTREACH-SNYDER
OUTREACH-STAMFORD
OUTREACH-SWEETWATER
REEDY -NORTH
TRAUTH-NORTH
TRAUTH-SOUTH
VICTOR J HIRSCH M.D.
WALK-INS NORTH
X-RAY
YIMER
October 16, 2008 at 4:39 pm
Please review the following article and provide the relevant data so we can provide a tested and validated solution:
Best Practices: http://www.sqlservercentral.com/articles/Best+Practices/61537/
In the meantime, you could do something along the lines of:
;WITH myAppointments (ResourceName)
AS (SELECT replace(replace(replace(Resource, '-SOUTH', ''), '-NORTH', ''), 'CHRISTOPHER J ', '')
FROM ResourceTable)
SELECT ID
FROM coApptResources r
JOIN myAppointments a ON a.ResourceName = r.Name;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 17, 2008 at 8:11 am
While that helps me some, I can't make it work in this:
DELETE FROM ptSchedule
WITH MyAppointments (ResourceName)
AS (SELECT REPLACE(REPLACE(REPLACE(Resource, '-SOUTH', ''), '-NORTH', ''), 'CHRISTOPHER J ', '')
FROM ptAppointments)
INSERT INTO ptSchedule
([MRN]
, [ApptTypeID]
, [ResourceId]
, [StartDtTm]
, [EndDtTm]
, [Description]
, [Notes]
, [Status]
, [CheckedInDtTm]
, [CheckedOutDtTm]
, [Flow]
, [ModifiedDtTm]
, [ModifiedByUserID]
, [WaitList]
, [ApptBlockID]
, [temp]
, [EncounterID])
SELECT
PID -- MRN
, (CASE WHEN UPPER(TYPE) LIKE 'CHEMO%' THEN 1
WHEN UPPER(TYPE) LIKE 'OV%' THEN 2
WHEN UPPER(TYPE) LIKE 'BMBX%' THEN 3
WHEN UPPER(TYPE) LIKE 'COUMADIN%' THEN 4
WHEN UPPER(TYPE) LIKE 'LAB%' THEN 5
WHEN UPPER(TYPE) LIKE 'DRS%' THEN 6
WHEN UPPER(TYPE) LIKE 'HOSP%' THEN 7
WHEN UPPER(TYPE) LIKE 'F/U%' THEN 8
WHEN UPPER(TYPE) LIKE 'LUNCH%' THEN 9
WHEN UPPER(TYPE) LIKE 'NV%' THEN 10
WHEN UPPER(TYPE) LIKE 'WALK-IN%' THEN 11
WHEN UPPER(TYPE) LIKE 'X-RAY%' THEN 12
WHEN UPPER(TYPE) LIKE 'NEW%' THEN 13
WHEN UPPER(TYPE) LIKE 'SHORT APPT' THEN 14
WHEN UPPER(TYPE) LIKE 'ESTABLISHED%' THEN 15 -- AppTypeID
ELSE 2
END)
, (SELECT r.ResourceID
FROM coApptResources r
JOIN MyAppointments a ON a.ResourceName = r.Name) -- Resource ID
, StartTime -- StartDtTm
, EndTime -- EndDtTm
, Description -- Description
, Notes -- Notes
, (SELECT StatusID FROM coApptStatus coa
WHERE coa.Status = UPPER(Status)) -- Status
, NULL -- CheckedInDtTm
, NULL -- CheckedOutDtTm
, NULL -- Flow
, NULL -- ModifiedDtTm
, NULL -- ModifiedByUserID
, NULL -- WaitList
, NULL -- ApptBlockID
, NULL -- temp
, NULL -- EncounterID
FROM ptAppointments
I get the dreaded: Subquery returned more than 1 value. Can you see something obvious?
October 17, 2008 at 8:51 am
Creation and dummy data scripts avail here:
October 17, 2008 at 9:05 am
Thanks for the creation scripts. Is there any reason you can't simply do this?
[font="Courier New"]SELECT DISTINCT *
FROM ptAppointments P
INNER JOIN coApptResources C ON LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(P.Location,'-SOUTH',''),'-NORTH',''),'CHRISTOPHER J ',''))) = LTRIM(RTRIM(C.Name))
[/font]
October 17, 2008 at 9:16 am
I should note, that I wouldn't necessarily use that as a final solution unless you had to. That should get the links right for the time being, but it'd be better in the long run if you could assign those locations id's and just link based on ID rather than on manipulated varchar's.
If it's not possible to do that, you could also create a reference table to link through which would still probably be better than inline string manipulation.
IE.
CValueVValue
TRAUTHTRAUTH-NORTH
TRAUTHTRAUTH-SOUTH
TRAUTHCHRISTOPHER J TRAUTH
...
October 17, 2008 at 9:17 am
Garadin (10/17/2008)
Thanks for the creation scripts. Is there any reason you can't simply do this?
I don't see what that does for me.
October 17, 2008 at 9:25 am
MrBaseball34 (10/17/2008)
Garadin (10/17/2008)
Thanks for the creation scripts. Is there any reason you can't simply do this?I don't see what that does for me.
Then apparently I'm on the wrong track and don't understand what it is you're asking for. I thought you were trying to link the Location in coApptResources to the location in ptAppointments, sans modifiers. I'm sure someone else on this thread does tho, so feel free to ignore the previous two posts =).
October 17, 2008 at 9:42 am
Take a look at my code to do the insert, that should show you what I'm trying to do.
October 17, 2008 at 10:08 am
I did, and it doesn't help me see how my solution wouldn't work, because the way I'm looking at it, you would still just do:
FROM ptAppointments P
LEFT JOIN coApptResources C ON LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(P.Location,'-SOUTH',''),'-NORTH',''),'CHRISTOPHER J ',''))) = LTRIM(RTRIM(C.Name))
Then, replace the subquery / cte with C.ResourceID and alias your Type case to P.TYPE
By the way, the Status subquery is returning more than 1 value (not sure if the cte sq is doing it too, but the status one definitely is, as all you're doing is selecting the entire table and capitalizing it).
Also, I used a LEFT JOIN because "DRSG CHANGE/FLUSH" isn't in your coapptResources table, although "DRSG CHANGE" is, so you may want to add that to your list of things to be modified.
October 17, 2008 at 1:02 pm
Final working code, thanks, Seth.
DELETE FROM ptSchedule
INSERT INTO ptSchedule
([MRN]
, [ApptTypeID]
, [ResourceId]
, [StartDtTm]
, [EndDtTm]
, [Description]
, [Notes]
, [Status]
, [CheckedInDtTm]
, [CheckedOutDtTm]
, [Flow]
, [ModifiedDtTm]
, [ModifiedByUserID]
, [WaitList]
, [ApptBlockID]
, [temp]
, [EncounterID])
SELECT
PID as MRN
, (CASE WHEN UPPER(P.TYPE) LIKE 'CHEMO%' THEN 1
WHEN UPPER(P.TYPE) LIKE 'OV%' THEN 2
WHEN UPPER(P.TYPE) LIKE 'BMBX%' THEN 3
WHEN UPPER(P.TYPE) LIKE 'COUMADIN%' THEN 4
WHEN UPPER(P.TYPE) LIKE 'LAB%' THEN 5
WHEN UPPER(P.TYPE) LIKE 'DRS%' THEN 6
WHEN UPPER(P.TYPE) LIKE 'HOSP%' THEN 7
WHEN UPPER(P.TYPE) LIKE 'F/U%' THEN 8
WHEN UPPER(P.TYPE) LIKE 'LUNCH%' THEN 9
WHEN UPPER(P.TYPE) LIKE 'NV%' THEN 10
WHEN UPPER(P.TYPE) LIKE 'WALK-IN%' THEN 11
WHEN UPPER(P.TYPE) LIKE 'X-RAY%' THEN 12
WHEN UPPER(P.TYPE) LIKE 'NEW%' THEN 13
WHEN UPPER(P.TYPE) LIKE 'SHORT APPT' THEN 14
WHEN UPPER(P.TYPE) LIKE 'ESTABLISHED%' THEN 15
ELSE 2
END) as ApppTypeID
, c.ResourceID as ResourceID
, StartTime as StartDtTm
, EndTime as EndDtTm
, P.TYPE as Description
, SUBSTRING(Notes, 1, 100) as Notes -- Only first 100 chars allowed here
, (SELECT StatusID FROM coApptStatus coa WHERE coa.Status = UPPER(p.Status)) as Status
, NULL as CheckedInDtTm
, NULL as CheckedOutDtTm
, NULL as Flow
, NULL as ModifiedDtTm
, NULL as ModifiedByUserID
, NULL as WaitList
, NULL as ApptBlockID
, NULL as temp
, NULL as EncounterID
FROM ptAppointments P
INNER JOIN coApptResources C
ON LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(P.Location,'-SOUTH',''),'-NORTH',''),'CHRISTOPHER J ',''), '/FLUSH',''))) = LTRIM(RTRIM(C.Name))
October 17, 2008 at 1:11 pm
October 17, 2008 at 1:19 pm
MrBaseball34 (10/17/2008)
I get the dreaded: Subquery returned more than 1 value. Can you see something obvious?
Yes, the following code is the problem:
, (SELECT r.ResourceID
FROM coApptResources r
JOIN MyAppointments a ON a.ResourceName = r.Name) -- Resource ID
This will return multiple rows because the relationship between coApptResources and ptAppointments will return multiple rows. You can either do as Seth has recommended, or modify the above to return a distinct ResourceID:
, (SELECT DISTINCT r.ResourceID
FROM coApptResources r
JOIN MyAppointments a ON a.ResourceName = r.Name) -- Resource ID
Or, you can modify the CTE to return a distinct list as in:
;WITH MyAppointments (ResourceName)
AS (SELECT DISTINCT REPLACE(REPLACE(REPLACE(Resource
, '-SOUTH', ''), '-NORTH', ''), 'CHRISTOPHER J ', '')
FROM ptAppointments)
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 17, 2008 at 1:31 pm
I have CTE's down on my list of things to do some more research on, so this may be totally off base, but isn't that still going to return multiple values, regardless of the DISTINCT? He's still got a subquery that makes no reference to the outer query, and therefore should return every row from your CTE. I'm not really sure why either subquery is there at all, but it seems to me that they'd need the external reference back to ptAppointments to work.
October 17, 2008 at 1:40 pm
Seth, you are right - I missed that 'small' little detail 😉
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply