Sql and ASP help

[Ptaz]

I need help to work out how to do the following tasks. Can anyone help me to complete this and how?

1.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


2. 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. Urgent.

[tamilselvan]

I know ASAP. What is ASP?

What does "0-6 months" mean?

Tamil

[Ptaz]

ASP is Active Server Pages and 0-6 is period of months.!! So if u have any idea how i can go about the SQL query or anything with this problem, please let me know.

Thanks.

[tamilselvan]

SELECT ('0 - 6 MONTHS TOTAL : ' ||(M1+F1)
||CHR(10)||'0 - 6 MONTHS FEMALE : ' ||(F1)
||CHR(10)||'0 - 6 MONTHS MALE : ' ||(M1)
||CHR(10)||'7 - 12 MONTHS TOTAL : ' ||(M2+F2)
||CHR(10)||'7 - 12 MONTHS FEMALE : ' ||(F2)
||CHR(10)||'7 - 12 MONTHS MALE : ' ||(M2)
||CHR(10)||'0 - 12 MONTHS TOTAL : ' ||(M1+M2+F1+F2)
||CHR(10)||'0 - 12 MONTHS FEMALE : ' ||(F1+F2)
||CHR(10)||'0 - 12 MONTHS MALE : ' ||(M1+M2)) FINALOUTPUT
FROM (
SELECT /*+ NO_MERGE */
( SELECT COUNT(1) FROM T1
WHERE TITLE = 'MR' AND TO_NUMBER(TO_CHAR(REGDATE,'MM')) <= 6) M1,
( SELECT COUNT(1) FROM T1
WHERE TITLE = 'MR' AND TO_NUMBER(TO_CHAR(REGDATE,'MM')) > 6) M2,
( SELECT COUNT(1) FROM T1
WHERE TITLE IN ('MS','MRS','MISS') AND TO_NUMBER(TO_CHAR(REGDATE,'MM')) <= 6) F1,
( SELECT COUNT(1) FROM T1
WHERE TITLE IN ('MS','MRS','MISS') AND TO_NUMBER(TO_CHAR(REGDATE,'MM')) > 6) F2
FROM DUAL)
/

This is my ouput from your sample data:
FINALOUTPUT
---------------------------------------
0 - 6 MONTHS TOTAL : 5
0 - 6 MONTHS FEMALE : 3
0 - 6 MONTHS MALE : 2
7 - 12 MONTHS TOTAL : 4
7 - 12 MONTHS FEMALE : 3
7 - 12 MONTHS MALE : 1
0 - 12 MONTHS TOTAL : 9
0 - 12 MONTHS FEMALE : 6
0 - 12 MONTHS MALE : 3

For ASP help, you may need to visit another web site.
Good Luck.

Tamil

[Ptaz]

Thanks Tamil,

But I put this code into SQL server and Access and it didnt work. what have I done wrong?? Didnt get any output, just errors.

[DaPi]

Originally posted by Ptaz
what have I done wrong??

You posted your problem in an Oracle forum

Beware, this forum is inhabited by a bunch of wicked Oracle DBA's who eat Access programmers for breakfast

[Ptaz]

thanks..that helps. Now can you tell me a good forum for sql/access?

[DaPi]

Try these guys: http://www.tek-tips.com/

[Ptaz]

kewl thanks.

[aspdba]

select * from
(
select'0-6 month' 'period of reg','total' 'gender',count(1) 'count' from dbatest where datepart(mm,regdate) >0 and datepart(mm,regdate) <7
union
select'0-6 month' 'period of reg','Female' 'gender',count(1) 'count' from dbatest where datepart(mm,regdate) >0 and datepart(mm,regdate) <7 and
upper(title) in ('MS','MRS','MISS')
union
select'0-6 month' 'period of reg','Male' 'gender',count(1) 'count' from dbatest where datepart(mm,regdate) >0 and datepart(mm,regdate) <7 and
upper(title) in ('MR')
union
select'7-12 month' 'period of reg','total' 'gender',count(1) 'count' from dbatest where datepart(mm,regdate) >6 and datepart(mm,regdate) <=12
union
select'7-12 month' 'period of reg','Female' 'gender',count(1) 'count' from dbatest where datepart(mm,regdate) >6 and datepart(mm,regdate) <=12 and
upper(title) in ('MS','MRS','MISS')
union
select'7-12 month' 'period of reg','Male' 'gender',count(1) 'count' from dbatest where datepart(mm,regdate) >6 and datepart(mm,regdate) <=12 and
upper(title) in ('MR')
union
select'1-12 month' 'period of reg','Male' 'gender',count(1) 'count' from dbatest WHERE upper(title) in ('MR')
union
select'1-12 month' 'period of reg','Female' 'gender',count(1) 'count' from dbatest WHERE upper(title) in ('MS','MISS','MRS')
union
select'1-12 month' 'period of reg','Total' 'gender',count(1) 'count' from dbatest)
sam
order by 'preiod of reg'

HTH