create table tb1(sPhoneid varchar(15),dtCreate DateTime)
insert into tb1
select '001','2006-1-1' union all
select '001','2006-2-5' union all
select '002','2006-2-5' union all
select '002','2006-1-1' union all
select '003','2006-1-2' union all
select '001','2006-1-25'
go
create procedure GetPhoneID(@FistDt DateTime,@LastDt DateTime)
as
select sPhoneid,sum(nums) c3 from
(select sPhoneid,count(sPhoneid) as nums
from [tb1]
where dtCreate>=@FistDt and dtCreate<@LastDt
group by sPhoneid,year(dtCreate),month(dtCreate)
) aa
group by sPhoneid
having count(*)=datediff(month,@FistDt,@LastDt)
go
存储过程:
--输入年份,开始月份,结束月份,例GetRecord 2006,1,5表示2006年1月到5月
CREATE PROCEDURE GetRecord(@iYear int=2006,@iMonth1 int=1,@iMonth2 int=12) as
BEGIN
Declare @DateStart varchar(10);
Declare @DateEnd varchar(10);
SEt @DateStart=@iYear+'-'+@iMonth1+'-1';--组合开始日期
SET @DateEnd=@iYear+'-'+(@iMonth2+1)+'-1';--组合结束日期(是所选日期的下个月第一天,自己体会)
--查询,大于等于开始月的第一天,小于结束月次月第一天
Select * From Ent_Members Where JoinTime>=@DateStart and JoinTime<@DateEnd;
End
=============
代码:
<%
if isNumeric(Request.form("year")) and isNumeric(Request.Form("month1")) and isNumeric(Request.Form("month2")) then
'调用存储过程
Set rst=conn.execute("GetRecord "&Request.Form("year")&","&Request.Form("month1")&","&Request.Form("month2"))
.....
rst.close
....
end if
select sPid,c3 from (select a.sPid,(c1+c2) as c3
from
(select sPhoneid as sPid,count(sPhoneid) as c1 from [tbl]
where month(dtCreate)='01' and year(dtCreate)=2006 group by sPhoneid) a,
(select sPhoneid as sPid,count(sPhoneid) as c2 from [tbl]
where month(dtCreate)='02' and year(dtCreate)=2006 group by sPhoneid) b
where a.sPid=b.sPid) aa order by c3 desc