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
Printable View
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...
Ahh I'll leave the wronged code there...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...
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...
[2] Create a view ...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
)
[3] Option 1: Not advisable!AS you asp script time out will bother your end users or you..whoever is calling this query.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
Bung this query into your ASP code(I'd use this in a stored proc and call it via ADO...
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: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
hope that helped...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
Cheers...