4,816
社区成员
发帖
与我相关
我的任务
分享
select ctrl_id,convert(varchar(20),lr.consume_dt,23) as con_date,sum(act_usedmoney) as total_money,count(act_usedmoney) as times,lr.shopTime from (select *, shopTime =
case when (substring(convert(varchar(20),consume_dt,120),12,2)>=2 and substring(convert(varchar(20),consume_dt,120),12,2) <=4) then 'weeHourMeal'
when (substring(convert(varchar(20),consume_dt,120),12,2)>=11 and substring(convert(varchar(20),consume_dt,120),12,2) <=14) then 'lunch'
when (substring(convert(varchar(20),consume_dt,120),12,2)>=17 and substring(convert(varchar(20),consume_dt,120),12,2) <=20) then 'supper'
else 'otherMeal' end
from location_records) as lr where 1=1 and act_usedmoney !=0
group by convert(varchar(20),lr.consume_dt,23),ctrl_id,shopTime order by ctrl_id,convert(varchar(20),lr.consume_dt,23)
select
ctrl_id,
convert(varchar(10),consume_dt,23) as con_date,
sum(case when datepart(hh,consume_dt) between 2 and 4 then act_usedmoney else 0 end) as weeHourMeal_total_money,
sum(case when datepart(hh,consume_dt) between 2 and 4 then 1 else 0 end) as weeHourMeal_times,
sum(case when datepart(hh,consume_dt) between 11 and 14 then act_usedmoney else 0 end) as lunch_total_money,
sum(case when datepart(hh,consume_dt) between 11 and 14 then 1 else 0 end) as lunch_times,
sum(case when datepart(hh,consume_dt) between 17 and 20 then act_usedmoney else 0 end) as supper_total_money,
sum(case when datepart(hh,consume_dt) between 17 and 20 then 1 else 0 end) as supper_times
from location_records
group by ctrl_id,convert(varchar(10),consume_dt,23)
order by ctrl_id,convert(varchar(10),consume_dt,23)