USE DP6_Philips
GOALTER PROCEDURE [dbo].[SSRS_Email_memberSwork_week_1]
(
@CorpId INT ,
@RegionIdParam VARCHAR(20) ,
@FilialeIdParam VARCHAR(20) ,
@AgentIdParam VARCHAR(20)
)
AS
BEGIN
DECLARE
@BEGIN DATETIME , -- 数据提取开始时间
@END DATETIME -- 数据提取结束时间
-- 自动推算上周时间
SELECT @BEGIN = DATEADD(DD , -6 , CAST(value AS DATETIME))
, @END = CAST(value AS DATETIME) + 1
FROM DBO.DropDownListDate_New(1,1,GETDATE())
PRINT CONVERT(VARCHAR(100),@BEGIN ,120)
PRINT CONVERT(VARCHAR(100),@END ,120)
--表1应报数的驻场名单
CREATE TABLE #a(JoinTime DATETIME,CorpId INT,MemberId INT,UserName VARCHAR(50),RoleId INT)
INSERT INTO #a
SELECT DISTINCT CAST(a.JoinTime AS DATE)
,a.CorpId
,a.MemberId
,b.UserName
,a.RoleId
FROM
dbo.DP_MembersStatusEveryDay a
-- 获取会员姓名 并 剔除测试账号
INNER JOIN dbo.DP_MemberInfo_View b
ON a.CorpId = b.CorpId AND a.MemberId = b.MemberId
AND b.UserName NOT LIKE '%测试%' AND b.IsDefault = 1
WHERE a.CorpId = @CorpId
AND a.JoinTime >= @BEGIN
AND a.JoinTime < @END
AND a.Status = 2
--------------------------------------------
CREATE TABLE #c(JoinTime DATETIME,Instruction VARCHAR(30),MemberId INT)
insert INTO #c
SELECT #a.JoinTime,e.Instruction , COUNT(DISTINCT #a.MemberId)
FROM #a
INNER JOIN (
SELECT DISTINCT d.CorpId ,d.MemberId,CAST(d.RecvTime AS DATE)reportDate,d.Instruction
FROM
dbo.DP_CommonSmsEntries d
WHERE d.CORPID = @CorpId AND d.Instruction IN ('PI','PL','PP','PM','PN')
AND d.RecvTime >= @BEGIN AND d.RecvTime < @END
)e ON #a.CorpId = e.CorpId AND #a.MemberId = e.MemberId AND CAST(#a.JoinTime AS DATE) = e.reportDate
GROUP BY #a.JoinTime,e.Instruction
SELECT * FROM #c
-------------------休假---------------------------
CREATE TABLE #b(JoinTime DATETIME,CorpId INT,MemberId INT,UserName VARCHAR(50),RoleId INT)
INSERT INTO #b
SELECT DISTINCT CAST(j.JoinTime AS DATE)reportDate
,j.CorpId
,j.MemberId
,MV.UserName
,j.RoleId
FROM
dbo.DP_MembersStatusEveryDay j
-- 休假
INNER JOIN dbo.DP_SysAttendanceEntries c ON j.CorpId = c.CorpId
AND j.MemberId = c.MemberId
AND CAST(j.JoinTime AS DATE) = CAST(c.AttendDate AS DATE)AND TypeId = 2
-- 获取会员姓名 并 剔除测试账号
INNER JOIN dbo.DP_MemberInfo_View MV
ON j.CorpId = MV.CorpId AND j.MemberId = MV.MemberId
AND MV.UserName NOT LIKE '%测试%' AND MV.IsDefault = 1
WHERE j.CorpId = @CorpId AND j.Status =2
AND j.JoinTime >= @BEGIN
AND j.JoinTime < @END--------结果-------------------
SELECT 会员角色,任务名称,上报频率,日期,应上报,ISNULL(实际上报,0) AS 实际上报 ,
CASE 应上报 WHEN 0 THEN 0
ELSE
CAST(ISNULL(实际上报,0)*1.0/应上报 AS DECIMAL(5,4)) END AS 日上报率
FROM (
SELECT '企业会员(督导)'AS 会员角色
,f.TaskName AS 任务名称
,'日报' AS 上报频率
,A.reportDate 日期
,(SELECT COUNT(DISTINCT MemberId ) FROM #a WHERE reportDate = A.reportDate) AS 应上报
,(SELECT #c.MemberId FROM #c WHERE reportDate = A.reportDate)AS 实际上报
FROM (SELECT CAST(DATEADD(DAY,NUMBER,@BEGIN)AS DATE) reportDate FROM master..SPT_VALUES
WHERE TYPE = 'P' AND number BETWEEN 0 AND DATEDIFF(DAY,@BEGIN,@END)-1 ) A
INNER JOIN dbo.DP_SmsTasks f ON 1=1 AND f.Instruction IN ('PI','PL','PP','PM','PN')
UNION ALL
SELECT '企业会员(督导)'AS 会员角色
,'巡店员-休假'AS 任务名称
,'日报' AS 上报频率
,A.reportDate 日期
,0 AS 应上报
,(SELECT COUNT(DISTINCT MemberId ) FROM #b WHERE reportDate = A.reportDate)AS 实际上报
FROM (SELECT CAST(DATEADD(DAY,NUMBER,@BEGIN)AS DATE) reportDate FROM master..SPT_VALUES
WHERE TYPE = 'P' AND number BETWEEN 0 AND DATEDIFF(DAY,@BEGIN,@END)-1 ) A
) RESULT
END
--验证
EXEC SSRS_Email_memberSwork_week_1
@CorpId=1 ,
@RegionIdParam='',
@FilialeIdParam='',
@AgentIdParam=''存储过程,运行的时候之前明明可以的,但不知道改哪里了,就出提示
GOALTER PROCEDURE [dbo].[SSRS_Email_memberSwork_week_1]
(
@CorpId INT ,
@RegionIdParam VARCHAR(20) ,
@FilialeIdParam VARCHAR(20) ,
@AgentIdParam VARCHAR(20)
)
AS
BEGIN
DECLARE
@BEGIN DATETIME , -- 数据提取开始时间
@END DATETIME -- 数据提取结束时间
-- 自动推算上周时间
SELECT @BEGIN = DATEADD(DD , -6 , CAST(value AS DATETIME))
, @END = CAST(value AS DATETIME) + 1
FROM DBO.DropDownListDate_New(1,1,GETDATE())
PRINT CONVERT(VARCHAR(100),@BEGIN ,120)
PRINT CONVERT(VARCHAR(100),@END ,120)
--表1应报数的驻场名单
CREATE TABLE #a(JoinTime DATETIME,CorpId INT,MemberId INT,UserName VARCHAR(50),RoleId INT)
INSERT INTO #a
SELECT DISTINCT CAST(a.JoinTime AS DATE)
,a.CorpId
,a.MemberId
,b.UserName
,a.RoleId
FROM
dbo.DP_MembersStatusEveryDay a
-- 获取会员姓名 并 剔除测试账号
INNER JOIN dbo.DP_MemberInfo_View b
ON a.CorpId = b.CorpId AND a.MemberId = b.MemberId
AND b.UserName NOT LIKE '%测试%' AND b.IsDefault = 1
WHERE a.CorpId = @CorpId
AND a.JoinTime >= @BEGIN
AND a.JoinTime < @END
AND a.Status = 2
--------------------------------------------
CREATE TABLE #c(JoinTime DATETIME,Instruction VARCHAR(30),MemberId INT)
insert INTO #c
SELECT #a.JoinTime,e.Instruction , COUNT(DISTINCT #a.MemberId)
FROM #a
INNER JOIN (
SELECT DISTINCT d.CorpId ,d.MemberId,CAST(d.RecvTime AS DATE)reportDate,d.Instruction
FROM
dbo.DP_CommonSmsEntries d
WHERE d.CORPID = @CorpId AND d.Instruction IN ('PI','PL','PP','PM','PN')
AND d.RecvTime >= @BEGIN AND d.RecvTime < @END
)e ON #a.CorpId = e.CorpId AND #a.MemberId = e.MemberId AND CAST(#a.JoinTime AS DATE) = e.reportDate
GROUP BY #a.JoinTime,e.Instruction
SELECT * FROM #c
-------------------休假---------------------------
CREATE TABLE #b(JoinTime DATETIME,CorpId INT,MemberId INT,UserName VARCHAR(50),RoleId INT)
INSERT INTO #b
SELECT DISTINCT CAST(j.JoinTime AS DATE)reportDate
,j.CorpId
,j.MemberId
,MV.UserName
,j.RoleId
FROM
dbo.DP_MembersStatusEveryDay j
-- 休假
INNER JOIN dbo.DP_SysAttendanceEntries c ON j.CorpId = c.CorpId
AND j.MemberId = c.MemberId
AND CAST(j.JoinTime AS DATE) = CAST(c.AttendDate AS DATE)AND TypeId = 2
-- 获取会员姓名 并 剔除测试账号
INNER JOIN dbo.DP_MemberInfo_View MV
ON j.CorpId = MV.CorpId AND j.MemberId = MV.MemberId
AND MV.UserName NOT LIKE '%测试%' AND MV.IsDefault = 1
WHERE j.CorpId = @CorpId AND j.Status =2
AND j.JoinTime >= @BEGIN
AND j.JoinTime < @END--------结果-------------------
SELECT 会员角色,任务名称,上报频率,日期,应上报,ISNULL(实际上报,0) AS 实际上报 ,
CASE 应上报 WHEN 0 THEN 0
ELSE
CAST(ISNULL(实际上报,0)*1.0/应上报 AS DECIMAL(5,4)) END AS 日上报率
FROM (
SELECT '企业会员(督导)'AS 会员角色
,f.TaskName AS 任务名称
,'日报' AS 上报频率
,A.reportDate 日期
,(SELECT COUNT(DISTINCT MemberId ) FROM #a WHERE reportDate = A.reportDate) AS 应上报
,(SELECT #c.MemberId FROM #c WHERE reportDate = A.reportDate)AS 实际上报
FROM (SELECT CAST(DATEADD(DAY,NUMBER,@BEGIN)AS DATE) reportDate FROM master..SPT_VALUES
WHERE TYPE = 'P' AND number BETWEEN 0 AND DATEDIFF(DAY,@BEGIN,@END)-1 ) A
INNER JOIN dbo.DP_SmsTasks f ON 1=1 AND f.Instruction IN ('PI','PL','PP','PM','PN')
UNION ALL
SELECT '企业会员(督导)'AS 会员角色
,'巡店员-休假'AS 任务名称
,'日报' AS 上报频率
,A.reportDate 日期
,0 AS 应上报
,(SELECT COUNT(DISTINCT MemberId ) FROM #b WHERE reportDate = A.reportDate)AS 实际上报
FROM (SELECT CAST(DATEADD(DAY,NUMBER,@BEGIN)AS DATE) reportDate FROM master..SPT_VALUES
WHERE TYPE = 'P' AND number BETWEEN 0 AND DATEDIFF(DAY,@BEGIN,@END)-1 ) A
) RESULT
END
--验证
EXEC SSRS_Email_memberSwork_week_1
@CorpId=1 ,
@RegionIdParam='',
@FilialeIdParam='',
@AgentIdParam=''存储过程,运行的时候之前明明可以的,但不知道改哪里了,就出提示
解决方案 »
- 我在visual studio2005中插入了一个imagebutton控件,想在点击它时关闭浏览器按钮,用C#语言怎么实现,我是初学者,先谢谢了!
- 如何去掉数据库字段中的最后一个","
- 帮我看看我写的触发器
- 两条sql语句的效率问题,高手进
- 请教高手,如何将表定义中的属性列修改为not null
- SQL语句求助,在线等!!
- create database成功执行,却没看到创建的数据库
- SQL Server 复制问题,急。 丢大分
- 请大家帮个忙 -----------------6
- 关于一个insert store procedure 无法生成必要的字段???急急!!!
- sql分类汇总语句的查询,麻烦大家帮忙看一下啦
- 如何计算出分组记录所在的页码[sql2000]!
......
SELECT 会员角色,任务名称,上报频率,日期,应上报,ISNULL(实际上报,0) AS 实际上报 ,
CASE 应上报 WHEN 0 THEN 0
ELSE
CAST(ISNULL(实际上报,0)*1.0/应上报 AS DECIMAL(5,4)) END AS 日上报率
FROM (
SELECT '企业会员(督导)'AS 会员角色
,f.TaskName AS 任务名称
,'日报' AS 上报频率
,A.reportDate 日期
,(SELECT COUNT(DISTINCT MemberId ) FROM #a WHERE reportDate = A.reportDate) AS 应上报
,(SELECT #c.MemberId FROM #c WHERE reportDate = A.reportDate)AS 实际上报
FROM (SELECT CAST(DATEADD(DAY,NUMBER,@BEGIN)AS DATE) reportDate FROM master..SPT_VALUES
WHERE TYPE = 'P' AND number BETWEEN 0 AND DATEDIFF(DAY,@BEGIN,@END)-1 ) A
INNER JOIN dbo.DP_SmsTasks f ON 1=1 AND f.Instruction IN ('PI','PL','PP','PM','PN')
UNION ALL
SELECT '企业会员(督导)'AS 会员角色
,'巡店员-休假'AS 任务名称
,'日报' AS 上报频率
,A.reportDate 日期
,0 AS 应上报
,(SELECT COUNT(DISTINCT MemberId ) FROM #b WHERE reportDate = A.reportDate)AS 实际上报
FROM (SELECT CAST(DATEADD(DAY,NUMBER,@BEGIN)AS DATE) reportDate FROM master..SPT_VALUES
WHERE TYPE = 'P' AND number BETWEEN 0 AND DATEDIFF(DAY,@BEGIN,@END)-1 ) A
) RESULT
........