asked on

How to Change My Current Case Statment in SQL to Get The Minimum, Average and Max Based off The Titles

I have the following SQL Query and I need to change the Case Statement to get the minimum, average and max values based off of the Shareholder and Associate Titles (t.Description is the alias name for the Title Field in the SQLTable).

There are 2 different Rates that they are based off of (Discount Rates) and (Standard Rates).

Here is the Case Statement I currently have (I've attached a Word Document with what my Results should look like):

	, max(case when tr.RateType = 'Rack' then trd.DefaultRate else 0 end) as [Disc Rate]
	, max(case when tr.RateType = 'Standard' then trd.DefaultRate else 0 end) as [Std Rate]
	, max(case when tr.RateType = 'National' then trd.DefaultRate else 0 end) as [Nat Rate]

Open in new window

Thanks,
gdunn59
ScreenShot_EE.docx

Brian Crowe

, min(case when tr.RateType = 'Rack' then trd.DefaultRate else 0 end) as [min Disc Rate]
, avg(case when tr.RateType = 'Rack' then trd.DefaultRate else 0 end) as [avg Disc Rate]
, max(case when tr.RateType = 'Rack' then trd.DefaultRate else 0 end) as [max Disc Rate]
, min(case when tr.RateType = 'Standard' then trd.DefaultRate else 0 end) as [min Std Rate]
, avg(case when tr.RateType = 'Standard' then trd.DefaultRate else 0 end) as [avg Std Rate]
, max(case when tr.RateType = 'Standard' then trd.DefaultRate else 0 end) as [max Std Rate]

Open in new window

gdunn59

ASKER

Brian Crowe,

I tried your solution, but all of the Min are coming up 0.00, and there are 2 different shareholders (Shareholder-Income and Shareholder-Equity), these totals need to be combined for the 2 Shareholders.

Here is my entire SQL Query:

select distinct
	  t.Description as 'Title'
	, min(case when tr.RateType = 'Rack' then trd.DefaultRate else 0 end) as [min Disc Rate]
	, avg(case when tr.RateType = 'Rack' then trd.DefaultRate else 0 end) as [avg Disc Rate]
	, max(case when tr.RateType = 'Rack' then trd.DefaultRate else 0 end) as [max Disc Rate]
	, min(case when tr.RateType = 'Standard' then trd.DefaultRate else 0 end) as [min Std Rate]
	, avg(case when tr.RateType = 'Standard' then trd.DefaultRate else 0 end) as [avg Std Rate]
	, max(case when tr.RateType = 'Standard' then trd.DefaultRate else 0 end) as [max Std Rate]
from Timekeeper tk
inner join TkprDate td on td.TimekeeperLkUp = tk.TkprIndex
	and td.NxEndDate = '9999-12-31 00:00:00.000' and td.NxStartDate <> '9999-12-31 00:00:00.000'
inner join Title t on td.Title = t.Code
inner join TkprRate tr (nolock) on tr.Timekeeper = tk.TkprIndex
inner join TkprRateDate trd (nolock) on tr.TkprRateID = trd.TkprRateLkUp
where TkprStatus <> 'INACTIVE'
and t.Description in ('Shareholder-Income','Shareholder-Equity','Associate')
group by
	 t.Description

Open in new window

Thanks,

gdunn59

Scott Pletcher

You can't use "ELSE 0" with a MIN or AVG calc because it will corrupt the calculations. Instead, the value default to NULL when it doesn't match the CASE: SQL will ignore the NULL value. I'm not sure if the AVG will work correctly: if not, compute the AVG yourself by doing dividing the total by the count, as in: SUM(CASE ... THEN Rate END) / SUM(CASE ... THEN 1 END)

select
       t.Description as 'Title'
      , isnull(min(case when tr.RateType = 'Rack' then trd.DefaultRate end), 0) as [min Disc Rate]
      , isnull(avg(case when tr.RateType = 'Rack' then trd.DefaultRate end), 0) as [avg Disc Rate]
      , isnull(max(case when tr.RateType = 'Rack' then trd.DefaultRate end), 0) as [max Disc Rate]
      , isnull(min(case when tr.RateType = 'Standard' then trd.DefaultRate end), 0) as [min Std Rate]
      , isnull(avg(case when tr.RateType = 'Standard' then trd.DefaultRate end), 0) as [avg Std Rate]
      , isnull(max(case when tr.RateType = 'Standard' then trd.DefaultRate end), 0) as [max Std Rate]
from Timekeeper tk
inner join TkprDate td on td.TimekeeperLkUp = tk.TkprIndex
      and td.NxEndDate = '99991231 00:00:00.000' and td.NxStartDate <> '99991231 00:00:00.000'
inner join Title t on td.Title = t.Code
inner join TkprRate tr (nolock) on tr.Timekeeper = tk.TkprIndex
inner join TkprRateDate trd (nolock) on tr.TkprRateID = trd.TkprRateLkUp
where TkprStatus <> 'INACTIVE'
and t.Description in ('Shareholder-Income','Shareholder-Equity','Associate')
group by
       t.Description

gdunn59

ASKER

ScottPletcher,

Your posting is working somewhat. The only ones that are calculating properly are the Max ones, the MIN or AVG are not calculating properly.

I've tried this from your posting for the Avg, and still getting the same results:

, SUM(case when tr.RateType = 'Rack' then trd.DefaultRate end) / SUM(case when tr.RateType = 'Rack' THEN 1 END) as 'Avg Disc Rate 2'

Also, I posted in my last posting that there are 2 different shareholders (Shareholder-Income and Shareholder-Equity), these totals need to be combined for the 2 Shareholders (see the file I attached which shows how my results should look).
ScreenShot_EE.docx

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

gdunn59, do you still need help with this question?