Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Date Range in my SQL report


Date Range in my SQL report

Author
Message
D-SQL
D-SQL
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 754
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
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;




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
D-SQL
D-SQL
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 754
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
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)

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
D-SQL
D-SQL
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 754
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
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;




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
D-SQL
D-SQL
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 754
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24207 Visits: 37978
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
D-SQL
D-SQL
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 754
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 Smile

Thanks
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search