Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to use the subquery column highglighted in the GROUP BY Expand / Collapse
Author
Message
Posted Thursday, December 19, 2013 11:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:16 AM
Points: 85, Visits: 345
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,
[color=#8ab74e]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[/color]
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
Post #1524733
Posted Thursday, December 19, 2013 12:01 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:16 AM
Points: 85, Visits: 345
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
Post #1524738
Posted Friday, December 20, 2013 9:59 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:46 AM
Points: 500, Visits: 1,386
(Without going into the details of your query) maybe you can convert your query to a CTE then group the result?
Post #1525079
Posted Friday, December 20, 2013 10:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:46 AM
Points: 13,078, Visits: 12,528
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1525097
Posted Friday, December 20, 2013 1:19 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:16 AM
Points: 85, Visits: 345
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
Post #1525169
Posted Friday, December 20, 2013 1:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:46 AM
Points: 13,078, Visits: 12,528
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1525171
Posted Monday, December 23, 2013 3:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:16 AM
Points: 85, Visits: 345
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
Post #1525458
Posted Monday, December 23, 2013 4:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 6,780, Visits: 13,980
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?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1525464
Posted Monday, December 23, 2013 5:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 2:16 AM
Points: 85, Visits: 345
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.
Post #1525472
Posted Monday, December 23, 2013 5:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 6,780, Visits: 13,980
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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1525480
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse