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不了解你的具体逻辑,但是,直接也算进去可以是这样子
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不了解你的具体逻辑,但是,直接也算进去可以是这样子
********************************************************************
表中有一个【zhuanchu】的datetime,相当于是流水单的性质。
我现在要统计“昨日转出”多少,“当日转出”多少,“本月合计转出”多少(时间是上月26日到本月25日)
现在在“昨日转出”、“当日转出”统计后,在“本月合计”中就没有再统计了,但这个是我需要的。
你的这个合计是固定25号,还是根据当前日期才判断?另外,
如果今天是28号,你原本的语句自然是不会算进去的,加了两个CASE应该是会算进去的
如果今天是24号,那已经算进去了
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