September 2, 2009 at 11:41 am
I have a report which attempts to grab Marketing/Sales data based on LeadSources.
There is a PrimaryLeadSource and then each PrimaryLeadSource may or may not have
a SecondaryLeadSource associated with it. After some messing around, I finally have a query which will at return a result set that includes PrimaryLeadSources who have no SecondaryLeadSource associated with it; however the data for these PrimaryLeadSources return all 0s -- most likely because it doesn't find a SecondaryLeadSource that points back to it.
Full SQL:
SELECTlsp.LEAD_SOURCE_DESC AS LeadSourcePrimary,
ISNULL(lss.SECONDARY_SOURCE_DESCRIPTION,'No Entry') AS LeadSourceSecondary,
ISNULL(count(l.oid), 0) AS Inquiries,
ISNULL((select count(*) from T_LEAD l2
where l2.lead_source_primary_oid = lsp.oid and (l2.lead_source_secondary_oid = lss.oid or l2.lead_source_secondary_oid is null)
and l2.lead_inquiry_date is not null),0) as LeadsIssued, --used to be LeadSetDate in CE
ISNULL((select count(*) from T_LEAD_APPOINTMENT la inner join t_lead l2 on l2.oid = la.appt_lead_oid
where l2.lead_source_primary_oid = lsp.oid and (l2.lead_source_secondary_oid = lss.oid or l2.lead_source_secondary_oid is null)
and lead_issued_date is not null),0) As ApptsSet, --AppointmentDate in CE
ISNULL((select count(*) from T_LEAD_APPOINTMENT la inner join t_lead l2 on l2.oid = la.appt_lead_oid
where l2.lead_source_primary_oid = lsp.oid and (l2.lead_source_secondary_oid = lss.oid or l2.lead_source_secondary_oid is null)
and appt_confirmed = 'true'),0) As Confirmed,
ISNULL((select count(*) from T_LEAD_APPOINTMENT la inner join t_lead l2 on l2.oid = la.appt_lead_oid
where l2.lead_source_primary_oid = lsp.oid and (l2.lead_source_secondary_oid = lss.oid or l2.lead_source_secondary_oid is null)
and APPT_EMP_OID is not null),0) As IssuedToSales,
ISNULL((select count(*) from T_LEAD_APPOINTMENT la inner join t_lead l2 on l2.oid = la.appt_lead_oid
where l2.lead_source_primary_oid = lsp.oid and (l2.lead_source_secondary_oid = lss.oid or l2.lead_source_secondary_oid is null)
and appt_result_oid in (select oid from t_appt_result where APPT_RESULT_DEMO = 'TRUE' )),0) As Demo,
ISNULL((select count(*) from T_LEAD_APPOINTMENT la inner join t_lead l2 on l2.oid = la.appt_lead_oid
where l2.lead_source_primary_oid = lsp.oid and (l2.lead_source_secondary_oid = lss.oid or l2.lead_source_secondary_oid is null)
and appt_result_oid in (select oid from t_appt_result where APPT_RESULT_SOLD = 'TRUE' )),0) As Sold,
ISNULL((select sum(contract_total) from T_CONTRACT ctrt inner join T_CUST_PROJ cp on cp.contact_oid = ctrt.contact_oid and cp.proj_oid = ctrt.proj_oid inner join
T_LEAD l2 on cp.lead_oid = l2.oid and l2.lead_source_primary_oid = lsp.oid and (l2.lead_source_secondary_oid = lss.oid or l2.lead_source_secondary_oid is null)),0) as Gross,
ISNULL((select sum(1) from T_CONTRACT ctrt inner join T_CUST_PROJ cp on cp.contact_oid = ctrt.contact_oid and cp.proj_oid = ctrt.proj_oid inner join
T_LEAD l2 on cp.lead_oid = l2.oid and l2.lead_source_primary_oid = lsp.oid and (l2.lead_source_secondary_oid = lss.oid or l2.lead_source_secondary_oid is null)
where ctrt.CONTRACT_STATUS = 'Canceled'),0) As NoCanceled,
ISNULL((select sum(contract_total) from T_CONTRACT ctrt inner join T_CUST_PROJ cp on cp.contact_oid = ctrt.contact_oid and cp.proj_oid = ctrt.proj_oid inner join
T_LEAD l2 on cp.lead_oid = l2.oid and l2.lead_source_primary_oid = lsp.oid and (l2.lead_source_secondary_oid = lss.oid or l2.lead_source_secondary_oid is null)
where ctrt.CONTRACT_STATUS = 'Canceled'),0) As Canceled,
ISNULL((select sum(1) from T_CONTRACT ctrt inner join T_CUST_PROJ cp on cp.contact_oid = ctrt.contact_oid and cp.proj_oid = ctrt.proj_oid inner join
T_LEAD l2 on cp.lead_oid = l2.oid and l2.lead_source_primary_oid = lsp.oid and (l2.lead_source_secondary_oid = lss.oid or l2.lead_source_secondary_oid is null)
where ctrt.CONTRACT_STATUS = 'FinRejected'),0) As NoFinRej,
ISNULL((select sum(contract_total) from T_CONTRACT ctrt inner join T_CUST_PROJ cp on cp.contact_oid = ctrt.contact_oid and cp.proj_oid = ctrt.proj_oid inner join
T_LEAD l2 on cp.lead_oid = l2.oid and l2.lead_source_primary_oid = lsp.oid and (l2.lead_source_secondary_oid = lss.oid or l2.lead_source_secondary_oid is null)
where ctrt.CONTRACT_STATUS = 'FinRejected'),0) As FinRejected,
ISNULL((select sum(1) from T_CONTRACT ctrt inner join T_CUST_PROJ cp on cp.contact_oid = ctrt.contact_oid and cp.proj_oid = ctrt.proj_oid inner join
T_LEAD l2 on cp.lead_oid = l2.oid and l2.lead_source_primary_oid = lsp.oid and (l2.lead_source_secondary_oid = lss.oid or l2.lead_source_secondary_oid is null)
where ctrt.CONTRACT_STATUS = 'Pending'),0) As Pending,
ISNULL((select sum(contract_total) from T_CONTRACT ctrt inner join T_CUST_PROJ cp on cp.contact_oid = ctrt.contact_oid and cp.proj_oid = ctrt.proj_oid inner join
T_LEAD l2 on cp.lead_oid = l2.oid and l2.lead_source_primary_oid = lsp.oid and (l2.lead_source_secondary_oid = lss.oid or l2.lead_source_secondary_oid is null)
where ctrt.CONTRACT_STATUS = 'Pending'),0) As PendingAmt,
ISNULL((select sum(1) from T_CONTRACT ctrt inner join T_CUST_PROJ cp on cp.contact_oid = ctrt.contact_oid and cp.proj_oid = ctrt.proj_oid inner join
T_LEAD l2 on cp.lead_oid = l2.oid and l2.lead_source_primary_oid = lsp.oid and (l2.lead_source_secondary_oid = lss.oid or l2.lead_source_secondary_oid is null)
where ctrt.CONTRACT_STATUS = 'Approved'),0) As NoApproved,
ISNULL((select sum(contract_total) from T_CONTRACT ctrt inner join T_CUST_PROJ cp on cp.contact_oid = ctrt.contact_oid and cp.proj_oid = ctrt.proj_oid inner join
T_LEAD l2 on cp.lead_oid = l2.oid and l2.lead_source_primary_oid = lsp.oid and (l2.lead_source_secondary_oid = lss.oid or l2.lead_source_secondary_oid is null)
where ctrt.CONTRACT_STATUS = 'Approved'),0) As Approved_NET
FROM T_LEAD_SOURCE_SECONDARY AS lss RIGHT OUTER JOIN
T_LEAD_SOURCE_PRIMARY AS lsp ON lsp.OID = lss.LEAD_SOURCE_PRIMARY_OID LEFT OUTER JOIN
T_LEAD AS l ON l.LEAD_SOURCE_PRIMARY_OID = lsp.oid and (l.lead_source_secondary_oid = lss.oid OR
l.LEAD_SOURCE_SECONDARY_OID IS NULL)
GROUP BY lsp.LEAD_SOURCE_DESC, lss.SECONDARY_SOURCE_DESCRIPTION, lsp.oid, lss.oid
ORDER BY lsp.LEAD_SOURCE_DESC, lss.SECONDARY_SOURCE_DESCRIPTION, lsp.oid, lss.oid
The trick to getting the PrimaryLeadSources who have no associated SecondaryLeadSources associated with them was as follows:
* RIGHT OUTER JOIN to t_lead_source_secondary (though maybe we should maybe swap the table order)
* LEFT OUTER JOIN to t_lead
* OR t_lead.lead_source_secondary_oid IS NULL
----------------------------------------------------------------------------
Below is the query that grabs data for PrimaryLeadSources that have no associated SecondaryLeadSource and actually returns the correct results:
SQL (Primary w/o Secondary):
select lsp.lead_source_desc as LeadSourcePrimary,
'No Entry' as LeadSourceSecondary,
ISNULL(count(l.oid), 0) AS Inquiries,
ISNULL((select count(*) from T_LEAD l2
where l2.lead_source_primary_oid = lsp.oid and l2.lead_inquiry_date is not null),0) as LeadsIssued, --used to be LeadSetDate in CE
ISNULL((select count(*) from T_LEAD_APPOINTMENT la inner join t_lead l2 on l2.oid = la.appt_lead_oid
where l2.lead_source_primary_oid = lsp.oid and lead_issued_date is not null),0) As ApptsSet, --AppointmentDate in CE
ISNULL((select count(*) from T_LEAD_APPOINTMENT la inner join t_lead l2 on l2.oid = la.appt_lead_oid
where l2.lead_source_primary_oid = lsp.oid and appt_confirmed = 'true'),0) As Confirmed,
ISNULL((select count(*) from T_LEAD_APPOINTMENT la inner join t_lead l2 on l2.oid = la.appt_lead_oid
where l2.lead_source_primary_oid = lsp.oid and APPT_EMP_OID is not null),0) As IssuedToSales,
ISNULL((select count(*) from T_LEAD_APPOINTMENT la inner join t_lead l2 on l2.oid = la.appt_lead_oid
where l2.lead_source_primary_oid = lsp.oid and appt_result_oid in (select oid from t_appt_result where APPT_RESULT_DEMO = 'TRUE' )),0) As Demo,
ISNULL((select count(*) from T_LEAD_APPOINTMENT la inner join t_lead l2 on l2.oid = la.appt_lead_oid
where l2.lead_source_primary_oid = lsp.oid and appt_result_oid in (select oid from t_appt_result where APPT_RESULT_SOLD = 'TRUE' )),0) As Sold,
ISNULL((select sum(contract_total) from T_CONTRACT ctrt inner join T_CUST_PROJ cp on cp.contact_oid = ctrt.contact_oid and cp.proj_oid = ctrt.proj_oid inner join
T_LEAD l2 on cp.lead_oid = l2.oid and l2.lead_source_primary_oid = lsp.oid ),0) as Gross,
ISNULL((select sum(1) from T_CONTRACT ctrt inner join T_CUST_PROJ cp on cp.contact_oid = ctrt.contact_oid and cp.proj_oid = ctrt.proj_oid inner join
T_LEAD l2 on cp.lead_oid = l2.oid and l2.lead_source_primary_oid = lsp.oid
where ctrt.CONTRACT_STATUS = 'Canceled'),0) As NoCanceled,
ISNULL((select sum(contract_total) from T_CONTRACT ctrt inner join T_CUST_PROJ cp on cp.contact_oid = ctrt.contact_oid and cp.proj_oid = ctrt.proj_oid inner join
T_LEAD l2 on cp.lead_oid = l2.oid and l2.lead_source_primary_oid = lsp.oid
where ctrt.CONTRACT_STATUS = 'Canceled'),0) As Canceled,
ISNULL((select sum(1) from T_CONTRACT ctrt inner join T_CUST_PROJ cp on cp.contact_oid = ctrt.contact_oid and cp.proj_oid = ctrt.proj_oid inner join
T_LEAD l2 on cp.lead_oid = l2.oid and l2.lead_source_primary_oid = lsp.oid
where ctrt.CONTRACT_STATUS = 'FinRejected'),0) As NoFinRej,
ISNULL((select sum(contract_total) from T_CONTRACT ctrt inner join T_CUST_PROJ cp on cp.contact_oid = ctrt.contact_oid and cp.proj_oid = ctrt.proj_oid inner join
T_LEAD l2 on cp.lead_oid = l2.oid and l2.lead_source_primary_oid = lsp.oid
where ctrt.CONTRACT_STATUS = 'FinRejected'),0) As FinRejected,
ISNULL((select sum(1) from T_CONTRACT ctrt inner join T_CUST_PROJ cp on cp.contact_oid = ctrt.contact_oid and cp.proj_oid = ctrt.proj_oid inner join
T_LEAD l2 on cp.lead_oid = l2.oid and l2.lead_source_primary_oid = lsp.oid
where ctrt.CONTRACT_STATUS = 'Pending'),0) As Pending,
ISNULL((select sum(contract_total) from T_CONTRACT ctrt inner join T_CUST_PROJ cp on cp.contact_oid = ctrt.contact_oid and cp.proj_oid = ctrt.proj_oid inner join
T_LEAD l2 on cp.lead_oid = l2.oid and l2.lead_source_primary_oid = lsp.oid
where ctrt.CONTRACT_STATUS = 'Pending'),0) As PendingAmt,
ISNULL((select sum(1) from T_CONTRACT ctrt inner join T_CUST_PROJ cp on cp.contact_oid = ctrt.contact_oid and cp.proj_oid = ctrt.proj_oid inner join
T_LEAD l2 on cp.lead_oid = l2.oid and l2.lead_source_primary_oid = lsp.oid
where ctrt.CONTRACT_STATUS = 'Approved'),0) As NoApproved,
ISNULL((select sum(contract_total) from T_CONTRACT ctrt inner join T_CUST_PROJ cp on cp.contact_oid = ctrt.contact_oid and cp.proj_oid = ctrt.proj_oid inner join
T_LEAD l2 on cp.lead_oid = l2.oid and l2.lead_source_primary_oid = lsp.oid
where ctrt.CONTRACT_STATUS = 'Approved'),0) As Approved_NET
from t_lead_source_primary as lsp INNER JOIN
T_LEAD AS l ON l.LEAD_SOURCE_PRIMARY_OID = lsp.oid
where lsp.oid not in (select lss.lead_source_primary_oid from t_lead_source_secondary as lss)
group by lsp.lead_source_desc, lsp.oid
Does anyone have any advice as to how I get results to return for said problem PrimaryLeadSources (with no SecondaryLeadSource)????
September 3, 2009 at 3:47 am
This query is a real nightmare...
First of all, I would say that this query will never perform well, since you are using several correlated subqueries in the select list.
I suggest that you buil one grouped subquery and put it in outer join.
One thing that I find useful to get clearer queries (but it could be clearer to me only) is always starting from the "main" table, in other words the table that contains the information I'm looking for and will always hold the data. Later I add the other tables using INNER JOIN or LEFT JOIN. I don't like RIGHT JOIN because it makes things a bit harder to understand.
I would rewrite it as:
SELECT lsp.LEAD_SOURCE_DESC AS LeadSourcePrimary,
ISNULL(lss.SECONDARY_SOURCE_DESCRIPTION, 'No Entry') AS LeadSourceSecondary,
ISNULL(COUNT(l.oid), 0) AS Inquiries
FROM T_LEAD_SOURCE_PRIMARY AS lsp
LEFT OUTER JOIN T_LEAD_SOURCE_SECONDARY AS lss
ON lsp.OID = lss.LEAD_SOURCE_PRIMARY_OID
LEFT OUTER JOIN T_LEAD AS l
ON l.LEAD_SOURCE_PRIMARY_OID = lsp.oid
AND l.lead_source_secondary_oid = lss.oid
GROUP BY lsp.LEAD_SOURCE_DESC,
lss.SECONDARY_SOURCE_DESCRIPTION,
lsp.oid,
lss.oid
ORDER BY lsp.LEAD_SOURCE_DESC,
lss.SECONDARY_SOURCE_DESCRIPTION,
lsp.oid,
lss.oid
I don't understand why you are using ON l.LEAD_SOURCE_PRIMARY_OID = lsp.oid and ( l.lead_source_secondary_oid = lss.oid OR l.LEAD_SOURCE_SECONDARY_OID IS NULL) as join condition. What is the desired output?
For what subqueries is concerned, I would write something like this:
;
WITH SubQry ( Name, primary_id, secondary_id, value )
AS ( select Name = 'LeadsIssued',
l2.lead_source_primary_oid,
l2.lead_source_secondary_oid,
count(*) as value
from T_LEAD l2
where l2.lead_inquiry_date is not null
group by l2.lead_source_primary_oid,
l2.lead_source_secondary_oid
UNION ALL
select Name = 'ApptsSet',
l2.lead_source_primary_oid,
l2.lead_source_secondary_oid,
count(*) as value
from T_LEAD_APPOINTMENT la
inner join t_lead l2
on l2.oid = la.appt_lead_oid
where lead_issued_date is not null
group by l2.lead_source_primary_oid,
l2.lead_source_secondary_oid
UNION ALL
select Name = 'IssuedToSales',
l2.lead_source_primary_oid,
l2.lead_source_secondary_oid,
count(*) as value
from T_LEAD_APPOINTMENT la
inner join t_lead l2
on l2.oid = la.appt_lead_oid
where APPT_EMP_OID is not null
group by l2.lead_source_primary_oid,
l2.lead_source_secondary_oid
UNION ALL
select Name = 'Demo',
l2.lead_source_primary_oid,
l2.lead_source_secondary_oid,
count(*) as value
from T_LEAD_APPOINTMENT la
inner join t_lead l2
on l2.oid = la.appt_lead_oid
where appt_result_oid in ( select oid
from t_appt_result
where APPT_RESULT_DEMO = 'TRUE' )
group by l2.lead_source_primary_oid,
l2.lead_source_secondary_oid
UNION ALL
select Name = 'Sold',
l2.lead_source_primary_oid,
l2.lead_source_secondary_oid,
count(*) as value
from T_LEAD_APPOINTMENT la
inner join t_lead l2
on l2.oid = la.appt_lead_oid
where appt_result_oid in ( select oid
from t_appt_result
where APPT_RESULT_SOLD = 'TRUE' )
group by l2.lead_source_primary_oid,
l2.lead_source_secondary_oid
UNION ALL
select Name = 'Gross',
l2.lead_source_primary_oid,
l2.lead_source_secondary_oid,
sum(contract_total) as value
from T_CONTRACT ctrt
inner join T_CUST_PROJ cp
on cp.contact_oid = ctrt.contact_oid
and cp.proj_oid = ctrt.proj_oid
inner join T_LEAD l2
on cp.lead_oid = l2.oid
group by l2.lead_source_primary_oid,
l2.lead_source_secondary_oid
UNION ALL
select Name = 'NoCanceled',
l2.lead_source_primary_oid,
l2.lead_source_secondary_oid,
sum(1) as value
from T_CONTRACT ctrt
inner join T_CUST_PROJ cp
on cp.contact_oid = ctrt.contact_oid
and cp.proj_oid = ctrt.proj_oid
inner join T_LEAD l2
on cp.lead_oid = l2.oid
where ctrt.CONTRACT_STATUS = 'Canceled'
group by l2.lead_source_primary_oid,
l2.lead_source_secondary_oid
UNION ALL
select Name = 'Canceled',
l2.lead_source_primary_oid,
l2.lead_source_secondary_oid,
sum(contract_total) as value
from T_CONTRACT ctrt
inner join T_CUST_PROJ cp
on cp.contact_oid = ctrt.contact_oid
and cp.proj_oid = ctrt.proj_oid
inner join T_LEAD l2
on cp.lead_oid = l2.oid
where ctrt.CONTRACT_STATUS = 'Canceled'
group by l2.lead_source_primary_oid,
l2.lead_source_secondary_oid
UNION ALL
select Name = 'NoFinRej',
l2.lead_source_primary_oid,
l2.lead_source_secondary_oid,
sum(1) as value
from T_CONTRACT ctrt
inner join T_CUST_PROJ cp
on cp.contact_oid = ctrt.contact_oid
and cp.proj_oid = ctrt.proj_oid
inner join T_LEAD l2
on cp.lead_oid = l2.oid
where ctrt.CONTRACT_STATUS = 'FinRejected'
group by l2.lead_source_primary_oid,
l2.lead_source_secondary_oid
UNION ALL
select Name = 'FinRejected',
l2.lead_source_primary_oid,
l2.lead_source_secondary_oid,
sum(contract_total) as value
from T_CONTRACT ctrt
inner join T_CUST_PROJ cp
on cp.contact_oid = ctrt.contact_oid
and cp.proj_oid = ctrt.proj_oid
inner join T_LEAD l2
on cp.lead_oid = l2.oid
where ctrt.CONTRACT_STATUS = 'FinRejected'
group by l2.lead_source_primary_oid,
l2.lead_source_secondary_oid
UNION ALL
select Name = 'Pending',
l2.lead_source_primary_oid,
l2.lead_source_secondary_oid,
sum(1) as value
from T_CONTRACT ctrt
inner join T_CUST_PROJ cp
on cp.contact_oid = ctrt.contact_oid
and cp.proj_oid = ctrt.proj_oid
inner join T_LEAD l2
on cp.lead_oid = l2.oid
where ctrt.CONTRACT_STATUS = 'Pending'
group by l2.lead_source_primary_oid,
l2.lead_source_secondary_oid
UNION ALL
select Name = 'PendingAmt',
l2.lead_source_primary_oid,
l2.lead_source_secondary_oid,
sum(contract_total) as value
from T_CONTRACT ctrt
inner join T_CUST_PROJ cp
on cp.contact_oid = ctrt.contact_oid
and cp.proj_oid = ctrt.proj_oid
inner join T_LEAD l2
on cp.lead_oid = l2.oid
where ctrt.CONTRACT_STATUS = 'Pending'
group by l2.lead_source_primary_oid,
l2.lead_source_secondary_oid
UNION ALL
select Name = 'NoApproved',
l2.lead_source_primary_oid,
l2.lead_source_secondary_oid,
sum(1) as value
from T_CONTRACT ctrt
inner join T_CUST_PROJ cp
on cp.contact_oid = ctrt.contact_oid
and cp.proj_oid = ctrt.proj_oid
inner join T_LEAD l2
on cp.lead_oid = l2.oid
where ctrt.CONTRACT_STATUS = 'Approved'
group by l2.lead_source_primary_oid,
l2.lead_source_secondary_oid
UNION ALL
select Name = 'Approved_NET',
l2.lead_source_primary_oid,
l2.lead_source_secondary_oid,
sum(contract_total) as value
from T_CONTRACT ctrt
inner join T_CUST_PROJ cp
on cp.contact_oid = ctrt.contact_oid
and cp.proj_oid = ctrt.proj_oid
inner join T_LEAD l2
on cp.lead_oid = l2.oid
where ctrt.CONTRACT_STATUS = 'Approved'
group by l2.lead_source_primary_oid,
l2.lead_source_secondary_oid
)
SELECT lsp.LEAD_SOURCE_DESC AS LeadSourcePrimary,
ISNULL(lss.SECONDARY_SOURCE_DESCRIPTION, 'No Entry') AS LeadSourceSecondary,
ISNULL(COUNT(l.oid), 0) AS Inquiries,
S1.Value AS LeadsIssued,
S2.Value AS ApptsSet,
S3.Value AS IssuedToSales,
S4.Value AS Demo,
S5.Value AS Sold,
S6.Value AS Gross,
S7.Value AS NoCanceled,
S8.Value AS Canceled,
S9.Value AS NoFinRej,
S10.Value AS FinRejected,
S11.Value AS Pending,
S12.Value AS PendingAmt,
S13.Value AS NoApproved,
S14.Value AS Approved_NET
FROM T_LEAD_SOURCE_PRIMARY AS lsp
LEFT OUTER JOIN T_LEAD_SOURCE_SECONDARY AS lss
ON lsp.OID = lss.LEAD_SOURCE_PRIMARY_OID
LEFT OUTER JOIN T_LEAD AS l
ON l.LEAD_SOURCE_PRIMARY_OID = lsp.oid
AND l.lead_source_secondary_oid = lss.oid
LEFT OUTER JOIN SubQry AS S1
ON S1.primary_id = lsp.oid
AND ISNULL(S1.secondary_id, lss.oid) = lss.oid
AND S1.Name = 'LeadsIssued'
LEFT OUTER JOIN SubQry AS S2
ON S2.primary_id = lsp.oid
AND ISNULL(S2.secondary_id, lss.oid) = lss.oid
AND S2.Name = 'ApptsSet'
LEFT OUTER JOIN SubQry AS S3
ON S3.primary_id = lsp.oid
AND ISNULL(S3.secondary_id, lss.oid) = lss.oid
AND S3.Name = 'IssuedToSales'
LEFT OUTER JOIN SubQry AS S4
ON S4.primary_id = lsp.oid
AND ISNULL(S4.secondary_id, lss.oid) = lss.oid
AND S4.Name = 'Demo'
LEFT OUTER JOIN SubQry AS S5
ON S5.primary_id = lsp.oid
AND ISNULL(S5.secondary_id, lss.oid) = lss.oid
AND S5.Name = 'Sold'
LEFT OUTER JOIN SubQry AS S6
ON S6.primary_id = lsp.oid
AND ISNULL(S6.secondary_id, lss.oid) = lss.oid
AND S6.Name = 'Gross'
LEFT OUTER JOIN SubQry AS S7
ON S7.primary_id = lsp.oid
AND ISNULL(S7.secondary_id, lss.oid) = lss.oid
AND S7.Name = 'NoCanceled'
LEFT OUTER JOIN SubQry AS S8
ON S8.primary_id = lsp.oid
AND ISNULL(S8.secondary_id, lss.oid) = lss.oid
AND S8.Name = 'Canceled'
LEFT OUTER JOIN SubQry AS S9
ON S9.primary_id = lsp.oid
AND ISNULL(S9.secondary_id, lss.oid) = lss.oid
AND S9.Name = 'NoFinRej'
LEFT OUTER JOIN SubQry AS S10
ON S10.primary_id = lsp.oid
AND ISNULL(S10.secondary_id, lss.oid) = lss.oid
AND S10.Name = 'FinRejected'
LEFT OUTER JOIN SubQry AS S11
ON S11.primary_id = lsp.oid
AND ISNULL(S11.secondary_id, lss.oid) = lss.oid
AND S11.Name = 'Pending'
LEFT OUTER JOIN SubQry AS S12
ON S12.primary_id = lsp.oid
AND ISNULL(S12.secondary_id, lss.oid) = lss.oid
AND S12.Name = 'PendingAmt'
LEFT OUTER JOIN SubQry AS S13
ON S13.primary_id = lsp.oid
AND ISNULL(S13.secondary_id, lss.oid) = lss.oid
AND S13.Name = 'NoApproved'
LEFT OUTER JOIN SubQry AS S14
ON S14.primary_id = lsp.oid
AND ISNULL(S14.secondary_id, lss.oid) = lss.oid
AND S14.Name = 'Approved_NET'
GROUP BY lsp.LEAD_SOURCE_DESC,
lss.SECONDARY_SOURCE_DESCRIPTION,
lsp.oid,
lss.oid
ORDER BY lsp.LEAD_SOURCE_DESC,
lss.SECONDARY_SOURCE_DESCRIPTION,
lsp.oid,
lss.oid
I don't have your tables, so it's hard to say if it performs better or worse.
Give it a try.
-- Gianluca Sartori
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply