sqlserver中自动获取上周区间: 上周一到上周日
WHERE tjsj BETWEEN
DATEADD(dd,-DATEPART(dw,GETDATE()-8),CONVERT(VARCHAR(10),GETDATE(),120))-6 AND
DATEADD(dd,-DATEPART(dw,GETDATE()-8),CONVERT(VARCHAR(10),GETDATE(),120))
SELECT KSDM ,TJSJ
,cyrc = SUM(cyrc)
,RYRC=SUM(ryrc)
,zyrc = max(CASE WHEN RN=1 THEN ZYRC ELSE 0 END)
,ZYTS=SUM(zyts)
,ZKRC=SUM(zkrc)
,ssrc=sum(ssrc)
,QJRC=SUM(qjrc)
,SWRC=SUM(swrc)
FROM (
SELECT * ,datepart(weekday,tjsj)wk,ROW_NUMBER()OVER(PARTITION BY ksdm ORDER BY tjsj DESC) AS RN
FROM JSZYY_WX.dbo.FACT_ZY_GZL
WHERE tjsj BETWEEN
DATEADD(dd,-DATEPART(dw,GETDATE()-8),CONVERT(VARCHAR(10),GETDATE(),120))-6 AND
DATEADD(dd,-DATEPART(dw,GETDATE()-8),CONVERT(VARCHAR(10),GETDATE(),120))
)a
GROUP BY TJSJ,KSDM
)b