请恕我愚钝 问题和上次差不多就是有点变化
表
intime(进来时间) outtime (出去时间) intime、outtime都是 20080512101523格式-》 2008-05-12 10:15:23 现在有个报表 格式如下 日期 进来次数 出去次数
2008-05 m n
2008-06
2008-07
2008-08
select convert(char(7),dt,120) as 日期,
sum(case when ty=1 then 1 else 0 end) as 进场次数,
sum(case when ty=2 then 1 else 0 end) as 出场次数
from (
select cast(left(intime,6) as datetime) as dt,ty=1 from inouttable where intime is not null
union all
select cast(left(outtime,6) as datetime) as dt,ty=2 from inouttable where outtime is not null
)tp
group by dt having convert(char(4),dt,120)='2000'
出错 时间转换上出错 估计是cast(left(intime,6) as datetime) as dt 错了
表
intime(进来时间) outtime (出去时间) intime、outtime都是 20080512101523格式-》 2008-05-12 10:15:23 现在有个报表 格式如下 日期 进来次数 出去次数
2008-05 m n
2008-06
2008-07
2008-08
select convert(char(7),dt,120) as 日期,
sum(case when ty=1 then 1 else 0 end) as 进场次数,
sum(case when ty=2 then 1 else 0 end) as 出场次数
from (
select cast(left(intime,6) as datetime) as dt,ty=1 from inouttable where intime is not null
union all
select cast(left(outtime,6) as datetime) as dt,ty=2 from inouttable where outtime is not null
)tp
group by dt having convert(char(4),dt,120)='2000'
出错 时间转换上出错 估计是cast(left(intime,6) as datetime) as dt 错了
dt as 日期,
sum(case when ty=1 then 1 else 0 end) as 进场次数,
sum(case when ty=2 then 1 else 0 end) as 出场次数
from
(
select convert(varchar(7),intime,120) as dt,ty=1 from inouttable
union all
select convert(varchar(7),outtime,120) as dt,ty=2 from inouttable
)tp
where dt>='2008-01'
group by dt