Sql and ASP help
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Sql and ASP help

Hybrid View

  1. #1
    Join Date
    Dec 2003
    Posts
    6

    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.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I know ASAP. What is ASP?

    What does "0-6 months" mean?

    Tamil

  3. #3
    Join Date
    Dec 2003
    Posts
    6
    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.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.

  5. #5
    Join Date
    Dec 2003
    Posts
    6
    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.

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  7. #7
    Join Date
    Dec 2003
    Posts
    6
    thanks..that helps. Now can you tell me a good forum for sql/access?

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Try these guys: http://www.tek-tips.com/

  9. #9
    Join Date
    Dec 2003
    Posts
    6
    kewl thanks.

  10. #10
    Join Date
    Aug 2002
    Posts
    115
    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
  •  


Click Here to Expand Forum to Full Width