Solved

IF statement infront of an aggregate SUM function

Posted on 2008-06-22
28
1,002 Views
Last Modified: 2013-12-05
I have a large transactional table that I'm trying to roll-up to produce a few summarized views. Here's the portion of the table that I'm interested in summarizing:


Transaction Period   |   Qty  |  Bill_Rt   | Pay_Rt  |  Type
June                             100       10                         REV
June                             200        12                        REV
July                               200       13                         REV
June                             100                     8             LAB
June                               200                    11           LAB
June                             200                      12           LAB
           
The end output I'd like is one line for june and one line for july showing the weighted average of the bill rate and pay rate

Transaction Period   |   Qty   |        Bill_Rt                                   |          Pay_Rt
June                            300      (10*100+200*12)/(100+200)=11.33     (100*8+200*11)/100+200)=10    
July                             200                      13                                                   12                        

I've used the formula: Sum(Bill_Rt*Qty)/Sum(Qty) with some nested IFs to check whether type = REV or LAB and summarize accordingly. However, I need a final check to see whether the divisor = 0 to avoid a #error. Please advise...

SELECT [Master ABC Transactional].Sell_Dist, [Master ABC Transactional].Mnmt_Dist, [Master ABC Transactional].Home_Dist, [Master ABC Transactional].Cust_ID, [Master ABC Transactional].Cust_Name, [Master ABC Transactional].Project, [Master ABC Transactional].Empl_ID, [Master ABC Transactional].Empl_Name, Sum([Master ABC Transactional].Quantity) AS SumOfQuantity, Last([Master ABC Transactional].Sys_Src) AS LastOfSys_Src, Last([Master ABC Transactional].Comm_Margin) AS LastOfComm_Margin, Last([Master ABC Transactional].Jrnl_Line_Descrip) AS LastOfJrnl_Line_Descrip, Last([Master ABC Transactional].Svc_Line) AS LastOfSvc_Line, Last([Master ABC Transactional].Source) AS LastOfSource, Last([Master ABC Transactional].Trans_Pd) AS LastOfTrans_Pd, Sum([Master ABC Transactional].CM_REV) AS SumOfCM_REV, [Master ABC Transactional].ProjType, [Master ABC Transactional].FiscYr, [Master ABC Transactional].Acct_Pd, [Master ABC Transactional].BU, Sum(IIf([Res_Type]="REV",Amount,Null)) AS REV, Sum(IIf([Res_Type]="LAB",Amount,Null)) AS Labor, 

 

Sum(IIf([Res_Type]="REV",Bill_Rt*Quantity,null))/Sum(IIf([Res_Type]="REV",Quantity,null)), Sum(IIf([Res_Type]="LAB",Pay_Rt*Quantity,null))/Sum(IIf([Res_Type]="LAB",Quantity,null)),

 

 

Sum(IIf([Res_Type]="EXP",Amount,Null)) AS Expense, Sum(IIf([Res_Type]="BCH",Amount,Null)) AS Bench, Sum(IIf([Res_Type]="PDO",Amount,Null)) AS PDO, Sum(IIf([Res_Type]="HOL",Amount,Null)) AS Holiday, Sum(IIf([Res_Type]="NBS",Amount,Null)) AS NBS, Sum(IIf([Res_Type]="FRG",Amount,Null)) AS Fringe, Sum(IIf([Res_Type]="TAX",Amount,Null)) AS Tax, Sum(IIf([Res_Type]="RCT",Amount,Null)) AS Recruiter, Sum(IIf([Res_Type]="SUP",Amount,Null)) AS Support

 

 

FROM [Master ABC Transactional]

GROUP BY [Master ABC Transactional].Sell_Dist, [Master ABC Transactional].Mnmt_Dist, [Master ABC Transactional].Home_Dist, [Master ABC Transactional].Cust_ID, [Master ABC Transactional].Cust_Name, [Master ABC Transactional].Project, [Master ABC Transactional].Empl_ID, [Master ABC Transactional].Empl_Name, [Master ABC Transactional].ProjType, [Master ABC Transactional].FiscYr, [Master ABC Transactional].Acct_Pd, [Master ABC Transactional].BU;

