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

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.

Quote:

Originally posted by Ptaz
what have I done wrong??

You posted your problem in an Oracle forum :rolleyes:

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?

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

kewl thanks.

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

Thanks for that aspdba.

I did that, I get the result as you should, but don't get any figures. All counts are 0. Y?

taz

You wanted to go to London from NewYork, but boarded the flight to Mexico.

Tamil

Try this...

Code:

SELECT '0 - 6 MONTHS TOTAL : ' +(M1+F1) +CHAR(10)+'0 - 6 MONTHS FEMALE : ' +(F1) +CHAR(10)+'0 - 6 MONTHS MALE : ' +(M1) +CHAR(10)+'7 - 12 MONTHS TOTAL : ' +(M2+F2) +CHAR(10)+'7 - 12 MONTHS FEMALE : ' +(F2) +CHAR(10)+'7 - 12 MONTHS MALE : ' +(M2) +CHAR(10)+'0 - 12 MONTHS TOTAL : ' +(M1+M2+F1+F2) +CHAR(10)+'0 - 12 MONTHS FEMALE : ' +(F1+F2) +CHAR(10)+'0 - 12 MONTHS MALE : ' +(M1+M2) FINALOUTPUT FROM ( SELECT ( SELECT COUNT(1) FROM tmp WHERE TITLE = 'MR' AND datepart(mm,regdate) <= 6) M1, ( SELECT COUNT(1) FROM tmp WHERE TITLE = 'MR' AND datepart(mm,regdate) > 6) M2, ( SELECT COUNT(1) FROM tmp WHERE TITLE IN ('MS','MRS','MISS') AND datepart(mm,regdate) <= 6) F1, ( SELECT COUNT(1) FROM tmp WHERE TITLE IN ('MS','MRS','MISS') AND datepart(mm,regdate) > 6) F2 FROM tmp)--On my SQL 2K Server this didn't work But just to show that I worked on your code and adapted from Tamil's contribution...

Ahh I'll leave the wronged code there...
anyways here goes....

[1] Make Table First:
I made a table/BTW you can also use the DTS to read in the values from the text/csv whatever files...

Code:

CREATE TABLE [tarry].[dbo].[tmp] ( [Title] varchar(5) NOT NULL, [FirstName] varchar(30) NOT NULL, [LastName] varchar(50) NOT NULL, [RegDate] datetime NOT NULL )

[2] Create a view ...

Code:

CREATE view tmp_vw as SELECT ( SELECT COUNT(1) FROM tmp WHERE TITLE = 'MR' AND datepart(mm,regdate) <= 6) M1, ( SELECT COUNT(1) FROM tmp WHERE TITLE = 'MR' AND datepart(mm,regdate) > 6) M2, ( SELECT COUNT(1) FROM tmp WHERE TITLE IN ('MS','MRS','MISS') AND datepart(mm,regdate) <= 6) F1, ( SELECT COUNT(1) FROM tmp WHERE TITLE IN ('MS','MRS','MISS') AND datepart(mm,regdate) > 6) F2

[3] Option 1: Not advisable!AS you asp script time out will bother your end users or you..whoever is calling this query.
Bung this query into your ASP code(I'd use this in a stored proc and call it via ADO...

Code:

SELECT '0 - 6 MONTHS TOTAL : ' +convert(varchar(10),(M1+F1)) +char(10)+'0 - 6 MONTHS FEMALE : ' +convert(varchar(10),(F1)) +char(10)+'0 - 6 MONTHS MALE : ' +convert(varchar(10),(M1)) +char(10)+'7 - 12 MONTHS TOTAL : ' +convert(varchar(10),(M2+F2)) +char(10)+'7 - 12 MONTHS FEMALE : ' +convert(varchar(10),(F2)) +char(10)+'7 - 12 MONTHS MALE : ' +convert(varchar(10),(M2)) +char(10)+'0 - 12 MONTHS TOTAL : ' +convert(varchar(10),(M1+M2+F1+F2)) +char(10)+'0 - 12 MONTHS FEMALE : ' +convert(varchar(10),(F1+F2)) +char(10)+'0 - 12 MONTHS MALE : ' +convert(varchar(10),(M1+M2)) FINALOUTPUT FROM tmp_vw FINALOUTPUT --------------------------- 0 - 6 MONTHS TOTAL : 6 0 - 6 MONTHS FEMALE : 3 0 - 6 MONTHS MALE : 3 7 - 12 MONTHS TOTAL : 3 7 - 12 MONTHS FEMALE : 3 7 - 12 MONTHS MALE : 0 0 - 12 MONTHS TOTAL : 9 0 - 12 MONTHS FEMALE : 6 0 - 12 MONTHS MALE : 3

Option2: Create a stored procedure. Don't make a sp_, I see a lot of people using the sp_, it's primarily for master db.

Code:

create procedure call_emp as SELECT '0 - 6 MONTHS TOTAL : ' +convert(varchar(10),(M1+F1)) +char(10)+'0 - 6 MONTHS FEMALE : ' +convert(varchar(10),(F1)) +char(10)+'0 - 6 MONTHS MALE : ' +convert(varchar(10),(M1)) +char(10)+'7 - 12 MONTHS TOTAL : ' +convert(varchar(10),(M2+F2)) +char(10)+'7 - 12 MONTHS FEMALE : ' +convert(varchar(10),(F2)) +char(10)+'7 - 12 MONTHS MALE : ' +convert(varchar(10),(M2)) +char(10)+'0 - 12 MONTHS TOTAL : ' +convert(varchar(10),(M1+M2+F1+F2)) +char(10)+'0 - 12 MONTHS FEMALE : ' +convert(varchar(10),(F1+F2)) +char(10)+'0 - 12 MONTHS MALE : ' +convert(varchar(10),(M1+M2)) FINALOUTPUT FROM tmp_vw --and then call it thru your asp code exec call_emp

hope that helped...

Cheers...