大分求一个高手解答

[qq_26006573]

create table Usertb --人员表
(
Usertb_id int identity(1,1), --序号
Usertb_code varchar(50),--人员编号
Usertb_name varchar(50),--人员名称
)

create table Sntb --时段表
(
Sntb_id int identity(1,1), --序号
Sntb_name varchar(50),--时段名称
Sntb_ksdt datetime,-- 开始时间
Sntb_jsdt datetime-- 结束时间
)

create table Xfsource --消费表
(
Xfsource_id int identity(1,1), --序号
Xfsource_code varchar(50),--人员编号
Xfsource_jr int ,--消费金额
Xfsource_sj datetime,-- 消费时间
)

哪位神人帮写一个存储过程,返回一个查询,时段表可能会添加1个或多个时段,查询出所有人在已添加的时段内的消费额,
(列如:张三 早餐消费了多少,中餐消费了多少 合计多少...),需要注意的是,如果我的一个时段设置为23:00:00
到 01:00:00 就跨天了,这个时段的数据也要统计到第二天1点前的.

[qq_26006573]

引用 1 楼 roy_88 的回复:

Sntb_ksdt datetime,-- 开始时间 Sntb_jsdt datetime-- 结束时间 --跨天的日期格式为？ 1900-01-01 22：00 ~1900-01-02 08：00 这样？

是的

[qq_26006573]

引用 1 楼 roy_88 的回复:

Sntb_ksdt datetime,-- 开始时间 Sntb_jsdt datetime-- 结束时间 --跨天的日期格式为？ 1900-01-01 22：00 ~1900-01-02 08：00 这样？

create table Usertb --人员表 ( Usertb_id int identity(1,1), --序号 Usertb_code varchar(50),--人员编号 Usertb_name varchar(50),--人员名称 ) insert Usertb values('00001','李四') insert Usertb values('00002','李五') create table Sntb --时段表 ( Sntb_id int identity(1,1), --序号 Sntb_name varchar(50),--时段名称 Sntb_ksdt datetime,-- 开始时间 Sntb_jsdt datetime-- 结束时间 ) insert Sntb values('早餐','2016-08-01 07:00:00','2016-08-01 08:30:00') insert Sntb values('晚餐','2016-08-01 18:00:00','2016-08-01 20:30:00') insert Sntb values('夜餐','2016-08-01 23:00:00','2016-08-02 01:30:00') create table Xfsourcee --消费表 ( Xfsourcee_id int identity(1,1), --序号 Xfsourcee_code varchar(50),--人员编号 Xfsourcee_jr int ,--消费金额 Xfsourcee_sj datetime,-- 消费时间 ) insert Xfsourcee values('00001',100,'2016-08-01 07:30:00') insert Xfsourcee values('00001',200,'2016-08-01 07:31:00') insert Xfsourcee values('00001',300,'2016-08-01 18:30:00') insert Xfsourcee values('00001',500,'2016-08-02 01:30:00') 结果效果 姓名 早餐合计 晚餐合计 夜餐合计 消费次数合计 消费人数合计 总计 李四 300 300 500 4 1 1100

[唐诗三百首]

-- 建存储过程
create proc sp_getSummary(@fdate datetime,@empno varchar(50))
as
begin

select 姓名=a.Usertb_name,
       早餐合计=sum(case when c.Sntb_name='早餐' then b.Xfsourcee_jr else 0 end),
       晚餐合计=sum(case when c.Sntb_name='晚餐' then b.Xfsourcee_jr else 0 end),
       夜餐合计=sum(case when c.Sntb_name='夜餐' then b.Xfsourcee_jr else 0 end),
       消费次数合计=count(1),
       消费人数合计=count(distinct a.Usertb_code),
       总计=sum(b.Xfsourcee_jr)
 from Usertb a
 inner join Xfsourcee b on a.Usertb_code=b.Xfsourcee_code
 inner join Sntb c on b.Xfsourcee_sj between c.Sntb_ksdt and c.Sntb_jsdt
 where a.Usertb_code=@empno
 and b.Xfsourcee_sj between @fdate and cast(convert(varchar,dateadd(d,1,@fdate),23)
                                            +' '
                                            +(select top 1 convert(varchar,Sntb_jsdt,108) from Sntb where Sntb_name='夜餐') as datetime)
 group by a.Usertb_code,a.Usertb_name
end


-- 执行
exec sp_getSummary @fdate='2016-08-01',@empno='00001'

/*
姓名            早餐合计        晚餐合计        夜餐合计        消费次数合计      消费人数合计      总计
------------- ----------- ----------- ----------- ----------- ----------- -----------
李四             300         300         500         4           1           1100

(1 row(s) affected)
*/

[中国风]

你是查某一个时间段还是只是某一天 比如：查8月一个月某天的小计，某个月一个总计 这样的情况，应该 加上一个日期段

[中国风]

看你的测试数据，时段表 不需要指定具体日期 就一个时间 就行了，这样应用更有效

[中国风]

消费人数在这里没意义，都会显示为1，你是显示人名，把分组去掉时才有意义

CREATE PROCEDURE P1
    (
      @Date date ,
      @Usertb_code varchar(50)
    )
AS
    SELECT  ISNULL(SUM(CASE WHEN c.Sntb_name = '早餐' THEN b.Xfsourcee_jr
                     ELSE 0
                END),0) AS 早餐合计 ,
            ISNULL(SUM(CASE WHEN c.Sntb_name = '晚餐' THEN b.Xfsourcee_jr
                     ELSE 0
                END),0) AS 晚餐合计 ,
            ISNULL(SUM(CASE WHEN c.Sntb_name = '夜餐' THEN b.Xfsourcee_jr
                     ELSE 0
                END),0) AS 夜餐合计 ,
            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
            INNER JOIN dbo.Sntb AS c ON b.Xfsourcee_sj BETWEEN c.Sntb_ksdt
                                                       AND    c.Sntb_jsdt
    WHERE   DATEDIFF(dd, @Date, c.Sntb_ksdt) = 0;

GO
EXEC dbo.P1 @Date = '2016-08-01', -- date
    @Usertb_code = '00001' -- varchar(50)

[中国风]

Sntb_ksdt datetime,-- 开始时间 Sntb_jsdt datetime-- 结束时间 --跨天的日期格式为？ 1900-01-01 22：00 ~1900-01-02 08：00 这样？