Open in new window

0
Comment
Question by:jimbofish8
  • 11
  • 6
  • 6
  • +2
28 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 21841568
You show a Pay_Rt of 12 for July when there is no value in the detail table??
0
 

Author Comment

by:jimbofish8
ID: 21841582
Sorry the last line "June                             200                      12           LAB"
should be for July
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 21841637
SELECT
s.period,
Sum(s.[Average Bill Rate]) AS [SumOfAverage Bill Rate],
Sum(s.[Average Pay Rate]) AS [SumOfAverage Pay Rate]

FROM

[SELECT TABLE1.period,
Sum(BILL_RT*QTY)/Sum(QTY) AS [Average Bill Rate],
Sum(QTY*PAY_RT)/Sum(QTY) AS [Average Pay Rate]
FROM TABLE1
GROUP BY TABLE1.period, TABLE1.type
]. AS s

GROUP BY s.period
ORDER BY s.period;
0
 

Author Comment

by:jimbofish8
ID: 21841643
The problem I have is that there are some cases where QTY = 0. I need some sort of if statement that will output 0 if QTY is 0
0
 
LVL 8

Expert Comment

by:Emil_Gray
ID: 21841687
OK.
If QTY <> 0 Then _

SELECT 

s.period, 

Sum(s.[Average Bill Rate]) AS [SumOfAverage Bill Rate], 

Sum(s.[Average Pay Rate]) AS [SumOfAverage Pay Rate]
 

FROM 
 

[SELECT TABLE1.period, 

Sum(BILL_RT*QTY)/Sum(QTY) AS [Average Bill Rate], 

Sum(QTY*PAY_RT)/Sum(QTY) AS [Average Pay Rate]

FROM TABLE1

GROUP BY TABLE1.period, TABLE1.type

]. AS s
 

GROUP BY s.period

ORDER BY s.period;
 

Else
 

[Average Bill Rate] = 0

[Average Pay Rate] = 0
 

End If

Open in new window

0
 
LVL 44

Expert Comment

by:GRayL
ID: 21841699
A somewhat easier way to look at it.  Use an alias for long table names to ease the typing.  Are there any other [Res_Type]'s other than those listed?  If not why not include [Res_Type] in the GROUP BY and Sum(a.Amount)?  The query can simplify to the latter SQL
SELECT 

a.Sell_Dist, 

a.Mnmt_Dist, 

a.Home_Dist, 

a.Cust_ID, 

a.Cust_Name, 

a.Project, 

a.Empl_ID, 

a.Empl_Name, 

Sum(a.Quantity) AS SumOfQuantity, 

Last(a.Sys_Src) AS LastOfSys_Src, 

Last(a.Comm_Margin) AS LastOfComm_Margin, 

Last(a.Jrnl_Line_Descrip) AS LastOfJrnl_Line_Descrip, 

Last(a.Svc_Line) AS LastOfSvc_Line, 

Last(a.Source) AS LastOfSource, 

Last(a.Trans_Pd) AS LastOfTrans_Pd, 

Sum(a.CM_REV) AS SumOfCM_REV, 

a.ProjType, 

a.FiscYr, 

a.Acct_Pd, 

a.BU, 

Sum(IIf(a.[Res_Type]="REV",a.Amount,Null)) AS REV, 

Sum(IIf(a.[Res_Type]="LAB",a.Amount,Null)) AS Labor, 

 

Sum(IIf(a.[Res_Type]="REV",a.Bill_Rt*a.Quantity,null))/Sum(IIf([Res_Type]="REV",Quantity,null)), Sum(IIf(a.[Res_Type]="LAB",a.Pay_Rt*a.Quantity,null))/Sum(IIf([Res_Type]="LAB",Quantity,null)),

 

 

Sum(IIf(a.[Res_Type]="EXP",a.Amount,Null)) AS Expense, 

Sum(IIf(a.[Res_Type]="BCH",a.Amount,Null)) AS Bench, 

Sum(IIf(a.[Res_Type]="PDO",a.Amount,Null)) AS PDO, 

Sum(IIf(a.[Res_Type]="HOL",a.Amount,Null)) AS Holiday, 

Sum(IIf(a.[Res_Type]="NBS",a.Amount,Null)) AS NBS, 

Sum(IIf(a.[Res_Type]="FRG",a.Amount,Null)) AS Fringe, 

Sum(IIf(a.[Res_Type]="TAX",a.Amount,Null)) AS Tax, 

Sum(IIf(a.[Res_Type]="RCT",a.Amount,Null)) AS Recruiter, 

Sum(IIf(a.[Res_Type]="SUP",a.Amount,Null)) AS Support
 

FROM [Master ABC Transactional] a
 

GROUP BY a.Sell_Dist, a.Mnmt_Dist, a.Home_Dist, a.Cust_ID, a.Cust_Name, 

a.Project, a.Empl_ID, a.Empl_Name, a.ProjType, a.FiscYr, a.Acct_Pd, a.BU;
 

Simplified SQL:
 

SELECT 

a.Sell_Dist, 

a.Mnmt_Dist, 

a.Home_Dist, 

a.Cust_ID, 

a.Cust_Name, 

a.Project, 

a.Empl_ID, 

a.Empl_Name, 

Sum(a.Quantity) AS SumOfQuantity, 

Last(a.Sys_Src) AS LastOfSys_Src, 

Last(a.Comm_Margin) AS LastOfComm_Margin, 

Last(a.Jrnl_Line_Descrip) AS LastOfJrnl_Line_Descrip, 

Last(a.Svc_Line) AS LastOfSvc_Line, 

Last(a.Source) AS LastOfSource, 

Last(a.Trans_Pd) AS LastOfTrans_Pd, 

Sum(a.CM_REV) AS SumOfCM_REV, 

a.ProjType, 

a.FiscYr, 

a.Acct_Pd, 

a.BU,

a.[Res_Type] 

Sum(a.Amount), 
 

Sum(IIf(a.[Res_Type]="REV",a.Bill_Rt*a.Quantity,null))/Sum(IIf([Res_Type]="REV",Quantity,null)), Sum(IIf(a.[Res_Type]="LAB",a.Pay_Rt*a.Quantity,null))/Sum(IIf([Res_Type]="LAB",Quantity,null)),
 

FROM [Master ABC Transactional] a
 

GROUP BY a.Sell_Dist, a.Mnmt_Dist, a.Home_Dist, a.Cust_ID, a.Cust_Name, 

a.Project, a.Empl_ID, a.Empl_Name, a.ProjType, a.FiscYr, a.Acct_Pd, a.BU, a.[Res_Type];

Open in new window

0
 
LVL 44

Expert Comment

by:GRayL
ID: 21841710
If any of the sums are null, then wrap them in the Nz() function if you want to 'see' a zero:

Nz(Sum(a.AnyValue),0)
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 200 total points
ID: 21841763
SELECT
Table1.period,
Sum(NZ([AverageBilLRate],0)) AS Expr1,
Sum(NZ([AveragePayRate],0)) AS Expr2

FROM
[SELECT TABLE1.period,
Sum(BILL_RT*QTY)/Sum(QTY) AS [AverageBilLRate],
Sum(QTY*PAY_RT)/Sum(QTY) AS [AveragePayRate]
FROM TABLE1
WHERE QTY<>0
GROUP BY TABLE1.period, TABLE1.type
]. AS S
RIGHT JOIN Table1 ON S.period = Table1.period
GROUP BY Table1.period;
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 21841773
Emil_Gray, a little warning: you can improve code provided by someone else only when that someone else has obviously stopped participation.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21841784
Do not convert nulls to zeroes before you apply any aggregate function.  

Null/Null = Null  ie.  a null in the denominator will not throw an error.  After the aggregate, wrap it in the Nz() function as I showed you above to 'see' the zero.
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 200 total points
ID: 21841898
This is a Model:
Table iifBill
period      type      qty      Bill-rt      Pay-rt
June      A      100      10      
June      A      200      12      
July      A      200      13      
June      B      100            8
June      B      200            11
July      B      200            12

Query:

Select     period,BillSum,PaySum,ASum,BSum,ARatio,BRatio
FROM
(SELECT iifBill.period, Sum(IIf([type]="A",[qty]*[bill-rt],0)) AS BillSum, Sum(IIf([type]="B",[qty]*[Pay-rt],0)) AS PaySum, Sum(IIf([type]="A",[qty],0)) AS ASum, Sum(IIf([type]="B",[qty],0)) AS BSum, IIf([ASUm]=0,0,[BillSum]/[ASum]) AS ARatio, IIf([BSum]=0,0,[PaySum]/[ASum]) AS BRatio
FROM iifBill
GROUP BY iifBill.period) AS tbl;

Result:
period      BillSum      PaySum      ASum      BSum      ARatio      BRatio
July      2600      2400      200      200      13      12
June      3400      3000      300      300      11.3333333333333      10
0
 

Author Comment

by:jimbofish8
ID: 21843481
GrayL, i like your suggestion about using aliases' however, I need to transpose the resource types into columns... So, your solution doesn't really work for me. will keep trying down this list..
0
 

Author Comment

by:jimbofish8
ID: 21843584
hrmm these solutions still don't work. maybe i wasn't explicit in the case that i'm trying to eliminate

Sometimes there is:
Transaction Period   |   Qty  |  Bill_Rt   | Pay_Rt  |  Type
Aug                               0                          10         LAB
Aug                               0            0                          REV

In this case the Augus value would have #Error in both Bill_rt and Pay_rt because there is a 0 value as a divisor.. How can I avoid this?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 21843801
jimbofish8,
Can you tell me where is the problem in my comment?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:jimbofish8
ID: 21843891
hnsar.. I can't get it to work. can you use the table names that i have in the code snippet?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 21844301
Try this model!
table:iifBill
query: iifBill_q

Good Luck!
iif.mdb
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 21845110
jimbofish8, did you try mine?
0
 

Author Comment

by:jimbofish8
ID: 21845514
vadimrapp1, i don't know why your code keeps giving me a syntax error in the FROM clause... I don't know how to fix it.
0
 

Author Comment

by:jimbofish8
ID: 21845609
hnasr:your solution works, but I can't figure out how to add the additional fields I had in my first query? It seems that you're creating a table alias? Where do I put in the additional fields from [Master ABC Transactional]?
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 100 total points
ID: 21847420
Create a small zero-to-null function in a standard module:

Public Function Zn(myVal) as Double) as Double

Zn = iif(myVal = 0, Null, myVal) ' <--This changes the zero value to a null

End Function

Now just wrap the fields in your Aggregate with the Zn() function to get rid of the divide-by-zero problem.  
0
 
LVL 30

Expert Comment

by:hnasr
ID: 21847678
I used notepad,
1 Pasted my sql
2 Replace relevant field names as you suppliedin question.
3 Alias can be removed, but access puts a unique name as alias if ommited.
4 Alias is usefull if joining with other tables

SELECT [Transaction Period], BillSum, PaySum, ASum, BSum, ARatio, BRatio
FROM (SELECT [Master ABC Transactional].[Transaction Period], Sum(IIf([type]="A",[qty]*[Bill_rt],0)) AS BillSum, Sum(IIf([type]="B",[qty]*[pay_rt],0)) AS PaySum, Sum(IIf([type]="A",[qty],0)) AS ASum, Sum(IIf([type]="B",[qty],0)) AS BSum, IIf([ASUm]=0,0,[BillSum]/[ASum]) AS ARatio, IIf([BSum]=0,0,[PaySum]/[ASum]) AS BRatio FROM [Master ABC Transactional] GROUP BY [Master ABC Transactional].[Transaction Period])  AS tbl;

5  Modify if not exact, and insert it as an sql to a qwery in your database then try.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 21847757
<Where do I put in the additional fields from [Master ABC Transactional]?>
As you see from my comment, there is a nested sql.

Select nqField1, nqField2, nqField3, nqField4
FROM
(Select yourTable.Field1 AS nqField1, yourTable.Field2 AS nqField2, yourTable.Field3 AS nqField3, yourTable.Field4 AS nqField4 From yourTable);
0
 

Author Comment

by:jimbofish8
ID: 21851172
OK -- still not working. I was forced to upsize the database to a SQL server because it took 10 mins just to run a query on my machine and i couldn't force it to limit the number of records. each time it'd query the whole thing.

The code below doesn't work. I get the following error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'BILL_RT_DIV'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'BILL_RT_NUM'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'BILL_RT_DIV'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'PAY_RT_DIV'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'PAY_RT_NUM'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'PAY_RT_DIV'.

select Empl_id,empl_name,Cust_Id,Cust_name,ACCT_PD,BU,ProjType,FiscYr,

SUM(CASE WHEN Res_Type = 'REV' THEN Amount ELSE NULL END) AS REV_AMT,

SUM(CASE WHEN Res_Type = 'LAB' THEN Amount ELSE NULL END) AS LAB_AMT,

SUM(CASE WHEN Res_Type = 'EXP' THEN Amount ELSE NULL END) AS EXP_AMT,

SUM(CASE WHEN Res_Type = 'PDO' THEN Amount ELSE NULL END) AS PDO_AMT,

SUM(CASE WHEN Res_Type = 'HOL' THEN Amount ELSE NULL END) AS HOL_AMT,

SUM(CASE WHEN Res_Type = 'NBS' THEN Amount ELSE NULL END) AS NBS_AMT,

SUM(CASE WHEN Res_Type = 'FRG' THEN Amount ELSE NULL END) AS FRG_AMT,

SUM(CASE WHEN Res_Type = 'TAX' THEN Amount ELSE NULL END) AS TAX_AMT,

SUM(CASE WHEN Res_Type = 'RCT' THEN Amount ELSE NULL END) AS RCT_AMT,

SUM(CASE WHEN Res_Type = 'SUP' THEN Amount ELSE NULL END) AS SUP_AMT,
 

SUM(CASE WHEN Res_Type = 'LAB' AND QUANTITY<>0 AND PAY_RT<>0 THEN (PAY_RT * QUANTITY) ELSE 0 END) AS PAY_RT_NUM,

SUM(CASE WHEN Res_Type = 'LAB' AND QUANTITY<>0 AND PAY_RT<>0 THEN (QUANTITY) ELSE 0 END) AS PAY_RT_DIV,

SUM(CASE WHEN Res_Type = 'REV' AND QUANTITY<>0 AND BILL_RT<>0 THEN (BILL_RT*QUANTITY) ELSE 0 END) AS BILL_RT_NUM,

SUM(CASE WHEN Res_Type = 'REV' AND QUANTITY<>0 AND BILL_RT<>0 THEN (QUANTITY) ELSE 0 END) AS BILL_RT_DIV,
 

CASE WHEN BILL_RT_DIV = 0 THEN 0 ELSE (BILL_RT_NUM / BILL_RT_DIV) END AS AVG_BILL_RT,

CASE WHEN PAY_RT_DIV = 0 THEN 0 ELSE (PAY_RT_NUM / PAY_RT_DIV) END AS AVG_BILL_RT
 

from dbo.[Master CC Transactional] 

GROUP BY Cust_Id,ACCT_PD,cust_name,Empl_id,empl_name,BU, ProjType,FiscYr order by empl_id desc

Open in new window

0
 

Author Comment

by:jimbofish8
ID: 21851177
I also tried using hsnar's code directly, modifying it as needed for SQL server, but it doens't work either... Has the following error.


Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'REV'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'EXP'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'REV'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'EXP'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'BSum'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'BillSum'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'BSum'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'PSum'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'PaySum'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'PSum'.
SELECT Acct_Pd, BillSum, PaySum, BSum, BSum, PRatio, BRatio

FROM (SELECT [Master CC Transactional].Acct_Pd, 

Sum(CASE WHEN res_type = "REV" THEN quantity * bill_rt ELSE 0 END) AS BillSum,

Sum(CASE WHEN res_type = "EXP" THEN quantity * pay_rt ELSE 0 END) AS PaySum,

Sum(CASE WHEN res_type = "REV" THEN quantity ELSE 0 END) AS BSum,

Sum(CASE WHEN res_type = "EXP" THEN quantity ELSE 0 END) AS PSum,

CASE WHEN BSum = 0 THEN 0 ELSE BillSum / BSum END AS BRatio,

CASE WHEN PSum = 0 THEN 0 ELSE PaySum / PSum END AS PRatio
 

FROM [Master CC Transactional] 

GROUP BY [Master CC Transactional].[Acct_Pd])  AS tbl;

