Join Two queries with different declarations

ctesubquery

I need to join two different queries to get some columns from both. The first one has a CTE.

Query1:

   WITH StatusChanges AS 
(
    SELECT 
       HistoryID,PartID,Status, DateAdded, 
       rnAsset = ROW_NUMBER() OVER (PARTITION BY PartID ORDER BY DateAdded) 
    FROM aAssetHistory
) SELECT 
    A.isMarketed,A.AssetDescription,A.aPartID,A.SerialNumber,A.PartNumber,
    A.Location,A.Vintage,Ap.Fund,A.ProjectNumber,AP.ProjectName ,
    (
       SELECT Top 1 Disposition 
       FROM aAssetDisposition AD 
       WHERE (A.aPartid = AD.AssetID)) As Disposition, A.LocStatus ,
          CASE 
             WHEN A.LocStatus='In Repair' THEN EstimatedCompletion 
             WHEN A.LocStatus like '%lease%' THEN IIF(ProjectedReDeliveryDate='1/1/1900',LeaseExpiry,ProjectedReDeliveryDate)  
             WHEN A.LocStatus='Stored' OR A.LocStatus='Marketed' THEN SC.DateAdded 
          END 'Available',A.AssetComment, 
          Cast(ISNULL((SELECT Top 1 1 
                       FROM aAssetMarketingDocuments AMDocs 
                       WHERE A.aPartid=AMDocs.aPartid),0) As bit
       ) Attachment 
    FROM aAsset A  
INNER JOIN aProject AP On A.ProjectNumber=AP.ProjectNumber 
LEFT JOIN aAsset ParentA  On A.ParentACPartID=ParentA.aPartid 
LEFT JOIN aEngineShop ES ON A.LocStatus='In Repair' AND ES.PartID=A.aPartid 
LEFT JOIN vLeasedAsset LA ON A.LocStatus like '%lease%' AND LA.aPartID=A.aPartid 
LEFT JOIN (
          SELECT SC1.PartID, SC1.DateAdded,RN= ROW_NUMBER() OVER (PARTITION BY SC1.PartID ORDER BY SC1.DateAdded DESC) 
          FROM StatusChanges SC1 
          LEFT JOIN StatusChanges SC2 ON SC1.PartID=SC2.PartID AND SC2.rnAsset=SC1.rnAsset-1 
          WHERE (SC1.Status<>SC2.Status OR SC2.HistoryID IS NULL)) SC 
ON (A.LocStatus='Stored' OR A.LocStatus='Marketed') AND A.aPartid=SC.PartID AND SC.rn = 1 
WHERE ((A.isMarketed=1 AND (ParentA.isMarketed IS NULL OR ParentA.isMarketed=0)) )

Query2:

 SELECT 
    A.[AssetMarketingID],B.PartID, aCompany.Company 'PotentialCustomer', A.isActive, 
    ISNULL(CAST(Lease AS bit),0) 'Lease', ISNULL(CAST(Exchange AS bit),0) 'Exchange', 
    ISNULL(CAST(Sale AS bit),0) 'Sale' ,DocumentStage,ContactTB.FirstName + ' ' + ContactTB.LastName 'Contact', 
    A.ContactDate,A.Status,B.Amount, A.DateCreated, [Notes] 
FROM aAssetMarketing A 
LEFT JOIN aAssetMarketingAssets B ON A.AssetMarketingID=B.AssetMarketingID 
LEFT JOIN aCompany On [PotentialCustomerID]=COid 
LEFT JOIN aCompanyContact ContactTB ON [MarketingContactID]=ContactID 
LEFT JOIN (
          select AssetMarketingID,max(case when DealStructure = 'Exchange' then 1 ELSE 0 end) Exchange, 
          max(case when DealStructure = 'Lease' then 1 Else 0 end) Lease, 
          max(case when DealStructure = 'Sale' then 1 Else 0 end) Sale 
FROM [aAssetMarketingDealStructure] 
GROUP BY AssetMarketingID) DS     
    ON DS.AssetMarketingID=A.AssetMarketingID 
ORDER BY A.ContactDate DESC

Best Answer

   WITH StatusChanges AS 
