July 2, 2012 at 1:38 am
ALTER VIEW [dbo].[neoset_dailySalesManReport]
AS
SELECT distinct TOP (100) PERCENT s.contentid, s.entryDate AS 'date', s.customerName, s.customerLocation, sat.title AS 'action type' ,
sit.title AS 'invoice type', '€' + convert(nvarchar(max),CONVERT(DECIMAL(10, 2), s.invoiceAmount)) AS 'invoice amount'
,slt.title AS 'furniture location'
,s.customerPhones, s.mobilePhone, s.InformationPromotions,
c.lastName + ' ' + c.firstName AS 'salesman',
convert(nvarchar(max),c.contentid) as 'salesmanid',convert(nvarchar(max),st.contentid) as 'store',
convert(nvarchar(max),dbo.fn_RemoveHTML(s.comments)) as 'comments',
s.relativerecord, s.cultureid
FROM dbo.neoset_statisticsLangs AS s
LEFT JOIN dbo.neoset_statisticsActionTypeLangs AS sat ON s.actionType = sat.ContentId AND s.CultureId = sat.CultureId
INNER JOIN dbo.neoset_statisticsInvoiceTypesLangs AS sit ON s.invoiceType = sit.ContentId AND s.CultureId = sit.CultureId
LEFT JOIN dbo.neoset_statisticsLocation_types_jt sltjt on s.contentid = sltjt.ref1id
INNER JOIN dbo.neoset_statisticsLocationTypesLangs AS slt ON sltjt.ref2id = slt.ContentId and s.CultureId = slt.CultureId
INNER JOIN dbo.neoset_contactsLangs AS c ON s.salesMan = c.ContentId AND s.CultureId = c.CultureId
INNER JOIN dbo.neoset_storesLangs as st on s.store = st.contentid AND st.cultureid = s.cultureid
WHERE s.IsDeleted = 0 and s.isApproved = 1
--group by s.contentid ,s.entryDate,s.customerName, s.customerLocation, sat.title,sit.title,s.invoiceAmount,
--s.customerPhones, s.mobilePhone, s.InformationPromotions, c.contentid,st.contentid,c.lastName,c.firstName,
--convert(nvarchar(max),dbo.fn_RemoveHTML(s.comments)),s.relativerecord, s.cultureid --,slt.title
ORDER BY 'salesman', s.customerName, 'date' ,'furniture location'
GO
The code above returns a result set of 3 rows, two of which share the same contentId and one with a unique contentid. The current appearance of the data is like the following
contentid ................... furniture location ..............
1 xxxx
1 yyyy
2 aaaa
I want the data to appear summarized as follows
contentid ................... furniture location ..............
1 xxxx,yyyy
2 aaaa
can anyone help? I hope I defined my problem clearly enough
July 2, 2012 at 2:03 am
Those two rows with contentid = 1, which have a different furniturelocation: which other columns are different between the two rows?
Avoid using ORDER BY in views. If you check the output of that view, you will find that it's not ordered.
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
July 2, 2012 at 2:03 am
This problem has been bugginh me since friday! After a good weekend I managed to find the solution 🙂
ALTER VIEW [dbo].[neoset_dailySalesManReport]
AS
SELECT distinct TOP (100) PERCENT s.contentid, s.entryDate AS 'date', s.customerName, s.customerLocation, sat.title AS 'action type' ,
sit.title AS 'invoice type', '€' + convert(nvarchar(max),CONVERT(DECIMAL(10, 2), s.invoiceAmount)) AS 'invoice amount',
(STUFF((SELECT ',' + title FROM neoset_statisticsLocationTypesLangs slt2
INNER JOIN neoset_statisticsLocation_types_jt sltjt2 on slt2.contentid = sltjt2.ref2id and s.CultureId = slt2.cultureid
WHERE sltjt2.ref1id = s.contentid ORDER BY title
FOR XML PATH(''), TYPE, ROOT).value('root[1]','nvarchar(max)'),1,1,'')) as 'furniture location'
--,slt.title AS 'furniture location'
,s.customerPhones, s.mobilePhone, s.InformationPromotions,
c.lastName + ' ' + c.firstName AS 'salesman',
convert(nvarchar(max),c.contentid) as 'salesmanid',convert(nvarchar(max),st.contentid) as 'store',
convert(nvarchar(max),dbo.fn_RemoveHTML(s.comments)) as 'comments',
s.relativerecord, s.cultureid
FROM dbo.neoset_statisticsLangs AS s
LEFT JOIN dbo.neoset_statisticsActionTypeLangs AS sat ON s.actionType = sat.ContentId AND s.CultureId = sat.CultureId
INNER JOIN dbo.neoset_statisticsInvoiceTypesLangs AS sit ON s.invoiceType = sit.ContentId AND s.CultureId = sit.CultureId
LEFT JOIN dbo.neoset_statisticsLocation_types_jt sltjt on s.contentid = sltjt.ref1id
INNER JOIN dbo.neoset_statisticsLocationTypesLangs AS slt ON sltjt.ref2id = slt.ContentId and s.CultureId = slt.CultureId
INNER JOIN dbo.neoset_contactsLangs AS c ON s.salesMan = c.ContentId AND s.CultureId = c.CultureId
INNER JOIN dbo.neoset_storesLangs as st on s.store = st.contentid AND st.cultureid = s.cultureid
WHERE s.IsDeleted = 0 and s.isApproved = 1
--group by s.contentid ,s.entryDate,s.customerName, s.customerLocation, sat.title,sit.title,s.invoiceAmount,
--s.customerPhones, s.mobilePhone, s.InformationPromotions, c.contentid,st.contentid,c.lastName,c.firstName,
--convert(nvarchar(max),dbo.fn_RemoveHTML(s.comments)),s.relativerecord, s.cultureid --,slt.title
ORDER BY 'salesman', s.customerName, 'date'
GO
July 2, 2012 at 2:25 am
mixalissen (7/2/2012)
This problem has been bugginh me since friday! After a good weekend I managed to find the solution 🙂
ALTER VIEW [dbo].[neoset_dailySalesManReport]
AS
SELECT distinct TOP (100) PERCENT s.contentid, s.entryDate AS 'date', s.customerName, s.customerLocation, sat.title AS 'action type' ,
sit.title AS 'invoice type', '€' + convert(nvarchar(max),CONVERT(DECIMAL(10, 2), s.invoiceAmount)) AS 'invoice amount',
(STUFF((SELECT ',' + title FROM neoset_statisticsLocationTypesLangs slt2
INNER JOIN neoset_statisticsLocation_types_jt sltjt2 on slt2.contentid = sltjt2.ref2id and s.CultureId = slt2.cultureid
WHERE sltjt2.ref1id = s.contentid ORDER BY title
FOR XML PATH(''), TYPE, ROOT).value('root[1]','nvarchar(max)'),1,1,'')) as 'furniture location'
--,slt.title AS 'furniture location'
,s.customerPhones, s.mobilePhone, s.InformationPromotions,
c.lastName + ' ' + c.firstName AS 'salesman',
convert(nvarchar(max),c.contentid) as 'salesmanid',convert(nvarchar(max),st.contentid) as 'store',
convert(nvarchar(max),dbo.fn_RemoveHTML(s.comments)) as 'comments',
s.relativerecord, s.cultureid
FROM dbo.neoset_statisticsLangs AS s
LEFT JOIN dbo.neoset_statisticsActionTypeLangs AS sat ON s.actionType = sat.ContentId AND s.CultureId = sat.CultureId
INNER JOIN dbo.neoset_statisticsInvoiceTypesLangs AS sit ON s.invoiceType = sit.ContentId AND s.CultureId = sit.CultureId
LEFT JOIN dbo.neoset_statisticsLocation_types_jt sltjt on s.contentid = sltjt.ref1id
INNER JOIN dbo.neoset_statisticsLocationTypesLangs AS slt ON sltjt.ref2id = slt.ContentId and s.CultureId = slt.CultureId
INNER JOIN dbo.neoset_contactsLangs AS c ON s.salesMan = c.ContentId AND s.CultureId = c.CultureId
INNER JOIN dbo.neoset_storesLangs as st on s.store = st.contentid AND st.cultureid = s.cultureid
WHERE s.IsDeleted = 0 and s.isApproved = 1
--group by s.contentid ,s.entryDate,s.customerName, s.customerLocation, sat.title,sit.title,s.invoiceAmount,
--s.customerPhones, s.mobilePhone, s.InformationPromotions, c.contentid,st.contentid,c.lastName,c.firstName,
--convert(nvarchar(max),dbo.fn_RemoveHTML(s.comments)),s.relativerecord, s.cultureid --,slt.title
ORDER BY 'salesman', s.customerName, 'date'
GO
Not so fast!
* You still have tables neoset_statisticsLocationTypesLangs and neoset_statisticsLocation_types_jt in your
FROM list, even though neither are referenced in the output, or by any joins, or the WHERE clause.
Removing them will probably eliminate the requirement for DISTINCT.
* The joins and where clause in the correlated subquery generating 'furniture location' are confusingly jumbled.
* Your view definition still includes TOP and ORDER BY. The optimizer will ignore them - take them out.
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
July 2, 2012 at 11:56 pm
ChrisM@Work (7/2/2012)* You still have tables neoset_statisticsLocationTypesLangs and neoset_statisticsLocation_types_jt in your
FROM list, even though neither are referenced in the output, or by any joins, or the WHERE clause.
Removing them will probably eliminate the requirement for DISTINCT.
I ran into an interesting case the other day when I was trying to optimize a query. The original writer had included 4 tables in a JOIN that weren't being used at all. Since each JOIN was to a unique record (per row of the left table), it didn't result in any record duplication.
What I found out was that, after removing those JOINs, it appeared to have 0 impact on the query's execution speed. I'm wondering if the optimizer was smart enough to exclude them from the execution plan. Unfortunately, I didn't look at the plans to see if those 4 tables actually appeared or not.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 3, 2012 at 1:57 am
dwain.c (7/2/2012)
ChrisM@Work (7/2/2012)* You still have tables neoset_statisticsLocationTypesLangs and neoset_statisticsLocation_types_jt in your
FROM list, even though neither are referenced in the output, or by any joins, or the WHERE clause.
Removing them will probably eliminate the requirement for DISTINCT.
I ran into an interesting case the other day when I was trying to optimize a query. The original writer had included 4 tables in a JOIN that weren't being used at all. Since each JOIN was to a unique record (per row of the left table), it didn't result in any record duplication.
What I found out was that, after removing those JOINs, it appeared to have 0 impact on the query's execution speed. I'm wondering if the optimizer was smart enough to exclude them from the execution plan. Unfortunately, I didn't look at the plans to see if those 4 tables actually appeared or not.
It's worth a closer look if you get the opportunity. I know that the optimiser can snip out cascading CROSS APPLYs if they're not referenced in the output list (or anywhere else).
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply