hi
WHAT I MEAN BY DO THE JOIN SEPERATELY
SELECT * FROM TABLE1 A JOIN TABLE2 B ON A.ID = B.ID JOIN TABLE3 C ON A.ID= C.ID
SELECT * INTO #TEMP FROM TABLE1 A JOIN TABLE2 B ON A.ID = B.ID
NOW DO THE JOIN SEPERATELY
SELECT * FROM #TEMP A JOIN TABLEC B ON A.ID = C.ID
Thanks mike01,
I guess best approach would be to materialize the result of each TOP 1 statement in another temp table such that PartnerCode and ExperienceCount is unique and not null.
Any chance i can get any sample code how to achieve that?
I broke down some of this monstrosity and started looking at the first part where it starts if @BoxUniverse !- 6. I see 3 joins to relatedproductpackagingsection to get a Yes/No response. These nested selects can be removed and added as left joins (for Columns ExperienceWeb, ExperienceSpecimen and ExperienceBooklet). Then if Null then No else Yes. same kind of thing with the columns MinNumberOfPeople, MaxNumberOfPeople, [Michelin Rating], TripAdvisor Rating, [TripAdvisor Certificate of Excellence],[TripAdvisor Travellers Choice], [Logis de France], [Gault et Millaud], and many more. They are all using partnerfacetvalue and facetvalue with a different fkfacetid. What is the PK on these tables?
Thanks Mike
I have reworked on the query and updated as below. Main concern remain the Columns you mentioned above. Still not sure how to avoid those TOP 1 statements
DECLARE @BoxId int =1471
DECLARE @PackagingSectionId int = 1
DECLARE @LanguageId int = 77
DECLARE @noLocation varchar(20) = 'Hide phone and email' --'noLocation'
DECLARE @hiddenContact varchar(20) = 'Hide map localisation' --'hiddenContact'
DECLARE @PhysicalProductFormatId int = 1
DECLARE @BoxUniverse int
DECLARE @BoxCode int, @BoxCodeS int, @BoxVersionS int
DECLARE @BoxCountryId int = (SELECT DISTINCT fkCountryId FROM ProductCountry WHERE fkProductId = @BoxId) -- 109
DECLARE @MasterBoxCode int
DECLARE @ItalyCountryId INT = 109
DECLARE @RomaMarketingRegionId INT = (SELECT MarketingRegionId FROM MarketingRegion WHERE MarketingRegionName = 'Roma')--12
DECLARE @MilanoMarketingRegionId INT = (SELECT MarketingRegionId FROM MarketingRegion WHERE MarketingRegionName = 'Milano')--11
DECLARE @WellnessUniverseId INT = 8
DECLARE @GastronomyUniverseId INT = 2
SELECT @MasterBoxCode = dbo.udf_GetMasterProductByProductId(@BoxId) --1471
SELECT @BoxCode = dbo.udf_GetPhysicalProductIdForLanguage(@BoxId, @LanguageId), -- 1471
@BoxCodeS = dbo.udf_GetLatestSpecimenVersionId(@BoxId), -- 489191
@BoxVersionS = dbo.udf_GetLatestSpecimenVersion(@BoxId) --2
SELECT @BoxUniverse = fkUniverseId from Product INNER JOIN ProductUniverse on Product.ProductId = ProductUniverse.fkProductId
WHERE Product.ProductId = @BoxId -- 4
SELECT
P.ShortName
,P.ProductName as BoxTitle
,PR.PartnerId
,PR.PartnerName
,PR.PartnerCode
,PL.AddressLine2
,PL.PostCode
,PL.City
,PL.SubRegion as ParterSubRegion
,PL.Country
,PL.Website
,PL.Longitude
,PL.Latitude
,PL.MarketingRegion as LocationName
,cntry.CountryId
,SubRegionNumber = SUBSTRING(COALESCE(PL.SubRegionISOCode,''), 4, 10)
,B.BrandName AS BoxBrand
,CP.ProductId AS ExperienceId
,CP.ProductCode
,EU.UniverseName
,BU.UniverseName as BoxUniverse
,BXL.LanguageName as BoxLanguage
,BXL.SBLanguageCode
,EPT.ProductTemplateId
,PPT.PartnerTemplateId
,C.CountryName as BoxCountry
,BoxCode = @BoxCode
,BoxCodeS = @BoxCodeS
,BoxVersionS = @BoxVersionS
,MasterBoxCode = @MasterBoxCode
,ExperienceWeb = 'Yes'
,ExperienceSpecimen = 'Yes'
,ExperienceBooklet ='Yes'
,ExperienceCount = DENSE_RANK() OVER (PARTITION BY P.ProductId,PR.PartnerId ORDER BY ExperienceRank desc,CP.Productid)
,AddressLine1 = PL.AddressLine1
,PartnerRegion = case when COALESCE(C.CountryName,'') <> PL.Country then COALESCE(CL.CountryName,PL.Country) else PL.Region end
,Email = PL.Email
,MarketingRegionClassification = case when COALESCE(C.CountryName,'') <> PL.Country then COALESCE(CL.CountryName,PL.Country) else MRU.MarketingRegion END
,PhoneNo = '0'+ PL.PhoneNo
,CanBeBooked = 1
,MinNumberOfPeople = (select top 1 FV.Value from ProductFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 45 and PFV.fkProductId = CP.ProductId ORDER BY FV.Value)
,MaxNumberOfPeople = (select top 1 FV.Value from ProductFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 44 and PFV.fkProductId = CP.ProductId ORDER BY FV.Value)
,[Michelin Rating] = COALESCE((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 61 and PFV.fkPartnerId = PR.PartnerId ORDER BY FV.Value),'')
,[TripAdvisor Rating] = COALESCE((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 35 and PFV.fkPartnerId = PR.PartnerId ORDER BY FV.Value),'')
,[TripAdvisor Certificate of Excellence] = COALESCE((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 34 and PFV.fkPartnerId = PR.PartnerId ORDER BY FV.Value),'')
,[TripAdvisor Travellers Choice] = COALESCE((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 36 and PFV.fkPartnerId = PR.PartnerId ORDER BY FV.Value),'')
,[Logis de France] = COALESCE((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 23 and PFV.fkPartnerId = PR.PartnerId ORDER BY FV.Value),'')
,[Gault et Millaud] = COALESCE((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 14 and PFV.fkPartnerId = PR.PartnerId ORDER BY FV.Value),'')
,[Bib gourmand] = COALESCE((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 62 and PFV.fkPartnerId = PR.PartnerId ORDER BY FV.Value),'')
,[Plate] = COALESCE((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 63 and PFV.fkPartnerId = PR.PartnerId ORDER BY FV.Value),'')
,[Hotel di charme] = COALESCE((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 18 and PFV.fkPartnerId = PR.PartnerId ORDER BY FV.Value),'')
--Temp table columns starts here
,RP.RelatedProductId As TempRelatedProductId
,PL.AddressLine1 AS TempAddressLine1
,fvloc.[Value] as TempFVLocValue
,fv.[Value] as TempFVValue
,PL.Email AS TempEmail
,PL.PhoneNo AS TempPhoneNo
INTO #BoxExtract2
FROM Product P
INNER JOIN RelatedProduct RP with (nolock)
ON RP.fkProductId = P.ProductId and RP.Active = 1
INNER JOIN PartnerProduct PP with (nolock)
ON PP.fkProductId = RP.fkProduct1Id and PP.Active = 1
INNER JOIN [Partner] PR with (nolock)
ON PR.PartnerId = PP.fkPartnerId and PR.PartnerStatus = 1
INNER JOIN PartnerLocation PL with (nolock)
ON PL.fkPartnerId = PR.PartnerId
INNER JOIN Country cntry
ON cntry.CountryName = PL.Country
LEFT JOIN MasterLocations ml1
ON ml1.MarketingSubRegion = PL.MarketingSubRegion
LEFT JOIN MasterLocations ml2
ON ml2.SubRegionISO = PL.SubRegionISOCode
LEFT JOIN MasterLocations ml3
ON ml3.RegionName = PL.Region
LEFT JOIN MarketingRegionClassification mrc
ON mrc.fkUniverseId = @BoxUniverse
AND mrc.fkBoxCountryId = @BoxCountryId
AND mrc.fkMasterLocationId IS NULL
AND mrc.fkPartnerCountryId = cntry.CountryId
LEFT JOIN MarketingRegionClassification mrc2
ON mrc2.fkUniverseId = @BoxUniverse
AND mrc2.fkBoxCountryId = @BoxCountryId
AND mrc2.fkMasterLocationId = COALESCE(ml1.MasterLocationsId,ml2.MasterLocationsId,ml3.MasterLocationsId)
AND mrc2.fkPartnerCountryId = cntry.CountryId
LEFT JOIN MarketingRegionClassification mrc3
ON mrc3.fkUniverseId = @BoxUniverse
AND mrc3.fkBoxCountryId = @BoxCountryId
AND mrc3.fkMasterLocationId IS NULL
AND mrc3.fkPartnerCountryId = cntry.CountryId
LEFT JOIN MarketingRegion mr1
ON mr1.MarketingRegionId = mrc2.fkMarketingRegionId
LEFT JOIN MarketingRegion mr2
ON mr2.MarketingRegionId = mrc3.fkMarketingRegionId
INNER JOIN ProductUniverse PU WITH (NOLOCK)
ON PU.fkProductId = P.ProductId
LEFT JOIN ProductBrand PB WITH (NOLOCK)
ON PB.fkProductId= P.ProductId
LEFT JOIN Brand B WITH (NOLOCK)
ON B.BrandId=PB.fkBrandId
Left Join Universe BU WITH (NOLOCK)
ON BU.UniverseId = P.fkUniverseBoxId
LEFT JOIN MarketingRegionUniverse MRU WITH (NOLOCK)
ON MRU.UniverseId = P.fkUniverseBoxId
AND MRU.SubregionISOCode = PL.SubRegionISOCode
INNER JOIN Product CP WITH (NOLOCK)
ON CP.ProductId = RP.fkProduct1Id and CP.fkTriggerStateId = 4
INNER JOIN ProductUniverse CPU WITH (NOLOCK)
ON CPU.fkProductId = CP.ProductId
Inner Join Universe EU WITH (NOLOCK)
ON EU.UniverseId = CPU.fkUniverseId
INNER JOIN ProductLanguage BL with (nolock)
ON BL.fkProductId = P.ProductId
LEFT JOIN Country PLC WITH (NOLOCK)
ON PLC.CountryName = PL.Country
LEFT JOIN CountryLocale CL WITH (NOLOCK) --TODO::Execution plan is not as expected
ON CL.fkCountryId = PLC.CountryId
AND CL.fkLanguageId = BL.fkLanguageId
INNER JOIN [Language] BXL WITH (NOLOCK)
ON BXL.LanguageId= BL.fkLanguageId
LEFT JOIN ProductCountry PC WITH (NOLOCK)
ON PC.fkProductId = P.ProductId
LEFT JOIN Country C WITH (NOLOCK)
ON C.CountryId = PC.fkCountryId
INNER JOIN ProductDataTemplate PDT WITH (NOLOCK) --TODO:: Index scan not proper, (need to check how to create uniquq contraint without index)
ON PDT.fkProductId = p.ProductId
AND PDT.fkProductTypeId = 2
AND PDT.fkUniverseId IS NULL
INNER JOIN ProductDataTemplate PDPT WITH (NOLOCK) --TODO:: Index scan not proper,
ON PDPT.fkProductId = p.ProductId
AND PDPT.isPartnerTemplate = 1
AND PDPT.fkUniverseId IS NULL
INNER JOIN ProductTemplate EPT WITH (NOLOCK)
ON EPT.fkProductId = CP.ProductId
AND EPT.fkLanguageId = BL.fkLanguageId
AND EPT.IsDeleted = 0
AND EPT.fkProductTemplateId IS NULL
AND EPT.fkTriggerStateId = 14
AND EPT.fkDataTemplateId = PDT.fkDataTemplateId
INNER JOIN PartnerTemplate PPT WITH (NOLOCK)
ON PPT.fkPartnerId = PR.PartnerId
AND PPT.fkLanguageId = BL.fkLanguageId
AND PPT.IsDeleted = 0
AND PPT.fkPartnerTemplateId IS NULL
AND PPT.fkTriggerStateId = 14
AND PPT.fkDataTemplateId = PDPT.fkDataTemplateId
LEFT JOIN [Facet] f
ON f.FacetName = @hiddenContact
LEFT JOIN PartnerFacetValue PFV
ON PFV.fkPartnerId = ppt.fkPartnerId
AND pfv.fkFacetId = f.facetid
LEFT JOIN [FacetValue] fv --TODO:: not showing in execution plan
ON fv.FacetValueId = pfv.fkFacetValueId
LEFT JOIN [Facet] floc
ON floc.FacetName = @noLocation
LEFT JOIN PartnerFacetValue PFVloc
ON PFVloc.fkPartnerId = ppt.fkPartnerId
AND PFVloc.fkFacetId = floc.facetid
LEFT JOIN [FacetValue] fvloc
ON fvloc.FacetValueId = PFVloc.fkFacetValueId
LEFT JOIN (SELECT SUM( ps.rank) AS ExperienceRank,RPPS.fkRelatedProductId
FROM RelatedProductPackagingSection RPPS WITH (NOLOCK)
INNER JOIN packagingsection ps WITH (NOLOCK)
ON ps.packagingsectionid= rpps.fkpackagingsectionid
GROUP BY RPPS.fkRelatedProductId ) AS ERank
ON rp.RelatedProductId = Erank.fkRelatedProductId
LEFT JOIN [ProductVersion] WITH (NOLOCK)
ON [ProductVersion].[fkProductId] = [P].[ProductId]
AND [ProductVersion].[ProductVersionId] = (
SELECT
MAX([ProductVersionId])
FROM [ProductVersion] [prv]
WHERE [prv].[fkProductId] = [P].[ProductId]
AND fkProductVersionFormatId = @PhysicalProductFormatId
)
WHERE P.ProductId = @BoxId and BL.fkLanguageId = @LanguageId
ORDER BY P.ProductId,PR.PartnerId, CP.ProductId
UPDATE #BoxExtract2
SET
ExperienceWeb = CASE WHEN EXISTS(SELECT 1 FROM RelatedProductPackagingSection RPPS WITH (NOLOCK) WHERE RPPS.fkRelatedProductId = #BoxExtract2.TempRelatedProductId AND RPPS.fkPackagingSectionId = 1) THEN 'Yes' ELSE 'No' END
,ExperienceSpecimen = CASE WHEN EXISTS(SELECT 1 FROM RelatedProductPackagingSection RPPS WITH (NOLOCK) WHERE RPPS.fkRelatedProductId = #BoxExtract2.TempRelatedProductId AND RPPS.fkPackagingSectionId = 3) THEN 'Yes' ELSE 'No' END
,ExperienceBooklet=CASE WHEN EXISTS(SELECT 1 FROM RelatedProductPackagingSection RPPS WITH (NOLOCK) WHERE RPPS.fkRelatedProductId = #BoxExtract2.TempRelatedProductId AND RPPS.fkPackagingSectionId = 4) THEN 'Yes' ELSE 'No' END
,AddressLine1 = CASE WHEN #BoxExtract2.[TempFVLocValue]=1 THEN NULL ELSE #BoxExtract2.TempAddressLine1 END
,Email = CASE WHEN #BoxExtract2.TempFVValue = 1 THEN NULL ELSE #BoxExtract2.TempEmail END
,PhoneNo = CASE WHEN #BoxExtract2.TempFVValue = 1 THEN NULL ELSE CASE WHEN left(COALESCE(#BoxExtract2.TempPhoneNo,''),1) <> '0' and #BoxExtract2.TempPhoneNo IS NOT NULL THEN '0'+ #BoxExtract2.TempPhoneNo ELSE #BoxExtract2.TempPhoneNo END END
,CanBeBooked = CASE WHEN EXISTS( SELECT 1 FROM Product P JOIN RelatedProduct RP ON P.ProductId = RP.fkProduct1Id WHERE RP.fkRelationTypeId = 40 AND RP.fkProductId = #BoxExtract2.ExperienceId and P.isReservable = 1)THEN 1 ELSE 0 END
create nonclustered index idx_tempindex on #BoxExtract2 (PartnerCode, ExperienceCount)
select a.*,COALESCE(PTA.ProductContents,'') ExperienceA_Contents,COALESCE(PTB.ProductContents,'') ExperienceB_Contents,COALESCE(PTC.ProductContents,'') ExperienceC_Contents,COALESCE(PTT.PartnerContents,'') Partner_Contents from (
Select distinct
--[IndexPageNumber],
--[BoxFormat],
--IndexLayout,
--PartnerPagesLayout,
--[BrandPartnership],
ExperienceUniverse = COALESCE((select top 1 B2.UniverseName from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'') ,
Web = COALESCE((select top 1 B2.ExperienceWeb from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) and B2.ExperienceWeb = 'Yes'),'No') ,
Specimen = COALESCE((select top 1 B2.ExperienceSpecimen from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) and B2.ExperienceSpecimen = 'Yes'),'No') ,
Booklet = COALESCE((select top 1 B2.ExperienceBooklet from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) and B2.ExperienceBooklet = 'Yes'),'No') ,
PartnerId, PartnerName,PartnerCode,replace(COALESCE(AddressLine1,''),',','') as AddressLine1,COALESCE(AddressLine2,'') as AddressLine2,COALESCE(PostCode,'') as PostCode,COALESCE(City,'') as City,
COALESCE(PartnerRegion,'') as RegionName,
COALESCE(ParterSubRegion,'') AS SubRegionName /as [Parnter Subregion Name]/,
SubRegionNumber,
COALESCE(Country,'') as Country,
PhoneNo as 'PhoneNumber',
COALESCE(Email,'') as Email,COALESCE(Website,'') as Website,Longitude,Latitude,
CanBeBooked = cast(COALESCE((select top 1 B2.CanBeBooked from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),0) as int) ,
MaxNumberOfPeople = COALESCE((select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) order by B2.MaxNumberOfPeople),'') ,
MinNumberOfPeople = COALESCE((select top 1 B2.MinNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) order by B2.MinNumberOfPeople),'') ,
ExperienceA_Id = COALESCE((select top 1 B2.ExperienceId from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'') ,
ExperienceA_Code = COALESCE((select top 1 B2.ProductCode from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'') ,
ExperienceA_Template = COALESCE((select top 1 B2.ProductTemplateId from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'') ,
ExperienceB_Id = COALESCE((select top 1 B2.ExperienceId from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'') ,
ExperienceB_Code = COALESCE((select top 1 B2.ProductCode from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'') ,
ExperienceB_Template = COALESCE((select top 1 B2.ProductTemplateId from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'') ,
ExperienceC_Id = COALESCE((select top 1 B2.ExperienceId from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'') ,
ExperienceC_Code = COALESCE((select top 1 B2.ProductCode from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'') ,
ExperienceC_Template = COALESCE((select top 1 B2.ProductTemplateId from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'') ,
ExperienceA_Entitles = dbo.udf_LocaleData(case when (select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1) is not null then 'Pour ' + COALESCE((select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'0') + ' personne' + case when COALESCE((select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'1') = '1' then '.' else 's.' end else '' end,SBLanguageCode),
ExperienceB_Entitles = dbo.udf_LocaleData(case when (select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2) is not null then 'Pour ' + COALESCE((select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'0') + ' personne' + case when COALESCE((select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'1') = '1' then '.' else 's.' end else '' end,SBLanguageCode),
ExperienceC_Entitles = dbo.udf_LocaleData(case when (select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3) is not null then 'Pour ' + COALESCE((select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'0') + ' personne' + case when COALESCE((select top 1 B2.MaxNumberOfPeople from #BoxExtract2 B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'1') = '1' then '.' else 's.' end else '' end,SBLanguageCode),
COALESCE(PartnerTemplateId,'') as PartnerTemplateId,
COALESCE(MarketingRegionClassification,'') as [Marketing Region Classification],
[Michelin Rating],[TripAdvisor Rating],[TripAdvisor Certificate of Excellence],[TripAdvisor Travellers Choice],[Logis de France],[Gault et Millaud],
[Bib gourmand],[Plate],[Hotel di charme]
From #BoxExtract2 BOX
) a
left join ProductTemplate PTA on PTA.ProductTemplateId = a.ExperienceA_Template
left join ProductTemplate PTB on PTB.ProductTemplateId = a.ExperienceB_Template
left join ProductTemplate PTC on PTC.ProductTemplateId = a.ExperienceC_Template
left join PartnerTemplate PTT on PTT.PartnerTemplateId = a.PartnerTemplateId
where (case when @PackagingSectionId = 3 and a.Specimen = 'Yes' then 1
when @PackagingSectionId = 4 and a.Booklet = 'Yes' then 1
when @PackagingSectionId = 1 and a.Web = 'Yes' then 1
else 0 end) = 1
order by PartnerCode
can you provide table structure and indexes for the tables I identified ?
Hi Mike,
Please find below table structure along with index and keys
/****** Object: Table [dbo].[RelatedProductPackagingSection] Script Date: 29/01/2020 21:02:00 ******/
CREATE TABLE [dbo].[RelatedProductPackagingSection](
[RelatedProductPackagingSectionId] [int] IDENTITY(1,1) NOT NULL,
[fkPackagingSectionId] [int] NOT NULL,
[fkRelatedProductId] [int] NOT NULL,
[Timestamp] [timestamp] NOT NULL,
PRIMARY KEY CLUSTERED
(
[RelatedProductPackagingSectionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
CONSTRAINT [AK_RelatedProductPackagingSection] UNIQUE NONCLUSTERED
(
[fkPackagingSectionId] ASC,
[fkRelatedProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RelatedProductPackagingSection] WITH NOCHECK ADD CONSTRAINT [FK_RelatedProductPackagingSection_PackagingSection] FOREIGN KEY([fkPackagingSectionId])
REFERENCES [dbo].[PackagingSection] ([PackagingSectionId])
GO
ALTER TABLE [dbo].[RelatedProductPackagingSection] CHECK CONSTRAINT [FK_RelatedProductPackagingSection_PackagingSection]
GO
ALTER TABLE [dbo].[RelatedProductPackagingSection] WITH NOCHECK ADD CONSTRAINT [FK_RelatedProductPackagingSection_RelatedProduct] FOREIGN KEY([fkRelatedProductId])
REFERENCES [dbo].[RelatedProduct] ([RelatedProductId])
GO
ALTER TABLE [dbo].[RelatedProductPackagingSection] CHECK CONSTRAINT [FK_RelatedProductPackagingSection_RelatedProduct]
GO
CREATE NONCLUSTERED INDEX [_dta_index_RelatedProductPackagingSection_14_1894297808__K3_K1_2] ON [dbo].[RelatedProductPackagingSection]
(
[fkRelatedProductId] ASC,
[RelatedProductPackagingSectionId] ASC
)
INCLUDE([fkPackagingSectionId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RelatedProductPackagingSection] ADD CONSTRAINT [AK_RelatedProductPackagingSection] UNIQUE NONCLUSTERED
(
[fkPackagingSectionId] ASC,
[fkRelatedProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [RelatedProductPackagingSection_fkRelatedProductId] ON [dbo].[RelatedProductPackagingSection]
(
[fkRelatedProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
USE [PIM]
GO
/****** Object: Table [dbo].[ProductFacetValue] Script Date: 29/01/2020 21:02:00 ******/
CREATE TABLE [dbo].[ProductFacetValue](
[ProductFacetValueId] [int] IDENTITY(1,1) NOT NULL,
[fkFacetValueId] [int] NOT NULL,
[fkProductId] [int] NOT NULL,
[fkFacetId] [int] NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[UpdatedBy] [int] NULL,
[UpdatedOn] [datetime] NULL,
[Version] [int] NOT NULL,
[Timestamp] [timestamp] NOT NULL,
[fkFilterBoostValue] [int] NULL,
CONSTRAINT [PK_ProductFacetValue] PRIMARY KEY CLUSTERED
(
[ProductFacetValueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [Unique_Product_Facet_FacetValue] UNIQUE NONCLUSTERED
(
[fkProductId] ASC,
[fkFacetId] ASC,
[fkFacetValueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProductFacetValue] ADD DEFAULT ((1)) FOR [CreatedBy]
GO
ALTER TABLE [dbo].[ProductFacetValue] ADD DEFAULT (getdate()) FOR [CreatedOn]
GO
ALTER TABLE [dbo].[ProductFacetValue] ADD DEFAULT ((1)) FOR [Version]
GO
ALTER TABLE [dbo].[ProductFacetValue] WITH CHECK ADD FOREIGN KEY([fkFilterBoostValue])
REFERENCES [dbo].[FilterBoost] ([Value])
GO
ALTER TABLE [dbo].[ProductFacetValue] WITH CHECK ADD CONSTRAINT [FK_ProductFacetValue_Facet] FOREIGN KEY([fkFacetId])
REFERENCES [dbo].[Facet] ([FacetId])
GO
ALTER TABLE [dbo].[ProductFacetValue] CHECK CONSTRAINT [FK_ProductFacetValue_Facet]
GO
ALTER TABLE [dbo].[ProductFacetValue] WITH CHECK ADD CONSTRAINT [FK_ProductFacetValue_FacetValue] FOREIGN KEY([fkFacetValueId])
REFERENCES [dbo].[FacetValue] ([FacetValueId])
GO
ALTER TABLE [dbo].[ProductFacetValue] CHECK CONSTRAINT [FK_ProductFacetValue_FacetValue]
GO
ALTER TABLE [dbo].[ProductFacetValue] WITH CHECK ADD CONSTRAINT [FK_ProductFacetValue_Product] FOREIGN KEY([fkProductId])
REFERENCES [dbo].[Product] ([ProductId])
GO
ALTER TABLE [dbo].[ProductFacetValue] CHECK CONSTRAINT [FK_ProductFacetValue_Product]
GO
EXEC sys.sp_addextendedproperty @name=N'FD', @value=N'System generated Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProductFacetValue', @level2type=N'COLUMN',@level2name=N'ProductFacetValueId'
GO
EXEC sys.sp_addextendedproperty @name=N'FD', @value=N'Facet Value Id (foreign key)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProductFacetValue', @level2type=N'COLUMN',@level2name=N'fkFacetValueId'
GO
EXEC sys.sp_addextendedproperty @name=N'FD', @value=N'Product Id (foreign key)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProductFacetValue', @level2type=N'COLUMN',@level2name=N'fkProductId'
GO
EXEC sys.sp_addextendedproperty @name=N'FD', @value=N'Facet Id (foreign key)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProductFacetValue', @level2type=N'COLUMN',@level2name=N'fkFacetId'
GO
EXEC sys.sp_addextendedproperty @name=N'FD', @value=N'For audit purpose only, who created the record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProductFacetValue', @level2type=N'COLUMN',@level2name=N'CreatedBy'
GO
EXEC sys.sp_addextendedproperty @name=N'FD', @value=N'For audit purpose only, when the record was created' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProductFacetValue', @level2type=N'COLUMN',@level2name=N'CreatedOn'
GO
EXEC sys.sp_addextendedproperty @name=N'TD', @value=N'Bridge table of product and facet value, this table contains all possible product dimensions / facets' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProductFacetValue'
GO
CREATE NONCLUSTERED INDEX [_dta_index_ProductFacetValue_6_821577965__K3_1_2_4_5_6] ON [dbo].[ProductFacetValue]
(
[fkProductId] ASC
)
INCLUDE([CreatedBy],[CreatedOn],[fkFacetId],[fkFacetValueId],[ProductFacetValueId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idx_ProductFacetValue_fkFacetValueId] ON [dbo].[ProductFacetValue]
(
[fkFacetValueId] ASC
)
INCLUDE([fkProductId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProductFacetValue] ADD CONSTRAINT [PK_ProductFacetValue] PRIMARY KEY CLUSTERED
(
[ProductFacetValueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ProductFacetValue] ADD CONSTRAINT [Unique_Product_Facet_FacetValue] UNIQUE NONCLUSTERED
(
[fkProductId] ASC,
[fkFacetId] ASC,
[fkFacetValueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER INDEX [Unique_Product_Facet_FacetValue] ON [dbo].[ProductFacetValue] DISABLE
GO
USE [PIM]
GO
/****** Object: Table [dbo].[PartnerFacetValue] Script Date: 29/01/2020 21:03:31 ******/
CREATE TABLE [dbo].[PartnerFacetValue](
[PartnerFacetValueId] [int] IDENTITY(1,1) NOT NULL,
[fkFacetValueId] [int] NOT NULL,
[fkPartnerId] [int] NOT NULL,
[fkFacetId] [int] NULL,
[CreatedBy] [int] NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[UpdatedBy] [int] NULL,
[UpdatedOn] [datetime] NULL,
[Version] [int] NOT NULL,
[Timestamp] [timestamp] NOT NULL,
CONSTRAINT [PK_PartnerFacetValue] PRIMARY KEY CLUSTERED
(
[PartnerFacetValueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PartnerFacetValue] ADD DEFAULT ((1)) FOR [CreatedBy]
GO
ALTER TABLE [dbo].[PartnerFacetValue] ADD DEFAULT (getdate()) FOR [CreatedOn]
GO
ALTER TABLE [dbo].[PartnerFacetValue] ADD DEFAULT ((1)) FOR [Version]
GO
ALTER TABLE [dbo].[PartnerFacetValue] WITH CHECK ADD CONSTRAINT [FK_PartnerFacetValue_Facet] FOREIGN KEY([fkFacetId])
REFERENCES [dbo].[Facet] ([FacetId])
GO
ALTER TABLE [dbo].[PartnerFacetValue] CHECK CONSTRAINT [FK_PartnerFacetValue_Facet]
GO
ALTER TABLE [dbo].[PartnerFacetValue] WITH CHECK ADD CONSTRAINT [FK_PartnerFacetValue_FacetValue] FOREIGN KEY([fkFacetValueId])
REFERENCES [dbo].[FacetValue] ([FacetValueId])
GO
ALTER TABLE [dbo].[PartnerFacetValue] CHECK CONSTRAINT [FK_PartnerFacetValue_FacetValue]
GO
ALTER TABLE [dbo].[PartnerFacetValue] WITH CHECK ADD CONSTRAINT [FK_PartnerFacetValue_Partner] FOREIGN KEY([fkPartnerId])
REFERENCES [dbo].[Partner] ([PartnerId])
GO
ALTER TABLE [dbo].[PartnerFacetValue] CHECK CONSTRAINT [FK_PartnerFacetValue_Partner]
GO
/****** Object: Index [idx_PartnerFacetValue_fkFacetValueId] Script Date: 29/01/2020 21:03:50 ******/
CREATE NONCLUSTERED INDEX [idx_PartnerFacetValue_fkFacetValueId] ON [dbo].[PartnerFacetValue]
(
[fkFacetValueId] ASC
)
INCLUDE([fkPartnerId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [PartnerFacetValue_fkPartnerId_fkFacetId] ON [dbo].[PartnerFacetValue]
(
[fkPartnerId] ASC,
[fkFacetId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PartnerFacetValue] ADD CONSTRAINT [PK_PartnerFacetValue] PRIMARY KEY CLUSTERED
(
[PartnerFacetValueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
This table ddl is still missing. facetvalue
Hi Mike,
Find below DDL...
CREATE TABLE [dbo].[FacetValue](
[FacetValueId] [int] IDENTITY(1,1) NOT NULL,
[fkFacetId] [int] NOT NULL,
[Value] varchar NOT NULL,
[CreatedBy] [int] NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[UpdatedBy] [int] NOT NULL,
[UpdatedOn] [datetime] NULL,
[Sequence] [int] NULL,
[Timestamp] [timestamp] NOT NULL,
CONSTRAINT [PK_FacetValue] PRIMARY KEY CLUSTERED
(
[FacetValueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[FacetValue] WITH CHECK ADD CONSTRAINT [FK_FacetValue_Facet] FOREIGN KEY([fkFacetId])
REFERENCES [dbo].[Facet] ([FacetId])
GO
ALTER TABLE [dbo].[FacetValue] CHECK CONSTRAINT [FK_FacetValue_Facet]
GO
I'm thinking of something like below. Instead of the inline selects for each row, up can get the max values (You are selecting top 1 and no order by, so I assume it doesn't matter which one you bring back) and then use those
(select PFV.fkpartnerid
,max(Case when FV.fkfacetid = 61 then FV.value else 0 end) as [Michelin Rating]
,max(Case when FV.fkfacetid = 35 then FV.value else 0 end) as [TripAdvisor Rating]
,max(Case when FV.fkfacetid = 14 then FV.value else 0 end) as [Gault et Millaud]
,max(Case when FV.fkfacetid = 23 then FV.value else 0 end) as [Logis de France]
,max(Case when FV.fkfacetid = 36 then FV.value else 0 end) as [TripAdvisor Travellers Choice]
,max(Case when FV.fkfacetid = 34 then FV.value else 0 end) as [TripAdvisor Certificate of Excellence]
,max(Case when FV.fkfacetid = 35 then FV.value else 0 end) as [TripAdvisor Rating]
,max(Case when FV.fkfacetid = 62 then FV.value else 0 end) as [Bib gourmand]
,max(Case when FV.fkfacetid = 63 then FV.value else 0 end) as [Plate]
,max(Case when FV.fkfacetid = 18 then FV.value else 0 end) as[Hotel di charme]
FROM partnerfacetvalue PFV
INNER JOIN facetvalue FV
ON FV.facetvalueid = PFV.fkfacetvalueid
WHERE FV.fkfacetid in (61,35,14,23,36,34,35,62,63,18)
group by PFV.fkpartnerid) ptnr
on ptnr.fkpartnerid = PR.partnerid(select PFV.fkproductid
,max(Case when FV.fkfacetid = 44 then FV.value else 0 end) as [MinNumberOfPeople]
,max(Case when FV.fkfacetid = 45 then FV.value else 0 end) as [MaxNumberOfPeople]
FROM partnerfacetvalue PFV
INNER JOIN facetvalue FV
ON FV.facetvalueid = PFV.fkfacetvalueid
WHERE FV.fkfacetid in (44,45)
group by PFV.fkproductid) prod
on prod.fkproductid = pr.fkproductid
Hi Mike,
Not getting the idea how to include this statement in the current select statement
it's a derived table, so you can join to it like a real table, then pick the columns in the select above
i.e.
select [Michelin Rating], [TripAdvisor Rating], [Gault et Millaud],[Logis de France], [TripAdvisor Travellers Choice], etc..
from .....
Tried below SQL and it gives me error like incorrect statement
(select PFV.fkproductid
,max(Case when FV.fkfacetid = 44 then FV.value else 0 end) as [MinNumberOfPeople]
,max(Case when FV.fkfacetid = 45 then FV.value else 0 end) as [MaxNumberOfPeople]
FROM partnerfacetvalue PFV
INNER JOIN facetvalue FV
ON FV.facetvalueid = PFV.fkfacetvalueid
WHERE FV.fkfacetid in (44,45)
group by PFV.fkproductid) prod
on prod.fkproductid = pr.fkproductid
Here's a sample of how to join to Partner table. I used pr.* to show all columns from Partner, but you would fill it in with the columns you need. Then add in other tables (derived or real) as you need them.
select pr.*, prod.[MinNumberOfPeople], prod.[MaxNumberOfPeople]
from
(select PFV.fkproductid
,max(Case when FV.fkfacetid = 44 then FV.value else 0 end) as [MinNumberOfPeople]
,max(Case when FV.fkfacetid = 45 then FV.value else 0 end) as [MaxNumberOfPeople]
FROM partnerfacetvalue PFV
INNER JOIN facetvalue FV
ON FV.facetvalueid = PFV.fkfacetvalueid
WHERE FV.fkfacetid in (44,45)
group by PFV.fkproductid) prod
join Partner PR
on prod.fkproductid = pr.fkproductidHi Mike,
Guess i am not able to get it properly as my DB is not that great 
Just wanted to check how i can merge your query in below query?
Select P.ShortName,[ProductVersion].[VersionNumber] as BoxVersion ,BXL.LanguageName as BoxLanguage,BXL.SBLanguageCode,BU.UniverseName as BoxUniverse,P.ProductName as BoxTitle,
B.BrandName as BoxBrand,C.CountryName as BoxCountry,
PR.PartnerId, PR.PartnerName,PR.PartnerCode,
CASE WHEN fvloc.value=1 THEN null ELSE PL.AddressLine1 END as AddressLine1,
PL.AddressLine2,PL.PostCode,PL.City,
PL.SubRegion as ParterSubRegion,
PartnerRegion = case when isnull(C.CountryName,'') <> PL.Country then isnull(CL.CountryName,PL.Country) else PL.Region end,
PL.Country,
CASE WHEN fv.value=1 THEN null
ELSE CASE WHEN left(isnull(PL.PhoneNo,''),1) <> '0' and PL.PhoneNo is not null THEN '0'+ PL.PhoneNo
ELSE PL.PhoneNo
END
END as PhoneNo,
CASE WHEN fv.value = 1 THEN null ELSE PL.Email END as Email,
PL.Website,PL.Longitude,PL.Latitude,
CanBeBooked = isnull((SELECT top 1 P.isReservable FROM Product P
JOIN RelatedProduct RP ON P.ProductId = RP.fkProduct1Id
WHERE RP.fkRelationTypeId = 40 AND RP.fkProductId = CP.ProductId
and P.isReservable = 1 ),0),
PL.MarketingRegion as LocationName,
EU.UniverseName, CP.ProductId AS ExperienceId, CP.ProductCode,
ExperienceCount = DENSE_RANK() OVER (PARTITION BY P.ProductId,PR.PartnerId ORDER BY ExperienceRank desc,CP.Productid),
EPT.ProductTemplateId,
PPT.PartnerTemplateId,
MarketingRegionClassification = case when isnull(C.CountryName,'') <> PL.Country then isnull(CL.CountryName,PL.Country) else MRU.MarketingRegion END,
ExperienceWeb = case when exists(select RelatedProductPackagingSectionId from RelatedProductPackagingSection RPPS where RPPS.fkRelatedProductId = RP.RelatedProductId and RPPS.fkPackagingSectionId = 1) then 'Yes' else 'No' end,
ExperienceSpecimen = case when exists(select RelatedProductPackagingSectionId from RelatedProductPackagingSection RPPS where RPPS.fkRelatedProductId = RP.RelatedProductId and RPPS.fkPackagingSectionId = 3) then 'Yes' else 'No' end,
ExperienceBooklet = case when exists(select RelatedProductPackagingSectionId from RelatedProductPackagingSection RPPS where RPPS.fkRelatedProductId = RP.RelatedProductId and RPPS.fkPackagingSectionId = 4) then 'Yes' else 'No' end,
SubRegionNumber = substring(isnull(PL.SubRegionISOCode,''), 4, 10) ,
BoxCode = @BoxCode,
BoxCodeS = @BoxCodeS,
BoxVersionS = @BoxVersionS,
MasterBoxCode = @MasterBoxCode,
MinNumberOfPeople = (select top 1 FV.Value from ProductFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 45 and PFV.fkProductId = CP.ProductId),
MaxNumberOfPeople = (select top 1 FV.Value from ProductFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 44 and PFV.fkProductId = CP.ProductId),
[Michelin Rating] = isnull((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 61 and PFV.fkPartnerId = PR.PartnerId),''),
[TripAdvisor Rating] = isnull((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 35 and PFV.fkPartnerId = PR.PartnerId),''),
[TripAdvisor Certificate of Excellence] = isnull((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 34 and PFV.fkPartnerId = PR.PartnerId),''),
[TripAdvisor Travellers Choice] = isnull((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 36 and PFV.fkPartnerId = PR.PartnerId),''),
[Logis de France] = isnull((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 23 and PFV.fkPartnerId = PR.PartnerId),''),
[Gault et Millaud] = isnull((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 14 and PFV.fkPartnerId = PR.PartnerId),''),
[Bib gourmand] = isnull((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 62 and PFV.fkPartnerId = PR.PartnerId),''),
[Plate] = isnull((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 63 and PFV.fkPartnerId = PR.PartnerId),''),
[Hotel di charme] = isnull((select top 1 FV.Value from PartnerFacetValue PFV
Inner Join FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
where FV.fkFacetId = 18 and PFV.fkPartnerId = PR.PartnerId),''),
[IndexPageNumber] = (
SELECT TOP 1 NumberOfIndexPages FROM [EditionBrief]
INNER JOIN [EditionBriefRegionalIndexPages] ON [EditionBriefRegionalIndexPages].[fkEditionBriefId] = [EditionBrief].[EditionBriefId]
WHERE [fkBoxId] = [P].[ProductId] AND fkMarketingRegionsId =
CASE
WHEN cntry.CountryId <> @BoxCountryId THEN mr2.MarketingRegionId
WHEN (@BoxCountryId = @ItalyCountryId AND BU.UniverseId IN (@WellnessUniverseId, @GastronomyUniverseId)) THEN
CASE PL.City
WHEN 'Roma' THEN @RomaMarketingRegionId
WHEN 'Milano' THEN @MilanoMarketingRegionId
ELSE mr1.MarketingRegionId
END
ELSE mr1.MarketingRegionId
END
AND EditionBrief.fkSpecimenId = @BoxCodeS
),
[BoxFormat] = STUFF((SELECT ',' + [Format].[Name] FROM [AvailableProductFormat] INNER JOIN [Format] ON [Format].[FormatId] = [AvailableProductFormat].[fkFormatId] WHERE [fkProductId]=[P].[ProductId] ORDER BY [PriorityForConcept] ASC FOR XML PATH('')),1,1,''),
[IndexLayout] = (SELECT TOP 1 edb.IndexLayout FROM EditionBrief as edb WHERE fkBoxId = @BoxId AND fkSpecimenId = @BoxCodeS),
[PartnerPagesLayout] = (SELECT TOP 1 edb.PartnerPagesLayout FROM EditionBrief as edb WHERE fkBoxId = @BoxId),
[BrandPartnership] = (SELECT ISNULL([BrandPartnership].[Name],'none') FROM [Product] LEFT JOIN [BrandPartnership] ON [BrandPartnership].[BrandPartnershipId] = [Product].[fkBrandPartnershipId] WHERE [Product].[ProductId] = [P].[ProductId])
Into #BoxExtract2
From Product P
Inner Join RelatedProduct RP with (nolock) on RP.fkProductId = P.ProductId and RP.Active = 1
Inner Join PartnerProduct PP with (nolock) on PP.fkProductId = RP.fkProduct1Id and PP.Active = 1
Inner Join Partner PR with (nolock) on PR.PartnerId = PP.fkPartnerId and PR.PartnerStatus = 1
Inner Join PartnerLocation PL with (nolock) on PL.fkPartnerId = PR.PartnerIdI didn't create your whole schema, but I did modify your code to include what I was talking about (you may have to tweak it for syntax or fat-finger errors). I didn't do the Product because I didn't see anything aliased as CP.
SELECT P.shortname,
[productversion].[VersionNumber] AS BoxVersion,
bxl.languagename AS BoxLanguage,
bxl.sblanguagecode,
bu.universename AS BoxUniverse,
P.productname AS BoxTitle,
b.brandname AS BoxBrand,
c.countryname AS BoxCountry,
PR.partnerid,
PR.partnername,
PR.partnercode,
CASE
WHEN fvloc.value = 1 THEN NULL
ELSE PL.addressline1
END AS AddressLine1,
PL.addressline2,
PL.postcode,
PL.city,
PL.subregion AS ParterSubRegion,
PartnerRegion = CASE
WHEN Isnull(c.countryname, '') <> PL.country THEN Isnull(cl.countryname, PL.country)
ELSE PL.region
END,
PL.country,
CASE
WHEN fv.value = 1 THEN NULL
ELSE
CASE
WHEN LEFT(Isnull(PL.phoneno, ''), 1) <> '0'
AND PL.phoneno IS NOT NULL THEN '0' + PL.phoneno
ELSE PL.phoneno
END
END AS PhoneNo,
CASE
WHEN fv.value = 1 THEN NULL
ELSE PL.email
END AS Email,
PL.website,
PL.longitude,
PL.latitude,
CanBeBooked = Isnull((SELECT TOP 1 P.isreservable
FROM product P
JOIN relatedproduct RP
ON P.productid = RP.fkproduct1id
WHERE RP.fkrelationtypeid = 40
AND RP.fkproductid = cp.productid
AND P.isreservable = 1), 0),
PL.marketingregion AS LocationName,
eu.universename,
cp.productid AS ExperienceId,
cp.productcode,
ExperienceCount = Dense_rank()
OVER (
partition BY P.productid, PR.partnerid
ORDER BY experiencerank DESC, cp.productid),
ept.producttemplateid,
ppt.partnertemplateid,
MarketingRegionClassification = CASE
WHEN Isnull(c.countryname, '') <> PL.country THEN Isnull(cl.countryname, PL.country)
ELSE mru.marketingregion
END,
ExperienceWeb = CASE
WHEN EXISTS(SELECT relatedproductpackagingsectionid
FROM relatedproductpackagingsection RPPS
WHERE RPPS.fkrelatedproductid = RP.relatedproductid
AND RPPS.fkpackagingsectionid = 1) THEN 'Yes'
ELSE 'No'
END,
ExperienceSpecimen = CASE
WHEN EXISTS(SELECT relatedproductpackagingsectionid
FROM relatedproductpackagingsection RPPS
WHERE RPPS.fkrelatedproductid = RP.relatedproductid
AND RPPS.fkpackagingsectionid = 3) THEN 'Yes'
ELSE 'No'
END,
ExperienceBooklet = CASE
WHEN EXISTS(SELECT relatedproductpackagingsectionid
FROM relatedproductpackagingsection RPPS
WHERE RPPS.fkrelatedproductid = RP.relatedproductid
AND RPPS.fkpackagingsectionid = 4) THEN 'Yes'
ELSE 'No'
END,
SubRegionNumber = Substring(Isnull(PL.subregionisocode, ''), 4, 10),
BoxCode = @BoxCode,
BoxCodeS = @BoxCodeS,
BoxVersionS = @BoxVersionS,
MasterBoxCode = @MasterBoxCode,
MinNumberOfPeople = (SELECT TOP 1 FV.value
FROM productfacetvalue PFV
INNER JOIN facetvalue FV
ON FV.facetvalueid = PFV.fkfacetvalueid
WHERE FV.fkfacetid = 45
AND PFV.fkproductid = cp.productid),
MaxNumberOfPeople = (SELECT TOP 1 FV.value
FROM productfacetvalue PFV
INNER JOIN facetvalue FV
ON FV.facetvalueid = PFV.fkfacetvalueid
WHERE FV.fkfacetid = 44
AND PFV.fkproductid = cp.productid),
IsNull([Michelin Rating], '') as [Michelin Rating],
IsNull([TripAdvisor Rating], '') as [TripAdvisor Rating],
IsNull([TripAdvisor Certificate of Excellence], '') as [TripAdvisor Certificate of Excellence],
IsNull([TripAdvisor Travellers Choice], '') as [TripAdvisor Travellers Choice],
IsNull([Logis de France], '') as [Logis de France],
IsNull([Gault et Millaud], '') as [Gault et Millaud],
IsNull([Bib gourmand], '') as [Bib gourmand],
IsNull([Plate], '') as [Plate],
IsNull([Hotel di charme], '') as [Hotel di charme],
/*
[Michelin Rating] = Isnull((SELECT TOP 1 FV.value
FROM partnerfacetvalue PFV
INNER JOIN facetvalue FV
ON FV.facetvalueid = PFV.fkfacetvalueid
WHERE FV.fkfacetid = 61
AND PFV.fkpartnerid = PR.partnerid), ''),
[TripAdvisor Rating] = Isnull((SELECT TOP 1 FV.value
FROM partnerfacetvalue PFV
INNER JOIN facetvalue FV
ON FV.facetvalueid = PFV.fkfacetvalueid
WHERE FV.fkfacetid = 35
AND PFV.fkpartnerid = PR.partnerid), ''),
[TripAdvisor Certificate of Excellence] = Isnull((SELECT TOP 1 FV.value
FROM partnerfacetvalue PFV
INNER JOIN facetvalue FV
ON FV.facetvalueid = PFV.fkfacetvalueid
WHERE FV.fkfacetid = 34
AND PFV.fkpartnerid = PR.partnerid), ''),
[TripAdvisor Travellers Choice] = Isnull((SELECT TOP 1 FV.value
FROM partnerfacetvalue PFV
INNER JOIN facetvalue FV
ON FV.facetvalueid = PFV.fkfacetvalueid
WHERE FV.fkfacetid = 36
AND PFV.fkpartnerid = PR.partnerid), ''),
[Logis de France] = Isnull((SELECT TOP 1 FV.value
FROM partnerfacetvalue PFV
INNER JOIN facetvalue FV
ON FV.facetvalueid = PFV.fkfacetvalueid
WHERE FV.fkfacetid = 23
AND PFV.fkpartnerid = PR.partnerid), ''),
[Gault et Millaud] = Isnull((SELECT TOP 1 FV.value
FROM partnerfacetvalue PFV
INNER JOIN facetvalue FV
ON FV.facetvalueid = PFV.fkfacetvalueid
WHERE FV.fkfacetid = 14
AND PFV.fkpartnerid = PR.partnerid), ''),
[Bib gourmand] = Isnull((SELECT TOP 1 FV.value
FROM partnerfacetvalue PFV
INNER JOIN facetvalue FV
ON FV.facetvalueid = PFV.fkfacetvalueid
WHERE FV.fkfacetid = 62
AND PFV.fkpartnerid = PR.partnerid), ''),
[Plate] = Isnull((SELECT TOP 1 FV.value
FROM partnerfacetvalue PFV
INNER JOIN facetvalue FV
ON FV.facetvalueid = PFV.fkfacetvalueid
WHERE FV.fkfacetid = 63
AND PFV.fkpartnerid = PR.partnerid), ''),
[Hotel di charme] = Isnull((SELECT TOP 1 FV.value
FROM partnerfacetvalue PFV
INNER JOIN facetvalue FV
ON FV.facetvalueid = PFV.fkfacetvalueid
WHERE FV.fkfacetid = 18
AND PFV.fkpartnerid = PR.partnerid), ''),
*/
[IndexPageNumber] = (SELECT TOP 1 numberofindexpages
FROM [editionbrief]
INNER JOIN [editionbriefregionalindexpages]
ON [editionbriefregionalindexpages].[fkeditionbriefid] = [editionbrief].[editionbriefid]
WHERE [fkboxid] = [P].[productid]
AND fkmarketingregionsid = CASE
WHEN cntry.countryid <> @BoxCountryId THEN mr2.marketingregionid
WHEN ( @BoxCountryId = @ItalyCountryId
AND bu.universeid IN ( @WellnessUniverseId, @GastronomyUniverseId ) ) THEN
CASE PL.city
WHEN 'Roma' THEN @RomaMarketingRegionId
WHEN 'Milano' THEN @MilanoMarketingRegionId
ELSE mr1.marketingregionid
END
ELSE mr1.marketingregionid
END
AND editionbrief.fkspecimenid = @BoxCodeS),
[BoxFormat] = Stuff((SELECT ',' + [format].[name]
FROM [availableproductformat]
INNER JOIN [format]
ON [format].[formatid] = [availableproductformat].[fkformatid]
WHERE [fkproductid] = [P].[productid]
ORDER BY [priorityforconcept] ASC
FOR xml path('')), 1, 1, ''),
[IndexLayout] = (SELECT TOP 1 edb.indexlayout
FROM editionbrief AS edb
WHERE fkboxid = @BoxId
AND fkspecimenid = @BoxCodeS),
[PartnerPagesLayout] = (SELECT TOP 1 edb.partnerpageslayout
FROM editionbrief AS edb
WHERE fkboxid = @BoxId),
[BrandPartnership] = (SELECT Isnull([brandpartnership].[name], 'none')
FROM [product]
LEFT JOIN [brandpartnership]
ON [brandpartnership].[brandpartnershipid] = [product].[fkbrandpartnershipid]
WHERE [product].[productid] = [P].[productid])
INTO #boxextract2
FROM product P
INNER JOIN relatedproduct RP WITH (nolock)
ON RP.fkproductid = P.productid
AND RP.active = 1
INNER JOIN partnerproduct PP WITH (nolock)
ON PP.fkproductid = RP.fkproduct1id
AND PP.active = 1
INNER JOIN partner PR WITH (nolock)
ON PR.partnerid = PP.fkpartnerid
AND PR.partnerstatus = 1
INNER JOIN partnerlocation PL WITH (nolock)
ON PL.fkpartnerid = PR.partnerid
JOIN
(select PFV.fkpartnerid
,max(Case when FV.fkfacetid = 61 then FV.value else '' end) as [Michelin Rating]
,max(Case when FV.fkfacetid = 25 then FV.value else '' end) as [TripAdvisor Rating]
,max(Case when FV.fkfacetid = 34 then FV.value else '' end) as [TripAdvisor Certificate of Excellence]
,max(Case when FV.fkfacetid = 35 then FV.value else '' end) as [TripAdvisor Travellers Choice]
,max(Case when FV.fkfacetid = 23 then FV.value else '' end) as [Logis de France]
,max(Case when FV.fkfacetid = 14 then FV.value else '' end) as [Gault et Millaud]
,max(Case when FV.fkfacetid = 62 then FV.value else '' end) as [Bib gourmand]
,max(Case when FV.fkfacetid = 63 then FV.value else '' end) as [Plate]
,max(Case when FV.fkfacetid = 18 then FV.value else '' end) as [Hotel di charme]
FROM partnerfacetvalue PFV
INNER JOIN facetvalue FV
ON FV.facetvalueid = PFV.fkfacetvalueid
WHERE FV.fkfacetid in (61, 35, 34, 35, 23, 14, 62, 63, 18)
group by PFV.fkpartnerid) ptnr
on ptnr.fkpartnerid = PR.partneridThanks Mike,
I made the changes and it is working, however it did not improve the performance
Anyways, I appreciate your inputs and help in solving this query. Find below my final changes if you want to give it a final try else, i am happy to close this thread.
Thanks again!!!
DECLARE @BoxId int = 1217707
DECLARE @PackagingSectionId int = 1
DECLARE @LanguageId int = 149
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
declare @noLocation varchar(20) = 'Hide phone and email' --'noLocation'
declare @hiddenContact varchar(20) = 'Hide map localisation' --'hiddenContact'
declare @PhysicalProductFormatId int = 1
declare @BoxUniverse int
declare @BoxCode int, @BoxCodeS int, @BoxVersionS int
declare @BoxCountryId int = (SELECT DISTINCT fkCountryId FROM [dbo].ProductCountry WHERE fkProductId = @BoxId)
declare @MasterBoxCode int
declare @ItalyCountryId INT = 109
declare @RomaMarketingRegionId INT = (SELECT MarketingRegionId FROM MarketingRegion WHERE MarketingRegionName = 'Roma')
declare @MilanoMarketingRegionId INT = (SELECT MarketingRegionId FROM MarketingRegion WHERE MarketingRegionName = 'Milano')
declare @WellnessUniverseId INT = 8
declare @GastronomyUniverseId INT = 2
select @MasterBoxCode = dbo.udf_GetMasterProductByProductId(@BoxId)
select @BoxCode = dbo.udf_GetPhysicalProductIdForLanguage(@BoxId, @LanguageId),
@BoxCodeS = dbo.udf_GetLatestSpecimenVersionId(@BoxId),
@BoxVersionS = dbo.udf_GetLatestSpecimenVersion(@BoxId)
select @BoxUniverse = fkUniverseId from Product inner join ProductUniverse on Product.ProductId = ProductUniverse.fkProductId
where Product.ProductId = @BoxId
Select P.ShortName,[ProductVersion].[VersionNumber] as BoxVersion ,BXL.LanguageName as BoxLanguage,BXL.SBLanguageCode,BU.UniverseName as BoxUniverse,P.ProductName as BoxTitle,B.BrandName as BoxBrand,C.CountryName as BoxCountry,
PR.PartnerId, PR.PartnerName,PR.PartnerCode, PDPT.fkUniverseId as PartnerTemplateUniverseId,
CASE WHEN fvloc.value=1 THEN null ELSE PL.AddressLine1 END as AddressLine1,
PL.AddressLine2,PL.PostCode,PL.City,
PL.SubRegion as ParterSubRegion,
PartnerRegion = case when isnull(C.CountryName,'') <> PL.Country then isnull(CL.CountryName,PL.Country) else PL.Region end,
PL.Country,
CASE WHEN fv.value=1 THEN null
ELSE CASE WHEN left(isnull(PL.PhoneNo,''),1) <> '0' and PL.PhoneNo is not null THEN '0'+ PL.PhoneNo
ELSE PL.PhoneNo
END
END as PhoneNo,
CASE WHEN fv.value = 1 THEN null
ELSE PL.Email
END as Email,
PL.Website,PL.Longitude,PL.Latitude,
CanBeBooked = isnull((SELECT top 1 P.isReservable FROM Product P
JOIN RelatedProduct RP ON P.ProductId = RP.fkProduct1Id
WHERE RP.fkRelationTypeId = 40 AND RP.fkProductId = CP.ProductId
and P.isReservable = 1 ),0),
PL.MarketingRegion as LocationName,
EU.UniverseId, EU.UniverseName, CP.ProductId as ExperienceId, CP.ProductCode,
ExperienceCount = DENSE_RANK() OVER (PARTITION BY P.ProductId,PR.PartnerId ORDER BY ExperienceRank desc,CP.Productid),
EPT.ProductTemplateId ,
PPT.PartnerTemplateId ,
MarketingRegionClassification = case when isnull(C.CountryName,'') <> PL.Country then isnull(CL.CountryName,PL.Country) else MRU.MarketingRegion END,
ExperienceWeb = case when exists(select RelatedProductPackagingSectionId from RelatedProductPackagingSection RPPS where RPPS.fkRelatedProductId = RP.RelatedProductId and RPPS.fkPackagingSectionId = 1) then 'Yes' else 'No' end,
ExperienceSpecimen = case when exists(select RelatedProductPackagingSectionId from RelatedProductPackagingSection RPPS where RPPS.fkRelatedProductId = RP.RelatedProductId and RPPS.fkPackagingSectionId = 3) then 'Yes' else 'No' end,
ExperienceBooklet = case when exists(select RelatedProductPackagingSectionId from RelatedProductPackagingSection RPPS where RPPS.fkRelatedProductId = RP.RelatedProductId and RPPS.fkPackagingSectionId = 4) then 'Yes' else 'No' end,
SubRegionNumber = substring(isnull(PL.SubRegionISOCode,''), 4, 10) ,
BoxCode = @BoxCode,
BoxCodeS = @BoxCodeS,
BoxVersionS = @BoxVersionS,
MasterBoxCode = @MasterBoxCode,
IsNull([MinNumberOfPeople], '') as [MinNumberOfPeople],
IsNull([MaxNumberOfPeople], '') as [MaxNumberOfPeople],
IsNull([Michelin Rating], '') as [Michelin Rating],
IsNull([TripAdvisor Rating], '') as [TripAdvisor Rating],
IsNull([TripAdvisor Certificate of Excellence], '') as [TripAdvisor Certificate of Excellence],
IsNull([TripAdvisor Travellers Choice], '') as [TripAdvisor Travellers Choice],
IsNull([Logis de France], '') as [Logis de France],
IsNull([Gault et Millaud], '') as [Gault et Millaud],
IsNull([Bib gourmand], '') as [Bib gourmand],
IsNull([Plate], '') as [Plate],
IsNull([Hotel di charme], '') as [Hotel di charme],
[Trip Advisor Review Count] = CAST(CAST(PPT.PartnerContents AS XML).query('/Contents/Content[@Name=''Trip Advisor Review Count'' and @SectionId=''1'']/text()[1]') as nvarchar(max)), -- OLD QUERTY
[IndexPageNumber] = CONVERT(INT, NULL),
[BoxFormat] = STUFF((SELECT ',' + [Format].[Name] FROM [AvailableProductFormat] INNER JOIN [Format] ON [Format].[FormatId] = [AvailableProductFormat].[fkFormatId] WHERE [fkProductId]=[P].[ProductId] ORDER BY [PriorityForConcept] ASC FOR XML PATH('')),1,1,''),
[IndexLayout] = (SELECT TOP 1 edb.IndexLayout FROM EditionBrief as edb WHERE fkBoxId = @BoxId AND fkSpecimenId = @BoxCodeS),
[PartnerPagesLayout] = (SELECT TOP 1 edb.PartnerPagesLayout FROM EditionBrief as edb WHERE fkBoxId = @BoxId),
[BrandPartnership] = (SELECT ISNULL([BrandPartnership].[Name],'none') FROM [Product] LEFT JOIN [BrandPartnership] ON [BrandPartnership].[BrandPartnershipId] = [Product].[fkBrandPartnershipId] WHERE [Product].[ProductId] = [P].[ProductId])
Into #BoxExtract
From Product P
Inner Join RelatedProduct RP with (nolock) on RP.fkProductId = P.ProductId and RP.Active = 1
Inner Join PartnerProduct PP with (nolock) on PP.fkProductId = RP.fkProduct1Id and PP.Active = 1
Inner Join Partner PR with (nolock) on PR.PartnerId = PP.fkPartnerId and PR.PartnerStatus = 1
Inner Join PartnerLocation PL with (nolock) on PL.fkPartnerId = PR.PartnerId
Inner Join ProductUniverse PU with (nolock) on PU.fkProductId = P.ProductId
Left Join MarketingRegionUniverse MRU with (nolock) on MRU.UniverseId = P.fkUniverseBoxId and MRU.SubregionISOCode = PL.SubRegionISOCode
left join ProductBrand PB with (nolock) on PB.fkProductId = P.ProductId
left join Brand B with (nolock) on PB.fkBrandId = B.BrandId
Left Join Universe BU with (nolock) on BU.UniverseId = P.fkUniverseBoxId
Inner Join Product CP with (nolock) on CP.ProductId = RP.fkProduct1Id and CP.fkTriggerStateId = 4
Inner Join ProductUniverse CPU with (nolock) on CPU.fkProductId = CP.ProductId
Inner Join Universe EU with (nolock) on EU.UniverseId = CPU.fkUniverseId
Inner Join ProductLanguage BL with (nolock) on BL.fkProductId = P.ProductId
Left join Country PLC with (nolock) on PLC.CountryName = PL.Country
Left join CountryLocale CL with (nolock) on CL.fkCountryId = PLC.CountryId and BL.fkLanguageId = CL.fkLanguageId
Inner Join Language BXL with (nolock) on BXL.LanguageId= BL.fkLanguageId
left join ProductCountry PC with (nolock) on PC.fkProductId = P.ProductId
left join Country C with (nolock) on C.CountryId = PC.fkCountryId
inner join ProductDataTemplate PDT with (nolock) on PDT.fkProductId = P.ProductId AND PDT.fkProductTypeId = 2 and PDT.fkUniverseId = cpu.fkuniverseid
inner join ProductDataTemplate PDPT with (nolock) on PDPT.fkProductId = P.ProductId AND PDPT.isPartnerTemplate = 1 and PDPT.fkUniverseId = cpu.fkuniverseid
inner Join ProductTemplate EPT with (nolock) on EPT.fkProductId = CP.ProductId and EPT.fkLanguageId = BL.fkLanguageId and EPT.IsDeleted = 0 and EPT.fkProductTemplateId is null and EPT.fkTriggerStateId = 14 and EPT.fkDataTemplateId = PDT.fkDataTemplateId
inner Join PartnerTemplate PPT with (nolock) on PPT.fkPartnerId = PR.PartnerId and PPT.fkLanguageId = BL.fkLanguageId and PPT.IsDeleted = 0 and PPT.fkPartnerTemplateId is null and PPT.fkTriggerStateId = 14 and PPT.fkDataTemplateId = PDPT.fkDataTemplateId
left join facet f on f.FacetName = @hiddenContact
left join PartnerFacetValue PFV on PFV.fkPartnerId = ppt.fkPartnerId and pfv.fkFacetId = f.facetid
left join facetValue fv on fv.facetvalueid = pfv.fkfacetvalueid
Left join facet floc on floc.FacetName = @noLocation
Left join PartnerFacetValue PFVloc on PFVloc.fkPartnerId = ppt.fkPartnerId and PFVloc.fkFacetId = floc.facetid
Left join facetValue fvloc on fvloc.facetvalueid = PFVloc.fkfacetvalueid
Left Join (Select sum( ps.rank) as ExperienceRank,RPPS.fkRelatedProductId from RelatedProductPackagingSection RPPS with (nolock)
inner join packagingsection ps with (nolock) on ps.packagingsectionid= rpps.fkpackagingsectionid group by RPPS.fkRelatedProductId ) as ERank on rp.RelatedProductId = Erank.fkRelatedProductId
LEFT JOIN [ProductVersion] WITH (NOLOCK) ON [ProductVersion].[fkProductId] = [P].[ProductId] AND [ProductVersion].[ProductVersionId] = (SELECT MAX([ProductVersionId]) FROM [ProductVersion] [prv] WHERE [prv].[fkProductId] = [P].[ProductId] AND fkProductVersionFormatId = @PhysicalProductFormatId)
JOIN
(select PFV.fkpartnerid
,max(Case when FV.fkfacetid = 61 then FV.value else '' end) as [Michelin Rating]
,max(Case when FV.fkfacetid = 25 then FV.value else '' end) as [TripAdvisor Rating]
,max(Case when FV.fkfacetid = 34 then FV.value else '' end) as [TripAdvisor Certificate of Excellence]
,max(Case when FV.fkfacetid = 35 then FV.value else '' end) as [TripAdvisor Travellers Choice]
,max(Case when FV.fkfacetid = 23 then FV.value else '' end) as [Logis de France]
,max(Case when FV.fkfacetid = 14 then FV.value else '' end) as [Gault et Millaud]
,max(Case when FV.fkfacetid = 62 then FV.value else '' end) as [Bib gourmand]
,max(Case when FV.fkfacetid = 63 then FV.value else '' end) as [Plate]
,max(Case when FV.fkfacetid = 18 then FV.value else '' end) as [Hotel di charme]
FROM partnerfacetvalue PFV
INNER JOIN facetvalue FV
ON FV.facetvalueid = PFV.fkfacetvalueid
WHERE FV.fkfacetid in (61, 35, 34, 35, 23, 14, 62, 63, 18)
group by PFV.fkpartnerid) ptnr
on ptnr.fkpartnerid = PR.partnerid
JOIN
( select PFV.fkproductid
,max(Case when FV.fkfacetid = 45 then FV.value else '' end) as [MinNumberOfPeople]
,max(Case when FV.fkfacetid = 44 then FV.value else '' end) as [MaxNumberOfPeople]
FROM ProductFacetValue PFV
INNER JOIN facetvalue FV
ON FV.FacetValueId = PFV.fkFacetValueId
WHERE FV.fkFacetId in (44,45)
GROUP BY PFV.fkproductid) prod
on prod.fkProductId = CP.ProductId
Where P.ProductId = @BoxId and BL.fkLanguageId = @LanguageId--and PR.PartnerId = 8725
Order by P.ProductId,PR.PartnerId,CP.ProductId
select
a.*,
isnull(PTA.ProductContents,'') ExperienceA_Contents,
isnull(PTB.ProductContents,'') ExperienceB_Contents,
isnull(PTC.ProductContents,'') ExperienceC_Contents,
isnull(PTT.PartnerContents,'') Partner_Contents
from (
Select distinct
ShortName,
MasterBoxCode,
BoxCode,
BoxCodeS ,
BoxVersion,
BoxVersionS,
[IndexPageNumber],
[BoxFormat],
IndexLayout,
PartnerPagesLayout,
BoxBrand,
BoxCountry,
BoxLanguage,
BoxUniverse,
[BrandPartnership],
[BoxTitle],
ExperienceUniverse = isnull(Box.UniverseName,''),
Web = isnull((select top 1 B2.ExperienceWeb from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) and B2.ExperienceWeb = 'Yes'),'No') ,
Specimen = isnull((select top 1 B2.ExperienceSpecimen from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) and B2.ExperienceSpecimen = 'Yes'),'No') ,
Booklet = isnull((select top 1 B2.ExperienceBooklet from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) and B2.ExperienceBooklet = 'Yes'),'No') ,
PartnerId, PartnerName,PartnerCode,replace(isnull(AddressLine1,''),',','') as AddressLine1,isnull(AddressLine2,'') as AddressLine2,isnull(PostCode,'') as PostCode,isnull(City,'') as City,
isnull(PartnerRegion,'') as RegionName,
isnull(ParterSubRegion,'') AS SubRegionName /*as [Parnter Subregion Name]*/,
SubRegionNumber,
isnull(Country,'') as Country,
PhoneNo as 'PhoneNumber',
PartnerTemplateId,
isnull(Email,'') as Email,isnull(Website,'') as Website,Longitude,Latitude,
CanBeBooked = cast(isnull((select top 1 B2.CanBeBooked from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),0) as int) ,
MaxNumberOfPeople = isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) order by B2.MaxNumberOfPeople),'') ,
MinNumberOfPeople = isnull((select top 1 B2.MinNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) order by B2.MinNumberOfPeople),'') ,
ExperienceA_Id = isnull((select top 1 B2.ExperienceId from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'') ,
ExperienceA_Code = isnull((select top 1 B2.ProductCode from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'') ,
ExperienceA_Template = isnull((select top 1 B2.ProductTemplateId from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'') ,
ExperienceB_Id = isnull((select top 1 B2.ExperienceId from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'') ,
ExperienceB_Code = isnull((select top 1 B2.ProductCode from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'') ,
ExperienceB_Template = isnull((select top 1 B2.ProductTemplateId from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'') ,
ExperienceC_Id = isnull((select top 1 B2.ExperienceId from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'') ,
ExperienceC_Code = isnull((select top 1 B2.ProductCode from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'') ,
ExperienceC_Template = isnull((select top 1 B2.ProductTemplateId from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'') ,
ExperienceA_Entitles = dbo.udf_LocaleData(case when (select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1) is not null then 'Pour ' + isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'0') + ' personne' + case when isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'1') = '1' then '.' else 's.' end else '' end,SBLanguageCode),
ExperienceB_Entitles = dbo.udf_LocaleData(case when (select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2) is not null then 'Pour ' + isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'0') + ' personne' + case when isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'1') = '1' then '.' else 's.' end else '' end,SBLanguageCode),
ExperienceC_Entitles = dbo.udf_LocaleData(case when (select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3) is not null then 'Pour ' + isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'0') + ' personne' + case when isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'1') = '1' then '.' else 's.' end else '' end,SBLanguageCode),
isnull(MarketingRegionClassification,'') as [Marketing Region Classification],
[Michelin Rating],[TripAdvisor Rating],
[TripAdvisor Certificate of Excellence],
[TripAdvisor Travellers Choice],
[Logis de France],
[Gault et Millaud],
[Bib gourmand],[Plate],[Hotel di charme]
From #BoxExtract BOX
where BOX.PartnerTemplateUniverseId = (select top 1 B2.UniverseId from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1)
) a
left join ProductTemplate PTA on PTA.ProductTemplateId = a.ExperienceA_Template
left join ProductTemplate PTB on PTB.ProductTemplateId = a.ExperienceB_Template
left join ProductTemplate PTC on PTC.ProductTemplateId = a.ExperienceC_Template
left join PartnerTemplate PTT on PTT.PartnerTemplateId = a.PartnerTemplateId
where (case when @PackagingSectionId = 3 and a.Specimen = 'Yes' then 1
when @PackagingSectionId = 4 and a.Booklet = 'Yes' then 1
when @PackagingSectionId = 1 and a.Web = 'Yes' then 1
else 0 end) = 1
order by PartnerCodeThat was just the tip of the iceberg. There are alot more, that was just one example
Hey Mike,
Tested it again and indeed made the difference, now performance is improved..Thanks to you man!!!
Can you please provide some inputs for [IndexPageNumber] code as well please. I am sure this will make a huge difference.
,[IndexPageNumber] = (
SELECT TOP 1 NumberOfIndexPages FROM [dbo].[EditionBrief]
INNER JOIN [dbo].[EditionBriefRegionalIndexPages] ON [dbo].[EditionBriefRegionalIndexPages].[fkEditionBriefId] = [dbo].[EditionBrief].[EditionBriefId]
WHERE [fkBoxId] = [P].[ProductId] AND fkMarketingRegionsId =
CASE
WHEN cntry.CountryId <> @BoxCountryId THEN mr2.MarketingRegionId
WHEN (@BoxCountryId = @ItalyCountryId AND BU.UniverseId IN (@WellnessUniverseId, @GastronomyUniverseId)) THEN
CASE PL.City
WHEN 'Roma' THEN @RomaMarketingRegionId
WHEN 'Milano' THEN @MilanoMarketingRegionId
ELSE mr1.MarketingRegionId
END
ELSE mr1.MarketingRegionId
END
AND EditionBrief.fkSpecimenId = @BoxCodeS
)
All below joins are being used to fetch just IndexPageNumber values
inner join [dbo].Country cntry ON cntry.CountryName = PL.Country
left join [dbo].MasterLocations ml1 ON ml1.MarketingSubRegion = PL.MarketingSubRegion
left JOIN [dbo].MasterLocations ml2 ON ml2.SubRegionISO = PL.SubRegionISOCode
left JOIN [dbo].MasterLocations ml3 ON ml3.RegionName = PL.Region
left join [dbo].MarketingRegionClassification mrc on mrc.fkUniverseId = @BoxUniverse and mrc.fkBoxCountryId = @BoxCountryId AND mrc.fkMasterLocationId IS NULL AND mrc.fkPartnerCountryId = cntry.CountryId
left join [dbo].MarketingRegionClassification mrc2 on mrc2.fkUniverseId = @BoxUniverse and mrc2.fkBoxCountryId = @BoxCountryId AND mrc2.fkMasterLocationId = isnull(ml1.MasterLocationsId,isnull(ml2.MasterLocationsId,ml3.MasterLocationsId)) AND mrc2.fkPartnerCountryId = cntry.CountryId
left join [dbo].MarketingRegionClassification mrc3 on mrc3.fkUniverseId = @BoxUniverse and mrc3.fkBoxCountryId = @BoxCountryId AND mrc3.fkMasterLocationId is null AND mrc3.fkPartnerCountryId = cntry.CountryId
left join [dbo].MarketingRegion mr1 on mr1.MarketingRegionId = mrc2.fkMarketingRegionId
left join [dbo].MarketingRegion mr2 on mr2.MarketingRegionId = mrc3.fkMarketingRegionIdI'm not sure if this will create dups or even compile, but try this.
Add this to the end of the joins. I think this will still be slow, but worth a try
join (Select [fkboxid] as ProductID, fk.marketingregionsid, Max(numberofindexpages) as numberofindexpages
from [editionbrief] eb
JOIN [editionbriefregionalindexpages] ibip
ON ebip.[fkeditionbriefid] = eb.[editionbriefid]
where eb.fkspecimenid = @BoxCodeS
group by [fkboxid], fk.marketingregionsid) NumIdx
on [P].[productid] = NumIdx.ProductID
AND fkmarketingregionsid =
CASE
WHEN cntry.countryid <> @BoxCountryId THEN
mr2.marketingregionid
WHEN (@BoxCountryId = @ItalyCountryId
AND BU.universeid IN (@WellnessUniverseId, @GastronomyUniverseId ) ) THEN
CASE PL.city
WHEN 'Roma' THEN @RomaMarketingRegionId
WHEN 'Milano' THEN @MilanoMarketingRegionId
ELSE mr1.marketingregionid
END
ELSE mr1.marketingregionid
END
Add this to the column
[IndexPageNumber] = NumIdx.numberofindexpages
Thanks for the response Mike. I updated the query as per your suggestion and getting results back. However no performance improvement this time 
Can we do something like??
,[IndexPageNumber] = convert(int, NULL)
And then run separate updates, taking one join to MarketLocation at a time. That is, first try the mapping for what now is ml1, and once that is done you try the mapping for ml2 for those that are still NULL.
DECLARE @BoxId int = 1217707
DECLARE @PackagingSectionId int = 1
DECLARE @LanguageId int = 149
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
declare @noLocation varchar(20) = 'Hide phone and email' --'noLocation'
declare @hiddenContact varchar(20) = 'Hide map localisation' --'hiddenContact'
declare @PhysicalProductFormatId int = 1
declare @BoxUniverse int
declare @BoxCode int, @BoxCodeS int, @BoxVersionS int
declare @BoxCountryId int = (SELECT DISTINCT fkCountryId FROM [dbo].ProductCountry WHERE fkProductId = @BoxId)
declare @MasterBoxCode int
declare @ItalyCountryId INT = 109
declare @RomaMarketingRegionId INT = (SELECT MarketingRegionId FROM MarketingRegion WHERE MarketingRegionName = 'Roma')
declare @MilanoMarketingRegionId INT = (SELECT MarketingRegionId FROM MarketingRegion WHERE MarketingRegionName = 'Milano')
declare @WellnessUniverseId INT = 8
declare @GastronomyUniverseId INT = 2
select @MasterBoxCode = dbo.udf_GetMasterProductByProductId(@BoxId)
select @BoxCode = dbo.udf_GetPhysicalProductIdForLanguage(@BoxId, @LanguageId),
@BoxCodeS = dbo.udf_GetLatestSpecimenVersionId(@BoxId),
@BoxVersionS = dbo.udf_GetLatestSpecimenVersion(@BoxId)
select @BoxUniverse = fkUniverseId from Product inner join ProductUniverse on Product.ProductId = ProductUniverse.fkProductId
where Product.ProductId = @BoxId
Select P.ShortName,[ProductVersion].[VersionNumber] as BoxVersion ,BXL.LanguageName as BoxLanguage,BXL.SBLanguageCode,BU.UniverseName as BoxUniverse,P.ProductName as BoxTitle,B.BrandName as BoxBrand,C.CountryName as BoxCountry,
PR.PartnerId, PR.PartnerName,PR.PartnerCode, PDPT.fkUniverseId as PartnerTemplateUniverseId,
CASE WHEN fvloc.value=1 THEN null ELSE PL.AddressLine1 END as AddressLine1,
PL.AddressLine2,PL.PostCode,PL.City,
PL.SubRegion as ParterSubRegion,
PartnerRegion = case when isnull(C.CountryName,'') <> PL.Country then isnull(CL.CountryName,PL.Country) else PL.Region end,
PL.Country,
CASE WHEN fv.value=1 THEN null
ELSE CASE WHEN left(isnull(PL.PhoneNo,''),1) <> '0' and PL.PhoneNo is not null THEN '0'+ PL.PhoneNo
ELSE PL.PhoneNo
END
END as PhoneNo,
CASE WHEN fv.value = 1 THEN null
ELSE PL.Email
END as Email,
PL.Website,PL.Longitude,PL.Latitude,
CanBeBooked = isnull((SELECT top 1 P.isReservable FROM Product P
JOIN RelatedProduct RP ON P.ProductId = RP.fkProduct1Id
WHERE RP.fkRelationTypeId = 40 AND RP.fkProductId = CP.ProductId
and P.isReservable = 1 ),0),
PL.MarketingRegion as LocationName,
EU.UniverseId, EU.UniverseName, CP.ProductId as ExperienceId, CP.ProductCode,
ExperienceCount = DENSE_RANK() OVER (PARTITION BY P.ProductId,PR.PartnerId ORDER BY ExperienceRank desc,CP.Productid),
EPT.ProductTemplateId ,
PPT.PartnerTemplateId ,
MarketingRegionClassification = case when isnull(C.CountryName,'') <> PL.Country then isnull(CL.CountryName,PL.Country) else MRU.MarketingRegion END,
ExperienceWeb = case when exists(select RelatedProductPackagingSectionId from RelatedProductPackagingSection RPPS where RPPS.fkRelatedProductId = RP.RelatedProductId and RPPS.fkPackagingSectionId = 1) then 'Yes' else 'No' end,
ExperienceSpecimen = case when exists(select RelatedProductPackagingSectionId from RelatedProductPackagingSection RPPS where RPPS.fkRelatedProductId = RP.RelatedProductId and RPPS.fkPackagingSectionId = 3) then 'Yes' else 'No' end,
ExperienceBooklet = case when exists(select RelatedProductPackagingSectionId from RelatedProductPackagingSection RPPS where RPPS.fkRelatedProductId = RP.RelatedProductId and RPPS.fkPackagingSectionId = 4) then 'Yes' else 'No' end,
SubRegionNumber = substring(isnull(PL.SubRegionISOCode,''), 4, 10) ,
BoxCode = @BoxCode,
BoxCodeS = @BoxCodeS,
BoxVersionS = @BoxVersionS,
MasterBoxCode = @MasterBoxCode,
IsNull([MinNumberOfPeople], '') as [MinNumberOfPeople],
IsNull([MaxNumberOfPeople], '') as [MaxNumberOfPeople],
IsNull([Michelin Rating], '') as [Michelin Rating],
IsNull([TripAdvisor Rating], '') as [TripAdvisor Rating],
IsNull([TripAdvisor Certificate of Excellence], '') as [TripAdvisor Certificate of Excellence],
IsNull([TripAdvisor Travellers Choice], '') as [TripAdvisor Travellers Choice],
IsNull([Logis de France], '') as [Logis de France],
IsNull([Gault et Millaud], '') as [Gault et Millaud],
IsNull([Bib gourmand], '') as [Bib gourmand],
IsNull([Plate], '') as [Plate],
IsNull([Hotel di charme], '') as [Hotel di charme],
[Trip Advisor Review Count] = CAST(CAST(PPT.PartnerContents AS XML).query('/Contents/Content[@Name=''Trip Advisor Review Count'' and @SectionId=''1'']/text()[1]') as nvarchar(max)), -- OLD QUERTY
[IndexPageNumber] = NumIdx.numberofindexpages,
--(
-- SELECT TOP 1 NumberOfIndexPages FROM [dbo].[EditionBrief] ebf
-- INNER JOIN [dbo].[EditionBriefRegionalIndexPages] ebip ON ebip.[fkEditionBriefId] = ebf.[EditionBriefId]
-- WHERE ebf.[fkBoxId] = [P].[ProductId] AND ebip.fkMarketingRegionsId =
-- CASE
-- WHEN cntry.CountryId <> @BoxCountryId THEN mr2.MarketingRegionId
-- WHEN (@BoxCountryId = @ItalyCountryId AND BU.UniverseId IN (@WellnessUniverseId, @GastronomyUniverseId)) THEN
-- CASE PL.City
-- WHEN 'Roma' THEN @RomaMarketingRegionId
-- WHEN 'Milano' THEN @MilanoMarketingRegionId
-- ELSE mr1.MarketingRegionId
-- END
-- ELSE mr1.MarketingRegionId
-- END
-- AND ebf.fkSpecimenId = @BoxCodeS
-- ),
[BoxFormat] = STUFF((SELECT ',' + [Format].[Name] FROM [AvailableProductFormat] INNER JOIN [Format] ON [Format].[FormatId] = [AvailableProductFormat].[fkFormatId] WHERE [fkProductId]=[P].[ProductId] ORDER BY [PriorityForConcept] ASC FOR XML PATH('')),1,1,''),
[IndexLayout] = (SELECT TOP 1 edb.IndexLayout FROM EditionBrief as edb WHERE fkBoxId = @BoxId AND fkSpecimenId = @BoxCodeS),
[PartnerPagesLayout] = (SELECT TOP 1 edb.PartnerPagesLayout FROM EditionBrief as edb WHERE fkBoxId = @BoxId),
[BrandPartnership] = (SELECT ISNULL([BrandPartnership].[Name],'none') FROM [Product] LEFT JOIN [BrandPartnership] ON [BrandPartnership].[BrandPartnershipId] = [Product].[fkBrandPartnershipId] WHERE [Product].[ProductId] = [P].[ProductId])
Into #BoxExtract
From Product P
Inner Join RelatedProduct RP with (nolock) on RP.fkProductId = P.ProductId and RP.Active = 1
Inner Join PartnerProduct PP with (nolock) on PP.fkProductId = RP.fkProduct1Id and PP.Active = 1
Inner Join Partner PR with (nolock) on PR.PartnerId = PP.fkPartnerId and PR.PartnerStatus = 1
LEFT JOIN
(SELECT PFV.fkPartnerId
,max(Case when FV.fkfacetid = 61 then FV.[Value] else '' end) as [Michelin Rating]
,max(Case when FV.fkfacetid = 25 then FV.[Value] else '' end) as [TripAdvisor Rating]
,max(Case when FV.fkfacetid = 34 then FV.[Value] else '' end) as [TripAdvisor Certificate of Excellence]
,max(Case when FV.fkfacetid = 35 then FV.[Value] else '' end) as [TripAdvisor Travellers Choice]
,max(Case when FV.fkfacetid = 23 then FV.[Value] else '' end) as [Logis de France]
,max(Case when FV.fkfacetid = 14 then FV.[Value] else '' end) as [Gault et Millaud]
,max(Case when FV.fkfacetid = 62 then FV.[Value] else '' end) as [Bib gourmand]
,max(Case when FV.fkfacetid = 63 then FV.[Value] else '' end) as [Plate]
,max(Case when FV.fkfacetid = 18 then FV.[Value] else '' end) as [Hotel di charme]
FROM PartnerFacetValue PFV
INNER JOIN [dbo].FacetValue FV
ON FV.FacetValueId = PFV.fkFacetValueId
WHERE FV.fkfacetid IN (61, 25, 34, 35, 23, 14, 62, 63, 18)
GROUP BY PFV.fkPartnerId) ptnr
on ptnr.fkPartnerId = PR.PartnerId
--SELECT TOP 1 FV.[Value] FROM [dbo].PartnerFacetValue PFV
-- Inner Join [dbo].FacetValue FV on FV.FacetValueId = PFV.fkFacetValueId
-- WHERE FV.fkFacetId = 61 and PFV.fkPartnerId = PR.PartnerId
Inner Join PartnerLocation PL with (nolock) on PL.fkPartnerId = PR.PartnerId
-- BEGINNING Modification DB
inner join [dbo].Country cntry ON cntry.CountryName = PL.Country
left join [dbo].MasterLocations ml1 ON ml1.MarketingSubRegion = PL.MarketingSubRegion
left JOIN [dbo].MasterLocations ml2 ON ml2.SubRegionISO = PL.SubRegionISOCode
left JOIN [dbo].MasterLocations ml3 ON ml3.RegionName = PL.Region
left join [dbo].MarketingRegionClassification mrc on mrc.fkUniverseId = @BoxUniverse and mrc.fkBoxCountryId = @BoxCountryId AND mrc.fkMasterLocationId IS NULL AND mrc.fkPartnerCountryId = cntry.CountryId
left join [dbo].MarketingRegionClassification mrc2 on mrc2.fkUniverseId = @BoxUniverse and mrc2.fkBoxCountryId = @BoxCountryId AND mrc2.fkMasterLocationId = isnull(ml1.MasterLocationsId,isnull(ml2.MasterLocationsId,ml3.MasterLocationsId)) AND mrc2.fkPartnerCountryId = cntry.CountryId
left join [dbo].MarketingRegionClassification mrc3 on mrc3.fkUniverseId = @BoxUniverse and mrc3.fkBoxCountryId = @BoxCountryId AND mrc3.fkMasterLocationId is null AND mrc3.fkPartnerCountryId = cntry.CountryId
left join [dbo].MarketingRegion mr1 on mr1.MarketingRegionId = mrc2.fkMarketingRegionId
left join [dbo].MarketingRegion mr2 on mr2.MarketingRegionId = mrc3.fkMarketingRegionId
-- END Modification DB
Inner Join ProductUniverse PU with (nolock) on PU.fkProductId = P.ProductId
Left Join MarketingRegionUniverse MRU with (nolock) on MRU.UniverseId = P.fkUniverseBoxId and MRU.SubregionISOCode = PL.SubRegionISOCode
left join ProductBrand PB with (nolock) on PB.fkProductId = P.ProductId
left join Brand B with (nolock) on PB.fkBrandId = B.BrandId
Left Join Universe BU with (nolock) on BU.UniverseId = P.fkUniverseBoxId
JOIN ( Select ebf.fkBoxId, ebip.fkMarketingRegionsId, Max(numberofindexpages) as numberofindexpages
FROM [EditionBrief] ebf
JOIN [EditionBriefRegionalIndexPages] ebip
ON ebip.[fkEditionBriefId] = ebf.[EditionBriefId]
where ebf.fkSpecimenId = @BoxCodeS
group by ebf.fkBoxId, ebip.fkMarketingRegionsId) NumIdx
on [P].[ProductID] = NumIdx.fkBoxId
AND NumIdx.fkMarketingRegionsId =
CASE
WHEN cntry.countryid <> @BoxCountryId THEN
mr2.marketingregionid
WHEN (@BoxCountryId = @ItalyCountryId
AND BU.universeid IN (@WellnessUniverseId, @GastronomyUniverseId ) ) THEN
CASE PL.city
WHEN 'Roma' THEN @RomaMarketingRegionId
WHEN 'Milano' THEN @MilanoMarketingRegionId
ELSE mr1.marketingregionid
END
ELSE mr1.marketingregionid
END
Inner Join Product CP with (nolock) on CP.ProductId = RP.fkProduct1Id and CP.fkTriggerStateId = 4
LEFT JOIN
( select PFV.fkproductid
,max(Case when FV.fkfacetid = 45 then FV.value else '' end) as [MinNumberOfPeople]
,max(Case when FV.fkfacetid = 44 then FV.value else '' end) as [MaxNumberOfPeople]
FROM ProductFacetValue PFV
INNER JOIN FacetValue FV
ON FV.FacetValueId = PFV.fkFacetValueId
WHERE FV.fkFacetId in (44,45)
GROUP BY PFV.fkproductid) prod
on prod.fkProductId = CP.ProductId
Inner Join ProductUniverse CPU with (nolock) on CPU.fkProductId = CP.ProductId
Inner Join Universe EU with (nolock) on EU.UniverseId = CPU.fkUniverseId
Inner Join ProductLanguage BL with (nolock) on BL.fkProductId = P.ProductId
Left join Country PLC with (nolock) on PLC.CountryName = PL.Country
Left join CountryLocale CL with (nolock) on CL.fkCountryId = PLC.CountryId and BL.fkLanguageId = CL.fkLanguageId
Inner Join Language BXL with (nolock) on BXL.LanguageId= BL.fkLanguageId
left join ProductCountry PC with (nolock) on PC.fkProductId = P.ProductId
left join Country C with (nolock) on C.CountryId = PC.fkCountryId
inner join ProductDataTemplate PDT with (nolock) on PDT.fkProductId = P.ProductId AND PDT.fkProductTypeId = 2 and PDT.fkUniverseId = cpu.fkuniverseid
inner join ProductDataTemplate PDPT with (nolock) on PDPT.fkProductId = P.ProductId AND PDPT.isPartnerTemplate = 1 and PDPT.fkUniverseId = cpu.fkuniverseid
inner Join ProductTemplate EPT with (nolock) on EPT.fkProductId = CP.ProductId and EPT.fkLanguageId = BL.fkLanguageId and EPT.IsDeleted = 0 and EPT.fkProductTemplateId is null and EPT.fkTriggerStateId = 14 and EPT.fkDataTemplateId = PDT.fkDataTemplateId
inner Join PartnerTemplate PPT with (nolock) on PPT.fkPartnerId = PR.PartnerId and PPT.fkLanguageId = BL.fkLanguageId and PPT.IsDeleted = 0 and PPT.fkPartnerTemplateId is null and PPT.fkTriggerStateId = 14 and PPT.fkDataTemplateId = PDPT.fkDataTemplateId
left join facet f on f.FacetName = @hiddenContact
left join PartnerFacetValue PFV on PFV.fkPartnerId = ppt.fkPartnerId and pfv.fkFacetId = f.facetid
left join facetValue fv on fv.facetvalueid = pfv.fkfacetvalueid
Left join facet floc on floc.FacetName = @noLocation
Left join PartnerFacetValue PFVloc on PFVloc.fkPartnerId = ppt.fkPartnerId and PFVloc.fkFacetId = floc.facetid
Left join facetValue fvloc on fvloc.facetvalueid = PFVloc.fkfacetvalueid
Left Join (Select sum( ps.rank) as ExperienceRank,RPPS.fkRelatedProductId from RelatedProductPackagingSection RPPS with (nolock)
inner join packagingsection ps with (nolock) on ps.packagingsectionid= rpps.fkpackagingsectionid group by RPPS.fkRelatedProductId ) as ERank on rp.RelatedProductId = Erank.fkRelatedProductId
LEFT JOIN [ProductVersion] WITH (NOLOCK) ON [ProductVersion].[fkProductId] = [P].[ProductId] AND [ProductVersion].[ProductVersionId] = (SELECT MAX([ProductVersionId]) FROM [ProductVersion] [prv] WHERE [prv].[fkProductId] = [P].[ProductId] AND fkProductVersionFormatId = @PhysicalProductFormatId)
Where P.ProductId = @BoxId and BL.fkLanguageId = @LanguageId--and PR.PartnerId = 8725
Order by P.ProductId,PR.PartnerId,CP.ProductId
select
a.*,
isnull(PTA.ProductContents,'') ExperienceA_Contents,
isnull(PTB.ProductContents,'') ExperienceB_Contents,
isnull(PTC.ProductContents,'') ExperienceC_Contents,
isnull(PTT.PartnerContents,'') Partner_Contents
from (
SELECT DISTINCT
ShortName,
MasterBoxCode,
BoxCode,
BoxCodeS ,
BoxVersion,
BoxVersionS,
[IndexPageNumber],
[BoxFormat],
IndexLayout,
PartnerPagesLayout,
BoxBrand,
BoxCountry,
BoxLanguage,
BoxUniverse,
[BrandPartnership],
[BoxTitle],
ExperienceUniverse = isnull(Box.UniverseName,''),
Web = isnull((select top 1 B2.ExperienceWeb from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) and B2.ExperienceWeb = 'Yes'),'No') ,
Specimen = isnull((select top 1 B2.ExperienceSpecimen from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) and B2.ExperienceSpecimen = 'Yes'),'No') ,
Booklet = isnull((select top 1 B2.ExperienceBooklet from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) and B2.ExperienceBooklet = 'Yes'),'No') ,
PartnerId, PartnerName,PartnerCode,replace(isnull(AddressLine1,''),',','') as AddressLine1,isnull(AddressLine2,'') as AddressLine2,isnull(PostCode,'') as PostCode,isnull(City,'') as City,
isnull(PartnerRegion,'') as RegionName,
isnull(ParterSubRegion,'') AS SubRegionName /*as [Parnter Subregion Name]*/,
SubRegionNumber,
isnull(Country,'') as Country,
PhoneNo as 'PhoneNumber',
PartnerTemplateId,
isnull(Email,'') as Email,isnull(Website,'') as Website,Longitude,Latitude,
CanBeBooked = cast(isnull((select top 1 B2.CanBeBooked from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),0) as int) ,
MaxNumberOfPeople = isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) order by B2.MaxNumberOfPeople),'') ,
MinNumberOfPeople = isnull((select top 1 B2.MinNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount in (1,2,3) order by B2.MinNumberOfPeople),'') ,
ExperienceA_Id = isnull((select top 1 B2.ExperienceId from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'') ,
ExperienceA_Code = isnull((select top 1 B2.ProductCode from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'') ,
ExperienceA_Template = isnull((select top 1 B2.ProductTemplateId from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'') ,
ExperienceB_Id = isnull((select top 1 B2.ExperienceId from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'') ,
ExperienceB_Code = isnull((select top 1 B2.ProductCode from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'') ,
ExperienceB_Template = isnull((select top 1 B2.ProductTemplateId from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'') ,
ExperienceC_Id = isnull((select top 1 B2.ExperienceId from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'') ,
ExperienceC_Code = isnull((select top 1 B2.ProductCode from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'') ,
ExperienceC_Template = isnull((select top 1 B2.ProductTemplateId from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'') ,
ExperienceA_Entitles = dbo.udf_LocaleData(case when (select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1) is not null then 'Pour ' + isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'0') + ' personne' + case when isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1),'1') = '1' then '.' else 's.' end else '' end,SBLanguageCode),
ExperienceB_Entitles = dbo.udf_LocaleData(case when (select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2) is not null then 'Pour ' + isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'0') + ' personne' + case when isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 2),'1') = '1' then '.' else 's.' end else '' end,SBLanguageCode),
ExperienceC_Entitles = dbo.udf_LocaleData(case when (select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3) is not null then 'Pour ' + isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'0') + ' personne' + case when isnull((select top 1 B2.MaxNumberOfPeople from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 3),'1') = '1' then '.' else 's.' end else '' end,SBLanguageCode),
isnull(MarketingRegionClassification,'') as [Marketing Region Classification],
[Michelin Rating],[TripAdvisor Rating],
[TripAdvisor Certificate of Excellence],
[TripAdvisor Travellers Choice],
[Logis de France],
[Gault et Millaud],
[Bib gourmand],[Plate],[Hotel di charme]
FROM #BoxExtract BOX
WHERE BOX.PartnerTemplateUniverseId = (select top 1 B2.UniverseId from #BoxExtract B2 Where B2.PartnerCode = Box.PartnerCode and B2.ExperienceCount = 1)
) a
left join ProductTemplate PTA on PTA.ProductTemplateId = a.ExperienceA_Template
left join ProductTemplate PTB on PTB.ProductTemplateId = a.ExperienceB_Template
left join ProductTemplate PTC on PTC.ProductTemplateId = a.ExperienceC_Template
left join PartnerTemplate PTT on PTT.PartnerTemplateId = a.PartnerTemplateId
WHERE (CASE WHEN @PackagingSectionId = 3 and a.Specimen = 'Yes' then 1
when @PackagingSectionId = 4 and a.Booklet = 'Yes' then 1
when @PackagingSectionId = 1 and a.Web = 'Yes' then 1
else 0 end) = 1
order by PartnerCode