October 2, 2012 at 10:33 am
I need this in a report to pull in a specific range, My tsql skills are still growing and never heard of derived table until i researched this morning. The developer passed this query onto me to automate in SSRS. The report pulls from beginning of the year to current date
Any help is appreciated Thanks in advance
Here is the what I have
SELECT TOP (100) PERCENT tblObject_1.Name AS Agent, COUNT(dbo.tblLoan.Object_ID) AS Count, SUM(dbo.tblProcessingFile.LoanAmount_MIP_FF) AS Volume,
derivedtbl_1.Purchase, derivedtbl_2.Refi
FROM dbo.tblLoan INNER JOIN
dbo.tblObject ON dbo.tblLoan.Object_ID = dbo.tblObject.Object_ID INNER JOIN
dbo.tblProcessingFile ON dbo.tblLoan.Object_ID = dbo.tblProcessingFile.Loan_ID INNER JOIN
dbo.tblObject AS tblObject_1 ON dbo.tblLoan.ContactOwnerID = tblObject_1.Object_ID INNER JOIN
dbo.tblDomain ON tblObject_1.Domain_ID = dbo.tblDomain.Object_ID INNER JOIN
dbo.tblObject AS tblObject_2 ON dbo.tblLoan.Source_ID = tblObject_2.Object_ID LEFT OUTER JOIN
(SELECT tblLoan_2.ContactOwnerID, COUNT(tblLoan_2.Object_ID) AS Refi
FROM dbo.tblLoan AS tblLoan_2 INNER JOIN
dbo.tblProcessingFile AS tblProcessingFile_2 ON tblLoan_2.Object_ID = tblProcessingFile_2.Loan_ID INNER JOIN
dbo.enumLoanPurpose AS enumLoanPurpose_1 ON tblProcessingFile_2.enumLoanPurpose = enumLoanPurpose_1.enumLoanPurposeID INNER JOIN
dbo.tblObject AS tblObject_4 ON tblLoan_2.ContactOwnerID = tblObject_4.Object_ID INNER JOIN
dbo.tblDomain AS tblDomain_2 ON tblObject_4.Domain_ID = tblDomain_2.Object_ID
WHERE (tblProcessingFile_2.Funded BETWEEN CONVERT(DATETIME, '2012-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2012-09-07 00:00:00', 102)) AND
(enumLoanPurpose_1.LoanPurpose LIKE '%refi%') AND (tblDomain_2.DomainCategoryTypeID = 2)
GROUP BY tblLoan_2.ContactOwnerID) AS derivedtbl_2 ON tblObject_1.Object_ID = derivedtbl_2.ContactOwnerID LEFT OUTER JOIN
(SELECT tblLoan_1.ContactOwnerID, COUNT(tblLoan_1.Object_ID) AS Purchase
FROM dbo.tblLoan AS tblLoan_1 INNER JOIN
dbo.tblProcessingFile AS tblProcessingFile_1 ON tblLoan_1.Object_ID = tblProcessingFile_1.Loan_ID INNER JOIN
dbo.enumLoanPurpose ON tblProcessingFile_1.enumLoanPurpose = dbo.enumLoanPurpose.enumLoanPurposeID INNER JOIN
dbo.tblObject AS tblObject_3 ON tblLoan_1.ContactOwnerID = tblObject_3.Object_ID INNER JOIN
dbo.tblDomain AS tblDomain_1 ON tblObject_3.Domain_ID = tblDomain_1.Object_ID
WHERE (tblProcessingFile_1.Funded BETWEEN CONVERT(DATETIME, '2012-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2012-09-07 00:00:00', 102)) AND
(dbo.enumLoanPurpose.LoanPurpose LIKE '%purchase%') AND (tblDomain_1.DomainCategoryTypeID = 2)
GROUP BY tblLoan_1.ContactOwnerID) AS derivedtbl_1 ON tblObject_1.Object_ID = derivedtbl_1.ContactOwnerID
WHERE (dbo.tblDomain.DomainCategoryTypeID = 2) AND (dbo.tblProcessingFile.Funded BETWEEN CONVERT(DATETIME, '2012-01-01 00:00:00', 102) AND
CONVERT(DATETIME, '2012-09-07 00:00:00', 102))
GROUP BY tblObject_1.Name, derivedtbl_1.Purchase, derivedtbl_2.Refi
ORDER BY Count DESC, Agent
October 26, 2012 at 6:25 am
Hi,
Just create a proc and replace your hard coded values with variables so that you can use them
as report parameters in SSRS. Something like this:
CREATE PROCEDURE mySSRSReport
(
@startyear DATETIME ,
@endyear DATETIME
)
AS
BEGIN
SELECT TOP ( 100 ) PERCENT
tblObject_1.Name AS Agent ,
COUNT(dbo.tblLoan.Object_ID) AS Count ,
SUM(dbo.tblProcessingFile.LoanAmount_MIP_FF) AS Volume ,
derivedtbl_1.Purchase ,
derivedtbl_2.Refi
FROM dbo.tblLoan
INNER JOIN dbo.tblObject ON dbo.tblLoan.Object_ID = dbo.tblObject.Object_ID
INNER JOIN dbo.tblProcessingFile ON dbo.tblLoan.Object_ID = dbo.tblProcessingFile.Loan_ID
INNER JOIN dbo.tblObject AS tblObject_1 ON dbo.tblLoan.ContactOwnerID = tblObject_1.Object_ID
INNER JOIN dbo.tblDomain ON tblObject_1.Domain_ID = dbo.tblDomain.Object_ID
INNER JOIN dbo.tblObject AS tblObject_2 ON dbo.tblLoan.Source_ID = tblObject_2.Object_ID
LEFT OUTER JOIN ( SELECT tblLoan_2.ContactOwnerID ,
COUNT(tblLoan_2.Object_ID) AS Refi
FROM dbo.tblLoan AS tblLoan_2
INNER JOIN dbo.tblProcessingFile
AS tblProcessingFile_2 ON tblLoan_2.Object_ID = tblProcessingFile_2.Loan_ID
INNER JOIN dbo.enumLoanPurpose AS enumLoanPurpose_1 ON tblProcessingFile_2.enumLoanPurpose = enumLoanPurpose_1.enumLoanPurposeID
INNER JOIN dbo.tblObject AS tblObject_4 ON tblLoan_2.ContactOwnerID = tblObject_4.Object_ID
INNER JOIN dbo.tblDomain AS tblDomain_2 ON tblObject_4.Domain_ID = tblDomain_2.Object_ID
WHERE ( tblProcessingFile_2.Funded BETWEEN @startyear
AND
@endyear )
AND ( enumLoanPurpose_1.LoanPurpose LIKE '%refi%' )
AND ( tblDomain_2.DomainCategoryTypeID = 2 )
GROUP BY tblLoan_2.ContactOwnerID
) AS derivedtbl_2 ON tblObject_1.Object_ID = derivedtbl_2.ContactOwnerID
LEFT OUTER JOIN ( SELECT tblLoan_1.ContactOwnerID ,
COUNT(tblLoan_1.Object_ID) AS Purchase
FROM dbo.tblLoan AS tblLoan_1
INNER JOIN dbo.tblProcessingFile
AS tblProcessingFile_1 ON tblLoan_1.Object_ID = tblProcessingFile_1.Loan_ID
INNER JOIN dbo.enumLoanPurpose ON tblProcessingFile_1.enumLoanPurpose = dbo.enumLoanPurpose.enumLoanPurposeID
INNER JOIN dbo.tblObject AS tblObject_3 ON tblLoan_1.ContactOwnerID = tblObject_3.Object_ID
INNER JOIN dbo.tblDomain AS tblDomain_1 ON tblObject_3.Domain_ID = tblDomain_1.Object_ID
WHERE ( tblProcessingFile_1.Funded BETWEEN @startyear
AND
@endyear )
AND ( dbo.enumLoanPurpose.LoanPurpose LIKE '%purchase%' )
AND ( tblDomain_1.DomainCategoryTypeID = 2 )
GROUP BY tblLoan_1.ContactOwnerID
) AS derivedtbl_1 ON tblObject_1.Object_ID = derivedtbl_1.ContactOwnerID
WHERE ( dbo.tblDomain.DomainCategoryTypeID = 2 )
AND ( dbo.tblProcessingFile.Funded BETWEEN @startyear
AND @endyear )
GROUP BY tblObject_1.Name ,
derivedtbl_1.Purchase ,
derivedtbl_2.Refi
ORDER BY Count DESC ,
Agent
END
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply