Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

IF statement infront of an aggregate SUM function

Posted on 2008-06-22
28
Medium Priority
?
1,017 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 800 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 31

Assisted Solution

by:hnasr
hnasr earned 800 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 31

Expert Comment

by:hnasr
ID: 21843801
jimbofish8,
Can you tell me where is the problem in my comment?
0
 

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 31

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 400 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 31

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 31

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 31

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

604 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