field but, instead, the rows are created as if the Tenure field were an
Employee ID. So I have duplicate Tenure fields when I thought there
would be just one per different Tenure field with the results summed
within each Tenure. In fact, if I swap out the Tenure field output with
the Employee ID, I get the same results with the exception of the
Employee ID field. I have been trying to really understand the
mechanics of SQL to solve these problems not just to find a quick fix.
This one appears that the "hiredate" field is my problem even though I
am using the DateDiff function to return a result. This is what I think
makes the row think it's an individual record (like a single employee
id) instead of grouping and summing on the Tenure result (such as
">12M"). I just can't figure out how to make it group and sum by
Tenure. Please help! Here is my query:
SELECT
iif(DateDiff("m ",e.HireDate,No w()) is null,"Unknown"
,iif(DateDiff(" m",e.HireDate,N ow()) <= 3,"0-3M"
,iif(DateDiff(" m",e.HireDate,N ow()) > 3 and
DateDiff("m",e. HireDate,Now()) <= 6,"4-6M"
,iif(DateDiff(" m",e.HireDate,N ow()) > 6 and
DateDiff("m",e. HireDate,Now()) <= 9,"7-9M"
,iif(DateDiff(" m",e.HireDate,N ow()) > 9 and
DateDiff("m",e. HireDate,Now()) <= 12,"10-12M"
,iif(DateDiff(" m",e.HireDate,N ow()) > 12,">12M",""))) ))) as [Tenure]
, b.division as [Division]
, b.region as [Region]
, b.district as [District]
, b.rbr as [Cost Center]
, b.name as [Branch]
, sum(iif(a.loanf unddate >= #1/1/2005# and a.loanfunddate <=
#1/31/2005#,1,0)) as [Jan #]
, sum(iif(a.loanf unddate >= #1/1/2005# and a.loanfunddate <=
#1/31/2005#,a.loanamo unt,0)) as [Jan $]
, sum(iif(a.loanf unddate >= #2/1/2005# and a.loanfunddate <=
#2/28/2005#,1,0)) as [Feb #]
, sum(iif(a.loanf unddate >= #2/1/2005# and a.loanfunddate <=
#2/28/2005#,a.loanamo unt,0)) as [Feb $]
, sum(iif(a.loanf unddate >= #3/1/2005# and a.loanfunddate <=
#3/31/2005#,1,0)) as [Mar #]
, sum(iif(a.loanf unddate >= #3/1/2005# and a.loanfunddate <=
#3/31/2005#,a.loanamo unt,0)) as [Mar $]
, sum(iif(a.loanf unddate >= #4/1/2005# and a.loanfunddate <=
#4/30/2005#,1,0)) as [Apr #]
, sum(iif(a.loanf unddate >= #4/1/2005# and a.loanfunddate <=
#4/30/2005#,a.loanamo unt,0)) as [Apr $]
, sum(iif(a.loanf unddate >= #5/1/2005# and a.loanfunddate <=
#5/31/2005#,1,0)) as [May #]
, sum(iif(a.loanf unddate >= #5/1/2005# and a.loanfunddate <=
#5/31/2005#,a.loanamo unt,0)) as [May $]
, sum(iif(a.loanf unddate >= #6/1/2005# and a.loanfunddate <=
#6/30/2005#,1,0)) as [Jun #]
, sum(iif(a.loanf unddate >= #6/1/2005# and a.loanfunddate <=
#6/30/2005#,a.loanamo unt,0)) as [Jun $]
, sum(iif(a.loanf unddate >= #1/1/2005# and a.loanfunddate <=
#6/30/2005#,1,0)) as [YTD #]
, sum(iif(a.loanf unddate >= #1/1/2005# and a.loanfunddate <=
#6/30/2005#,a.loanamo unt,0)) as [YTD $]
FROM (((fundings AS a LEFT JOIN branch AS b ON b.branch = a.branchno)
LEFT JOIN loanofficers AS lo ON lo.loanproducer code =
a.loanproducerc ode)
LEFT JOIN eeqry AS e ON e.empno = lo.empno)
WHERE
a.loanfunddate >= #1/1/2005#
and a.loanfunddate <= #6/30/2005#
and e.terminationda te is null
group by DateDiff("m",e. HireDate,Now()) , b.division, b.region,
b.district, b.rbr, b.name
order by b.district, b.rbr, DateDiff("m",e. HireDate,Now()) ;
A sample output is the following:
Tenure Division Region District Cost Center Branch Jan # Jan $ Feb
# Feb $ Mar # Mar $ Apr # Apr $ May # May $ Jun # Jun $ YTD # YTD $
4-6M C C12 C1212 123 OK 1 $220,000 0 $0 1 $162,000 0 $0 0 $0 0 $0 2 $382,000
7-9M C C12 C1212 123 OK 1 $348,000 0 $0 1 $50,000 0 $0 0 $0 0 $0 2 $398,000
10-12M C C12 C1212 123 OK 0 $0 1 $216,000 0 $0 0 $0 0 $0 0 $0 1 $216,000
12M C C12 C1212 123 OK 0 $0 2 $411,860 4 $598,300 0 $0 0 $0 0 $0 6 $1,120,160
12M C C12 C1212 123 OK 0 $0 4 $593,697 3 $475,385 0 $0 0 $0 0 $0 7 $1,069,082
12M C C12 C1212 123 OK 3 $459,000 17 $3,085,304 5 $662,805 0 $0 0 $0 0 $0 25 $4,207,109
12M C C12 C1212 123 OK 13 $1,392,620 18 $2,450,210 11 $2,272,032 0 $0 0 $0 0 $0 42 $6,114,862
