DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: Sql and ASP help

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

  2. #12
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You wanted to go to London from NewYork, but boarded the flight to Mexico.

    Tamil

  3. #13
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    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...
    Last edited by Tarry; 02-19-2004 at 06:02 PM.
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

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