34,587
社区成员
发帖
与我相关
我的任务
分享
SELECT
ItemID as '服务编号',
ItemName as '服务项目',
sum(case when datediff(day,ConsumeDate,getdate())=0 then (ServiceItemMoney) else 0 end) as '本日',
sum(case when datediff(day,ConsumeDate,getdate())=-1 then (ServiceItemMoney) else 0 end) as '上日累计',
sum(case when datediff(month,ConsumeDate,getdate())=0 then (ServiceItemMoney) else 0 end)as '本月累计',
sum(case when datediff(month,ConsumeDate,getdate())=-1 then (ServiceItemMoney) else 0 end)as '上月累计',
sum(case when datediff(year,ConsumeDate,getdate())=0 then (ServiceItemMoney) else 0 end)as '本年累计'
from tb_ClientConsume
where SortType=1
group by ItemID,ItemName
SELECT
ItemID as '服务编号',
ItemName as '服务项目',
sum(case when datediff(day,ConsumeDate,getdate())=0 then (ServiceItemMoney) else 0 end) as '本日',
sum(case when datediff(day,ConsumeDate,getdate())=1 then (ServiceItemMoney) else 0 end) as '上日累计',
sum(case when datediff(month,ConsumeDate,getdate())=0 then (ServiceItemMoney) else 0 end)as '本月累计',
sum(case when datediff(month,ConsumeDate,getdate())=1 then (ServiceItemMoney) else 0 end)as '上月累计',
sum(case when datediff(year,ConsumeDate,getdate())=0 then (ServiceItemMoney) else 0 end)as '本年累计'
from tb_ClientConsume
where SortType=1
group by ItemID,ItemName
datediff(day,ConsumeDate,getdate())为:getdate() - ConsumeDate
SELECT
ItemID as '服务编号',
ItemName as '服务项目',
sum(case when datediff(day,ConsumeDate,getdate())=0 then (ServiceItemMoney) else 0 end) as '本日',
sum(case when datediff(day,ConsumeDate,getdate())=1 then (ServiceItemMoney) else 0 end) as '上日累计',
sum(case when datediff(month,ConsumeDate,getdate())=0 then (ServiceItemMoney) else 0 end)as '本月累计',
sum(case when datediff(month,ConsumeDate,getdate())=1 then (ServiceItemMoney) else 0 end)as '上月累计',
sum(case when datediff(year,ConsumeDate,getdate())=0 then (ServiceItemMoney) else 0 end)as '本年累计'
from tb_ClientConsume
where SortType=1
group by ItemID,ItemName