Calculating averages
-
I have the following sql code that gets me Total no of days taken for each workflow step to process: I trying to get averages of how many days an enrollment takes to complete a particular workflow step for each program,
SELECT DISTINCT
E.EnrollmentNumber
,P.ProgramName
,E.EnrollmentDate
,CWS.WorkflowStep
,EWS.BeginDate
,EWS.FinishDate
,CASE WHEN (DATEDIFF(D, CAST(EWS.BeginDate AS DATE), CAST(EWS.FinishDate AS DATE))) = 0 THEN 1
ELSE (DATEDIFF(D, CAST(EWS.BeginDate AS DATE), CAST(EWS.FinishDate AS DATE))) END AS [DaysTaken]FROM
Enrollment AS E
LEFT JOIN EnrollmentWorkflowStep AS EWS ON E.EnrollmentID = EWS.EnrollmentId
LEFT JOIN Code_WorkflowStep AS CWS ON EWS.WorkflowStepID = CWS.WorkflowStepID
LEFT JOIN Program AS P ON E.ProgramID = P.ProgramIDWHERE EWS.FinishDate IS NOT NULL
Result set would look something like this:
I am trying to get average for each workflow step, something like the format below, please advise.
-
SQLPain - Monday, August 28, 2017 5:27 PMI have the following sql code that gets me Total no of days taken for each workflow step to process: I trying to get averages of how many days an enrollment takes to complete a particular workflow step for each program,
SELECT DISTINCT
E.EnrollmentNumber
,P.ProgramName
,E.EnrollmentDate
,CWS.WorkflowStep
,EWS.BeginDate
,EWS.FinishDate
,CASE WHEN (DATEDIFF(D, CAST(EWS.BeginDate AS DATE), CAST(EWS.FinishDate AS DATE))) = 0 THEN 1
ELSE (DATEDIFF(D, CAST(EWS.BeginDate AS DATE), CAST(EWS.FinishDate AS DATE))) END AS [DaysTaken]FROM
Enrollment AS E
LEFT JOIN EnrollmentWorkflowStep AS EWS ON E.EnrollmentID = EWS.EnrollmentId
LEFT JOIN Code_WorkflowStep AS CWS ON EWS.WorkflowStepID = CWS.WorkflowStepID
LEFT JOIN Program AS P ON E.ProgramID = P.ProgramIDWHERE EWS.FinishDate IS NOT NULL
Result set would look something like this:
I am trying to get average for each workflow step, something like the format below, please advise.
Please post DDL and follow ANSI/ISO standards when asking for help.
-
SQLPain - Monday, August 28, 2017 5:27 PM
Also never post pictures on SQL forum. This means in order to get this into DDL, we have to transcribe the picture you posted. We doing this for free and you simply expect us to be awed by you so that we will do this for you for free? It's just plain rude
Guessing at the crap you posted, why are you casting a "begin date" (of what, dammit??) as a date? It should already be a date in the DDL you never bothered to post for us. There should also be a constraint in your DDL to make sure that something isn't ended before it begins.
As an aside, I get a kick out of the way you put commas, the front of each line. You see, I grew up in the old days over six decades ago, with punch cards. Congratulations you're doing just what we did back then. The reason we did it was because it let us rearrange the punch cards in a deck and reuse the cards. Of course it actually is measurably wrong for human readability. Last figures I remember was that adds about 8% to your maintenance time.
Also, why do you have so many left outer joins? I seldom have more than three outer joins in an entire schema.. This is because in a properly designed data model, which we can determine if you posted that DDL, almost all the tables reference each other with DRI constraints. This is not automatically wrong, but it smells really really bad.
Get several hundred dollars worth of free consulting, simply by posting correctly on SQL forums. It's really worth doing things right 🙂
Did you read the basic netiquette for posting on SQL forms? We expect you to post the DDL for your tables so we can see the keys, constraints, and references among the tables. We have none of this in your posting. If you are working in a shop that will give you this fundamental information so you can do your job, it is probably time to update your resume; you are working for idiots.
Also never post pictures on SQL forum. This means in order to get this into DDL, we have to transcribe the picture you posted. We doing this for free and you simply expect us to be awed by you so that we will do this for you for free? It's just plain rude
Guessing at the crap you posted, why are you casting a "begin date" (of what, dammit??) as a date? It should already be a date in the DDL you never bothered to post for us. There should also be a constraint in your DDL to make sure that something isn't ended before it begins.
As an aside, I get a kick out of the way you put commas, the front of each line. You see, I grew up in the old days over six decades ago, with punch cards. Congratulations you're doing just what we did back then. The reason we did it was because it let us rearrange the punch cards in a deck and reuse the cards. Of course it actually is measurably wrong for human readability. Last figures I remember was that adds about 8% to your maintenance time.
Also, why do you have so many left outer joins? I seldom have more than three outer joins in an entire schema.. This is because in a properly designed data model, which we can determine if you posted that DDL, almost all the tables reference each other with DRI constraints. This is not automatically wrong, but it smells really really bad.
Get several hundred dollars worth of free consulting, simply by posting correctly on SQL forums. It's really worth doing things right 🙂
Please post DDL and follow ANSI/ISO standards when asking for help.
-
From what you posted, you'll want to select the step name and average number of days. Don't forget to group by the name. Take a look at the third link in my signature for how to post DDL and test data for your tables if you'd like a tested solution.
-
Thanks Joe and Ed for your honest opinions, I would take it as positively. I went ahead and created the DDL as below. I am looking to calculate the average number of days it takes for each workflow step to process for each program ( as seen in the pic I attached before). I have calculated the 'Days Taken' as you can see on my earlier post. Do let me know if you all need anymore information.
--===== If the test table already exists, drop it
---DROP TABLE #EnrollmentTest
---DROP TABLE #EnrollmentWorkFlowStepTest
---DROP TABLE #Code_WorkFlowStepTest
---DROP TABLE #CodeProgramTest--===== Create the test table with
/*
CREATE TABLE #EnrollmentTest
(
EnrollmentID INT PRIMARY KEY,
EnrollmentNumber BIGINT,
ProgramID INT,
EnrollmentDate AS DATETIME
)CREATE TABLE #EnrollmentWorflowStepTest
(
EnrollmentWorkFlowStepID INT PRIMARY KEY,
EnrollmentID INT,
WorkFlowStepID INT,
BeginDate AS DATETIME,
FinishDate AS DATETIME
)CREATE TABLE #Code_WorkFlowStepTest
(
WorkFlowStepID INT PRIMARY KEY,
WorkFlowStep varchar(100))
CREATE TABLE #ProgramTest
(
ProgramID INT PRIMARY KEY,
ProgramName varchar(255))
--===== Insert the test data into the test table
INSERT INTO #EnrollmentTest
(EnrollmentID, EnrollmentNumber, ProgramID, EnrollmentDate)
SELECT '001', '101', '1', '2017-01-01 00:00:00.000' UNION ALL
SELECT '002', '102', '2', '2017-01-02 00:00:00.000' UNION ALL
SELECT '003', '103', '3', '2017-01-03 00:00:00.000'INSERT INTO #EnrollmentWorkFlowTest
(EnrollmentWorkFlowStepID, EnrollmentID, WorkFlowStepID, BeginDate, FinishDate)
SELECT '10', '001', '1', '2017-01-01 00:00:00.000', '2017-01-02 00:00:00.000' UNION ALL
SELECT '11', '001', '4', '2017-01-04 00:00:00.000', '2017-01-06 00:00:00.000' UNION ALL
SELECT '12', '001', '3', '2017-01-02 00:00:00.000', '2017-01-03 00:00:00.000' UNION ALL
SELECT '13', '002', '1', '2017-01-02 00:00:00.000', '2017-01-05 00:00:00.000' UNION ALL
SELECT '14', '002', '2', '2017-01-06 00:00:00.000', '2017-01-08 00:00:00.000' UNION ALL
SELECT '15', '003', '1', '2017-01-03 00:00:00.000', '2017-01-04 00:00:00.000' UNION ALL
SELECT '16', '003', '2', '2017-01-05 00:00:00.000', '2017-01-06 00:00:00.000'INSERT INTO #Code_WorkFlowStepTest
(WorkFlowStepID, WorkFlowStep)
SELECT '1', 'Application' UNION ALL
SELECT '2', 'PreInspection' UNION ALL
SELECT '3', 'QA Review' UNION ALL
SELECT '4', 'Initiate Payment'INSERT INTO #ProgramTest
(ProgramID, ProgramName)
SELECT '1', 'AEP' UNION ALL
SELECT '2', 'SPVR' UNION ALL
SELECT '3', 'SBL' -
First, I did mean what I said positively, so thank you for taking it as such.
Thanks for the DDL and data. I don't have time to look at it right now, but will when I get home tonight. -
jcelko212 32090 - Monday, August 28, 2017 7:23 PM
Jeez... stop sipping the Ipecac and take a Midol, Joe.
--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 -
Using pivot in SQL should be your best bet. try using below code, this may be helpful. all unique name for the step need to be added in the query to get them as column.
SELECT *
FROM (
SELECT
P.ProgramName
,CWS.WorkflowStep
,(CASE WHEN (DATEDIFF(D, CAST(EWS.BeginDate AS DATE), CAST(EWS.FinishDate AS DATE))) = 0 THEN 1
ELSE (DATEDIFF(D, CAST(EWS.BeginDate AS DATE), CAST(EWS.FinishDate AS DATE))) END) AS [DaysTaken]
FROM
#EnrollmentTest AS E
LEFT JOIN #EnrollmentWorflowStepTest AS EWS ON E.EnrollmentID = EWS.EnrollmentId
LEFT JOIN #Code_WorkFlowStepTest AS CWS ON EWS.WorkflowStepID = CWS.WorkflowStepID
LEFT JOIN #ProgramTest AS P ON E.ProgramID = P.ProgramID
WHERE EWS.FinishDate IS NOT NULL
--group by P.ProgramName,CWS.WorkflowStep
) as s
PIVOT
(
AVG([DaysTaken])
FOR WorkflowStep IN ([Application],[PreInspection], [QA Review], [Initiate Payment])
)AS pvt -
I'm sorry, but I was unable to generate the results from your DDL, so I just accepted that you have the query written to get your base data. Proceeding from that point, I created the following table and populated it with the data I could read from your original post. This table is meant to simulate the results of your query.
IF OBJECT_ID('tempdb.dbo.#YourQuery', 'u') is not null drop table #YourQuery;
CREATE TABLE #YourQuery (
EnrollmentNumber Integer,
ProgramName Varchar(32),
EnrollmentDate Datetime,
WorkflowStep Varchar(32),
BeginDate Datetime,
FinishDate Datetime,
DaysTaken Integer);INSERT INTO #YourQuery(EnrollmentNumber, ProgramName, EnrollmentDate, WorkflowStep, BeginDate, FinishDate, DaysTaken)
VALUES(101294, 'Appliance Efficiency Program', '2017/06/07', 'Application', '2017/06/07', '2017/06/07', 1),
(101294, 'Appliance Efficiency Program', '2017/06/07', 'Initiate Payment', '2017/06/15', '2017/06/16', 1),
(102294, 'Appliance Efficiency Program', '2017/06/07', 'QA Review', '2017/06/07', '2017/06/15', 8),
(102295, 'Solar Program', '2017/06/07', 'Application', '2017/06/07', '2017/06/22', 15),
(102295, 'Solar Program', '2017/06/07', 'Pre-Inspection', '2017/06/22', '2017/06/23', 1),
(102296, 'Lighting Program', '2017/06/07', 'Pre-Inspection', '2017/06/07', '2017/06/08', 1),
(102296, 'Lighting Program', '2017/06/07', 'Pre-Inspection', '2017/06/08', '2017/07/07', 29);If I'm reading your request correctly, you wanted the average number of days for each workflow step. In your desired results picture in your original post, you show it by program. Instead of using a PIVOT, I'll use an old technique called a crosstab to calculate the averages. For information on the technique, Jeff Moden has an excellent article on it at http://qa.sqlservercentral.com/articles/T-SQL/63681/.
SELECT ProgramName,
Application = AVG(CASE WHEN WorkflowStep = 'Application' THEN DaysTaken END),
Correction = AVG(CASE WHEN WorkflowStep = 'Correction' THEN DaysTaken END),
InitiatePayment = AVG(CASE WHEN WorkflowStep = 'Initiate Payment' THEN DaysTaken END),
PostInspection = AVG(CASE WHEN WorkflowStep = 'Post-Inspection' THEN DaysTaken END),
PreInspection = AVG(CASE WHEN WorkflowStep = 'Pre-Inspection' THEN DaysTaken END),
QAReview = AVG(CASE WHEN WorkflowStep = 'QA Review' THEN DaysTaken END),
Reinspection = AVG(CASE WHEN WorkflowStep = 'Reinspection' THEN DaysTaken END)
FROM #YourQuery
GROUP BY ProgramName
ORDER BY ProgramName;I hope this at least helps get you closer to what you need.
-
Jeff Moden - Tuesday, August 29, 2017 3:36 PM
I think it's well beyond Midol. You'll have to recommend something a little stronger, like Troll-B-Gone.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply