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…
Yes sorry didn’t mean for this to sound like it was a bad thing, I just meant that need more context.
Thanks!
2 Likes
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.
1 Like
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.
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)
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.
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
1 Like
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]
Unfortunately, String_Agg is not a recognized built-in function name.
Unfortunately, this continued to give me a version error.
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.
2 Likes
What version of SQL are you running and what Compatibility Level?
View or change the compatibility level of a database - SQL Server | Microsoft Learn
1 Like
Just wondering if they need the actual quote information or just that they had one in the past 7 days?
If not, maybe you can use
REVERSE ( STUFF ( REVERSE ( REPLACE ( REPLACE
I would go into more detail, but I actually don’t understand it. I think I lifted it from @josecgomez but don’t remember the topic I got it from.
Found the thread
Yes, the thread is a nice history of getting rows into cells. And it’s a LONG thread, so read all the way to the bottom! Prior to SQL Serv2017, we had to use the FOR XML hack to accomplish this. If you’re on SQL 2017+, Microsoft created a T-SQL function to combine rows to columns with STRING_AGG. You must set the database compatibility level to 2017 or higher (140-150 if I recall).
@Mark_Wonsil we have 2016 installed and are using compatibility of 2014 as we had some major issues with clocking in and out when we tried to convert to 2016.
Yes, they are looking for the actual data.
I did use that format of the
REVERSE ( STUFF ( REVERSE ( REPLACE ( REPLACE
in another export that I am using, but it was very simple. This is combining multiple fields as well as multiple rows for the quote lines and part num with description, then combining that into another combination for each of the CustCnt.EMailAddresses across the past 7 days.
Yes, this thread is great and was helpful in my other BAQ as indicated in my other response, but I ended up getting to where I am currently using the following article at the end of the post you referenced.
KB0039364 - Turn Multiple Row Values from a Query into a Comma Separated List
That KB article does NOT use either method (FOR XML or the T-SQL), but uses CTE, UnionAll, and some calculated fields to pull the values together. That’s why I was hoping that someone would be able to help me get to the next step of combining the current data across the CustCnt.EMailAddress field. I’m still trying some things related to the FOR XML and some other possible items, but nothing is working so far.
If anyone has any other suggestions, I’m still all ears. Thanks to everyone that has posted all of the helpful information already!
If you can get a CTE to output each quote as
Email | Quote Info
You can then do a subquery and stuff it all into one line by email.
2 Likes
I will give that a try, but I’m not totally sure what I’m doing with it to get there. I’ll post back once I have a chance to try it out.