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;
Doc all_cases both
MrX 4 4
MrY 5 3
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply