TSQL Showing improvement in values on different rows
-
I have been asked to write a report to show the improvements based on multiple outcomes.
All of the data is stored in one table as below with a grading of 1-5 where 1 is poor and 5 is good.
I need to show a percentage of improvements from the outcomes, if it goes down or stays the same I need to ignore the result and only show the improvements as a total.caseid happiness outcome createddate 868 1 5 2018-04-09 09:24:55.000 868 5 1 2018-04-09 10:01:38.000 22609 1 1 2018-04-09 10:26:16.000 22609 2 1 2018-04-09 11:07:18.000 50159 2 3 2018-04-09 11:10:40.000 50159 1 2 2018-04-09 11:26:02.000 54085 3 1 2018-04-09 11:33:51.000 54085 1 1 2018-04-09 11:34:22.000 54085 1 5 2018-04-09 11:35:02.000 54085 3 5 2018-04-09 11:38:13.000 54085 1 5 2018-04-09 11:49:10.000 54085 3 5 2018-04-09 11:55:21.000 Something like this would be great but im not sure on how to achieve this. I can work out the percentage easy enough but its getting the count where an improvement has been made is what im struggling with.
Total Number happiness outcome 4 75% 25% Any help or suggestions would be greatly appreciated.
-
What have you tried so far? I also don't really understand how you get the expected results from your sample data. Could you explain further?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
dave 92282 - Wednesday, July 4, 2018 7:34 AM
First, help us help you. A lot of us like to test our solutions for folks before we post an answer. Please see the article at the first link in my signature line below under "Helpful Links'.
Second, if we did your process but only for Case_ID = 54085, are you saying that because just the last 2 entries show an improvement from 1 to 3, then that qualifies as a case that improved (1 changed to 3) even though the earliest entry started at the same value (3)?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Hi, First of all apologies for not sharing all of the infoand my bad post.
I have attached the create table and sample data into thescript.txt file.
The idea of the script I am trying to produce is to see ifthere are improvements being made in the way the case is being dealt with. Therefore, if the first recorded outcome for a case scoredis a 1 and a follow up score is 2 then this is classed as a positive movement.If the score goes up and then back to the original score this is classed as no improvement.I only need to look at the first value and the most recent anything in betweenis not relevant. The improvement measure needs to be captured for each questionasked and provide a % of improvement based on the logic above. By counting thedistinct number of cases and then counting where safeathome2nd is greater thansafeathome1st.
I have come up with the following script, which gives me thefirst and last values:
create table #tempegress2(
caseid int,
createddate datetime,
safeathome1st int,
LatestDate datetime,
safeathome2nd int
);WITH safeathome2nd AS
(SELECT caseid, safe_at_home, createddate, ROW_NUMBER() OVER (Partition BY caseid ORDER BY createddate DESC) rn FROM tempegress), safeathome1st AS
(SELECT caseid, safe_at_home, createddate, ROW_NUMBER() OVER (Partition BY caseid ORDER BY createddate ASC) rn FROM tempegress )insert into #tempegress2
SELECT a.caseid,
b.createddate,
b.Safe_At_Home safeathome1st,
c.createddate LatestDate,
c.Safe_At_Home safeathome2nd
FROM (SELECT DISTINCT caseid
FROM tempegress) a
INNER JOIN safeathome1st b ON a.caseID = b.caseID AND b.rn = 1
INNER JOIN safeathome2nd c ON a.caseid = c.caseID AND c.rn = 1
From here I can do a count and work out my desired % values.I am now struggling to incorporate the remaining fields without making thisscript rather long as there are 4 more fields to incorporate in this. Is therepossible a better way of doing this at all?
-
Here's a rewrite of your query. You should be able to see how to easily extend it to include the other values.
SELECT DISTINCT
caseid
, FIRST_VALUE(createddate) OVER (PARTITION BY caseid ORDER BY createddate) AS createddate
, FIRST_VALUE(safe_at_home) OVER (PARTITION BY caseid ORDER BY createddate) AS safeathome1st
, LAST_VALUE(createddate) OVER (PARTITION BY caseid ORDER BY createddate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS LatestDate
, LAST_VALUE(safe_at_home) OVER (PARTITION BY caseid ORDER BY createddate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS safeathome2nd
FROM tempegressJohn
-
Thanks John Mitchell-245523 thats much better than what i had, its worked a treat.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply