May 17, 2019 at 1:38 am
Well, I don't think the SAS code is that complex, it's just that T-SQL (while I do like it) is a bit ahem "archaic"?
For example, in a SELECT statement, I really wish I could use the derivation of a column to the "left" in a subsequent derivation to the "right", instead of having to repeat verbose code.
In any case, here is my SAS code:
/* manual fix for qualified_bed_days */
qualified_bed_days_recode=qualified_bed_days;
if episode_of_care_type ='5' then do;
if unqualified_bed_days le 10 then
qualified_bed_days_recode = sum(episode_length_of_stay, - unqualified_bed_days);
if qualified_bed_days_recode = . or qualified_bed_days_recode < 0 then
qualified_bed_days_recode = 0;
if qualified_bed_days_recode > episode_length_of_stay then
qualified_bed_days_recode = episode_length_of_stay;
end;
Comments on the SAS code:
Assume any of the columns could be NULL.
In SAS, the SUM function sums multiple columns (this is different than T-SQL), and handles nullability. For example, sum(5,-3)=2, sum(5,-null)=5, sum(null,-3)=-3, and sum(null,-null)=null.
For brevity, I won't expand on the rest of the logic. IMO it should be straightforward (even if you don't know SAS). I can update or reply to this post if needed.
This is the best T-SQL I can come up with so far:
WITH cte1
AS (
SELECT *
,qualified_bed_days_recode_temp =
CASE (episode_of_care_type)
WHEN ('5') THEN
CASE
WHEN (ISNULL(unqualified_bed_days,0) <= 10) THEN IIF(ISNULL(episode_length_of_stay,0) - ISNULL(unqualified_bed_days,0) > 0,ISNULL(episode_length_of_stay,0) - ISNULL(unqualified_bed_days,0),0)
ELSE qualified_bed_days
END
ELSE qualified_bed_days
END
FROM tmp.delete_qualified_bed_days_recode
),
cte2
AS (
SELECT *
,qualified_bed_days_recode =
CASE (episode_of_care_type)
WHEN ('5') THEN IIF(qualified_bed_days_recode_temp > ISNULL(episode_length_of_stay,0),episode_length_of_stay,qualified_bed_days_recode_temp)
ELSE qualified_bed_days_recode_temp
END
FROM cte1
)
SELECT *
FROM cte2
-- WHERE qualified_bed_days_recode_temp != qualified_bed_days_recode
I could split this into a 3rd CTE, moving the first IIF to the 2nd CTE and removing a bit of nesting.
My "real" code is a view containing 4 CTE's with about 400 columns. I have saved the view to a table, and was surprised that querying the view has good performance vs. querying the table.
However, is this a scenario where I'd be better off creating a scalar function, where the language is a bit more robust?
For example:
CREATE FUNCTION dbo.udf_qualified_bed_days_recode
(
@episode_of_care_type CHAR(1)
,@episode_length_of_stay INT
,@qualified_bed_days INT
,@unqualified_bed_days INT
)
-- WITH ENCRYPTION, SCHEMABINDING, EXECUTE AS CALLER|SELF|OWNER|USER
RETURNS INT
AS
BEGIN
DECLARE @qualified_bed_days_recode INT = @qualified_bed_days;
IF @episode_of_care_type != 5
RETURN @qualified_bed_days_recode
IF @unqualified_bed_days < 10
BEGIN
-- This takes care of NULLs - @qualified_bed_days_recode cannot be NULL
SET @qualified_bed_days_recode = ISNULL(@episode_length_of_stay,0) - ISNULL(@unqualified_bed_days,0)
IF @qualified_bed_days_recode < 0
SET @qualified_bed_days_recode = 0
IF @qualified_bed_days_recode > ISNULL(@episode_length_of_stay,0)
SET @qualified_bed_days_recode = @episode_length_of_stay
END
RETURN @qualified_bed_days_recode
END
GO
How would YOU implement this logic? Pure T-SQL, function, or some other approach?
May 17, 2019 at 2:02 pm
I had a go at simplifying your first piece of code. As I don't have the underlying table structures, I may have made some slight mistakes, but see whether you think that this is any better:
SELECT *
,qualified_bed_days_recode = CASE
WHEN qbdr.episode_of_care_type = '5'
AND qbdrt.qualified_bed_days_recode_temp > ISNULL(
qbdr.episode_length_of_stay
,0
) THEN
qbdr.episode_length_of_stay
ELSE
qbdrt.qualified_bed_days_recode_temp
END
FROM tmp.delete_qualified_bed_days_recode qbdr
CROSS APPLY
(
SELECT qualified_bed_days_recode_temp = CASE
WHEN qbdr.episode_of_care_type = '5'
AND ISNULL(qbdr.unqualified_bed_days, 0) <= 10 THEN
IIF(
ISNULL(qbdr.episode_length_of_stay, 0)
- ISNULL(qbdr.unqualified_bed_days, 0) > 0
,ISNULL(qbdr.episode_length_of_stay, 0)
- ISNULL(qbdr.unqualified_bed_days, 0)
,0)
ELSE
qualified_bed_days
END
) qbdrt;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply