BAQ Help - Combining multiple records into 1 field

EpsilonMaximus68Olds · September 22, 2022, 12:36pm

I’m in need of some assistance. I have the attached BAQ combining the QuoteDtl.QuoteNum with the QuoteDtl.PartNum and QuoteDtl.LineDesc of each line on the quote. The part I’m looking for help with is that we want to combine ALL quotes/lines across the last 7 days for each CustCnt.EMailAddress. If anyone can provide help with how I can bump this up to the next level, I would greatly appreciate it.
CombineAllQuotes4EachEmailLast7Days.baq (46.3 KB)

EpsilonMaximus68Olds · October 7, 2022, 3:11pm

@josecgomez , @Banderson , @ckrusen - Wondering if any of you might be able to help me take my BAQ to the next level?

josecgomez · October 7, 2022, 3:17pm

Eric,
Most people aren’t going to import your BAQ into their system. Can you explain a little more of what you need, and show us screen shot / sql something what the BAQ looks like? Then maybe we can give you some pointers.

utaylor · October 7, 2022, 3:28pm

You were looking at the last 10 quotes? And then what?

bordway · October 7, 2022, 3:30pm

Usually wouldn’t but…it’s a slow day (so far).


with [CTE] as 
(select 
	[BaseData].[QuoteDtl_Company] as [QuoteDtl_Company],
	[BaseData].[CustCnt_EMailAddress] as [CustCnt_EMailAddress],
	[BaseData].[QuoteHed_DateQuoted] as [QuoteHed_DateQuoted],
	[BaseData].[QuoteDtl_QuoteNum] as [QuoteDtl_QuoteNum],
	[BaseData].[QuoteDtl_QuoteLine] as [QuoteDtl_QuoteLine],
	[BaseData].[QuoteDtl_PartNum] as [QuoteDtl_PartNum],
	[BaseData].[QuoteDtl_LineDesc] as [QuoteDtl_LineDesc],
	[BaseData].[Calculated_QuoteRowNum] as [Calculated_QuoteRowNum],
	[BaseData].[Calculated_QuoteRowMax] as [Calculated_QuoteRowMax],
	(CAST(BaseData.QuoteDtl_QuoteNum AS NVARCHAR(4000)) + CAST(' - (LF) ' AS NVARCHAR(4000)) + CAST(BaseData.QuoteDtl_PartNum + '(' + LEFT(BaseData.QuoteDtl_LineDesc, CASE WHEN CHARINDEX(CHAR(13), BaseData.QuoteDtl_LineDesc) = 0 THEN 0 ELSE (CHARINDEX(CHAR(13), BaseData.QuoteDtl_LineDesc) - 1) END) + ')' AS NVARCHAR(4000))) as [Calculated_Quotes]
from  (select 
	[QuoteDtl].[Company] as [QuoteDtl_Company],
	[CustCnt].[EMailAddress] as [CustCnt_EMailAddress],
	[QuoteHed].[DateQuoted] as [QuoteHed_DateQuoted],
	[QuoteDtl].[QuoteNum] as [QuoteDtl_QuoteNum],
	[QuoteDtl].[QuoteLine] as [QuoteDtl_QuoteLine],
	[QuoteDtl].[PartNum] as [QuoteDtl_PartNum],
	[QuoteDtl].[LineDesc] as [QuoteDtl_LineDesc],
	(ROW_NUMBER() OVER(PARTITION BY QuoteDtl.Company, QuoteDtl.QuoteNum ORDER BY CustCnt.EMailAddress, QuoteHed.DateQuoted, QuoteDtl.QuoteNum)) as [Calculated_QuoteRowNum],
	(COUNT(1) OVER(PARTITION BY QuoteDtl.Company, QuoteDtl.QuoteNum)) as [Calculated_QuoteRowMax]
from Erp.QuoteDtl as QuoteDtl
inner join Erp.QuoteHed as QuoteHed on 
	QuoteDtl.Company = QuoteHed.Company
	and QuoteDtl.QuoteNum = QuoteHed.QuoteNum
	and ( QuoteHed.DateQuoted >= DATEADD (day, -7, GETDATE())  )

inner join Erp.QuoteCnt as QuoteCnt on 
	QuoteHed.Company = QuoteCnt.Company
	and QuoteHed.QuoteNum = QuoteCnt.QuoteNum
inner join Erp.CustCnt as CustCnt on 
	QuoteCnt.Company = CustCnt.Company
	and QuoteCnt.CustNum = CustCnt.CustNum
	and QuoteCnt.ShipToNum = CustCnt.ShipToNum
	and QuoteCnt.ConNum = CustCnt.ConNum
where (not QuoteDtl.AnalysisCode = 'ASC'))  as BaseData
where (BaseData.Calculated_QuoteRowNum = 1  and BaseData.Calculated_QuoteRowMax <= 10)
union all
select 
	[BaseData1].[QuoteDtl_Company] as [QuoteDtl_Company],
	[BaseData1].[CustCnt_EMailAddress] as [CustCnt_EMailAddress],
	[BaseData1].[QuoteHed_DateQuoted] as [QuoteHed_DateQuoted],
	[BaseData1].[QuoteDtl_QuoteNum] as [QuoteDtl_QuoteNum],
	[BaseData1].[QuoteDtl_QuoteLine] as [QuoteDtl_QuoteLine],
	[BaseData1].[QuoteDtl_PartNum] as [QuoteDtl_PartNum],
	[BaseData1].[QuoteDtl_LineDesc] as [QuoteDtl_LineDesc],
	[BaseData1].[Calculated_QuoteRowNum] as [Calculated_QuoteRowNum],
	[BaseData1].[Calculated_QuoteRowMax] as [Calculated_QuoteRowMax],
	(CTE1.Calculated_Quotes + CAST('(LF)' + CTE1.QuoteDtl_PartNum + '(' + LEFT(BaseData1.QuoteDtl_LineDesc, CASE WHEN CHARINDEX(CHAR(13), BaseData1.QuoteDtl_LineDesc) = 0 THEN 0 ELSE (CHARINDEX(CHAR(13), BaseData1.QuoteDtl_LineDesc) - 1) END) + ')' AS NVARCHAR(4000))) as [Calculated_Quotes2]
from  (select 
	[QuoteDtl].[Company] as [QuoteDtl_Company],
	[CustCnt].[EMailAddress] as [CustCnt_EMailAddress],
	[QuoteHed].[DateQuoted] as [QuoteHed_DateQuoted],
	[QuoteDtl].[QuoteNum] as [QuoteDtl_QuoteNum],
	[QuoteDtl].[QuoteLine] as [QuoteDtl_QuoteLine],
	[QuoteDtl].[PartNum] as [QuoteDtl_PartNum],
	[QuoteDtl].[LineDesc] as [QuoteDtl_LineDesc],
	(ROW_NUMBER() OVER(PARTITION BY QuoteDtl.Company, QuoteDtl.QuoteNum ORDER BY CustCnt.EMailAddress, QuoteHed.DateQuoted, QuoteDtl.QuoteNum)) as [Calculated_QuoteRowNum],
	(COUNT(1) OVER(PARTITION BY QuoteDtl.Company, QuoteDtl.QuoteNum)) as [Calculated_QuoteRowMax]
from Erp.QuoteDtl as QuoteDtl
inner join Erp.QuoteHed as QuoteHed on 
	QuoteDtl.Company = QuoteHed.Company
	and QuoteDtl.QuoteNum = QuoteHed.QuoteNum
	and ( QuoteHed.DateQuoted >= DATEADD (day, -7, GETDATE())  )

inner join Erp.QuoteCnt as QuoteCnt on 
	QuoteHed.Company = QuoteCnt.Company
	and QuoteHed.QuoteNum = QuoteCnt.QuoteNum
inner join Erp.CustCnt as CustCnt on 
	QuoteCnt.Company = CustCnt.Company
	and QuoteCnt.CustNum = CustCnt.CustNum
	and QuoteCnt.ShipToNum = CustCnt.ShipToNum
	and QuoteCnt.ConNum = CustCnt.ConNum
where (not QuoteDtl.AnalysisCode = 'ASC'))  as BaseData1
inner join  CTE  as CTE1 on 
	BaseData1.QuoteDtl_Company = CTE1.QuoteDtl_Company
	and BaseData1.QuoteDtl_QuoteNum = CTE1.QuoteDtl_QuoteNum
	and BaseData1.Calculated_QuoteRowNum = CTE1.Calculated_QuoteRowNum + 1
where (BaseData1.Calculated_QuoteRowMax <= 10))

select 
	[CTE].[CustCnt_EMailAddress] as [CustCnt_EMailAddress],
	[CTE].[QuoteHed_DateQuoted] as [QuoteHed_DateQuoted],
	[CTE].[Calculated_Quotes] as [Calculated_Quotes]
from  CTE  as CTE
where (CTE.Calculated_QuoteRowNum = CTE.Calculated_QuoteRowMax)
order by CTE.CustCnt_EMailAddress, CTE.QuoteHed_DateQuoted

and FWIW, screenshot of test system results (after removing the date criteria)

EpsilonMaximus68Olds · October 7, 2022, 3:31pm

Jose,

I posted the BAQ as I had seen other people do that and I figured it would be the easiest way to see what I’m trying to do. We want to combine the past 7 days worth of quotes and quote lines into 1 field based on a specific CustCnt.EMailAddress that is linked to the quotes. We need this to export for a marketing firm to help us generate emails to our customers so they only receive 1 weekly email instead of as many emails as quotes they generate per week.

Update 1
I have part of this working where it is combining the QuoteNum with each of the PartNum and LineDesc combinations, but I’m not sure how to do it so it will combine those already combined fields into a group based on the CustCnt.EMailAddress.
End Update 1

Update 2
Screenshot of sample Excel formatted data:

End Update 2

Update 3
I followed the article from Epicor KB0039364 and just modified it to work with what I needed.
End Update 3

The format for the combined field needs to include the characters “(LF)” as they will use this to parse items. Each PartNum (LineDesc) is for each line of the quote, then the semicolon ( is the splitter between quotes. If you need more details I will do my best to explain it. The BAQ will definitely be the fastest way to see what we are trying to do.
Here is the format in somewhat of a format it should be within the BAQ:
QuoteDtl.QuoteNum - (LF) QuoteDtl.PartNum (QuoteDtl.LineDesc) (LF) QuoteDtl.PartNum (QuoteDtl.LineDesc) (LF) QuoteDtl.PartNum (QuoteDtl.LineDesc); (LF) QuoteDtl.QuoteNum - (LF) QuoteDtl.PartNum (QuoteDtl.LineDesc) (LF) QuoteDtl.PartNum (QuoteDtl.LineDesc) (LF) QuoteDtl.PartNum (QuoteDtl.LineDesc)

Here is the SQL:

(select 
	[BaseData].[QuoteDtl_Company] as [QuoteDtl_Company],
	[BaseData].[CustCnt_EMailAddress] as [CustCnt_EMailAddress],
	[BaseData].[QuoteHed_DateQuoted] as [QuoteHed_DateQuoted],
	[BaseData].[QuoteDtl_QuoteNum] as [QuoteDtl_QuoteNum],
	[BaseData].[QuoteDtl_QuoteLine] as [QuoteDtl_QuoteLine],
	[BaseData].[QuoteDtl_PartNum] as [QuoteDtl_PartNum],
	[BaseData].[QuoteDtl_LineDesc] as [QuoteDtl_LineDesc],
	[BaseData].[Calculated_QuoteRowNum] as [Calculated_QuoteRowNum],
	[BaseData].[Calculated_QuoteRowMax] as [Calculated_QuoteRowMax],
	(CAST(BaseData.QuoteDtl_PartNum + '(' + LEFT(BaseData.QuoteDtl_LineDesc, CASE WHEN CHARINDEX(CHAR(13), BaseData.QuoteDtl_LineDesc) = 0 THEN 0 ELSE (CHARINDEX(CHAR(13), BaseData.QuoteDtl_LineDesc) - 1) END) + ')' AS NVARCHAR(4000))) as [Calculated_QuoteLines],
	[BaseData].[Calculated_QuoteTotal] as [Calculated_QuoteTotal]
from  (select 
	[QuoteDtl].[Company] as [QuoteDtl_Company],
	[CustCnt].[EMailAddress] as [CustCnt_EMailAddress],
	[QuoteHed].[DateQuoted] as [QuoteHed_DateQuoted],
	[QuoteDtl].[QuoteNum] as [QuoteDtl_QuoteNum],
	[QuoteDtl].[QuoteLine] as [QuoteDtl_QuoteLine],
	[QuoteDtl].[PartNum] as [QuoteDtl_PartNum],
	[QuoteDtl].[LineDesc] as [QuoteDtl_LineDesc],
	(ROW_NUMBER() OVER(PARTITION BY QuoteDtl.Company, QuoteDtl.QuoteNum ORDER BY CustCnt.EMailAddress, QuoteHed.DateQuoted, QuoteDtl.QuoteNum)) as [Calculated_QuoteRowNum],
	(COUNT(1) OVER(PARTITION BY QuoteDtl.Company, QuoteDtl.QuoteNum)) as [Calculated_QuoteRowMax],
	(SUM(QuoteDtl.OrderQty * (QuoteDtl.DocExpUnitPrice - (QuoteDtl.DocExpUnitPrice * (QuoteDtl.DiscountPercent / 100)))) OVER(PARTITION BY QuoteDtl.QuoteNum)) as [Calculated_QuoteTotal]
from Erp.QuoteDtl as QuoteDtl
inner join Erp.QuoteHed as QuoteHed on 
	QuoteDtl.Company = QuoteHed.Company
	and QuoteDtl.QuoteNum = QuoteHed.QuoteNum
	and ( QuoteHed.DateQuoted >= dateadd (day, -7, Constants.Today)  and QuoteHed.DateQuoted <= Constants.Today  )

inner join Erp.QuoteCnt as QuoteCnt on 
	QuoteHed.Company = QuoteCnt.Company
	and QuoteHed.QuoteNum = QuoteCnt.QuoteNum
inner join Erp.CustCnt as CustCnt on 
	QuoteCnt.Company = CustCnt.Company
	and QuoteCnt.CustNum = CustCnt.CustNum
	and QuoteCnt.ShipToNum = CustCnt.ShipToNum
	and QuoteCnt.ConNum = CustCnt.ConNum
	and ( not CustCnt.EMailAddress = ''  )

where (not QuoteDtl.AnalysisCode = 'ASC'))  as BaseData
where (BaseData.Calculated_QuoteRowNum = 1  and BaseData.Calculated_QuoteRowMax <= 20)
union all
select 
	[BaseData1].[QuoteDtl_Company] as [QuoteDtl_Company],
	[BaseData1].[CustCnt_EMailAddress] as [CustCnt_EMailAddress],
	[BaseData1].[QuoteHed_DateQuoted] as [QuoteHed_DateQuoted],
	[BaseData1].[QuoteDtl_QuoteNum] as [QuoteDtl_QuoteNum],
	[BaseData1].[QuoteDtl_QuoteLine] as [QuoteDtl_QuoteLine],
	[BaseData1].[QuoteDtl_PartNum] as [QuoteDtl_PartNum],
	[BaseData1].[QuoteDtl_LineDesc] as [QuoteDtl_LineDesc],
	[BaseData1].[Calculated_QuoteRowNum] as [Calculated_QuoteRowNum],
	[BaseData1].[Calculated_QuoteRowMax] as [Calculated_QuoteRowMax],
	(CTE1.Calculated_QuoteLines + CAST('(LF)' + CTE1.QuoteDtl_PartNum + '(' + LEFT(BaseData1.QuoteDtl_LineDesc, CASE WHEN CHARINDEX(CHAR(13), BaseData1.QuoteDtl_LineDesc) = 0 THEN 0 ELSE (CHARINDEX(CHAR(13), BaseData1.QuoteDtl_LineDesc) - 1) END) + ')' AS NVARCHAR(4000))) as [Calculated_QuoteLines2],
	[BaseData1].[Calculated_QuoteTotal] as [Calculated_QuoteTotal]
from  (select 
	[QuoteDtl].[Company] as [QuoteDtl_Company],
	[CustCnt].[EMailAddress] as [CustCnt_EMailAddress],
	[QuoteHed].[DateQuoted] as [QuoteHed_DateQuoted],
	[QuoteDtl].[QuoteNum] as [QuoteDtl_QuoteNum],
	[QuoteDtl].[QuoteLine] as [QuoteDtl_QuoteLine],
	[QuoteDtl].[PartNum] as [QuoteDtl_PartNum],
	[QuoteDtl].[LineDesc] as [QuoteDtl_LineDesc],
	(ROW_NUMBER() OVER(PARTITION BY QuoteDtl.Company, QuoteDtl.QuoteNum ORDER BY CustCnt.EMailAddress, QuoteHed.DateQuoted, QuoteDtl.QuoteNum)) as [Calculated_QuoteRowNum],
	(COUNT(1) OVER(PARTITION BY QuoteDtl.Company, QuoteDtl.QuoteNum)) as [Calculated_QuoteRowMax],
	(SUM(QuoteDtl.OrderQty * (QuoteDtl.DocExpUnitPrice - (QuoteDtl.DocExpUnitPrice * (QuoteDtl.DiscountPercent / 100)))) OVER(PARTITION BY QuoteDtl.QuoteNum)) as [Calculated_QuoteTotal]
from Erp.QuoteDtl as QuoteDtl
inner join Erp.QuoteHed as QuoteHed on 
	QuoteDtl.Company = QuoteHed.Company
	and QuoteDtl.QuoteNum = QuoteHed.QuoteNum
	and ( QuoteHed.DateQuoted >= dateadd (day, -7, Constants.Today)  and QuoteHed.DateQuoted <= Constants.Today  )

inner join Erp.QuoteCnt as QuoteCnt on 
	QuoteHed.Company = QuoteCnt.Company
	and QuoteHed.QuoteNum = QuoteCnt.QuoteNum
inner join Erp.CustCnt as CustCnt on 
	QuoteCnt.Company = CustCnt.Company
	and QuoteCnt.CustNum = CustCnt.CustNum
	and QuoteCnt.ShipToNum = CustCnt.ShipToNum
	and QuoteCnt.ConNum = CustCnt.ConNum
	and ( not CustCnt.EMailAddress = ''  )

where (not QuoteDtl.AnalysisCode = 'ASC'))  as BaseData1
inner join  CTE  as CTE1 on 
	BaseData1.QuoteDtl_Company = CTE1.QuoteDtl_Company
	and BaseData1.QuoteDtl_QuoteNum = CTE1.QuoteDtl_QuoteNum
	and BaseData1.Calculated_QuoteRowNum = CTE1.Calculated_QuoteRowNum + 1
where (BaseData1.Calculated_QuoteRowMax <= 20))

select 
	[CTE].[CustCnt_EMailAddress] as [CustCnt_EMailAddress],
	[CTE].[QuoteHed_DateQuoted] as [QuoteHed_DateQuoted],
	[CTE].[QuoteDtl_QuoteNum] as [QuoteDtl_QuoteNum],
	[CTE].[Calculated_QuoteLines] as [Calculated_QuoteLines],
	(CTE.Calculated_QuoteRowMax) as [Calculated_ItemsQuoted],
	[CTE].[Calculated_QuoteTotal] as [Calculated_QuoteTotal]
from  CTE  as CTE
where (CTE.Calculated_QuoteRowNum = CTE.Calculated_QuoteRowMax)
order by CTE.CustCnt_EMailAddress, CTE.QuoteHed_DateQuoted```

utaylor · October 7, 2022, 3:31pm

It’s odd that you have to take a data table from Epicor and then give them a concatenated list just so they can parse it back out…

EpsilonMaximus68Olds · October 7, 2022, 3:37pm

That’s what I thought, but when I was told that it was this or the company had to spend between 10k-25k and they really didn’t want to have to spend the money for that, I was tasked with figuring this out.

Hogardy · October 7, 2022, 3:41pm

Very strange indeed…
Could you not use this weird marketing firm and generate your own BAQ report looking nice ???
We generate once a week a report for our shipping companies…

josecgomez · October 7, 2022, 3:46pm

Yes sorry didn’t mean for this to sound like it was a bad thing, I just meant that need more context.

Thanks!

EpsilonMaximus68Olds · October 7, 2022, 3:51pm

No worries! Just wanted to make sure I wasn’t doing something that I shouldn’t be. Hoping the added details I posted above will be enough to help get this thing to the next step.

EpsilonMaximus68Olds · October 10, 2022, 7:25pm

I know everyone here is busy, but I wanted to follow up and see if anyone has any suggestions on how to take this BAQ to the next level by grouping the past 7-days worth of quotes (QuoteNum, PartNum, and LineDesc) across each Quote Contact? The existing format works for each quote to combine the Quote Lines, but we want to combine all quotes for each CustCnt.EMailAddress.

NateS · October 11, 2022, 1:24pm

I think you can do this with String_Agg. Make your top level an inner sub query. Remove the sorting on the previous top-level query. Then make a new top level that contains the email address with group by turned on. Add a calculated field:

String_Agg(FinalResults.Calculated_Quotes, ', ')

or this if you want to include the date:

String_Agg(Cast(FinalResults.QuoteHed_DateQuoted as nvarchar) + ' ' + FinalResults.Calculated_Quotes, ', ')

This should get you closer! Note I removed some criteria in the attached BAQ because it wouldn’t run in mine.
MarketingWklyQuoteFllwupExport_n.baq (56.9 KB)

EpsilonMaximus68Olds · October 13, 2022, 3:37pm

Thanks, @NateS! I could not import the BAQ as your version is newer than mine. As soon as I get a chance, I will try to follow the instructions you provided in addition to the copy of the BAQ. I will try to update with my results as soon as I test them out.

E102016 · October 13, 2022, 4:15pm

After you download the BAQ change the extension to .zip
Open the zip file then open the BAQVersion file(use notepad or other text editor), change the version to what is on your system.

Save the file then save the zip file
Rename the zip file back to baq
Import into your test system

NateS · October 17, 2022, 2:02pm

Here is the SQL if that helps.

/*
 * Disclaimer!!!
 * This is not a real query being executed, but a simplified version for general vision.
 * Executing it with any other tool may produce a different result.
 */
 
with [CTE] as 
(select 
	[BaseData].[QuoteDtl_Company] as [QuoteDtl_Company],
	[BaseData].[CustCnt_EMailAddress] as [CustCnt_EMailAddress],
	[BaseData].[QuoteHed_DateQuoted] as [QuoteHed_DateQuoted],
	[BaseData].[QuoteDtl_QuoteNum] as [QuoteDtl_QuoteNum],
	[BaseData].[QuoteDtl_QuoteLine] as [QuoteDtl_QuoteLine],
	[BaseData].[QuoteDtl_PartNum] as [QuoteDtl_PartNum],
	[BaseData].[QuoteDtl_LineDesc] as [QuoteDtl_LineDesc],
	[BaseData].[Calculated_QuoteRowNum] as [Calculated_QuoteRowNum],
	[BaseData].[Calculated_QuoteRowMax] as [Calculated_QuoteRowMax],
	(CAST(BaseData.QuoteDtl_QuoteNum AS NVARCHAR(4000)) + CAST(' - (LF) ' AS NVARCHAR(4000)) + CAST(BaseData.QuoteDtl_PartNum + '(' + LEFT(BaseData.QuoteDtl_LineDesc, CASE WHEN CHARINDEX(CHAR(13), BaseData.QuoteDtl_LineDesc) = 0 THEN 0 ELSE (CHARINDEX(CHAR(13), BaseData.QuoteDtl_LineDesc) - 1) END) + ')' AS NVARCHAR(4000))) as [Calculated_Quotes]
from  (select 
	[QuoteDtl].[Company] as [QuoteDtl_Company],
	[CustCnt].[EMailAddress] as [CustCnt_EMailAddress],
	[QuoteHed].[DateQuoted] as [QuoteHed_DateQuoted],
	[QuoteDtl].[QuoteNum] as [QuoteDtl_QuoteNum],
	[QuoteDtl].[QuoteLine] as [QuoteDtl_QuoteLine],
	[QuoteDtl].[PartNum] as [QuoteDtl_PartNum],
	[QuoteDtl].[LineDesc] as [QuoteDtl_LineDesc],
	(ROW_NUMBER() OVER(PARTITION BY QuoteDtl.Company, QuoteDtl.QuoteNum ORDER BY CustCnt.EMailAddress, QuoteHed.DateQuoted, QuoteDtl.QuoteNum)) as [Calculated_QuoteRowNum],
	(COUNT(1) OVER(PARTITION BY QuoteDtl.Company, QuoteDtl.QuoteNum)) as [Calculated_QuoteRowMax]
from Erp.QuoteDtl as QuoteDtl
inner join Erp.QuoteHed as QuoteHed on 
	QuoteDtl.Company = QuoteHed.Company
	and QuoteDtl.QuoteNum = QuoteHed.QuoteNum
	and ( QuoteHed.DateQuoted >= dateadd (year, -2, Constants.Today)  )

inner join Erp.QuoteCnt as QuoteCnt on 
	QuoteHed.Company = QuoteCnt.Company
	and QuoteHed.QuoteNum = QuoteCnt.QuoteNum
inner join Erp.CustCnt as CustCnt on 
	QuoteCnt.Company = CustCnt.Company
	and QuoteCnt.CustNum = CustCnt.CustNum
	and QuoteCnt.ShipToNum = CustCnt.ShipToNum
	and QuoteCnt.ConNum = CustCnt.ConNum)  as BaseData
where (BaseData.Calculated_QuoteRowNum = 1  and BaseData.Calculated_QuoteRowMax <= 10)
union all
select 
	[BaseData1].[QuoteDtl_Company] as [QuoteDtl_Company],
	[BaseData1].[CustCnt_EMailAddress] as [CustCnt_EMailAddress],
	[BaseData1].[QuoteHed_DateQuoted] as [QuoteHed_DateQuoted],
	[BaseData1].[QuoteDtl_QuoteNum] as [QuoteDtl_QuoteNum],
	[BaseData1].[QuoteDtl_QuoteLine] as [QuoteDtl_QuoteLine],
	[BaseData1].[QuoteDtl_PartNum] as [QuoteDtl_PartNum],
	[BaseData1].[QuoteDtl_LineDesc] as [QuoteDtl_LineDesc],
	[BaseData1].[Calculated_QuoteRowNum] as [Calculated_QuoteRowNum],
	[BaseData1].[Calculated_QuoteRowMax] as [Calculated_QuoteRowMax],
	(CTE1.Calculated_Quotes + CAST('(LF)' + CTE1.QuoteDtl_PartNum + '(' + LEFT(BaseData1.QuoteDtl_LineDesc, CASE WHEN CHARINDEX(CHAR(13), BaseData1.QuoteDtl_LineDesc) = 0 THEN 0 ELSE (CHARINDEX(CHAR(13), BaseData1.QuoteDtl_LineDesc) - 1) END) + ')' AS NVARCHAR(4000))) as [Calculated_Quotes2]
from  (select 
	[QuoteDtl].[Company] as [QuoteDtl_Company],
	[CustCnt].[EMailAddress] as [CustCnt_EMailAddress],
	[QuoteHed].[DateQuoted] as [QuoteHed_DateQuoted],
	[QuoteDtl].[QuoteNum] as [QuoteDtl_QuoteNum],
	[QuoteDtl].[QuoteLine] as [QuoteDtl_QuoteLine],
	[QuoteDtl].[PartNum] as [QuoteDtl_PartNum],
	[QuoteDtl].[LineDesc] as [QuoteDtl_LineDesc],
	(ROW_NUMBER() OVER(PARTITION BY QuoteDtl.Company, QuoteDtl.QuoteNum ORDER BY CustCnt.EMailAddress, QuoteHed.DateQuoted, QuoteDtl.QuoteNum)) as [Calculated_QuoteRowNum],
	(COUNT(1) OVER(PARTITION BY QuoteDtl.Company, QuoteDtl.QuoteNum)) as [Calculated_QuoteRowMax]
from Erp.QuoteDtl as QuoteDtl
inner join Erp.QuoteHed as QuoteHed on 
	QuoteDtl.Company = QuoteHed.Company
	and QuoteDtl.QuoteNum = QuoteHed.QuoteNum
	and ( QuoteHed.DateQuoted >= dateadd (year, -2, Constants.Today)  )

inner join Erp.QuoteCnt as QuoteCnt on 
	QuoteHed.Company = QuoteCnt.Company
	and QuoteHed.QuoteNum = QuoteCnt.QuoteNum
inner join Erp.CustCnt as CustCnt on 
	QuoteCnt.Company = CustCnt.Company
	and QuoteCnt.CustNum = CustCnt.CustNum
	and QuoteCnt.ShipToNum = CustCnt.ShipToNum
	and QuoteCnt.ConNum = CustCnt.ConNum)  as BaseData1
inner join  CTE  as CTE1 on 
	BaseData1.QuoteDtl_Company = CTE1.QuoteDtl_Company
	and BaseData1.QuoteDtl_QuoteNum = CTE1.QuoteDtl_QuoteNum
	and BaseData1.Calculated_QuoteRowNum = CTE1.Calculated_QuoteRowNum + 1
where (BaseData1.Calculated_QuoteRowMax <= 10))

select 
	[FinalResults].[CustCnt_EMailAddress] as [CustCnt_EMailAddress],
	(String_Agg(FinalResults.Calculated_Quotes, ', ')) as [Calculated_Combined]
from  (select 
	[CTE].[CustCnt_EMailAddress] as [CustCnt_EMailAddress],
	[CTE].[QuoteHed_DateQuoted] as [QuoteHed_DateQuoted],
	[CTE].[Calculated_Quotes] as [Calculated_Quotes]
from  CTE  as CTE
where (CTE.Calculated_QuoteRowNum = CTE.Calculated_QuoteRowMax))  as FinalResults
group by [FinalResults].[CustCnt_EMailAddress]

EpsilonMaximus68Olds · October 17, 2022, 3:24pm

Unfortunately, String_Agg is not a recognized built-in function name.

EpsilonMaximus68Olds · October 17, 2022, 3:25pm

Unfortunately, this continued to give me a version error.

NateS · October 17, 2022, 3:25pm

Ahh bummer! If you can’t use String_agg then you have to go back to the old way using FOR XML. That is a headache.

Mark_Wonsil · October 17, 2022, 5:52pm

What version of SQL are you running and what Compatibility Level?

View or change the compatibility level of a database - SQL Server | Microsoft Learn