Hi, I work in a hospital and look after a db containing the surgical procedures performed on our patients. I am trying to write a query that will count the number of specific combinations of procedures performed by our two surgeons but I'm struggling to extract just the right patient records.
I have attached a script that will produce a simplified cutdown version of the db but will hopefully demonstrate my problem. Each record has a PatID, Date, Doctor and upto four possible operation procedures (op1 to op4) that a patient may receive. There are 6 possible options for surgical procedure (a to f). I need to count the number of times each surgeon has done a procedure where op1 to op4 includes procedure b AND d OR just d irrespective of any other recorded procedures for that patient. I also need the same thing for procedures b AND e but I'm sure I can figure that from any help you give me with the former. I think my inexperience with resolving the boolean string is my problem? I don't seem to able to get to the right syntax on all four of the 'op' fields together.
The output should be no more than; Doc, CountOfCases
Thank you in advance for any help you can provide me.
Ian
NB: I'm not sure if my .sql file uploaded so I've added the text below:
CREATE TABLE [dbo].[Operation] (
[PatID] [nchar](10) NOT NULL,
[OpDate] [datetime] NULL,
[Doc] [char](20) NULL,
[Op1] [char](20) NULL,
[Op2] [char](20) NULL,
[Op3] [char](20) NULL,
[Op4] [char](20) NULL
)
GO
INSERT INTO dbo.Operation VALUES ('1', '20210101', 'MrX', 'a', '', '', '');
INSERT INTO dbo.Operation VALUES ('2', '20210101', 'MrY', 'c', '', '', '');
INSERT INTO dbo.Operation VALUES ('3', '20210101', 'MrX', 'a', 'b', 'd', 'c');
INSERT INTO dbo.Operation VALUES ('4', '20210103', 'MrX', 'b', 'e', 'f', 'a');
INSERT INTO dbo.Operation VALUES ('5', '20210103', 'MrX', 'c', '', '', '');
INSERT INTO dbo.Operation VALUES ('6', '20210104', 'MrY', 'd', 'f', '', '');
INSERT INTO dbo.Operation VALUES ('7', '20210105', 'MrX', 'b', '', '', '');
INSERT INTO dbo.Operation VALUES ('8', '20210105', 'MrY', 'd', '', '', '');
INSERT INTO dbo.Operation VALUES ('9', '20210105', 'MrY', 'e', '', '', '');
INSERT INTO dbo.Operation VALUES ('10', '20210108', 'MrX', 'a', 'd', 'b', 'f');
INSERT INTO dbo.Operation VALUES ('11', '20210108', 'MrX', 'e', 'f', 'c', 'a');
INSERT INTO dbo.Operation VALUES ('12', '20210112', 'MrY', 'b', 'a', 'd', '');
INSERT INTO dbo.Operation VALUES ('13', '20210114', 'MrX', 'd', 'b', '', '');
INSERT INTO dbo.Operation VALUES ('14', '20210114', 'MrX', 'e', 'b', '', '');
INSERT INTO dbo.Operation VALUES ('15', '20210114', 'MrY', 'a', 'b', 'c', 'd');
INSERT INTO dbo.Operation VALUES ('16', '20210114', 'MrY', 'b', 'd', 'f', '');
INSERT INTO dbo.Operation VALUES ('17', '20210115', 'MrX', 'b', 'd', 'f', '');
INSERT INTO dbo.Operation VALUES ('18', '20210115', 'MrX', 'c', '', '', '');
INSERT INTO dbo.Operation VALUES ('19', '20210118', 'MrY', 'e', 'c', '', '');
INSERT INTO dbo.Operation VALUES ('20', '20210119', 'MrY', 'b', 'f', '', '');
March 11, 2021 at 1:19 pm
I have 2 options assuming im understanding correctly what ur trying to achieve:
--##################################################################################
--UNPIVOT: Convert Columns to Rows
--##################################################################################
select Doc,Name_OF,Number_OF
from [dbo].[Operation
UNPIVOT
(Number_OF for Name_OF in (Op1,Op2,Op3,Op4)) up
--Filtered only rows where in the column/row theres no empty string
--Count/Group by cant be applied for multiple options this way
select DOC,count(*)
from (
select Doc,Name_OF,Number_OF
from [dbo].[Operation
UNPIVOT
(Number_OF for Name_OF in (Op1,Op2,Op3,Op4)) up
) kek
where Number_OF != ''
group by Doc
--##################################################################################
--Case when
--##################################################################################
--Count OPx if its not an empty string
select doc, sum(Count_OP1+Count_OP2+Count_OP3+Count_OP4)
from (
select *
,Count_OP1 = case when Op1 != ''
THEN 1
else 0 END
,Count_OP2 = case when Op2 != ''
THEN 1
else 0 END
,Count_OP3 = case when Op3 != ''
THEN 1
else 0 END
,Count_OP4 = case when Op4 != ''
THEN 1
else 0 END
from [dbo].[Operation
) kek
group by doc
I want to be the very best
Like no one ever was
Each record has a PatID, Date, Doctor and upto four possible operation procedures (op1 to op4) that a patient may receive. There are 6 possible options for surgical procedure (a to f). I need to count the number of times each surgeon has done a procedure where op1 to op4 includes procedure b AND d OR just d irrespective of any other recorded procedures for that patient.
'b AND d' is a subset of 'just d irrespective ...' so they're not mutually exclusive groups. In this case it seems the direct way to count would be using ktflash's method #2 using CASE WHEN's imo.
select o.Doc, count(*) all_cases, sum(v.b) both
from dbo.Operation o
cross apply (values ((case when o.Op1='d' then 1
when o.Op2='d' then 1
when o.Op3='d' then 1
when o.Op4='d' then 1 else 0 end),
(case when o.Op1='b' then 1
when o.Op2='b' then 1
when o.Op3='b' then 1
when o.Op4='b' then 1 else 0 end))) v(d, b)
where v.d=1
/*or (v.d+v.b)=2 ** adding this makes no difference to total rows ** */
group by o.Doc;
Docall_casesboth
MrX 44
MrY 53
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
March 11, 2021 at 7:39 pm
SELECT
Doc,
SUM(CASE WHEN CHARINDEX('d', Op1+Op2+Op3+Op4) > 0
THEN 1 ELSE 0 END) AS total_d,
SUM(CASE WHEN CHARINDEX('b', Op1+Op2+Op3+Op4) > 0 AND CHARINDEX('d', Op1+Op2+Op3+Op4) > 0
THEN 1 ELSE 0 END) AS total_b_and_d
FROM dbo.Operation
GROUP BY Doc
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 11, 2021 at 7:54 pm
I need to count the number of times each surgeon has done a procedure where op1 to op4 includes procedure b AND d OR just d irrespective of any other recorded procedures for that patient.
You know that: (b AND d OR d) = d?
It would be easier to answer if you supplied the output you expect.
March 11, 2021 at 8:05 pm
Hi ktflash and Steve,
Thank you both very much for showing me the error in my boolean logic, obvious really 🙂 and then how to get the results I needed. I've not come across 'cross apply' before, so thank you again.
Ian
March 11, 2021 at 8:13 pm
You really don't need UNPIVOT or CROSS APPLY for this. They do add some overhead.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 11, 2021 at 8:50 pm
Hi Scott, thanks for your solution, I'm really sorry I didn't see it before I posted my earlier reply. That's some helpful advice about UNPIVOT and CROSS APPLY. For someone with my experience I kinda know only what I need to know so this really is useful advice, thank you.
March 11, 2021 at 10:01 pm
No problem. I don't have a real problem with CROSS APPLY, other than the performance hit. I personally am not a big fan of UNPIVOT, I find the syntax kludgy and limited, but others don't mind it.
Here's an alternative coding of my approach that may be cleaner/clearer for some:
SELECT
Doc,
SUM(CASE WHEN 'd' IN (Op1, Op2, Op3, Op4) THEN 1 ELSE 0 END) AS total_d,
SUM(CASE WHEN 'b' IN (Op1, Op2, Op3, Op4) AND 'd' IN (Op1, Op2, Op3, Op4)
THEN 1 ELSE 0 END) AS total_b_and_d
FROM dbo.Operation
WHERE 'd' IN (Op1, Op2, Op3, Op4)
GROUP BY Doc
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 12, 2021 at 10:45 pm
Scott, just asking for clarification. You spoke of finding the UNPIVOT syntax kludgy (and I agree) but you posted the "crosstab" syntax which is the old-school way to PIVOT rows into columns.
Did you mean to post CROSS APPLY with a values clause as an example of how to UNPIVOT columns into rows?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 12, 2021 at 11:13 pm
Hmm, I don't believe I used a CROSS TAB. Yes, I used a CASE in a SUM, but not in a CROSS TAB fashion, from my understanding of a CROSS TAB. And I would not use a CROSS APPLY in this situation just because of the overhead.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 18, 2021 at 3:14 am
Summing case statements instead of doing a PIVOT, is referred to as a cross tab.
Also, I hear you talking about the overhead of a CROSS APPLY. I've never experienced any problems with it. Cpuld you please give us some examples, timing comparisons, etc?
I agree there is no need no need for CROSS APPLY when a simple join or existence test will do, but when you are looking for the latest sale for an account (for example) it can often speed things up significantly.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 18, 2021 at 1:47 pm
Summing case statements instead of doing a PIVOT, is referred to as a cross tab.
Exactly. I used a CASE in a SUM but not instead of a PIVOT, so it's not really a cross tab, at least as I see it.
Similarly, if I do a:
SELECT SUM(CASE WHEN active = 0 THEN 1 ELSE 0 END) AS inactive_count
FROM dbo.some_table_name
I've used a CASE within a SUM but, again, it's not a cross tab per se, or at least I don't see it as one.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply