-1

UPDATE: This has been solved. I created a "calculated variable" in tableau called [Lab Volume] and put this code into the calculation:

If [Average Results Per Day] <= 25 THEN 'Low'
ELSEIF  [Average Results Per Day] > 25 and [Average Results Per Day] < 75 THEN 'Medium'
ELSEIF [Average Results Per Day] >= 75 THEN 'High' 
END

Thank you for your help!

ORIGINAL POST: I am having trouble figuring out how to create new categories of data, sum these categories, and then find how many days since the lab last reported. Basically I have a list of unique [patientIDs], [Lab_Name], [result_reported_date], which I believe are the only variables I need for this project. SQL code will go into Tableau for data viz.

Here is my dilemma:

(1) I need to average the number of reported lab results for each laboratory [lab_names] and then categorize them into three volume categories (low volume labs, medium volume labs, high volume labs) based upon how many samples submitted each day in the last 30-day window: low is <25 samples/day; medium is 25-50 samples/day; high is 75+ samples/day.

Input looks something like this, with mulitple labs over multiple days with a sum of the number of samples submitted. The file has 750 different labs, with dates going back 30+ days, and a sum of the samples submitted on each day.

Lab_Name Date Samples_Submitted
LabA 1/01/22 80
LabB 1/01/22 22
LabA 1/02/22 101
LabB 1/01/22 12
LabA 1/03/22 95
LabB 1/03/22 80

Output would be something like this:

Lab_Name Vol_category Average_sample_per30day
LabA low 22
LabB medium 35
LabC high 85
LabD low 15
LabE medium 32

This is the code I have to count the number of samples submitted by Lab_Name. I don't know how to average the number of samples submitted over the last 30-days and then create low, medium, high categories.

select 
Lab_Name,
count(Lab_Name) as Lab_count
from dbo.Lab_tests

where 
result_reported_date > '2022-07-01' and
Test_LOINC in ('41458-1', '94306-8', '94309-2', '94500-6', '94502-2', '94531-1', '94533-7', 
                '94534-5', '94559-2', '94565-9', '94568-9', '94640-0', '94756-4', '94757-2', 
                '94759-8', '94760-6', '94845-5', '95406-5', '95409-9', '95423-0', '95425-5', 
                '95608-6', '96094-8', '96123-5', '96448-6', '96986-5', '99999-9', '94558-4', 
                                '95209-3', '96119-3', '97097-0') 
group by Lab_Name 
order by Lab_count desc ;
Moosetracks28
  • 13
  • 3
  • @JonasMetzler thank you for the feedback. Does this make it more understandable? – Moosetracks28 Nov 19 '22 at 04:35
  • Could you please add the corresponding sample input for that outcome? You are talking about an average, but your query doesn't build one. That's confusing. – Jonas Metzler Nov 19 '22 at 07:30
  • I don't understand what you are averaging. Your code is generating one count per lab. Did you want to generate a count per lab per some time period instead? Then you could get an average of those counts for each lab. – Tom Nov 19 '22 at 20:23
  • I have a raw file with hundreds of labs and how many samples they each have submitted each day. From this, I need to determine the average number of samples submitted by each lab per week, over the last 30-days. Once I have that number, I can divide them into low, medium, and high categories. – Moosetracks28 Nov 21 '22 at 01:21
  • @JonasMetzler I will add a corresponding in table. – Moosetracks28 Nov 21 '22 at 01:22
  • This needs to be an automated process in Tableau. I am trying to make SQL code to import into Tableau to make three tables -- (1) Low Volume labs that report < 25 samples per week on average over the last 30-days; (2) Medium Volume labs that report >= 25 to < 75 samples per week on average over the last 30-days; and (3) High Volume Labs that report >= 75 samples per week on average over the last 30-days. – Moosetracks28 Nov 21 '22 at 02:06

2 Answers2

1

Maybe...

select 
Lab_Name,
count(Lab_Name) as Lab_count,

CASE 
WHEN count(Lab_Name) <= 25 THEN 'Low'
WHEN count(Lab_Name) > 25 and count(Lab_Name) <= 50 THEN 'Medium'
WHEN count(Lab_Name) >= 75 THEN 'High'
ELSE 'Medium_High'
END AS Vol_category

from dbo.Lab_tests
Ari
  • 26
  • 1
1

I was able to use the code below to create a variable in Tableau "Lab_Volume" utilizing a version of the the code provided above, after calculating the average test over the last 30 days [test/30 days]:

If [Test/30 days] <= 25 THEN 'Low'
ELSEIF  [Test/30 days] > 25 and [Test/30 days] < 75 THEN 'Medium'
ELSEIF [Test/30 days] >= 75 THEN 'High' 
END
Moosetracks28
  • 13
  • 3