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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy