Month loop

[ross1228]

This is an asp.net and mysql question all in one..

I want a list of each month and prize money for that month, some months may not have any prizes awarded, but I still want to show the month name. Is there an easy way to do this using a repeater or something?


Ex:
January $200
February
March $4000
April $300
May
June
etc...


My two solutions so far are hard coding each month and run a query on each month. Or creating a table that has a list of the months and joining that on my query.

Thanks,
-Ross

 

[jmeckley]

what exactly is the question?
I would store this info in a table, query the table and user a repeater to display the results in the browser.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[ross1228]

I'm asking if there is an easy way to filter by each month in a loop or something.

Or is the best way to do it is to just make a month table and join on that?

 

[jmeckley]

depends on the overall scenario. without knowning more about the project it's like asking what the best color is.

it's usually a good idea to keep the number of db hits to a minimum. so 1 db call would be better than 12. depending on your DAL you could have many sql statements executed across a single connection which would be better for preformance.

with my limited knowledge I would query the db for any months contain prize money for the given year. within my business object i would then build a collection of objects (using DataTable is easiest) with each month and the associated cash prize. then i would bind this collection to a repeater.

in the end the databse might return 3 rows

Month | Amount
------+-------
1     | 100
3     | 200
11    | 300

the business logic would buid out my table like this (pseudo code)

IList<MonthlyWinnings> data = new List<MonthlyWinnings>(12);
data.Add(new MonthlyWinnings("Jan", 0);
data.Add(new MonthlyWinnings("Feb", 0);
data.Add(new MonthlyWinnings("Mar", 0);
//.. continue for all 12 months

foreach(row in DbRecords)
{
   int index = int.Parse(row["Month"]) - 1;
   data[index].Amount = int.Parse(row["Amount"]);
}
return data

finally the GUI code behind looks like this

MyRepeater.DataSource = new MyBusinessLogic().GetMonthlyWinnings(DateTime.Now.Year);
MyRepeater.DataBind();

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[ca8msm]

You could also have a "months" table in the database and outer join to it, grouping on the month name and counting the prize money. That's the option I'd probably go with.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[ca8msm]

Sorry, by "months" table, I really mean a "numbers" table which simply contains a row for each number ranging from 0 or 1 to x. If you ask in the SQL Server forum, you'll find that most people will have one of these created in most databases that they work with.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]