September 8, 2015 at 11:32 am
I inherited the following query which is used to create a SSRS report. It is used for the a financial organization to count and sum up payments by each individual collector and also the aggregates of each collectors. I am trying to see how we can write this query more efficiently, some of the cross joins used are not making a lot of sense to me. I also wanted to see If I can create a SP where the user enter the collectors name and all the loanID assigned to that collector are displayed, also the count and sum of the payments. there are total 6 collectors, in the below code I have only mentioned 1.
Any ideas or suggestions would be highly appreciated it.
SELECT
(SELECT sum (h.TransactionAmt)
FROM dbo.Company CROSS JOIN dbo.CollectorCodeHistory AS CC
INNER JOIN dbo.History AS H ON CC.LoanID = H.LoanID
AND CC.DateAdded <= H.SysDateTime
AND ISNULL(CC.EndDate, GETDATE()) >= H.SysDateTime
AND H.TransactionCode BETWEEN 100 AND 340
AND H.TransactionCode <> 140
AND H.TransactionAmt <> 0
INNER JOIN dbo.TransactionCode ON H.TransactionCode = dbo.TransactionCode.Code
INNER JOIN dbo.S_ReversalFlag ON H.ReversalFlag = dbo.S_ReversalFlag.Code
INNER JOIN dbo.S_MoneyType ON H.MoneyType = dbo.S_MoneyType.Code
Where MONTH(TransactionDate) = MONTH(GETDATE()) and YEAR(TransactionDate) = YEAR(GETDATE())
AND cc.collectorcode = '3') As David
,(SELECT Count (h.TransactionAmt)
FROM dbo.Company CROSS JOIN dbo.CollectorCodeHistory AS CC
INNER JOIN dbo.History AS H ON CC.LoanID = H.LoanID
AND CC.DateAdded <= H.SysDateTime
AND ISNULL(CC.EndDate, GETDATE()) >= H.SysDateTime
AND H.TransactionCode BETWEEN 100 AND 340
AND H.TransactionCode <> 140
AND H.TransactionAmt > 0 INNER JOIN dbo.TransactionCode ON H.TransactionCode = dbo.TransactionCode.Code
INNER JOIN dbo.S_ReversalFlag ON H.ReversalFlag = dbo.S_ReversalFlag.Code
INNER JOIN dbo.S_MoneyType ON H.MoneyType = dbo.S_MoneyType.Code Where
MONTH(TransactionDate) = MONTH(GETDATE()) and YEAR(TransactionDate) = YEAR(GETDATE())
AND cc.collectorcode = '3') As DavidCt
, H.LoanID
, CC.CollectorCode AS CollectorCode
, CC.CollectorName AS CollectorName
, H.TransactionAmt
, dbo.TransactionCode.Description AS TranCodeDesc
, H.TransactionDate
, dbo.S_ReversalFlag.Description AS ReversalFlagDesc
, H.MoneyType
, dbo.S_MoneyType.Description AS MoneyTypeDesc
FROM dbo.Company CROSS JOIN dbo.CollectorCodeHistory AS CC
INNER JOIN dbo.History AS H ON CC.LoanID = H.LoanID
AND CC.DateAdded <= H.SysDateTime AND ISNULL(CC.EndDate, GETDATE()) >= H.SysDateTime
AND H.TransactionCode BETWEEN 100 AND 340
AND H.TransactionCode <> 140 AND H.TransactionAmt <> 0
INNER JOIN dbo.TransactionCode ON H.TransactionCode = dbo.TransactionCode.Code
INNER JOIN dbo.S_ReversalFlag ON H.ReversalFlag = dbo.S_ReversalFlag.Code
INNER JOIN dbo.S_MoneyType ON H.MoneyType = dbo.S_MoneyType.Code
WHERE MONTH(TransactionDate) = MONTH(GETDATE()) and YEAR(TransactionDate) = YEAR(GETDATE())
ORDER BY CollectorCode, TransactionDate
September 8, 2015 at 12:16 pm
The subqueries make no sense as they're not tied to anything from the outside. You're probably after something simpler as this:
DECLARE @Start datetime,
@End datetime;
SELECT @Start = DATEADD( MM, DATEDIFF( MM, 0, GETDATE()), 0),
@End = DATEADD( MM, DATEDIFF( MM, 0, GETDATE()) + 1, 0)
SELECT SUM(H.TransactionAmt) AS David,
COUNT(H.TransactionAmt) AS DavidCt,
H.LoanID,
CC.CollectorCode AS CollectorCode,
CC.CollectorName AS CollectorName,
H.TransactionAmt,
T.Description AS TranCodeDesc,
H.TransactionDate,
R.Description AS ReversalFlagDesc,
H.MoneyType,
M.Description AS MoneyTypeDesc
FROM dbo.Company
CROSS JOIN dbo.CollectorCodeHistory AS CC
INNER JOIN dbo.History AS H ON CC.LoanID = H.LoanID
AND CC.DateAdded <= H.SysDateTime
AND (CC.EndDate >= H.SysDateTime OR (CC.EndDate IS NULL AND GETDATE() >= H.SysDateTime))
AND H.TransactionCode BETWEEN 100 AND 340
AND H.TransactionCode <> 140
AND H.TransactionAmt <> 0
INNER JOIN dbo.TransactionCode T ON H.TransactionCode = T.Code
INNER JOIN dbo.S_ReversalFlag R ON H.ReversalFlag = R.Code
INNER JOIN dbo.S_MoneyType M ON H.MoneyType = M.Code
WHERE TransactionDate >= @Start
AND TransactionDate < @End
GROUP BY H.LoanID,
CC.CollectorCode,
CC.CollectorName,
H.TransactionAmt,
T.Description,
H.TransactionDate,
R.Description,
H.MoneyType,
M.Description
ORDER BY CollectorCode,
TransactionDate
This code removes the subqueries and functions in the WHERE and JOIN conditions. I can't suggest anything about the CROSS JOINS because I have no idea of your database design.
This is merely a guess as we would need more information. To know what do we need, please read the link in my signature or this one: http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
And for performance read this one as well: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 8, 2015 at 2:25 pm
I don't see anywhere that the dbo.Company table is being used, so I removed it.
I also removed the multiple JOINs, although I did not add a GROUP BY because it looked to me like you wanted to list overall totals with detail/line data, so I moved the totals into a subquery.
Naturally I could be right or wrong on any/all of this since I don't know the details of your tables.
SELECT
H_Totals.David
, H_Totals.DavidCt
, H.LoanID
, CC.CollectorCode AS CollectorCode
, CC.CollectorName AS CollectorName
, H.TransactionAmt
, dbo.TransactionCode.Description AS TranCodeDesc
, H.TransactionDate
, dbo.S_ReversalFlag.Description AS ReversalFlagDesc
, H.MoneyType
, dbo.S_MoneyType.Description AS MoneyTypeDesc
FROM dbo.History AS H
INNER JOIN (
SELECT H.LoanID
, sum (h.TransactionAmt) AS David
, Count (h.TransactionAmt) AS DavidCt
FROM dbo.History AS H
INNER JOIN dbo.CollectorCodeHistory AS CC ON CC.LoanID = H.LoanID
AND CC.DateAdded <= H.SysDateTime
AND ISNULL(CC.EndDate, GETDATE()) >= H.SysDateTime
WHERE
H.TransactionCode BETWEEN 100 AND 340
AND H.TransactionCode <> 140
AND H.TransactionAmt <> 0
AND H.TransactionDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND H.TransactionDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
GROUP BY H.LoanID
) AS H_Totals ON H_Totals.LoanID = H.LoanID
INNER JOIN dbo.TransactionCode ON H.TransactionCode = dbo.TransactionCode.Code
INNER JOIN dbo.S_ReversalFlag ON H.ReversalFlag = dbo.S_ReversalFlag.Code
INNER JOIN dbo.S_MoneyType ON H.MoneyType = dbo.S_MoneyType.Code
WHERE
H.TransactionCode BETWEEN 100 AND 340
AND H.TransactionCode <> 140
AND H.TransactionAmt <> 0
AND H.TransactionDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND H.TransactionDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
ORDER BY CollectorCode, TransactionDate
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".
September 8, 2015 at 2:28 pm
Thanks Luis,
The reason for the sub query would be to reference the collector code, for example David is cc.collectorcode = '3'
September 8, 2015 at 2:33 pm
Thanks Scott.
I am getting the following error:
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "CC.CollectorCode" could not be bound.
Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "CC.CollectorName" could not be bound.
September 8, 2015 at 2:37 pm
Quite right, sorry, left that table out of the outer query. Also forgot to check the CollectorCode in the inner query, I've added that back as well:
SELECT
H_Totals.David
, H_Totals.DavidCt
, H.LoanID
, CC.CollectorCode AS CollectorCode
, CC.CollectorName AS CollectorName
, H.TransactionAmt
, dbo.TransactionCode.Description AS TranCodeDesc
, H.TransactionDate
, dbo.S_ReversalFlag.Description AS ReversalFlagDesc
, H.MoneyType
, dbo.S_MoneyType.Description AS MoneyTypeDesc
FROM dbo.History AS H
INNER JOIN (
SELECT H.LoanID
, sum (h.TransactionAmt) AS David
, Count (h.TransactionAmt) AS DavidCt
FROM dbo.History AS H
INNER JOIN dbo.CollectorCodeHistory AS CC ON CC.LoanID = H.LoanID
AND CC.DateAdded <= H.SysDateTime
AND ISNULL(CC.EndDate, GETDATE()) >= H.SysDateTime
WHERE
H.TransactionCode BETWEEN 100 AND 340
AND H.TransactionCode <> 140
AND H.TransactionAmt <> 0
AND H.TransactionDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND H.TransactionDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
AND CC.CollectorCode = '3'
GROUP BY H.LoanID
) AS H_Totals ON H_Totals.LoanID = H.LoanID
INNER JOIN dbo.CollectorCodeHistory AS CC ON CC.LoanID = H.LoanID
AND CC.DateAdded <= H.SysDateTime
AND ISNULL(CC.EndDate, GETDATE()) >= H.SysDateTime
INNER JOIN dbo.TransactionCode ON H.TransactionCode = dbo.TransactionCode.Code
INNER JOIN dbo.S_ReversalFlag ON H.ReversalFlag = dbo.S_ReversalFlag.Code
INNER JOIN dbo.S_MoneyType ON H.MoneyType = dbo.S_MoneyType.Code
WHERE
H.TransactionCode BETWEEN 100 AND 340
AND H.TransactionCode <> 140
AND H.TransactionAmt <> 0
AND H.TransactionDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND H.TransactionDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
ORDER BY CollectorCode, TransactionDate
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".
September 8, 2015 at 4:46 pm
Thank you guys for all your help !!!
Would the following 2 statements fetch the same result?
1)AND (CC.EndDate >= H.SysDateTime OR (CC.EndDate IS NULL AND GETDATE() >= H.SysDateTime))
2)AND ISNULL(CC.EndDate, GETDATE()) >= H.SysDateTime
Because I am getting slightly different results
September 9, 2015 at 8:53 am
SQLPain (9/8/2015)
Thank you guys for all your help !!!Would the following 2 statements fetch the same result?
1)AND (CC.EndDate >= H.SysDateTime OR (CC.EndDate IS NULL AND GETDATE() >= H.SysDateTime))
2)AND ISNULL(CC.EndDate, GETDATE()) >= H.SysDateTime
Because I am getting slightly different results
Something else should be affecting the results as those conditions should return the same results.
CREATE TABLE #TestDates(
Description varchar(100),
EndDate datetime,
SysDateTime datetime)
INSERT INTO #TestDates
VALUES
('EndDate Greater than SysDateTime & Lower Than Today', '20150901', '20150808'),
('EndDate Lower than SysDateTime & Lower Than Today', '20150901', '20151010'),
('EndDate Greater than SysDateTime & Greater Than Today', '20151111', '20151010'),
('EndDate Lower than SysDateTime & Greater Than Today', '20151111', '20151212'),
('EndDate Null & SysDateTime Lower Than Today', NULL, '20150808'),
('EndDate Null & SysDateTime Greater Than Today', NULL, '20151212');
SELECT *
FROM #TestDates
WHERE (EndDate >= SysDateTime OR (EndDate IS NULL AND GETDATE() >= SysDateTime));
SELECT *
FROM #TestDates
WHERE ISNULL(EndDate, GETDATE()) >= SysDateTime;
GO
DROP TABLE #TestDates;
September 9, 2015 at 9:42 am
Thank you Luis.
September 9, 2015 at 1:56 pm
Still not sure why am I missing few records when I am running the new query.
OLD Query.
SELECT
(SELECT Count (h.TransactionAmt)
FROM dbo.Company
CROSS JOIN dbo.CollectorCodeHistory AS CC
INNER JOIN dbo.History AS H ON CC.LoanID = H.LoanID
AND CC.DateAdded <= H.SysDateTime AND ISNULL(CC.EndDate, GETDATE()) >= H.SysDateTime
AND H.TransactionCode BETWEEN 100 AND 340
AND H.TransactionCode <> 140
AND H.TransactionAmt > 0
INNER JOIN dbo.TransactionCode ON H.TransactionCode = dbo.TransactionCode.Code
INNER JOIN dbo.S_ReversalFlag ON H.ReversalFlag = dbo.S_ReversalFlag.Code
INNER JOIN dbo.S_MoneyType ON H.MoneyType = dbo.S_MoneyType.Code
Where MONTH(TransactionDate) = MONTH(GETDATE()) and YEAR(TransactionDate) = YEAR(GETDATE())
and cc.collectorcode = '7') As JuanCt
, H.LoanID
, CC.CollectorCode AS CollectorCode
, CC.CollectorName AS CollectorName
, H.TransactionAmt
, dbo.TransactionCode.Description AS TranCodeDesc
, H.TransactionDate
, dbo.S_ReversalFlag.Description AS ReversalFlagDesc
, H.MoneyType
, dbo.S_MoneyType.Description AS MoneyTypeDesc
FROM dbo.Company CROSS JOIN dbo.CollectorCodeHistory AS CC
INNER JOIN dbo.History AS H ON CC.LoanID = H.LoanID AND CC.DateAdded <= H.SysDateTime AND ISNULL(CC.EndDate, GETDATE()) >= H.SysDateTime
AND H.TransactionCode BETWEEN 100 AND 340
AND H.TransactionCode <> 140 AND H.TransactionAmt <> 0
INNER JOIN dbo.TransactionCode ON H.TransactionCode = dbo.TransactionCode.Code
INNER JOIN dbo.S_ReversalFlag ON H.ReversalFlag = dbo.S_ReversalFlag.Code
INNER JOIN dbo.S_MoneyType ON H.MoneyType = dbo.S_MoneyType.Code
Where MONTH(TransactionDate) = MONTH(GETDATE()) and YEAR(TransactionDate) = YEAR(GETDATE())
AND CC.CollectorCode = '7'
order by TransactionDate
NEW Query
ALTER PROC USP_PaymentsByCollector
(
@CollectorName AS varchar(20) = NULL,
@CollectorCode AS varchar(20) = NULL,
@LoanID AS varchar (20) = NULL
)
AS
BEGIN
SELECT
SUM(H.TransactionAmt) AS Damon
,COUNT(H.TransactionAmt) AS TransactionCT
,H.LoanID
,CC.CollectorCode AS CollectorCode
,CC.CollectorName AS CollectorName
,H.TransactionAmt
,T.Description AS TranCodeDesc
,H.TransactionDate
,R.Description AS ReversalFlagDesc
,H.MoneyType
,M.Description AS MoneyTypeDesc
FROM dbo.Company
CROSS JOIN dbo.CollectorCodeHistory AS CC
INNER JOIN dbo.History AS H ON CC.LoanID = H.LoanID
AND CC.DateAdded <= H.SysDateTime
AND (CC.EndDate >= H.SysDateTime OR (CC.EndDate IS NULL AND GETDATE() >= H.SysDateTime))
AND H.TransactionCode BETWEEN 100 AND 340
AND H.TransactionCode <> 140
AND H.TransactionAmt <> 0
INNER JOIN dbo.TransactionCode T ON H.TransactionCode = T.Code
INNER JOIN dbo.S_ReversalFlag R ON H.ReversalFlag = R.Code
INNER JOIN dbo.S_MoneyType M ON H.MoneyType = M.Code
WHERE H.TransactionDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
AND H.TransactionDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
AND (@CollectorName IS NULL OR CC.CollectorName like '%' + @CollectorName + '%')
AND (@CollectorCode IS NULL OR CC.CollectorCode = @CollectorCode)
AND (@LoanID IS NULL OR H.LoanID LIKE '%' + @LoanID + '%')
GROUP BY H.LoanID
,CC.CollectorCode
,CC.CollectorName
,H.TransactionAmt
,T.Description
,H.TransactionDate
,R.Description
,H.MoneyType
,M.Description
ORDER BY TransactionDate
END
EXEC USP_PaymentsByCollector @CollectorCode = '7'
Can somebody please review
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply