roy_88版主大人请进下

qq_26006573 2016-08-04 01:56:00
ALTER PROCEDURE P1
(
@StartDate DATETIME , --2016-08-01
@EndDate DATETIME, --2016-08-01 23:59:59
@Usertb_code varchar(50)=NULL
)
AS

DECLARE @Sql NVARCHAR(max)

SELECT @Sql=ISNULL(@Sql+',','')+QUOTENAME(Sntb_name)+'=ISNULL(SUM(CASE WHEN '
+CASE WHEN CONVERT(VARCHAR(8),Sntb_ksdt,8)>CONVERT(VARCHAR(8),Sntb_jsdt,8)
THEN 'CONVERT(VARCHAR(8),b.Xfsourcee_sj,8) BETWEEN '''+CONVERT(VARCHAR(8),Sntb_ksdt,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.Xfsourcee_sj,8) BETWEEN ''00:00:00'' AND '''+CONVERT(VARCHAR(8),Sntb_jsdt,8) +''''
ELSE
'CONVERT(VARCHAR(8),b.Xfsourcee_sj,8) BETWEEN '''+CONVERT(VARCHAR(8),Sntb_ksdt,8) +''' AND '''+CONVERT(VARCHAR(8),Sntb_jsdt,8) +'''' END
+' THEN b.Xfsourcee_jr ELSE 0 END),0)'
FROM dbo.Sntb
--PRINT @Sql
SET @Sql='SELECT '+@Sql+',
COUNT(*) AS 消费次数 ,
COUNT(DISTINCT a.Usertb_code) AS 消费人数 , --这里没意义,显示固定为1，你查的是一个人
ISNULL(SUM(b.Xfsourcee_jr),0) AS 合计
FROM Usertb AS a
INNER JOIN Xfsourcee AS b ON b.Xfsourcee_code = a.Usertb_code
WHERE b.Xfsourcee_sj between @StartDate AND @EndDate'+CASE WHEN @Usertb_code>'' THEN ' AND a.Usertb_code=@Usertb_code' ELSE '' END
EXEC sys.sp_executesql @Sql,N'@StartDate datetime,@EndDate datetime , @Usertb_code varchar(50)',@StartDate,@EndDate,@Usertb_code
GO

版主大人 roy_88 你给我写的这个过程 有一个地方能修改下么，就是 我想统计出的过来是这样的

早餐金额 早餐人数 早餐笔数 加一个早餐人数和笔数 现在只有总合计的。
...全文
160 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
qq_26006573 2016-08-04
  • 打赏
  • 举报
 回复
引用 10 楼 roy_88 的回复:
少了个AND，加上 随便看一下PRINT @Andwhere生成的条件对不对 
CREATE PROCEDURE gc_sdhz
    (
      @tj int ,  --判断查调查询
      @StartDate DATETIME ,--开始时间
      @EndDate DATETIME,   --结束时间
      @Usertb_code varchar(50)=NULL --查询条件
    )
AS
 
DECLARE @Sql NVARCHAR(max),@Andwhere NVARCHAR(max)
BEGIN
IF (@tj<=3)--按1~4级部门
BEGIN
SELECT @Sql=ISNULL(@Sql+',','')+QUOTENAME(xfsd_name)+'=ISNULL(SUM(CASE WHEN '
+CASE WHEN CONVERT(VARCHAR(8),xfsd_ks,8)>CONVERT(VARCHAR(8),xfsd_js,8) 
THEN 'CONVERT(VARCHAR(8),b.recdt,8)  BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.recdt,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +''''
ELSE 
    'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' END 
+' THEN Convert(decimal(18,2),ISNULL(b.smm1+b.smm2,0)/100.0*-1) ELSE 0 END),0),'
+QUOTENAME(xfsd_name+'人数')+'=COUNT(DISTINCT CASE WHEN '
+CASE WHEN CONVERT(VARCHAR(8),xfsd_ks,8)>CONVERT(VARCHAR(8),xfsd_js,8) 
THEN 'CONVERT(VARCHAR(8),b.recdt,8)  BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.recdt,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +''''
ELSE 
    'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' END
+' THEN a.DeptNo1 END),'
+QUOTENAME(xfsd_name+'次数')+'=ISNULL(SUM(CASE WHEN '
+CASE WHEN CONVERT(VARCHAR(8),xfsd_ks,8)>CONVERT(VARCHAR(8),xfsd_js,8) 
THEN 'CONVERT(VARCHAR(8),b.recdt,8)  BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.recdt,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +''''
ELSE 
    'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' END
+' THEN 1 ELSE 0 END),0)'
,@Andwhere=ISNULL(@Andwhere+' OR ','')+
CASE WHEN CONVERT(VARCHAR(8),xfsd_ks,8)>CONVERT(VARCHAR(8),xfsd_js,8) 
THEN '(CONVERT(VARCHAR(8),b.recdt,8)  BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.recdt,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +''')'
ELSE 
    'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' END 

FROM dbo.xfsdHzN

PRINT @Sql
PRINT @Andwhere
SET @Sql='SELECT 
            a.DeptNo1, --部门编号
            a.DeptNa1, --部门名称
            '+@Sql+', --时段组合结果
            COUNT(*) AS cs , --消费次数
            COUNT(DISTINCT a.emcode) AS rs , --消费人数          
             Convert(decimal(18,2),ISNULL(SUM(ISNULL(b.smm1+b.smm2,0)/100.0*-1),0)) AS hj --合计
    FROM    VWpersonal AS a
            INNER JOIN xfsourceRec AS b ON b.hrcode = a.emcode
    WHERE  b.recdt between  @StartDate AND @EndDate AND '+@Usertb_code+' AND ('+@Andwhere+') GROUP BY a.DeptNo1,a.DeptNa1' 
EXEC sys.sp_executesql @Sql,N'@StartDate datetime,@EndDate datetime , @Usertb_code varchar(50)',@StartDate,@EndDate,@Usertb_code
END
END
GO
非常感谢版主的热心帮助结贴了。
中国风 2016-08-04
  • 打赏
  • 举报
 回复
少了个AND，加上 随便看一下PRINT @Andwhere生成的条件对不对 
CREATE PROCEDURE gc_sdhz
    (
      @tj int ,  --判断查调查询
      @StartDate DATETIME ,--开始时间
      @EndDate DATETIME,   --结束时间
      @Usertb_code varchar(50)=NULL --查询条件
    )
AS
 
DECLARE @Sql NVARCHAR(max),@Andwhere NVARCHAR(max)
BEGIN
IF (@tj<=3)--按1~4级部门
BEGIN
SELECT @Sql=ISNULL(@Sql+',','')+QUOTENAME(xfsd_name)+'=ISNULL(SUM(CASE WHEN '
+CASE WHEN CONVERT(VARCHAR(8),xfsd_ks,8)>CONVERT(VARCHAR(8),xfsd_js,8) 
THEN 'CONVERT(VARCHAR(8),b.recdt,8)  BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.recdt,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +''''
ELSE 
    'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' END 
+' THEN Convert(decimal(18,2),ISNULL(b.smm1+b.smm2,0)/100.0*-1) ELSE 0 END),0),'
+QUOTENAME(xfsd_name+'人数')+'=COUNT(DISTINCT CASE WHEN '
+CASE WHEN CONVERT(VARCHAR(8),xfsd_ks,8)>CONVERT(VARCHAR(8),xfsd_js,8) 
THEN 'CONVERT(VARCHAR(8),b.recdt,8)  BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.recdt,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +''''
ELSE 
    'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' END
+' THEN a.DeptNo1 END),'
+QUOTENAME(xfsd_name+'次数')+'=ISNULL(SUM(CASE WHEN '
+CASE WHEN CONVERT(VARCHAR(8),xfsd_ks,8)>CONVERT(VARCHAR(8),xfsd_js,8) 
THEN 'CONVERT(VARCHAR(8),b.recdt,8)  BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.recdt,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +''''
ELSE 
    'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' END
+' THEN 1 ELSE 0 END),0)'
,@Andwhere=ISNULL(@Andwhere+' OR ','')+
CASE WHEN CONVERT(VARCHAR(8),xfsd_ks,8)>CONVERT(VARCHAR(8),xfsd_js,8) 
THEN '(CONVERT(VARCHAR(8),b.recdt,8)  BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.recdt,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +''')'
ELSE 
    'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' END 

FROM dbo.xfsdHzN

PRINT @Sql
PRINT @Andwhere
SET @Sql='SELECT 
            a.DeptNo1, --部门编号
            a.DeptNa1, --部门名称
            '+@Sql+', --时段组合结果
            COUNT(*) AS cs , --消费次数
            COUNT(DISTINCT a.emcode) AS rs , --消费人数          
             Convert(decimal(18,2),ISNULL(SUM(ISNULL(b.smm1+b.smm2,0)/100.0*-1),0)) AS hj --合计
    FROM    VWpersonal AS a
            INNER JOIN xfsourceRec AS b ON b.hrcode = a.emcode
    WHERE  b.recdt between  @StartDate AND @EndDate AND '+@Usertb_code+' AND ('+@Andwhere+') GROUP BY a.DeptNo1,a.DeptNa1' 
EXEC sys.sp_executesql @Sql,N'@StartDate datetime,@EndDate datetime , @Usertb_code varchar(50)',@StartDate,@EndDate,@Usertb_code
END
END
GO
qq_26006573 2016-08-04
  • 打赏
  • 举报
 回复
引用 8 楼 roy_88 的回复:
用以下方法过滤时间段没有的消费 
CREATE PROCEDURE gc_sdhz
    (
      @tj int ,  --判断查调查询
      @StartDate DATETIME ,--开始时间
      @EndDate DATETIME,   --结束时间
      @Usertb_code varchar(50)=NULL --查询条件
    )
AS
 
DECLARE @Sql NVARCHAR(max),@Andwhere NVARCHAR(max)
BEGIN
IF (@tj<=3)--按1~4级部门
BEGIN
SELECT @Sql=ISNULL(@Sql+',','')+QUOTENAME(xfsd_name)+'=ISNULL(SUM(CASE WHEN '
+CASE WHEN CONVERT(VARCHAR(8),xfsd_ks,8)>CONVERT(VARCHAR(8),xfsd_js,8) 
THEN 'CONVERT(VARCHAR(8),b.recdt,8)  BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.recdt,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +''''
ELSE 
    'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' END 
+' THEN Convert(decimal(18,2),ISNULL(b.smm1+b.smm2,0)/100.0*-1) ELSE 0 END),0),'
+QUOTENAME(xfsd_name+'人数')+'=COUNT(DISTINCT CASE WHEN '
+CASE WHEN CONVERT(VARCHAR(8),xfsd_ks,8)>CONVERT(VARCHAR(8),xfsd_js,8) 
THEN 'CONVERT(VARCHAR(8),b.recdt,8)  BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.recdt,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +''''
ELSE 
    'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' END
+' THEN a.DeptNo1 END),'
+QUOTENAME(xfsd_name+'次数')+'=ISNULL(SUM(CASE WHEN '
+CASE WHEN CONVERT(VARCHAR(8),xfsd_ks,8)>CONVERT(VARCHAR(8),xfsd_js,8) 
THEN 'CONVERT(VARCHAR(8),b.recdt,8)  BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.recdt,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +''''
ELSE 
    'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' END
+' THEN 1 ELSE 0 END),0)'
,@Andwhere=ISNULL(@Andwhere+' OR ','')+
CASE WHEN CONVERT(VARCHAR(8),xfsd_ks,8)>CONVERT(VARCHAR(8),xfsd_js,8) 
THEN '(CONVERT(VARCHAR(8),b.recdt,8)  BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.recdt,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +''')'
ELSE 
    'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' END 

FROM dbo.xfsdHzN

PRINT @Sql
SET @Sql='SELECT 
            a.DeptNo1, --部门编号
            a.DeptNa1, --部门名称
            '+@Sql+', --时段组合结果
            COUNT(*) AS cs , --消费次数
            COUNT(DISTINCT a.emcode) AS rs , --消费人数          
             Convert(decimal(18,2),ISNULL(SUM(ISNULL(b.smm1+b.smm2,0)/100.0*-1),0)) AS hj --合计
    FROM    VWpersonal AS a
            INNER JOIN xfsourceRec AS b ON b.hrcode = a.emcode
    WHERE  b.recdt between  @StartDate AND @EndDate AND '+@Usertb_code+'('+@Andwhere+') GROUP BY a.DeptNo1,a.DeptNa1' 
EXEC sys.sp_executesql @Sql,N'@StartDate datetime,@EndDate datetime , @Usertb_code varchar(50)',@StartDate,@EndDate,@Usertb_code
END
END
GO
[中餐]=ISNULL(SUM(CASE WHEN CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '00:00:00' AND '23:59:59' THEN Convert(decimal(18,2),ISNULL(b.smm1+b.smm2,0)/100.0*-1) ELSE 0 END),0),[中餐人数]=COUNT(DISTINCT CASE WHEN CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '00:00:00' AND '23:59:59' THEN a.DeptNo1 END),[中餐次数]=ISNULL(SUM(CASE WHEN CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '00:00:00' AND '23:59:59' THEN 1 ELSE 0 END),0) 消息 156，级别 15，状态 1，第 10 行 关键字 'CONVERT' 附近有语法错误。
中国风 2016-08-04
  • 打赏
  • 举报
 回复
用以下方法过滤时间段没有的消费 
CREATE PROCEDURE gc_sdhz
    (
      @tj int ,  --判断查调查询
      @StartDate DATETIME ,--开始时间
      @EndDate DATETIME,   --结束时间
      @Usertb_code varchar(50)=NULL --查询条件
    )
AS
 
DECLARE @Sql NVARCHAR(max),@Andwhere NVARCHAR(max)
BEGIN
IF (@tj<=3)--按1~4级部门
BEGIN
SELECT @Sql=ISNULL(@Sql+',','')+QUOTENAME(xfsd_name)+'=ISNULL(SUM(CASE WHEN '
+CASE WHEN CONVERT(VARCHAR(8),xfsd_ks,8)>CONVERT(VARCHAR(8),xfsd_js,8) 
THEN 'CONVERT(VARCHAR(8),b.recdt,8)  BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.recdt,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +''''
ELSE 
    'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' END 
+' THEN Convert(decimal(18,2),ISNULL(b.smm1+b.smm2,0)/100.0*-1) ELSE 0 END),0),'
+QUOTENAME(xfsd_name+'人数')+'=COUNT(DISTINCT CASE WHEN '
+CASE WHEN CONVERT(VARCHAR(8),xfsd_ks,8)>CONVERT(VARCHAR(8),xfsd_js,8) 
THEN 'CONVERT(VARCHAR(8),b.recdt,8)  BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.recdt,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +''''
ELSE 
    'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' END
+' THEN a.DeptNo1 END),'
+QUOTENAME(xfsd_name+'次数')+'=ISNULL(SUM(CASE WHEN '
+CASE WHEN CONVERT(VARCHAR(8),xfsd_ks,8)>CONVERT(VARCHAR(8),xfsd_js,8) 
THEN 'CONVERT(VARCHAR(8),b.recdt,8)  BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.recdt,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +''''
ELSE 
    'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' END
+' THEN 1 ELSE 0 END),0)'
,@Andwhere=ISNULL(@Andwhere+' OR ','')+
CASE WHEN CONVERT(VARCHAR(8),xfsd_ks,8)>CONVERT(VARCHAR(8),xfsd_js,8) 
THEN '(CONVERT(VARCHAR(8),b.recdt,8)  BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.recdt,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +''')'
ELSE 
    'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' END 

FROM dbo.xfsdHzN

PRINT @Sql
SET @Sql='SELECT 
            a.DeptNo1, --部门编号
            a.DeptNa1, --部门名称
            '+@Sql+', --时段组合结果
            COUNT(*) AS cs , --消费次数
            COUNT(DISTINCT a.emcode) AS rs , --消费人数          
             Convert(decimal(18,2),ISNULL(SUM(ISNULL(b.smm1+b.smm2,0)/100.0*-1),0)) AS hj --合计
    FROM    VWpersonal AS a
            INNER JOIN xfsourceRec AS b ON b.hrcode = a.emcode
    WHERE  b.recdt between  @StartDate AND @EndDate AND '+@Usertb_code+'('+@Andwhere+') GROUP BY a.DeptNo1,a.DeptNa1' 
EXEC sys.sp_executesql @Sql,N'@StartDate datetime,@EndDate datetime , @Usertb_code varchar(50)',@StartDate,@EndDate,@Usertb_code
END
END
GO
qq_26006573 2016-08-04
  • 打赏
  • 举报
 回复
引用 6 楼 roy_88 的回复:
如果有消费不在时间段里会不一致，有这样的数据？
是的，我查过了，是因为下面的总计是吧 我传进去查询的所有数据总了 次数 人数 金额的合计，而这里面的数据有可能不在我所统计的那几个时段内，所以时段内没那些数据，总计有，所以就不一样了，下面的合计金额 次数 人数，应该要统计我目前查出的 那些时段里面的合计，而不是我传进去日期的查出来的数据合计。
中国风 2016-08-04
  • 打赏
  • 举报
 回复
如果有消费不在时间段里会不一致，有这样的数据？
qq_26006573 2016-08-04
  • 打赏
  • 举报
 回复
引用 3 楼 roy_88 的回复:
次数没问题，不需要过滤重复 人数会过滤重复 如：张三 早餐 张三 晚餐 计算人数过滤重复时，总人数为1，早餐1人，晚餐1人
我估计是不是，上面时段 统计的有些数据不在那些个时段里，下面总计把所有的都计算力面了，所以不一样
qq_26006573 2016-08-04
  • 打赏
  • 举报
 回复
