-
Sql and ASP help
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.
-
I know ASAP. What is ASP?
What does "0-6 months" mean?
Tamil
-
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.
-
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
Last edited by tamilselvan; 12-18-2003 at 05:49 PM.
-
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.
-
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
-
thanks..that helps. Now can you tell me a good forum for sql/access?
-
-
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|