Open in new window

0
 
LVL 44

Expert Comment

by:GRayL
ID: 21851850
I think you would be better off running the query I showed you, and then cross tab that result.  If you want to go there,  acknowledge.
0
 

Author Comment

by:jimbofish8
ID: 21852456
I can't run it on Access anymore... it takes 10 minutes to query.

anybody with T-SQL experience?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 21853093
jimbofish8,

<OK -- still not working. I was forced to upsize the database to a SQL server because it took 10 mins just ....>
Troubleshooting a problem, you need few records at first, then optimize and run on full.
<I also tried using hsnar's code directly, modifying it as needed for SQL server, but it doens't work either... Has the following error.


Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'REV'.>
If the code given works in access Then
-----This problem is resolved
----- Start a new question to use SQL
Else
-----If you still want to continue using access
---------Attach a small database with test data that recreates the problem
-----End if
End if
0
 

Author Comment

by:jimbofish8
ID: 21854929
OK -- I got it worked

Thx for the tips on troubleshooting.
ALTER   VIEW dbo.[CC Summarized]

AS

select top 50 sell_dist, Empl_id,empl_name,Cust_Id,Cust_name,ACCT_PD,BU, ProjType,FiscYr,

COALESCE(SUM(CASE WHEN Res_Type = 'REV' THEN Amount ELSE NULL END),0) AS REV_AMT,

COALESCE(SUM(CASE WHEN Res_Type = 'LAB' THEN Amount ELSE NULL END),0) AS LAB_AMT,

COALESCE(SUM(CASE WHEN Res_Type = 'EXP' THEN Amount ELSE NULL END),0) AS EXP_AMT,

COALESCE(SUM(CASE WHEN Res_Type = 'PDO' THEN Amount ELSE NULL END),0) AS PDO_AMT,

COALESCE(SUM(CASE WHEN Res_Type = 'HOL' THEN Amount ELSE NULL END),0) AS HOL_AMT,

COALESCE(SUM(CASE WHEN Res_Type = 'NBS' THEN Amount ELSE NULL END),0) AS NBS_AMT,

COALESCE(SUM(CASE WHEN Res_Type = 'FRG' THEN Amount ELSE NULL END),0) AS FRG_AMT,

COALESCE(SUM(CASE WHEN Res_Type = 'TAX' THEN Amount ELSE NULL END),0) AS TAX_AMT,

COALESCE(SUM(CASE WHEN Res_Type = 'RCT' THEN Amount ELSE NULL END),0) AS RCT_AMT,

COALESCE(SUM(CASE WHEN Res_Type = 'SUP' THEN Amount ELSE NULL END),0) AS SUP_AMT,

ISNULL(SUM(CASE WHEN Res_Type = 'LAB' AND QUANTITY<>0 AND PAY_RT<>0 THEN (PAY_RT*QUANTITY) ELSE 0 END)/NULLIF(SUM(CASE WHEN (Res_type = 'LAB' AND QUANTITY<>0 AND PAY_RT<>0) THEN Quantity  

      ELSE null END),0),0) AS PAY_RT,

ISNULL(SUM(CASE WHEN Res_Type = 'REV' AND QUANTITY<>0 AND BILL_RT<>0 THEN (BILL_RT*QUANTITY) ELSE 0 END)/NULLIF(SUM(CASE WHEN (Res_type = 'REV' AND QUANTITY<>0 AND BILL_RT<>0) THEN Quantity  

      ELSE null END),0),0) AS BILL_RT

from dbo.[Master CC Transactional] 

GROUP BY Cust_Id,ACCT_PD,cust_name,Empl_id,empl_name,BU, ProjType,FiscYr,BU,Sell_Dist order by empl_id desc

Open in new window

0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now