SQL and ASP

[Tiger]

Can any one help me to do the following please. I dont quite understand how to go about it.

The following Consumers Table in a database has the following structure

Title FirstName LastName RegDate
Ms Alethia Foster 17/04/2003
Mr Ken Noble 21/06/2003
Mr Wilfred Morris 03/01/2003
Mr Horace Morgan 02/12/2002
Mrs Heather Robertson 11/04/2003
Miss Wendy Charman 12/10/2003
Ms Lorna Jones 10/08/2003
Ms Edith Terresfield 02/05/2003
Mrs Anne Walters 02/11/2003
...

Make a single SQL query returning the registrations breakdown in the following form

Period of registration/Gender Number of consumers
0 - 6 month total: 8422
0 - 6 month females: 6704
0 - 6 month males: 1717
7 - 12 month total: 41265
7 - 12 month females: 31708
7 - 12 month males: 9556
0 - 12 month total: 49687
0 - 12 month total females: 38412
0 - 12 month total males: 11273

Using the return recordset of the previous task, write an ASP module representing the registration
breakdown in the form of a bar diagram.

please help.
:(

[CardboardHammer]

Why does this smell like homework?

[Tiger]

its not homework..but task i have been given at my work place. I need to get answer asap.. so please do help out.

[ChrisBrown]

What I would do is get a count of the records returned by your query, and then use that number to alter the height of a 1 pixel by 1 pixel solid color gif or jpg.

That way your database can still grow/change and the bar graph can change with it.

Do you need help with the queries?

[Tiger]

Chris,

Yes please. All help I can get is appreciated.!!

I tried to use count, but couldnt figure out how to get the number of consumers to be displayed. It just wouldn't work.

Could you help please.

Tahnks.

[CardboardHammer]

What DBMS do they use? If it supports anything like stored procedures, you're far better off storing the logic in the DBMS and calling upon it with a query from the ASP page.

[Tiger]

SQL Server. I have been using sql query analyser to get the sql statement done, whihc i have..but am finding it difficult to get the consumers total to be displayed correctly. At present it shows 0. So deffo i have done something wrong. Don't know what.

[ChrisBrown]

Tiger, here is an example of how to get a count from SQL Server, and store it in a variable so you can output it on screen or use the value somewhere else:

SQL Query:
sSQL = "select count(ordernumber) as ordercount from customerinformation;"
set rs=cn.Execute(sSQL)
- This says tell me how many rows in this table have a value in this column, and then store this value and return it for me in a temporary column named "ordercount"

Then, take your variable and assign it the value from the recordset:

totalorders=rs("ordercount")

Now you can use the value anywhere you want.

[CardboardHammer]

--Use a stored procedure.
----Use four variables.
----Use four selects to SET the four variables to the values for 0-6/7-12 m/f
----Use a final select to return all the counts, adding together the variable values to calculate the various totals.
--Call the stored procedure from your ASP page.


DECLARE @cnt0_6f AS int
SET @cnt0_6f = (SELECT count(*) ... etc.)

...

SELECT @cnt0_6f + @cnt0_6m, @cnt0_6f, @cnt0_6m, @cnt7_12f + @cnt7_12m, @cnt7_12f, @cnt7_12m, @cnt0_6f + @cnt0_6m + @cnt7_12f + @cnt7_12m, @cnt0_6f + @cnt7_12f, @cnt0_6m + @cnt7_12m


The numbers returned (in ONE row) will be in the order shown in your initial post.

[zachzach]

If you get assignments that are about ASP from wherever, whouldn't you learn it?