October 16, 2015 at 11:14 am
Can anyone let me know what I am doing wrong here, I have an inline query that I am trying to convert it into JOIN, results are not coming out the same:
Original query:
SELECT distinct
(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '3') As Dcount
My query:
SELECT DISTINCT
DCT
FROM Q_C_Main_Sub1 Q1
LEFT JOIN (SELECT Count(DISTINCT q1.LoanID) as DCT,
Q1.LoanID FROM Q_C_Main_Sub1 AS Q1
WHERE (DAY(Q1.LastWorked) = DAY(GETDATE())
and MONTH(Q1.LastWorked) = MONTH(GETDATE())
and YEAR(Q1.LastWorked) = YEAR(GETDATE()) AND Q1.PrimStat = '1' and Q1.Collector = '3')
GROUP BY q1.LoanID) AS DCT ON Q1.LoanID = DCT.LoanID
October 16, 2015 at 11:38 am
Both queries aren't equivalent. The first is a single value and the second one will show one value per LoanID. In both queries, there's no reason to use a subquery.
And you should change your conditions to make your query SARGable.
SELECT COUNT (DISTINCT LoanID)
FROM Q_C_Main_Sub1
WHERE LastWorked >= DATEADD(dd, DATEDIFF( dd, 0, GETDATE()),0)
AND LastWorked < DATEADD(dd, DATEDIFF( dd, 0, GETDATE()) + 1,0)
AND PrimStat = '1'
AND Collector = '3'
October 16, 2015 at 11:54 am
Thanks Luis, that works. the reason I wanted to do joins is because there are 8 collectors, and I wanted to get rid of inline queries, If I follow this approach I have to use the following code 8 times in select statement and that would also be inline queries.
October 16, 2015 at 11:58 am
SQLPain (10/16/2015)
Thanks Luis, that works. the reason I wanted to do joins is because there are 8 collectors, and I wanted to get rid of inline queries, If I follow this approach I have to use the following code 8 times in select statement and that would also be inline queries.
Not sure what your problem is, I don't have enough information to provide an advice. If you can share more details, we could give you better help.
October 16, 2015 at 12:04 pm
Thanks Luis,
I am trying to fine tune this:
SELECT distinct
(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '3') As DT
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '5') As AT
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '6') As CT
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '7') As JT
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '8') As AT
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '9') As PT
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '10') As DT
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '11') As RT
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '13') As IT
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '14') As JT
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1') As TotalCT
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE PromisedByDate >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0) and MONTH(PromisedByDate) = MONTH(GETDATE()) and PrimStat = '1' and Collector = '3') As DP2P
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE PromisedByDate >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0) and MONTH(PromisedByDate) = MONTH(GETDATE()) and PrimStat = '1' and Collector = '5') As AP2P
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE PromisedByDate >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0) and MONTH(PromisedByDate) = MONTH(GETDATE()) and PrimStat = '1' and Collector = '6') As CP2P
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE PromisedByDate >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0) and MONTH(PromisedByDate) = MONTH(GETDATE()) and PrimStat = '1' and Collector = '7') As JP2P
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE PromisedByDate >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0) and MONTH(PromisedByDate) = MONTH(GETDATE()) and PrimStat = '1' and Collector = '8') As AP2P
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE PromisedByDate >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0) and MONTH(PromisedByDate) = MONTH(GETDATE()) and PrimStat = '1' and Collector = '9') As PP2P
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE PromisedByDate >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0) and MONTH(PromisedByDate) = MONTH(GETDATE()) and PrimStat = '1' and Collector = '10') As DP2P
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE PromisedByDate >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0) and MONTH(PromisedByDate) = MONTH(GETDATE()) and PrimStat = '1' and Collector = '11') As RP2P
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE PromisedByDate >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0) and MONTH(PromisedByDate) = MONTH(GETDATE()) and PrimStat = '1' and Collector = '13') As IP2P
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE PromisedByDate >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0) and MONTH(PromisedByDate) = MONTH(GETDATE()) and PrimStat = '1' and Collector = '14') As JP2P
,(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE PromisedByDate >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0) and MONTH(PromisedByDate) = MONTH(GETDATE()) and PrimStat = '1') As TotalP2P
FROM Q_C_Main_Sub1
October 16, 2015 at 12:06 pm
I think I understand. You can get all the collectors from a single query, but you have to include the collector in a GROUP BY:
SELECT Collector, COUNT(DISTINCT LoanID) AS Loan_Count
FROM Q_C_Main_Sub1
WHERE
LastWorked >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND
LastWorked < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0) AND
PrimStat = '1'
GROUP BY Collector
ORDER BY Collector
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 16, 2015 at 12:20 pm
You might need something like this:
WITH Set1 AS(
SELECT COUNT (DISTINCT CASE WHEN Collector = '3' THEN LoanID END) DT,
COUNT (DISTINCT CASE WHEN Collector = '5' THEN LoanID END) AT,
COUNT (DISTINCT CASE WHEN Collector = '6' THEN LoanID END) CT,
COUNT (DISTINCT CASE WHEN Collector = '7' THEN LoanID END) JT,
COUNT (DISTINCT CASE WHEN Collector = '8' THEN LoanID END) AT,
COUNT (DISTINCT CASE WHEN Collector = '9' THEN LoanID END) PT,
COUNT (DISTINCT CASE WHEN Collector = '10' THEN LoanID END) DT,
COUNT (DISTINCT CASE WHEN Collector = '11' THEN LoanID END) RT,
COUNT (DISTINCT CASE WHEN Collector = '13' THEN LoanID END) IT,
COUNT (DISTINCT CASE WHEN Collector = '14' THEN LoanID END) JT
FROM Q_C_Main_Sub1
WHERE DAY(LastWorked) = DAY(GETDATE())
AND MONTH(LastWorked) = MONTH(GETDATE())
AND YEAR(LastWorked) = YEAR(GETDATE())
AND PrimStat = '1'
AND Collector IN( '3','5','6','7','8','9','10','11','13','14')
),
Set2 AS(
SELECT COUNT (DISTINCT CASE WHEN Collector = '3' THEN LoanID END) DP2P,
COUNT (DISTINCT CASE WHEN Collector = '5' THEN LoanID END) AP2P,
COUNT (DISTINCT CASE WHEN Collector = '6' THEN LoanID END) CP2P,
COUNT (DISTINCT CASE WHEN Collector = '7' THEN LoanID END) JP2P,
COUNT (DISTINCT CASE WHEN Collector = '8' THEN LoanID END) AP2P,
COUNT (DISTINCT CASE WHEN Collector = '9' THEN LoanID END) PP2P,
COUNT (DISTINCT CASE WHEN Collector = '10' THEN LoanID END) DP2P,
COUNT (DISTINCT CASE WHEN Collector = '11' THEN LoanID END) RP2P,
COUNT (DISTINCT CASE WHEN Collector = '13' THEN LoanID END) IP2P,
COUNT (DISTINCT CASE WHEN Collector = '14' THEN LoanID END) JP2P,
COUNT (DISTINCT LoanID) AS TotalP2P
FROM Q_C_Main_Sub1
WHERE PromisedByDate >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0)
AND MONTH(PromisedByDate) = MONTH(GETDATE())
AND PrimStat = '1'
AND Collector IN( '3','5','6','7','8','9','10','11','13','14')
)
SELECT *
FROM Set1, Set2
Half the code and less reads on the table. If TotalP2P gives a different result, remove the last filter in that query.
October 16, 2015 at 12:33 pm
Wow Luis, that's a masterpiece. !!!!
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply