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 12 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply