September 12, 2012 at 10:29 am
I am new to creating reports and I have a few that I need to have a date range.
Example: I need the Syntax to grab the fist day of the month to the last day of the month. Below is my syntax that I was trying to use parameters but I cant use that on an automated approach.
I need the syntax to grab the fist day of the month to the last day of the month. Thanks for anyones help in advance
SELECT TOP (100) PERCENT tblObject_1.Name AS Agent, COUNT(tblLoan.Object_ID) AS Count, SUM(tblProcessingFile.LoanAmount_MIP_FF) AS Volume
FROM tblLoan INNER JOIN
tblObject ON tblLoan.Object_ID = tblObject.Object_ID INNER JOIN
tblProcessingFile ON tblLoan.Object_ID = tblProcessingFile.Loan_ID INNER JOIN
tblObject AS tblObject_1 ON tblLoan.ContactOwnerID = tblObject_1.Object_ID INNER JOIN
tblDomain ON tblObject_1.Domain_ID = tblDomain.Object_ID INNER JOIN
tblObject AS tblObject_2 ON tblLoan.Source_ID = tblObject_2.Object_ID
WHERE (tblDomain.DomainCategoryTypeID = 2) AND (tblProcessingFile.Funded IS NOT NULL) AND (tblLoan.Expected_Close_Year = 2012) AND
(tblProcessingFile.Funded BETWEEN @Stardate AND @Enddate)
GROUP BY tblObject_1.Name
ORDER BY Count DESC, Agent
September 12, 2012 at 10:37 am
Give this a try for a starter.
SELECT
tblObject_1.Name AS Agent,
COUNT(tblLoan.Object_ID) AS Count,
SUM(tblProcessingFile.LoanAmount_MIP_FF) AS Volume
FROM
tblLoan
INNER JOIN tblObject
ON tblLoan.Object_ID = tblObject.Object_ID
INNER JOIN tblProcessingFile
ON tblLoan.Object_ID = tblProcessingFile.Loan_ID
INNER JOIN tblObject AS tblObject_1
ON tblLoan.ContactOwnerID = tblObject_1.Object_ID
INNER JOIN tblDomain
ON tblObject_1.Domain_ID = tblDomain.Object_ID
INNER JOIN tblObject AS tblObject_2
ON tblLoan.Source_ID = tblObject_2.Object_ID
WHERE
(tblDomain.DomainCategoryTypeID = 2) AND
--(tblProcessingFile.Funded IS NOT NULL) AND -- Not needed due to criterea below
(tblLoan.Expected_Close_Year = 2012) AND
--(tblProcessingFile.Funded BETWEEN @Stardate AND @Enddate)
tblProcessingFile.Funded >= dateadd(mm, datediff(mm, 0, getdate()), 0) and
tblProcessingFile.Funded < dateadd(mm, datediff(mm, 0, getdate()) + 1, 0)
GROUP BY
tblObject_1.Name
ORDER BY
Count DESC,
Agent;
September 12, 2012 at 5:34 pm
Thank you that worked perfectly and now of course, the requirement changes a little. I have been asked where this Expected_Close = 8) is in the where clause.
Could someone help- They want that field to just incremental example when the report runs at the end of the month on the last day to insert 9 and then the next month 10
is that possible? Thanks everyone
SELECT TOP (100) PERCENT tblObject_1.Name AS Agent, COUNT(dbo.tblLoan.Object_ID) AS Count, SUM(dbo.tblProcessingFile.LoanAmount_MIP_FF) AS Volume
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
WHERE (dbo.tblDomain.DomainCategoryTypeID = 2) AND (dbo.tblProcessingFile.Funded IS NOT NULL) AND (dbo.tblLoan.Expected_Close_Year = 2012) AND
(dbo.tblLoan.Expected_Close = 9)
GROUP BY tblObject_1.Name
ORDER BY Count DESC, Agent
September 12, 2012 at 7:46 pm
D-SQL (9/12/2012)
Thank you that worked perfectly and now of course, the requirement changes a little. I have been asked where this Expected_Close = 8) is in the where clause.Could someone help- They want that field to just incremental example when the report runs at the end of the month on the last day to insert 9 and then the next month 10
is that possible? Thanks everyone
SELECT TOP (100) PERCENT tblObject_1.Name AS Agent, COUNT(dbo.tblLoan.Object_ID) AS Count, SUM(dbo.tblProcessingFile.LoanAmount_MIP_FF) AS Volume
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
WHERE (dbo.tblDomain.DomainCategoryTypeID = 2) AND (dbo.tblProcessingFile.Funded IS NOT NULL) AND (dbo.tblLoan.Expected_Close_Year = 2012) AND
(dbo.tblLoan.Expected_Close = 9)
GROUP BY tblObject_1.Name
ORDER BY Count DESC, Agent
This doesn't look like the same code posted earlier or the code I provided.
Is this a different query?
Also, please explain this part of the WHERE clause, where does this information come from. Pretty sure if you ned to make tblLoan.Expected_Close dynamic, you will also need to make tblLoan.WExpected_Close_year dynamic.
(dbo.tblLoan.Expected_Close_Year = 2012) AND
(dbo.tblLoan.Expected_Close = 9)
September 12, 2012 at 11:25 pm
Yes this is the same query they just wanted instead of actual date range tblProcessingFile.Funded to (dbo.tblLoan.Expected_Close = 9) because some actual close on the 1st through the 5th.
Is there a way to use (dbo.tblLoan.Expected_Close = 9) and have the month change every month in the automated report? This month the report will run on the last day and have 9 then the next month at the end of the have 10 and so on.
Thanks for your help
September 13, 2012 at 6:15 am
D-SQL (9/12/2012)
Yes this is the same query they just wanted instead of actual date range tblProcessingFile.Funded to (dbo.tblLoan.Expected_Close = 9) because some actual close on the 1st through the 5th.Is there a way to use (dbo.tblLoan.Expected_Close = 9) and have the month change every month in the automated report? This month the report will run on the last day and have 9 then the next month at the end of the have 10 and so on.
Thanks for your help
This really isn't explaining or answering the question. I asked you where does this value come from. Is this the month during which the report is running or is it the month folling, what? I also asked about the hardcoded year. I am sure that you will want that automated as well so that you don't have to modify the code later when it needs to change to 2013.
If, as you say, this is same code why did you poost old code instead of the new code I provided? Just asking.
September 13, 2012 at 7:42 am
Is this what you are trying to accomplish?
SELECT
-- TOP (100) PERCENT << Totally unnecessary
ob1.Name AS Agent,
COUNT(ln.Object_ID) AS LoanCount, -- Sorry, but Count is just bad
SUM(pf.LoanAmount_MIP_FF) AS Volume
FROM -- added table aliases for all tables, using those elsewhere in the query
dbo.tblLoan ln
INNER JOIN dbo.tblObject ob
ON ln.Object_ID = ob.Object_ID
INNER JOIN dbo.tblProcessingFile pf
ON ln.Object_ID = pf.Loan_ID
INNER JOIN dbo.tblObject AS ob1
ON ln.ContactOwnerID = ob1.Object_ID
INNER JOIN dbo.tblDomain d
ON ob1.Domain_ID = d.Object_ID
INNER JOIN dbo.tblObject AS ob2
ON ln.Source_ID = ob2.Object_ID
WHERE
d.DomainCategoryTypeID = 2 AND
--pf.Funded IS NOT NULL AND -- Not needed due to criteria below
ln.Expected_Close_Year = year(getdate()) AND
ln.Expected_Close = month(getdate()) AND
pf.Funded >= dateadd(mm, datediff(mm, 0, getdate()), 0) and
pf.Funded < dateadd(mm, datediff(mm, 0, getdate()) + 1, 0)
GROUP BY
ob1.Name
ORDER BY
LoanCount DESC,
Agent;
September 13, 2012 at 8:48 am
Ill try this out. I copy pasted the script not the one you updated they are both the same except the where clause. The reason they needed dbo.tblLoan.Expected_Close = 9).
Is because some instances the loan is in progress and has for example the expected close month of 8 but actually doesnt close till the 1st or 2nd day in september they want to count that as previous month. I guess they have a period of 1-5 days each month that could count as previous if it was started inthe prior month this is why they use the expected close. Hope that answers your question. Once again thanks for your help much appreciated
September 13, 2012 at 9:07 am
D-SQL (9/13/2012)
Ill try this out. I copy pasted the script not the one you updated they are both the same except the where clause. The reason they needed dbo.tblLoan.Expected_Close = 9).Is because some instances the loan is in progress and has for example the expected close month of 8 but actually doesnt close till the 1st or 2nd day in september they want to count that as previous month. I guess they have a period of 1-5 days each month that could count as previous if it was started inthe prior month this is why they use the expected close. Hope that answers your question. Once again thanks for your help much appreciated
Nope, doesn't help as it provides me no information I can use to really determine how to calculate the value for Expected_Close based on the current date.
September 13, 2012 at 9:21 am
In there current query they don't use a a date range rather they go by the year that is hard coded in the query this is one of the columns in tbloan, then the expected_close is also a column in tbloan also which is hard coded in the current query. the expected_close is 1-12 for each month. That's how they are determining the counts for each month by inputting for example 9 for the month of Sept.
Better 🙂
Thanks
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply