December 19, 2013 at 11:57 am
Hi All,
I need to know how to use the subquery column (billings_to_date)in the GROUP BY as it is showing up as error.
SELECT p.period
,m.clnt_matt_code AS [Client Code]
,m.matter_name
,o.offc_desc
,'Insolvency' AS matter_code
,ISNULL(l.Merged_Source_of_Work_Lender_Desc,k.name) AS Bank
,CASE WHEN m.matter_code NOT IN ('IBR','ISR','BNK','ADV')
THEN ISNULL(mins.original_estimated_fee,0) + ISNULL(mins.original_contingent_fee,0)
ELSE 0
END AS [Original Fee]
,isnull((Select top 1 'Y'
from bo_live5.dbo.hba_source_bus AS ii
where ii.matter_uno=i.matter_uno and ii.comments LIKE '%walk-in%'),'N') AS [Walk In],em.employee_name,
isnull((SELECT sum(bi.PROGRESS_BIL + bi.HARD_BIL + bi.SOFT_BIL + bi.FEES_BIL + bi.OAFEE_BIL + bi.RETAINER_BIL + bi.PREMDISC_BIL - bi.CREDIT_TOT)
FROM bo_Live5.dbo.BLT_BILLM bi
WHERE bi.MATTER_UNO = m.MATTER_UNO), 0) AS billings_to_date
FROM bo_live5.dbo.hba_source_bus AS i
INNER JOIN bo_live5.dbo.cxa_folder_object AS j ON i.fo_uno = j.fo_uno
INNER JOIN bo_live5.dbo.hbm_name AS k ON k.name_uno = j.object_uno
LEFT JOIN BO_Custom.dbo.Source_of_work_Lender l ON l.Original_Source_of_Work_Lender_Desc = k.name --improtu look up for banks that have merged
INNER JOIN bo_live5.dbo.hbl_name_class AS h ON k.name_class_code = h.name_class_code
INNER JOIN bo_live5.dbo.hbm_matter AS m ON m.matter_uno = i.matter_uno
INNER JOIN bo_Live5.dbo.hbm_PERSNL AS em ON em.empl_uno = m.resp_empl_uno
INNER JOIN bo_Live5.dbo.BLT_BILLM AS bi ON bi.matter_uno = m.matter_uno
INNER JOIN bo_live5.dbo.hbl_office AS o ON o.offc_code = m.offc
INNER JOIN bo_live5.dbo.hba_offc_prof c ON c.offc = o.offc_code
AND LEFT(c.prof,2) IN (SELECT FieldName FROM dbo._000007_SplitList(@Profit_Center,',' ))
INNER JOIN CTE_Mins AS mins ON mins.matter_uno = m.matter_uno
LEFT JOIN bo_live5.dbo.glm_period_end AS p ON m.open_date BETWEEN p.per_begin_date AND p.per_end_date + '23:59:59'
WHERE ....................(Intentionally removed to reduce code)
GROUP BY p.period
,m.clnt_matt_code
,m.matter_name
,o.offc_desc
,ISNULL(l.Merged_Source_of_Work_Lender_Desc,k.name)
,m.matter_code
,mins.original_estimated_fee
,mins.original_contingent_fee, i.matter_uno ,em.employee_name
December 19, 2013 at 12:01 pm
Please my intention is to show the code that I have problem with in a different colour but it didnt work. so please ignore the colour code.
I want to know how to use this code in a GROUP BY clause
isnull((SELECT sum(bi.PROGRESS_BIL + bi.HARD_BIL + bi.SOFT_BIL + bi.FEES_BIL + bi.OAFEE_BIL + bi.RETAINER_BIL + bi.PREMDISC_BIL - bi.CREDIT_TOT)
FROM bo_Live5.dbo.BLT_BILLM bi
WHERE bi.MATTER_UNO = m.MATTER_UNO), 0) AS billings_to_date
December 20, 2013 at 9:59 am
(Without going into the details of your query) maybe you can convert your query to a CTE then group the result?
December 20, 2013 at 10:28 am
Using a subselect as a derived column is performance time bomb. You have at least a couple of those in there already. You also have a split function in there which raises at least a yellow flag. Does that split function use xml or looping?
Not quite sure why you have the second subselect in there. The one selecting from BLT_BILLM with the huge sum. You already have that table in your query.
See if something like this gets you closer.
SELECT p.period,
m.clnt_matt_code AS [Client Code],
m.matter_name,
o.offc_desc,
'Insolvency' AS matter_code,
Isnull(l.Merged_Source_of_Work_Lender_Desc, k.name) AS Bank,
CASE
WHEN m.matter_code NOT IN ( 'IBR', 'ISR', 'BNK', 'ADV' ) THEN Isnull(mins.original_estimated_fee, 0)
+ Isnull(mins.original_contingent_fee, 0)
ELSE 0
END AS [Original Fee],
Isnull(ii.WalkIn, 'N') AS [Walk In],
em.employee_name,
Isnull(Sum(bi.PROGRESS_BIL + bi.HARD_BIL + bi.SOFT_BIL + bi.FEES_BIL + bi.OAFEE_BIL + bi.RETAINER_BIL + bi.PREMDISC_BIL - bi.CREDIT_TOT), 0) AS billings_to_date
FROM bo_live5.dbo.hba_source_bus AS i
INNER JOIN bo_live5.dbo.cxa_folder_object AS j ON i.fo_uno = j.fo_uno
INNER JOIN bo_live5.dbo.hbm_name AS k ON k.name_uno = j.object_uno
LEFT JOIN BO_Custom.dbo.Source_of_work_Lender l ON l.Original_Source_of_Work_Lender_Desc = k.name --improtu look up for banks that have merged
INNER JOIN bo_live5.dbo.hbl_name_class AS h ON k.name_class_code = h.name_class_code
INNER JOIN bo_live5.dbo.hbm_matter AS m ON m.matter_uno = i.matter_uno
INNER JOIN bo_Live5.dbo.hbm_PERSNL AS em ON em.empl_uno = m.resp_empl_uno
INNER JOIN bo_Live5.dbo.BLT_BILLM AS bi ON bi.matter_uno = m.matter_uno
INNER JOIN bo_live5.dbo.hbl_office AS o ON o.offc_code = m.offc
INNER JOIN bo_live5.dbo.hba_offc_prof c ON c.offc = o.offc_code
AND LEFT(c.prof, 2) IN (SELECT FieldName
FROM dbo._000007_splitlist(@Profit_Center, ','))
INNER JOIN CTE_Mins AS mins
ON mins.matter_uno = m.matter_uno
LEFT JOIN bo_live5.dbo.glm_period_end AS p
ON m.open_date BETWEEN p.per_begin_date AND p.per_end_date + '23:59:59'
left join (SELECT TOP 1 'Y' as WalkIn FROM bo_live5.dbo.hba_source_bus) ii on ii.matter_uno = i.matter_uno AND ii.comments LIKE '%walk-in%'
GROUP BY p.period
,m.clnt_matt_code
,m.matter_name
,o.offc_desc
,ISNULL(l.Merged_Source_of_Work_Lender_Desc,k.name)
,m.matter_code
,mins.original_estimated_fee
,mins.original_contingent_fee, i.matter_uno ,em.employee_name
, Isnull(ii.WalkIn, 'N')
, Isnull(Sum(bi.PROGRESS_BIL + bi.HARD_BIL + bi.SOFT_BIL + bi.FEES_BIL + bi.OAFEE_BIL + bi.RETAINER_BIL + bi.PREMDISC_BIL - bi.CREDIT_TOT), 0)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 20, 2013 at 1:19 pm
Thanks SSC veteran and SSC Champion for your suggestions and also performance tips. However Sean, I would like to know the format that you used to get the SQL to be so readable in this forum. I mean mine always get messed up anytime I pasted the sql from my computer to this forum or is the format only reserved for veterans and champions?
EO
December 20, 2013 at 1:24 pm
eobiki10 (12/20/2013)
Thanks SSC veteran and SSC Champion for your suggestions and also performance tips. However Sean, I would like to know the format that you used to get the SQL to be so readable in this forum. I mean mine always get messed up anytime I pasted the sql from my computer to this forum or is the format only reserved for veterans and champions?EO
Not reserved at all. When you are posting you will notice IFCode shortcut on the left. Put your code and such inside those and it will keep things nice and tidy for you.
If you quote my previous post you can view them in action if that helps too.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 23, 2013 at 3:49 am
Sean,
I tried your suggestion and I have this error message. What should I do?
Msg 144, Level 15, State 1, Procedure sp_Bank_Work_Report1, Line 18
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause
December 23, 2013 at 4:40 am
Is bo_Live5.dbo.BLT_BILLM the only table in your FROMlist which has sets of rows having the same value of MATTER_UNO/matter_name?
Are you expecting, in your output, only one row per matter_name?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 23, 2013 at 5:04 am
Yes, I am expecting one row for matter name.
I just have a workaround on it and that is to remove the sum in the grouping in the code and now do the grouping in SSRS.
December 23, 2013 at 5:20 am
Have you tried aggregating the bills in isolation from the rest of the query? Here's one way which looks clean and performs quite well:
SELECT
p.period
,m.clnt_matt_code AS [Client Code]
,m.matter_name
,o.offc_desc
,'Insolvency' AS matter_code
,ISNULL(l.Merged_Source_of_Work_Lender_Desc,k.name) AS Bank
,CASE WHEN m.matter_code NOT IN ('IBR','ISR','BNK','ADV')
THEN ISNULL(mins.original_estimated_fee,0) + ISNULL(mins.original_contingent_fee,0)
ELSE 0
END AS [Original Fee]
,isnull((
Select top 1 'Y'
from bo_live5.dbo.hba_source_bus AS ii
where ii.matter_uno=i.matter_uno and ii.comments LIKE '%walk-in%'
),'N') AS [Walk In],
em.employee_name,
isnull(x.billings_to_date, 0) AS billings_to_date
FROM bo_live5.dbo.hba_source_bus AS i
INNER JOIN bo_live5.dbo.cxa_folder_object AS j ON i.fo_uno = j.fo_uno
INNER JOIN bo_live5.dbo.hbm_name AS k ON k.name_uno = j.object_uno
LEFT JOIN BO_Custom.dbo.Source_of_work_Lender l ON l.Original_Source_of_Work_Lender_Desc = k.name --improtu look up for banks that have merged
INNER JOIN bo_live5.dbo.hbl_name_class AS h ON k.name_class_code = h.name_class_code
INNER JOIN bo_live5.dbo.hbm_matter AS m ON m.matter_uno = i.matter_uno
INNER JOIN bo_Live5.dbo.hbm_PERSNL AS em ON em.empl_uno = m.resp_empl_uno
--INNER JOIN bo_Live5.dbo.BLT_BILLM AS bi ON bi.matter_uno = m.matter_uno
CROSS APPLY (
SELECT billings_to_date = sum(bi.PROGRESS_BIL + bi.HARD_BIL + bi.SOFT_BIL + bi.FEES_BIL + bi.OAFEE_BIL + bi.RETAINER_BIL + bi.PREMDISC_BIL - bi.CREDIT_TOT)
FROM bo_Live5.dbo.BLT_BILLM bi
WHERE bi.MATTER_UNO = m.MATTER_UNO
) x
INNER JOIN bo_live5.dbo.hbl_office AS o ON o.offc_code = m.offc
INNER JOIN bo_live5.dbo.hba_offc_prof c ON c.offc = o.offc_code
AND LEFT(c.prof,2) IN (SELECT FieldName FROM dbo._000007_SplitList(@Profit_Center,',' ))
INNER JOIN CTE_Mins AS mins ON mins.matter_uno = m.matter_uno
LEFT JOIN bo_live5.dbo.glm_period_end AS p ON m.open_date BETWEEN p.per_begin_date AND p.per_end_date + '23:59:59'
WHERE ....................(Intentionally removed to reduce code)
--GROUP BY p.period
--,m.clnt_matt_code
--,m.matter_name
--,o.offc_desc
--,ISNULL(l.Merged_Source_of_Work_Lender_Desc,k.name)
--,m.matter_code
--,mins.original_estimated_fee
--,mins.original_contingent_fee, i.matter_uno ,em.employee_name
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 23, 2013 at 7:51 am
Thanks Chris. Your suggestion works perfectly fine. The APPLY is really the magic wow!!!
EO
December 23, 2013 at 8:08 am
eobiki10 (12/23/2013)
Thanks Chris. Your suggestion works perfectly fine. The APPLY is really the magic wow!!!EO
Excellent. There's a lesson here - know your data. If you knew in advance of writing your query that most of your tables join together as one-to-one, you would have figured it out.
APPLY isn't key here - you could have done the same job with a joined derived table. Similar to the APPLY block but you return the key column in the subquery - in addition to the aggregated columns - then use it in the join.
You should investigate this:
isnull((Select top 1 'Y'
from bo_live5.dbo.hba_source_bus AS ii
where ii.matter_uno=i.matter_uno and ii.comments LIKE '%walk-in%'),'N') AS [Walk In]
It doesn't make sense.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 23, 2013 at 10:32 am
Thanks, Sean earlier pointed it out in an earlier response. However, I have this code from another table which I would want to add as a column to the table. What will be the best way to add it for good performance?
CASE WHEN isnull((SELECT top 1 i.comments
FROMHBA_SOURCE_BUS i
INNER JOINCXA_FOLDER_OBJECT j ON i.FO_UNO = j.FO_UNO
INNER JOINHBM_NAME k ON k.NAME_UNO = j.OBJECT_UNO
INNER JOINHBL_NAME_CLASS h on k.name_class_code = h.name_class_code
WHEREi.MATTER_UNO = m.MATTER_UNO
and ref_source_code = 'LEN1'), '') like '%DEBT:%'
THEN
ltrim(rtrim(replace(replace(replace(replace(replace(substring(isnull
((SELECT top 1 i.comments
FROM HBA_SOURCE_BUS i
INNER JOIN CXA_FOLDER_OBJECT j ON i.FO_UNO = j.FO_UNO
INNER JOIN HBM_NAME k ON k.NAME_UNO = j.OBJECT_UNO
inner join HBL_NAME_CLASS h on k.name_class_code = h.name_class_code
WHERE i.MATTER_UNO = m.MATTER_UNO and ref_source_code = 'LEN1'), ''), 6, len(isnull
((SELECT top 1 i.comments
FROM HBA_SOURCE_BUS i
INNER JOIN CXA_FOLDER_OBJECT j ON i.FO_UNO = j.FO_UNO
INNER JOIN HBM_NAME k ON k.NAME_UNO = j.OBJECT_UNO
inner join HBL_NAME_CLASS h on k.name_class_code = h.name_class_code
WHERE i.MATTER_UNO = m.MATTER_UNO and ref_source_code = 'LEN1'), '')
)), 'WALK-IN', ''), '|', ''), '£', ''), '¬', ''), ',', '')))
ELSE '0'
END AS lender1_debt
December 30, 2013 at 2:20 am
eobiki10 (12/23/2013)
Thanks, Sean earlier pointed it out in an earlier response. However, I have this code from another table which I would want to add as a column to the table. What will be the best way to add it for good performance?
CASE WHEN isnull((SELECT top 1 i.commentsFROMHBA_SOURCE_BUS i
INNER JOINCXA_FOLDER_OBJECT j ON i.FO_UNO = j.FO_UNO
INNER JOINHBM_NAME k ON k.NAME_UNO = j.OBJECT_UNO
INNER JOINHBL_NAME_CLASS h on k.name_class_code = h.name_class_code
WHEREi.MATTER_UNO = m.MATTER_UNO
and ref_source_code = 'LEN1'), '') like '%DEBT:%'
THEN
ltrim(rtrim(replace(replace(replace(replace(replace(substring(isnull
((SELECT top 1 i.comments
FROM HBA_SOURCE_BUS i
INNER JOIN CXA_FOLDER_OBJECT j ON i.FO_UNO = j.FO_UNO
INNER JOIN HBM_NAME k ON k.NAME_UNO = j.OBJECT_UNO
inner join HBL_NAME_CLASS h on k.name_class_code = h.name_class_code
WHERE i.MATTER_UNO = m.MATTER_UNO and ref_source_code = 'LEN1'), ''), 6, len(isnull
((SELECT top 1 i.comments
FROM HBA_SOURCE_BUS i
INNER JOIN CXA_FOLDER_OBJECT j ON i.FO_UNO = j.FO_UNO
INNER JOIN HBM_NAME k ON k.NAME_UNO = j.OBJECT_UNO
inner join HBL_NAME_CLASS h on k.name_class_code = h.name_class_code
WHERE i.MATTER_UNO = m.MATTER_UNO and ref_source_code = 'LEN1'), '')
)), 'WALK-IN', ''), '|', ''), '£', ''), '¬', ''), ',', '')))
ELSE '0'
END AS lender1_debt
What is the distribution of values in column 'MATTER_UNO' of table HBA_SOURCE_BUS? Are they unique?
Are values in column 'comments' of table HBA_SOURCE_BUS only valid in this context if HBA_SOURCE_BUS joins to the other three tables as shown?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 30, 2013 at 11:35 am
Thanks Chris for responding to this query. I have been able to solve the problem.
EO
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply