这样的SQL如何写？
表calendar:
date dateType
..........
2013-02-011
2013-02-022
2013-02-032
2013-02-041
2013-02-051
2013-02-061
2013-02-071
2013-02-081
2013-02-093
2013-02-103
2013-02-113
2013-02-123
2013-02-133
2013-02-143
2013-02-153
2013-02-161
2013-02-171
2013-02-181
2013-02-191
2013-02-201
2013-02-211
2013-02-221
2013-02-232
2013-02-242
2013-02-251
......
datetype: 1表示工作日，2表示周末，3表示节假日；
表A: 记录审批日期等相关信息
ID approvedate
001 2013-02-08
002 2013-02-18
003 2013-02-06
我想要查找的结果是：与当天相比（今天25号），审批总共用了多少工作日，不包括周末和节假日，也就是在表calendar中datatype=1;
ID approvedate approvedatenumber（天）
001 2013-02-08 7
002 2013-02-18 4
003 2013-02-20 2
[解决办法]
SELECT A.ID,A.approvedate,
COUNT(B.[date]) AS approvedatenumber
FROM A LEFT JOIN calendar AS B
ON B.[date] > A.approvedate
AND B.[date] < CONVERT(VARCHAR(10),GETDATE(),120)
AND B.dateType = 1
GROUP BY A.ID,A.approvedate
[解决办法]
我这个是存储过程，你看符不符合你的要求
use xuanya
if object_id('calendar','u') is not null
drop table calendar
create table calendar(date datetime,datetype int)
insert into calendar
select '2013-02-01',1 union all
select '2013-02-02',2 union all
select '2013-02-03',2 union all
select '2013-02-04',1 union all
select '2013-02-05',1 union all
select '2013-02-06',1 union all
select '2013-02-07',1 union all
select '2013-02-08',1 union all
select '2013-02-09',3 union all
select '2013-02-10',3 union all
select '2013-02-11',3 union all
select '2013-02-12',3 union all
select '2013-02-13',3 union all
select '2013-02-14',3 union all
select '2013-02-15',3 union all
select '2013-02-16',1 union all
select '2013-02-17',1 union all
select '2013-02-18',1 union all
select '2013-02-19',1 union all
select '2013-02-20',1 union all
select '2013-02-21',1 union all
select '2013-02-22',1 union all
select '2013-02-23',2 union all
select '2013-02-24',2 union all
select '2013-02-25',1
go
if object_id('yu','p') is not null
drop procedure yu
go
create procedure yu
@begindate datetime,
@enddate datetime
as
select distinct row_number() over(partition by date order by date) as ID,
@begindate as approvedate,count(*) over() as approvedatenumber
from calendar
where date>@begindate and date<@enddate and datetype=1
group by date,datetype
go
exec yu '2013-02-08','2013-02-25'
