bottom 4 records of each symbol

[mattgoody]

i have a table like this:

Company Quarter Year Income
A 1 2005 8
A 2 2005 6
A 3 2005 9
A 4 2005 4
A 1 2006 7
A 2 2006 8
A 3 2006 6
A 4 2006 6
B 1 2005 8
B 2 2005 9
B 3 2005 7
B 4 2005 2
B 1 2006 8
B 2 2006 8
B 3 2006 6
B 4 2006 11

I need to sum up the income of the last 4 quarters in a query. is this possible? im actually using access but i write all the queries in sql. this would be fairly easy, except i need to sum the 4 latest quarters together, even if the newest quarter is, say, quarter 1 of 2007. I would have to sum Q1 2007 and Q4,Q3,Q2 of 2006. I cant figure this out without writing some code and looping thru. im hoping its possible to do without that, just using sql commands.

-Matt

[aussie girl]

what about select SUM(income) from tablename
WHERE year = 2006 and year =2007

oops wrong column

[russell]

SELECT company, SUM(income) as totalIncome
FROM tbl as t
INNER JOIN
(
SELECT Distinct top 4 year, quarter, (year * 10) + quarter
FROM tbl
ORDER BY
(year * 10) + quarter DESC
) as x
On x.year = t.year
And x.quarter = t.quarter
Group By
company

[mattgoody]

thats great, the only problem is that some companies data isnt updated to the newest quarter. for example, company C's income isnt reported for Q1 2007 or Q4 2006, so the newest 4 quarters would be Q3,Q2,Q1 of 2006 and Q4 of 2005. This is the case for too many companies to overlook. Some have Q1 2007 included, and those work with the query as is, because the subquery returns the latest 4 dates. But for the other companies that i dont have the Q1 2007 data for yet, this only sums Q4,Q3,Q2 of 2006, when i need to include Q1 also. i hope this is understandable. thanks russell and aussie girl for responding to the original post.

Edit: If i include a where clause in the subquery like

SELECT Distinct top 4 year, quarter, (year*10) + quarter
FROM tbl
Where Cusip6 = 'A'
ORDER BY
(year*10) + quarter DESC;

that works, but only for the specific company, of course. i guess what im wondering is if its possible to do this subquery for each individual company.

-Matt

[russell]

i was afraid u were going to say that... :)

in this case, i'll probably create a table with company and last 4 quarters so i can join to that.

[mattgoody]

how would i create this table? im having trouble thinking about doing this without using a group by, which i cant because ill need to have 4 rows for each company, or without using a while or foreach or some sort of loop, which i also cant do unless i want to have to run the code each time to update a table, especially because im not going to be the one always running the queries.

[russell]

is data guranteed not to have holes in it (at least for purposes of this query) ? meaning, we wont have data like this

C 1 2006
C 3 2006
C 4 2006

[mattgoody]

yeah no holes. what ive done for now is wrote a new table in vba numbering the dates in descending order for each company, so im going to use a "WHERE number < 4" clause now.

[russell]

can do this:

in MS ACCESS VB Editor, Create a new function:
- tools, macro, Visual Basic Editor
- You'll see your db in the object explorer. Right-click it
- Click Insert
- Click Module
- In that module, create function:

Public Function subtract3(ByVal qDate As Long) As Long
Dim yr As Integer
Dim quarter As Integer

yr = CLng(Left(qDate, 4))
quarter = CLng(Right(qDate, 1))

If quarter = 4 Then
subtract3 = yr & "1"
Else
subtract3 = (yr - 1) & (quarter + 1)
End If
End Function

- close VB Editor.

Now you can execute that function from a query as though it were a built-in function:

SELECT t.company, SUM(income) as totalIncome
FROM tbl as t
INNER JOIN (
SELECT company, MAX((year * 10) + quarter) as maxQuarter, subtract3( MAX((year * 10) + quarter)) as minQuarter
FROM tbl
GROUP BY
company
) as x
On t.company = x.company
And (year * 10) + quarter BETWEEN minQuarter And maxQuarter
GROUP BY t.company

For testing purposes, I added to the sample data you provided another quarter for company B
B 1 2007 10

Result of query correctly is:

A 27
B 35

Hope this helps. :)
rb

[mattgoody]

o nice, i did not know you could write a function and then call it in a query like that. that is awesome, it will work much better than the workaround way im using as of now. thanks russell

Edit: Thanks a ton, being able to call functions i write is a huge help in doing even more manipulation to this data!

-Matt

[russell]

glad 2 help out :) :) :)