Date Problem

G

Guest

I have a table that is a list of new loans opened since 1/1/2005. The fields
include the loan officer number, the account number, and the date opened. I
also have a query based on this table that sums the new loans opened since
1/1/2005 for each loan officer. In this query, the date opened field is
formatted as a date field, but I have formatted it to display the name of the
month only and named it "Month". I need this query to pull just the first two
months of the most recent quarter. In other words, when I run the query in
July it should pull totals for each loan officer for April and May only.

How do I do this in my query? I am pasting the SQL below.

SELECT Officers.[Officer#], Officers.OfficerName, Format([Issue
Date],"mmmm") AS [Month], Sum(Nz([Current Bal],0)) AS [Current Balance]
FROM Officers LEFT JOIN newLoans ON Officers.[Officer#] = newLoans.[Officer#]
GROUP BY Officers.[Officer#], Officers.OfficerName, Format([Issue
Date],"mmmm")
HAVING (((Officers.[Officer#])<9000 And (Officers.[Officer#])<>107 And
(Officers.[Officer#])<>288 And (Officers.[Officer#])<>546 And
(Officers.[Officer#])<>143))
ORDER BY Officers.[Officer#], Format([Issue Date],"mmmm");
 
J

John Spencer (MVP)

I think that the following should work for you. AND it should be faster since
you apply the selection criteria BEFORE you aggregate the data.


SELECT Officers.[Officer#], Officers.OfficerName,
Format([Issue Date],"mmmm") AS [Month], Sum(Nz([Current Bal],0)) AS [Current Balance]
FROM Officers LEFT JOIN newLoans ON Officers.[Officer#] = newLoans.[Officer#]
WHERE [Issue Date]
Between DateSerial(Year(Date()),3*(Month(Date())\3) -2,1) and
DateSerial(Year(Date()),3*(Month(Date())\3),0)
AND Officers.[Officer#]<9000
And Officers.[Officer#] NOT IN (107, 288,546,143)
GROUP BY Officers.[Officer#], Officers.OfficerName,
Format([Issue Date],"mmmm")
ORDER BY Officers.[Officer#], Format([Issue Date],"mmmm");

The DateSerial calculation determines the quarter number Month(Date())\3
(returns 0-3)
Multiplies that by 3 to 0,3,6, or 9 (which is the last month of the prior quarter.
Then we adjust to get the 1st month of the quarter (subtract 2) or the last day
of the 2nd month of the quarter.

I also moved and simplified your other criteria.
 
G

Guest

Thank you so much!!!!!! It worked like a charm. Where can I go to get that
good at SQL?

John Spencer (MVP) said:
I think that the following should work for you. AND it should be faster since
you apply the selection criteria BEFORE you aggregate the data.


SELECT Officers.[Officer#], Officers.OfficerName,
Format([Issue Date],"mmmm") AS [Month], Sum(Nz([Current Bal],0)) AS [Current Balance]
FROM Officers LEFT JOIN newLoans ON Officers.[Officer#] = newLoans.[Officer#]
WHERE [Issue Date]
Between DateSerial(Year(Date()),3*(Month(Date())\3) -2,1) and
DateSerial(Year(Date()),3*(Month(Date())\3),0)
AND Officers.[Officer#]<9000
And Officers.[Officer#] NOT IN (107, 288,546,143)
GROUP BY Officers.[Officer#], Officers.OfficerName,
Format([Issue Date],"mmmm")
ORDER BY Officers.[Officer#], Format([Issue Date],"mmmm");

The DateSerial calculation determines the quarter number Month(Date())\3
(returns 0-3)
Multiplies that by 3 to 0,3,6, or 9 (which is the last month of the prior quarter.
Then we adjust to get the 1st month of the quarter (subtract 2) or the last day
of the 2nd month of the quarter.

I also moved and simplified your other criteria.

Gwen said:
I have a table that is a list of new loans opened since 1/1/2005. The fields
include the loan officer number, the account number, and the date opened. I
also have a query based on this table that sums the new loans opened since
1/1/2005 for each loan officer. In this query, the date opened field is
formatted as a date field, but I have formatted it to display the name of the
month only and named it "Month". I need this query to pull just the first two
months of the most recent quarter. In other words, when I run the query in
July it should pull totals for each loan officer for April and May only.

How do I do this in my query? I am pasting the SQL below.

SELECT Officers.[Officer#], Officers.OfficerName, Format([Issue
Date],"mmmm") AS [Month], Sum(Nz([Current Bal],0)) AS [Current Balance]
FROM Officers LEFT JOIN newLoans ON Officers.[Officer#] = newLoans.[Officer#]
GROUP BY Officers.[Officer#], Officers.OfficerName, Format([Issue
Date],"mmmm")
HAVING (((Officers.[Officer#])<9000 And (Officers.[Officer#])<>107 And
(Officers.[Officer#])<>288 And (Officers.[Officer#])<>546 And
(Officers.[Officer#])<>143))
ORDER BY Officers.[Officer#], Format([Issue Date],"mmmm");
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

Glad it worked.

In answer to your question.
Commercial classes
Books
Practice, Practice, Practice

One of my favorite books on the subject is
SQL for Mere Mortals

Not all of what you see is SQL.
The Date calculations use vba functions.


Gwen said:
Thank you so much!!!!!! It worked like a charm. Where can I go to get that
good at SQL?

John Spencer (MVP) said:
I think that the following should work for you. AND it should be faster since
you apply the selection criteria BEFORE you aggregate the data.


SELECT Officers.[Officer#], Officers.OfficerName,
Format([Issue Date],"mmmm") AS [Month], Sum(Nz([Current Bal],0)) AS [Current Balance]
FROM Officers LEFT JOIN newLoans ON Officers.[Officer#] = newLoans.[Officer#]
WHERE [Issue Date]
Between DateSerial(Year(Date()),3*(Month(Date())\3) -2,1) and
DateSerial(Year(Date()),3*(Month(Date())\3),0)
AND Officers.[Officer#]<9000
And Officers.[Officer#] NOT IN (107, 288,546,143)
GROUP BY Officers.[Officer#], Officers.OfficerName,
Format([Issue Date],"mmmm")
ORDER BY Officers.[Officer#], Format([Issue Date],"mmmm");

The DateSerial calculation determines the quarter number Month(Date())\3
(returns 0-3)
Multiplies that by 3 to 0,3,6, or 9 (which is the last month of the prior quarter.
Then we adjust to get the 1st month of the quarter (subtract 2) or the last day
of the 2nd month of the quarter.

I also moved and simplified your other criteria.

Gwen said:
I have a table that is a list of new loans opened since 1/1/2005. The fields
include the loan officer number, the account number, and the date opened. I
also have a query based on this table that sums the new loans opened since
1/1/2005 for each loan officer. In this query, the date opened field is
formatted as a date field, but I have formatted it to display the name of the
month only and named it "Month". I need this query to pull just the first two
months of the most recent quarter. In other words, when I run the query in
July it should pull totals for each loan officer for April and May only.

How do I do this in my query? I am pasting the SQL below.

SELECT Officers.[Officer#], Officers.OfficerName, Format([Issue
Date],"mmmm") AS [Month], Sum(Nz([Current Bal],0)) AS [Current Balance]
FROM Officers LEFT JOIN newLoans ON Officers.[Officer#] = newLoans.[Officer#]
GROUP BY Officers.[Officer#], Officers.OfficerName, Format([Issue
Date],"mmmm")
HAVING (((Officers.[Officer#])<9000 And (Officers.[Officer#])<>107 And
(Officers.[Officer#])<>288 And (Officers.[Officer#])<>546 And
(Officers.[Officer#])<>143))
ORDER BY Officers.[Officer#], Format([Issue Date],"mmmm");
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUM OR TOTAL Q 3
Problem with aggregate query 2
Conditional Negative Number 1
Other table design solutions for storing multiple employee ID's 3
Text in a Text Box 1
Nested Limitations 3
Duane Hookom - Crosstab Question 4
Better Alternative to Last() 7

Top