gulam
Posts: 55
|
| Posted: 11/01/2011, 9:56 PM |
|
Hi,
I am developing a small system IN CCS 4.3 that display a member's contributions for the year. Their financial year is July - June.
To simplify, I have 2 tables (these are pre-existing tables that are used by their other software and so cant change them)
MEMBERS
Memberno ->KEY
other static fields
CONTRIBUTIONS
contribution_id ->KEY
memberno - linked to the memberno in members table
year - this has a number for eg 2009 2010 2011
month - this has text for eg OCT NOV DEC
contribution amt
etc
When a member logs in and does a query based on his member no and year (for eg 2011), then the display should show
JULY 2010 Contribution Amt......
AUG 2010 ..................
.
.
JUNE 2011 ..............
How can I achieve? Would appreciate any assistance.
Gulam
|
 |
 |
andrewi
Posts: 162
|
| Posted: 11/02/2011, 4:29 AM |
|
Can you create your own helper tables? If so, I'd suggest creating a date helper table - called something like MONTHS (to match the existing naming convention).
MONTHS
MonthDate (data type: datetime)
Year (number)
MonthAbbreviation (text)
FinancialYear (text)
Fill the table like this:
MonthDate Year MonthAbbr FinancialYear
1/1/2011 2011 JAN 2010-2011
1/2/2011 2011 FEB 2010-2011
...
1/6/2011 2011 JUN 2010-2011
1/7/2011 2011 JUL 2011-2012
1/8/2011 2011 AUG 2011-2012
...
1/6/2012 2012 JUN 2011-2012
1/7/2012 2012 AUG 2012-2013
...
You need to pre-poulate this table from the date of earliest contribution to a good date in the future (say to 2100). Use Excel to create the rows then paste/import into the table.
So: now you can LEFT join this table to CONTRIBUTIONS on YearNo and MonthAbbreviation and aggregate the contriubtions for each calendar month. The advantages you get are:
- you can easily display months where zero contributions were made (because of the Left join)
- you can sort the list in date order by using the MonthDate column (which is a date, not a text column).
- You can summarise contributions by any random period - e.g. "Last 12 months" - because you've got a date-type column to work with (MonthDate).
- If you want to summarise the contributions by financial year (which you will, one day) then you just have to group by the FinancialYear column.
Andrew
|
|
|
|
gulam
Posts: 55
|
| Posted: 11/03/2011, 2:27 AM |
|
Thanks Andrew.
Yes I can create other tables and the idea of a helper table makes a lot of sense. I am however still a little confused on how exactly this will work.
The requirement is to be able to display a members contribution for the last financial year based on the year he enters. So assuming each member makes a single contribution per month and he enters 2011 then the system should display data of the financial year 2010-2011. The sample output is as follows:
MEMBER NO: XYZ
FINANCIAL YEAR: 2010 - 2011
MONTH YEAR CONTRIBUTION
JULY 2010 5656.00
AUG 2010 7676.00
.........
.......
......
JUNE 2011 4354.00
In this case, how will the SQL look?
Gulam
|
|
|
|
andrewi
Posts: 162
|
| Posted: 11/04/2011, 2:05 AM |
|
The SQL would be something like (freehand typed!)
SELECT MONTHS.MonthAbbr, MONTHS.YearNo, Sum(CONTRIBUTIONS.[Contribution Amt])
FROM MONTHS LEFT JOIN CONTRIBUTIONS
ON MONTHS.YearNo = CONTRIBUTIONS.Year And MONTHS.MonthAbbr = CONTRIBUTIONS.Month
WHERE MONTHS.FinancialYear = "2010 - 2011"
GROUP BY MONTHS.YearNo, MONTHS.MonthAbbr
ORDER BY MONTHS.MonthDate
Although you would probably construct this this in the visual query builder in CCS or use a view.
There are various ways that you can get the Where condition (e.g. "2010 - 2011"): you could present a list of different financial years to the user; you could select the latest financial year from MONTHS based on today's date...
|
|
|
|
andrewi
Posts: 162
|
| Posted: 11/04/2011, 12:26 PM |
|
I left out the GROUP BY clause originally in the SQL above.
You can build it all in the visual query builder. Link the two tables together, double-click the link and choose "Include ALL records from 'MONTHS' ..." for the Join Type.
|
|
|
|
|