引用 3 楼 roy_88 的回复:
次数没问题，不需要过滤重复 人数会过滤重复 如：张三 早餐 张三 晚餐 计算人数过滤重复时，总人数为1，早餐1人，晚餐1人
CREATE PROCEDURE gc_sdhz ( @tj int , --判断查调查询 @StartDate DATETIME ,--开始时间 @EndDate DATETIME, --结束时间 @Usertb_code varchar(50)=NULL --查询条件 ) AS DECLARE @Sql NVARCHAR(max) BEGIN IF (@tj<=3)--按1~4级部门 BEGIN SELECT @Sql=ISNULL(@Sql+',','')+QUOTENAME(xfsd_name)+'=ISNULL(SUM(CASE WHEN ' +CASE WHEN CONVERT(VARCHAR(8),xfsd_ks,8)>CONVERT(VARCHAR(8),xfsd_js,8) THEN 'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.recdt,8) BETWEEN ''00:00:00'' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' ELSE 'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' END +' THEN Convert(decimal(18,2),ISNULL(b.smm1+b.smm2,0)/100.0*-1) ELSE 0 END),0),' +QUOTENAME(xfsd_name+'人数')+'=COUNT(DISTINCT CASE WHEN ' +CASE WHEN CONVERT(VARCHAR(8),xfsd_ks,8)>CONVERT(VARCHAR(8),xfsd_js,8) THEN 'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.recdt,8) BETWEEN ''00:00:00'' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' ELSE 'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' END +' THEN a.DeptNo1 END),' +QUOTENAME(xfsd_name+'次数')+'=ISNULL(SUM(CASE WHEN ' +CASE WHEN CONVERT(VARCHAR(8),xfsd_ks,8)>CONVERT(VARCHAR(8),xfsd_js,8) THEN 'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.recdt,8) BETWEEN ''00:00:00'' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' ELSE 'CONVERT(VARCHAR(8),b.recdt,8) BETWEEN '''+CONVERT(VARCHAR(8),xfsd_ks,8) +''' AND '''+CONVERT(VARCHAR(8),xfsd_js,8) +'''' END +' THEN 1 ELSE 0 END),0)' FROM dbo.xfsdHzN PRINT @Sql SET @Sql='SELECT a.DeptNo1, --部门编号 a.DeptNa1, --部门名称 '+@Sql+', --时段组合结果 COUNT(*) AS cs , --消费次数 COUNT(DISTINCT a.emcode) AS rs , --消费人数 Convert(decimal(18,2),ISNULL(SUM(ISNULL(b.smm1+b.smm2,0)/100.0*-1),0)) AS hj --合计 FROM VWpersonal AS a INNER JOIN xfsourceRec AS b ON b.hrcode = a.emcode WHERE b.recdt between @StartDate AND @EndDate AND '+@Usertb_code+' GROUP BY a.DeptNo1,a.DeptNa1' EXEC sys.sp_executesql @Sql,N'@StartDate datetime,@EndDate datetime , @Usertb_code varchar(50)',@StartDate,@EndDate,@Usertb_code END END GO 版主大人我按照你给的，换成了查部门，结果就出现了 DeptNo1 DeptNa1 中餐 中餐人数 中餐次数 夜餐 夜餐人数 夜餐次数 cs rs hj 100002 业务部 -3.00 1 3 -377.00 1 179 194 6 -389.30 100003 生产部 -0.10 1 1 -190.00 1 81 82 2 -190.10 单独的次数和人数，金额 ， 和总数对不上了。
中国风 2016-08-04
  • 打赏
  • 举报
 回复
次数没问题，不需要过滤重复 人数会过滤重复 如：张三 早餐 张三 晚餐 计算人数过滤重复时，总人数为1，早餐1人，晚餐1人
qq_26006573 2016-08-04
  • 打赏
  • 举报
 回复
引用 1 楼 roy_88 的回复:
如果是这样，同一个人在早餐/晚餐出现时，会重复计算 这样改 

ALTER  PROCEDURE P1
    (
      @StartDate DATETIME ,    --2016-08-01
      @EndDate DATETIME,    --2016-08-01 23:59:59
      @Usertb_code varchar(50)=NULL 
    )
AS
 
DECLARE @Sql NVARCHAR(max)
  
SELECT @Sql=ISNULL(@Sql+',','')+QUOTENAME(Sntb_name)+'=ISNULL(SUM(CASE WHEN '
+CASE WHEN CONVERT(VARCHAR(8),Sntb_ksdt,8)>CONVERT(VARCHAR(8),Sntb_jsdt,8) 
THEN 'CONVERT(VARCHAR(8),b.Xfsourcee_sj,8)  BETWEEN '''+CONVERT(VARCHAR(8),Sntb_ksdt,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.Xfsourcee_sj,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),Sntb_jsdt,8) +''''
ELSE 
    'CONVERT(VARCHAR(8),b.Xfsourcee_sj,8) BETWEEN '''+CONVERT(VARCHAR(8),Sntb_ksdt,8) +''' AND '''+CONVERT(VARCHAR(8),Sntb_jsdt,8) +'''' END 
+' THEN b.Xfsourcee_jr ELSE 0 END),0),'
+QUOTENAME(Sntb_name+'人数')+'=COUNT(DISTINCT CASE WHEN '
+CASE WHEN CONVERT(VARCHAR(8),Sntb_ksdt,8)>CONVERT(VARCHAR(8),Sntb_jsdt,8) 
THEN 'CONVERT(VARCHAR(8),b.Xfsourcee_sj,8)  BETWEEN '''+CONVERT(VARCHAR(8),Sntb_ksdt,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.Xfsourcee_sj,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),Sntb_jsdt,8) +''''
ELSE 
    'CONVERT(VARCHAR(8),b.Xfsourcee_sj,8) BETWEEN '''+CONVERT(VARCHAR(8),Sntb_ksdt,8) +''' AND '''+CONVERT(VARCHAR(8),Sntb_jsdt,8) +'''' END
+' THEN a.Usertb_code END),'
+QUOTENAME(Sntb_name+'次数')+'=ISNULL(SUM(CASE WHEN '
+CASE WHEN CONVERT(VARCHAR(8),Sntb_ksdt,8)>CONVERT(VARCHAR(8),Sntb_jsdt,8) 
THEN 'CONVERT(VARCHAR(8),b.Xfsourcee_sj,8)  BETWEEN '''+CONVERT(VARCHAR(8),Sntb_ksdt,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.Xfsourcee_sj,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),Sntb_jsdt,8) +''''
ELSE 
    'CONVERT(VARCHAR(8),b.Xfsourcee_sj,8) BETWEEN '''+CONVERT(VARCHAR(8),Sntb_ksdt,8) +''' AND '''+CONVERT(VARCHAR(8),Sntb_jsdt,8) +'''' END
+' THEN 1 ELSE 0 END),0)'
FROM dbo.Sntb
--PRINT @Sql
SET @Sql='SELECT  '+@Sql+',
            COUNT(*) AS 消费次数 ,
            COUNT(DISTINCT a.Usertb_code) AS 消费人数 ,            --这里没意义,显示固定为1，你查的是一个人
            ISNULL(SUM(b.Xfsourcee_jr),0) AS 合计
    FROM    Usertb AS a
            INNER JOIN Xfsourcee AS b ON b.Xfsourcee_code = a.Usertb_code
    WHERE  b.Xfsourcee_sj between  @StartDate AND @EndDate'+CASE WHEN @Usertb_code>'' THEN ' AND a.Usertb_code=@Usertb_code' ELSE '' END 
EXEC sys.sp_executesql @Sql,N'@StartDate datetime,@EndDate datetime , @Usertb_code varchar(50)',@StartDate,@EndDate,@Usertb_code

GO
--查某一个员工
EXEC dbo.P1 @StartDate = '2016-08-01', -- date
    @EndDate='2016-08-02 23:59:59',
	@Usertb_code = '00001' -- varchar(50)

--查所有员工
EXEC dbo.P1 @StartDate = '2016-08-01', -- date
    @EndDate='2016-08-02 23:59:59',
    @Usertb_code = '' -- varchar(50)
那这个人早餐消费，晚餐也消费了会出问题么？不是应该2笔记录么？早餐1笔，晚餐1笔，您说的重复是什么意思？
中国风 2016-08-04
  • 打赏
  • 举报
 回复
如果是这样，同一个人在早餐/晚餐出现时，会重复计算 这样改 

ALTER  PROCEDURE P1
    (
      @StartDate DATETIME ,    --2016-08-01
      @EndDate DATETIME,    --2016-08-01 23:59:59
      @Usertb_code varchar(50)=NULL 
    )
AS
 
DECLARE @Sql NVARCHAR(max)
  
SELECT @Sql=ISNULL(@Sql+',','')+QUOTENAME(Sntb_name)+'=ISNULL(SUM(CASE WHEN '
+CASE WHEN CONVERT(VARCHAR(8),Sntb_ksdt,8)>CONVERT(VARCHAR(8),Sntb_jsdt,8) 
THEN 'CONVERT(VARCHAR(8),b.Xfsourcee_sj,8)  BETWEEN '''+CONVERT(VARCHAR(8),Sntb_ksdt,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.Xfsourcee_sj,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),Sntb_jsdt,8) +''''
ELSE 
    'CONVERT(VARCHAR(8),b.Xfsourcee_sj,8) BETWEEN '''+CONVERT(VARCHAR(8),Sntb_ksdt,8) +''' AND '''+CONVERT(VARCHAR(8),Sntb_jsdt,8) +'''' END 
+' THEN b.Xfsourcee_jr ELSE 0 END),0),'
+QUOTENAME(Sntb_name+'人数')+'=COUNT(DISTINCT CASE WHEN '
+CASE WHEN CONVERT(VARCHAR(8),Sntb_ksdt,8)>CONVERT(VARCHAR(8),Sntb_jsdt,8) 
THEN 'CONVERT(VARCHAR(8),b.Xfsourcee_sj,8)  BETWEEN '''+CONVERT(VARCHAR(8),Sntb_ksdt,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.Xfsourcee_sj,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),Sntb_jsdt,8) +''''
ELSE 
    'CONVERT(VARCHAR(8),b.Xfsourcee_sj,8) BETWEEN '''+CONVERT(VARCHAR(8),Sntb_ksdt,8) +''' AND '''+CONVERT(VARCHAR(8),Sntb_jsdt,8) +'''' END
+' THEN a.Usertb_code END),'
+QUOTENAME(Sntb_name+'次数')+'=ISNULL(SUM(CASE WHEN '
+CASE WHEN CONVERT(VARCHAR(8),Sntb_ksdt,8)>CONVERT(VARCHAR(8),Sntb_jsdt,8) 
THEN 'CONVERT(VARCHAR(8),b.Xfsourcee_sj,8)  BETWEEN '''+CONVERT(VARCHAR(8),Sntb_ksdt,8) +''' AND ''23:59:59'' OR CONVERT(VARCHAR(8),b.Xfsourcee_sj,8) BETWEEN  ''00:00:00'' AND '''+CONVERT(VARCHAR(8),Sntb_jsdt,8) +''''
ELSE 
    'CONVERT(VARCHAR(8),b.Xfsourcee_sj,8) BETWEEN '''+CONVERT(VARCHAR(8),Sntb_ksdt,8) +''' AND '''+CONVERT(VARCHAR(8),Sntb_jsdt,8) +'''' END
+' THEN 1 ELSE 0 END),0)'
FROM dbo.Sntb
--PRINT @Sql
SET @Sql='SELECT  '+@Sql+',
            COUNT(*) AS 消费次数 ,
            COUNT(DISTINCT a.Usertb_code) AS 消费人数 ,            --这里没意义,显示固定为1，你查的是一个人
            ISNULL(SUM(b.Xfsourcee_jr),0) AS 合计
    FROM    Usertb AS a
            INNER JOIN Xfsourcee AS b ON b.Xfsourcee_code = a.Usertb_code
    WHERE  b.Xfsourcee_sj between  @StartDate AND @EndDate'+CASE WHEN @Usertb_code>'' THEN ' AND a.Usertb_code=@Usertb_code' ELSE '' END 
EXEC sys.sp_executesql @Sql,N'@StartDate datetime,@EndDate datetime , @Usertb_code varchar(50)',@StartDate,@EndDate,@Usertb_code

GO
--查某一个员工
EXEC dbo.P1 @StartDate = '2016-08-01', -- date
    @EndDate='2016-08-02 23:59:59',
	@Usertb_code = '00001' -- varchar(50)

--查所有员工
EXEC dbo.P1 @StartDate = '2016-08-01', -- date
    @EndDate='2016-08-02 23:59:59',
    @Usertb_code = '' -- varchar(50)
表数据损坏，检查损坏表
FETCH NEXT FROM ROY_table INTO @table_name WHILE @@FETCH_STATUS = 0  BEGIN PRINT '--------------数据表'+@table_name + '的检查整理------------------' DBCC CheckTable (@table_name) PRINT '---...
版主
https://me.csdn.net/roy_88 转载于:https://www.cnblogs.com/MaxElephant/p/9887878.html
SQL SERVER性能分析--死锁检测数据库阻塞语句
SQL SERVER性能分析--死锁检测数据库阻塞语句 工作中数据库经常出现内存,找了篇文章 ...参照CSDN，中国风(Roy)一...整理人：中国风(Roy) 参照Roy_88的博客 http://blog.csdn.net/roy_88/archive/2008/07/21/2682
mysql触发器流水号问题的解决
首先感谢roy_88 版主的执教，这个编码，我就是稍微改动一下，让他更完善。 不知道为什么发帖没有成功。开个博客吧，记录一下自己的学习过程，也挺好。感谢回答我这老菜鸟问题的人们。 create table orders(orders_...
redis高并发抽奖
代码有点缭乱，没时间整理，如果有误还请留言斧正。现在进入正题。 一、思路 1.奖品：  奖品分为奖品id(编号)、count(数量)、pointVal(价值)、remainCount (剩余数量)分为四个参数组成。 2.概率规则：  单个产品...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章