34,590
社区成员
发帖
与我相关
我的任务
分享
DECLARE @today datetime
SET @today = Convert(datetime,
Convert(varchar(10),GetDate(),120),
120)
;WITH t1 AS (
SELECT g.gwname,c.chexing,x.chexi,
COUNT(*) AS c
FROM CheJianInfo c,chexing x,gongwei g
WHERE c.chexing=x.chexing
AND c.gwid=g.gwid
AND c.gwid=2000
AND [zhuanchu] >= DateAdd(day,1,DateAdd(month,-1,@today))
GROUP BY g.gwname,c.chexing,x.chexi
)
,t2 AS (
SELECT g.gwname,c.chexing,x.chexi,
COUNT(*) AS c
FROM CheJianInfo c,chexing x,gongwei g
WHERE c.chexing=x.chexing
AND c.gwid=g.gwid
AND c.gwid=2000
AND [zhuanchu] >= DateAdd(day,-1,@today)
AND [zhuanchu] < @today
GROUP BY g.gwname,c.chexing,x.chexi
)
,t3 AS (
SELECT g.gwname,c.chexing,x.chexi,
COUNT(*) AS c
FROM CheJianInfo c,chexing x,gongwei g
WHERE c.chexing=x.chexing
AND c.gwid=g.gwid
AND c.gwid=2000
AND [zhuanchu] >= @today
GROUP BY g.gwname,c.chexing,x.chexi
)
SELECT t1.gwname,t1.chexing,t1.chexi,
ISNULL(t2.c,0) AS 昨日转出,
ISNULL(t3.c,0) AS 当日转出,
t1.c AS 合计
FROM t1
LEFT JOIN t2
ON t1.gwname = t2.gwname
AND t1.chexing = t2.chexing
AND t1.chexi = t2.chexi
LEFT JOIN t3
ON t1.gwname = t3.gwname
AND t1.chexing = t3.chexing
AND t1.chexi = t3.chexi
select
sum(case when datediff(day,[zhuanchu],getdate())=1 and c.zhuanchu is not null then 1 else 0 end) as '昨日转出',
sum(case when datediff(day,[zhuanchu],getdate())=0 and c.zhuanchu is not null then 1 else 0 end) as '当日转出',
sum(CASE
when (((datediff(mm,[zhuanchu],getdate()) = 1 and datepart(dd,[zhuanchu]) >= 26)) or ((datediff(mm,[zhuanchu],getdate()) = 0 and datepart(dd,[zhuanchu]) <= 26))) then 1
when datediff(day,[zhuanchu],getdate())=1 and c.zhuanchu is not null then 1
when datediff(day,[zhuanchu],getdate())=0 and c.zhuanchu is not null then 1
else 0 end) as '合计'
from CheJianInfo c,chexing x,gongwei g
where c.chexing=x.chexing and c.gwid=g.gwid
and c.gwid=2000
group by g.gwname,c.chexing,x.chexi
不了解你的具体逻辑,但是,直接也算进去可以是这样子