34,576
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([日期] DATETIME,[金额] int)
Insert #T
select N'2011-11-01 16:42:00',1 union all
select N'2011-11-02 15:42:00',1 union all
select N'2011-11-02 19:42:00',1
Go
--测试数据结束
Select CASE WHEN 日期 BETWEEN
CONVERT(DATETIME,CONVERT(NVARCHAR(10),DATEADD(DAY,-1,日期),120)+' 16:00:00') AND
CONVERT(DATETIME,CONVERT(NVARCHAR(10),日期,120)+' 15:59:00') THEN CONVERT(NVARCHAR(10),日期,120)
ELSE CONVERT(NVARCHAR(10),DATEADD(DAY,1,日期),120) END AS 日期,
SUM(金额) AS 金额
FROM #T
GROUP BY CASE WHEN 日期 BETWEEN
CONVERT(DATETIME,CONVERT(NVARCHAR(10),DATEADD(DAY,-1,日期),120)+' 16:00:00') AND
CONVERT(DATETIME,CONVERT(NVARCHAR(10),日期,120)+' 15:59:00') THEN CONVERT(NVARCHAR(10),日期,120)
ELSE CONVERT(NVARCHAR(10),DATEADD(DAY,1,日期),120) END
--从出库单查询业务部门每日统计-国际业务部
SELECT F.ADMIN_UNIT_NAME AS [部门],
CONVERT(VARCHAR(20), A.DOC_DATE, 120) AS [日期],
SUM(B.BUSINESS_QTY * C.PRICE) AS [金额]
FROM dbo.SALES_ISSUE A
LEFT JOIN dbo.SALES_ISSUE_D B
ON B.SALES_ISSUE_ID = A.SALES_ISSUE_ID
LEFT JOIN dbo.SALES_DELIVERY_D C
ON B.SOURCE_ID_ROid = C.SALES_DELIVERY_D_ID
LEFT JOIN dbo.SALES_DELIVERY D
ON C.SALES_DELIVERY_ID = D.SALES_DELIVERY_ID
LEFT JOIN dbo.ADMIN_UNIT F
ON D.Owner_Dept = F.ADMIN_UNIT_ID
WHERE F.ADMIN_UNIT_NAME = '国际业务部'
AND A.TRANSACTION_DATE BETWEEN '2018-11-1' AND '2018-11-30'
AND a.CreateDate BETWEEN DATEADD(DAY,-1,'2018-11-1 16:00') AND '2018-11-30 16:00'
GROUP BY
F.ADMIN_UNIT_NAME,
A.DOC_DATE