(
    SELECT 
       HistoryID,PartID,Status, DateAdded, 
       rnAsset = ROW_NUMBER() OVER (PARTITION BY PartID ORDER BY DateAdded) 
    FROM aAssetHistory
) SELECT 
    A.isMarketed,A.AssetDescription,A.aPartID,A.SerialNumber,A.PartNumber,
    A.Location,A.Vintage,Ap.Fund,A.ProjectNumber,AP.ProjectName ,
    (
       SELECT Top 1 Disposition 
       FROM aAssetDisposition AD 
       WHERE (A.aPartid = AD.AssetID)) As Disposition, A.LocStatus ,
          CASE 
             WHEN A.LocStatus='In Repair' THEN EstimatedCompletion 
             WHEN A.LocStatus like '%lease%' THEN IIF(ProjectedReDeliveryDate='1/1/1900',LeaseExpiry,ProjectedReDeliveryDate)  
             WHEN A.LocStatus='Stored' OR A.LocStatus='Marketed' THEN SC.DateAdded 
          END 'Available',A.AssetComment, 
          Cast(ISNULL((SELECT Top 1 1 
                       FROM aAssetMarketingDocuments AMDocs 
                       WHERE A.aPartid=AMDocs.aPartid),0) As bit
       ) Attachment 
    INTO #tmp
    FROM aAsset A  
INNER JOIN aProject AP On A.ProjectNumber=AP.ProjectNumber 
LEFT JOIN aAsset ParentA  On A.ParentACPartID=ParentA.aPartid 
LEFT JOIN aEngineShop ES ON A.LocStatus='In Repair' AND ES.PartID=A.aPartid 
LEFT JOIN vLeasedAsset LA ON A.LocStatus like '%lease%' AND LA.aPartID=A.aPartid 
LEFT JOIN (
          SELECT SC1.PartID, SC1.DateAdded,RN= ROW_NUMBER() OVER (PARTITION BY SC1.PartID ORDER BY SC1.DateAdded DESC) 
          FROM StatusChanges SC1 
          LEFT JOIN StatusChanges SC2 ON SC1.PartID=SC2.PartID AND SC2.rnAsset=SC1.rnAsset-1 
          WHERE (SC1.Status<>SC2.Status OR SC2.HistoryID IS NULL)) SC 
ON (A.LocStatus='Stored' OR A.LocStatus='Marketed') AND A.aPartid=SC.PartID AND SC.rn = 1 
WHERE ((A.isMarketed=1 AND (ParentA.isMarketed IS NULL OR ParentA.isMarketed=0)) )

Query2:

SELECT 
    A.[AssetMarketingID],B.PartID, aCompany.Company 'PotentialCustomer', A.isActive, 
    ISNULL(CAST(Lease AS bit),0) 'Lease', ISNULL(CAST(Exchange AS bit),0) 'Exchange', 
    ISNULL(CAST(Sale AS bit),0) 'Sale' ,DocumentStage,ContactTB.FirstName + ' ' + ContactTB.LastName 'Contact', 
    A.ContactDate,A.Status,B.Amount, A.DateCreated, [Notes] 
FROM aAssetMarketing A 
LEFT JOIN aAssetMarketingAssets B ON A.AssetMarketingID=B.AssetMarketingID 
LEFT JOIN aCompany On [PotentialCustomerID]=COid 
LEFT JOIN aCompanyContact ContactTB ON [MarketingContactID]=ContactID 
LEFT JOIN (
          select AssetMarketingID,max(case when DealStructure = 'Exchange' then 1 ELSE 0 end) Exchange, 
          max(case when DealStructure = 'Lease' then 1 Else 0 end) Lease, 
          max(case when DealStructure = 'Sale' then 1 Else 0 end) Sale 
FROM [aAssetMarketingDealStructure] 
JOIN #tmp A ON 
A.SerialNumber, A.PartNumber, Location, A.Fund, Disposition, DocumentStage, Lease, Sale
GROUP BY AssetMarketingID) DS     
    ON DS.AssetMarketingID=A.AssetMarketingID 
ORDER BY A.ContactDate DESC

Related Solutions

Mysql – Subqueries run very fast individually, but when joined are very slow

You don't need all the derived tables. You are joining the basic (product) too many times. You can write the query joining it only once.

Compound indices are a must for EAV designs. Try adding an index on (attribute_id, product_id, value) and then the query:

SELECT t0.id, 
       t1.`value` AS length, 
       t2.`value` AS height, 
       t3.`value` AS family
FROM
  products t0

INNER JOIN 
  product_eav_decimal t1
    ON  t1.product_id = t0.id  
    AND t1.attribute_id = 91
    AND t1.`value` BETWEEN 15 AND 35

LEFT JOIN
  product_eav_decimal t2
    ON  t2.product_id = t0.id  
    AND t2.attribute_id = 80  
-- 
-- 
--

LEFT JOIN                              -- LEFT or INNER join
  product_eav_decimal t6
    ON  t6.product_id = t0.id  
 -- AND t6.attribute_id = 

ORDER BY t0.id ASC ;

SQL Server – Optimize SELECT on Subquery with COALESCE

This was rather longshot but since the OP says it worked, I'm adding it as an answer (feel free to correct it if you find anything wrong).

Try to break the internal query into three parts (INNER JOIN, LEFT JOIN with WHERE IS NULL check, RIGHT JOIN with IS NULL check) and then UNION ALL the three parts. This has the following advantages:

The optimizer has less transformation options available for FULL joins than for (the more common) INNER and LEFT joins.
The Z derived table can be removed (you can do that anyway) from the view definition.
The NOT(pe.ThisThing = 1 AND se.OtherThing = 0) will be needed only on the INNER join part.
Minor improvement, the use COALESCE() will be minimal if any at all (I assumed that se.SEId and pe.PEId are not nullable. If more columns are not nullable, you'll be able to remove more COALESCE() calls.)
More important, the optimizer may push down any conditions in your queries that involve these columns (now that COALESCE() is not blocking the push.)
All the above will give the optimizer more options to transform/rewrite any query that uses the view so it may find an execution plan that indexes on the underlying tables can be used.

In all, the view can be written as:

SELECT 
    se.SEId + '-' + pe.PEId AS Id,
    se.SEId, pe.PEId,
    pe.StaffName, 
    pe.EventTime,
    COALESCE(pe.EventType, se.EventType) AS EventType,
    pe.Duration,
    COALESCE(pe.Data, se.Data) AS Data,
    COALESCE(pe.Field, se.Field) AS Field,
    pe.ThisThing, se.OtherThing,
    DATEADD(minute, pe.Duration, pe.EventTime) AS EventEndTime
FROM PE pe INNER JOIN SE se 
  ON pe.StaffName = se.StaffName
 AND pe.Duration = se.Duration
 AND pe.EventTime = se.EventTime
WHERE NOT (pe.ThisThing = 1 AND se.OtherThing = 0) 

UNION ALL

SELECT 
    '0-0',
    NULL, pe.PEId,
    pe.StaffName, 
    pe.EventTime,
    pe.EventType,
    pe.Duration,
    pe.Data,
    pe.Field,
    pe.ThisThing, NULL,
    DATEADD(minute, pe.Duration, pe.EventTime) AS EventEndTime
FROM PE pe LEFT JOIN SE se 
  ON pe.StaffName = se.StaffName
 AND pe.Duration = se.Duration
 AND pe.EventTime = se.EventTime
WHERE NOT (pe.ThisThing = 1)
  AND se.StaffName IS NULL

UNION ALL

SELECT 
    '0-0',
    se.SEId, NULL,
    se.StaffName, 
    se.EventTime,
    se.EventType,
    se.Duration,
    se.Data,
    se.Field,
    NULL, se.OtherThing, 
    DATEADD(minute, se.Duration, se.EventTime) AS EventEndTime
FROM PE pe RIGHT JOIN SE se 
  ON pe.StaffName = se.StaffName
 AND pe.Duration = se.Duration
 AND pe.EventTime = se.EventTime
WHERE NOT (se.OtherThing = 0)
  AND pe.StaffName IS NULL ;

Best Answer

Related Solutions

Mysql – Subqueries run very fast individually, but when joined are very slow

SQL Server – Optimize SELECT on Subquery with COALESCE

Related Question