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