Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Date Range in my SQL report Expand / Collapse
Author
Message
Posted Wednesday, September 12, 2012 10:29 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:33 PM
Points: 82, Visits: 667
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
Post #1358106
Posted Wednesday, September 12, 2012 10:37 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 20,860, Visits: 32,884
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;





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)
Post #1358112
Posted Wednesday, September 12, 2012 5:34 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:33 PM
Points: 82, Visits: 667
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
Post #1358269
Posted Wednesday, September 12, 2012 7:46 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 20,860, Visits: 32,884
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)



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)
Post #1358291
Posted Wednesday, September 12, 2012 11:25 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:33 PM
Points: 82, Visits: 667
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
Post #1358345
Posted Thursday, September 13, 2012 6:15 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 20,860, Visits: 32,884
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.



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)
Post #1358501
Posted Thursday, September 13, 2012 7:42 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 20,860, Visits: 32,884
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;





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)
Post #1358557
Posted Thursday, September 13, 2012 8:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:33 PM
Points: 82, Visits: 667
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
Post #1358624
Posted Thursday, September 13, 2012 9:07 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:16 AM
Points: 20,860, Visits: 32,884
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.



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)
Post #1358641
Posted Thursday, September 13, 2012 9:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:33 PM
Points: 82, Visits: 667
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


Post #1358654
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse