Sql and ASP help

[Tarry]

Try this...

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

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

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

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.

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