22,209
社区成员
发帖
与我相关
我的任务
分享
DECLATE @write_time DATETIME
SET @write_time ='2009-05-12'
SELECT ship_id,
SUM(CASE WHEN DATEDIFF(dd,write_time, @write_time)=0 THEN sales ELSE 0 END) sales_today,
SUM(CASE WHEN DATEADD(yy,-1,@write_time) = write_time THEN sales ELSE 0 END) sales_pre_year_today,
SUM(CASE WHEN write_time BETWEEN CONVERT(VARCHAR(5),@write_time,120) + '01-01' AND @write_time THEN sales ELSE 0 END) sales_YTD,
SUM(CASE WHEN write_time BETWEEN DATEADD(yy,-1,CONVERT(VARCHAR(5),@write_time,120) + '01-01') AND DATEADD(yy,-1,@write_time) THEN sales ELSE 0 END) sales_pre_YTD
FROM tb
WHERE write_time BETWEEN DATEADD(yy,-1,CONVERT(VARCHAR(5),@write_time,120) + '01-01') AND DATEADD(yy,-1,@write_time)
OR
write_time BETWEEN BETWEEN CONVERT(VARCHAR(5),@write_time,120) + '01-01' AND @write_time
GROUP BY ship_id
declare @s datetime
set @s='2009-05-12'
select [Shop_Id],
今天销售额=sum(case when datediff(dd,Write_time,@s)=0 then DailySales_Profit else 0 end),
去年今天销售额=sum(case when datediff(yy,Write_time,@s)=1 then DailySales_Profit else 0 end),
今年累计=sum(case when datediff(dd,Write_time,@s) between 0 and datepart(dy,@s) then DailySales_Profit else 0 end),
去年累计=sum(case when datediff(dd,dateadd(yy,-1,@s),Write_time) between 0 and datepart(dd,dateadd(yy,-1,@s)) then DailySales_Profit else 0 end)
from [TB]
group by Shop_Id
/*
Shop_Id 今天销售额 去年今天销售额 今年累计 去年累计
----------- ----------- ----------- ----------- -----------
1 20 20 50 20
2 15 30 40 30
(所影响的行数为 2 行)
*/
/*
最终结果比如查看2009-05-12的记录
得到的是
[Shop_Id] 今天销售额 去年今天销售额 今年累计(2009-01-01到2009-05-12) 去年累计(2008-01-01到2008-05-12)
1 20 20 sum sum
2 15 30 sum sum
*/
--> 测试数据: @tb
declare @tb table ([Shop_Id] int,[DailySales_Profit] int,[Write_time] datetime,[Shop_Industry] varchar(4))
insert into @tb
select 1,20,'2009-05-12','餐饮' union all
select 1,30,'2009-05-11','餐饮' union all
select 1,20,'2008-05-12','餐饮' union all
select 2,15,'2009-05-12','百货' union all
select 2,25,'2009-05-11','百货' union all
select 2,20,'2009-05-13','百货' union all ---添加test,
select 2,30,'2008-05-12','百货'
declare @time datetime
set @time='2009-5-12'
select [Shop_Id],
[今天销售额]=max(case when datediff(day,[Write_time],@time)=0 then [DailySales_Profit] else 0 end),
[去年今天销售额]=max(case when datediff(year,[Write_time],@time)=1 then [DailySales_Profit] else 0 end),
[今年累计]=sum(case when datediff(year,[Write_time],@time)=0 and datediff(day,[Write_time],@time)>=0 then [DailySales_Profit] else 0 end),
[去年累计]=sum(case when datediff(year,[Write_time],@time)=1 and datediff(day,[Write_time],dateadd(year,-1,@time))>=0 then [DailySales_Profit] else 0 end )
from @tb
group by [Shop_Id]
Shop_Id 今天销售额 去年今天销售额 今年累计 去年累计
----------- ----------- ----------- ----------- -----------
1 20 20 50 20
2 15 30 40(看这个) 30
(2 行受影响)
/*
最终结果比如查看2009-05-12的记录
得到的是
[Shop_Id] 今天销售额 去年今天销售额 今年累计(2009-01-01到2009-05-12) 去年累计(2008-01-01到2008-05-12)
1 20 20 sum sum
2 15 30 sum sum
*/
--> 测试数据: @tb
declare @tb table ([Shop_Id] int,[DailySales_Profit] int,[Write_time] datetime,[Shop_Industry] varchar(4))
insert into @tb
select 1,20,'2009-05-12','餐饮' union all
select 1,30,'2009-05-11','餐饮' union all
select 1,20,'2008-05-12','餐饮' union all
select 2,15,'2009-05-12','百货' union all
select 2,25,'2009-05-11','百货' union all
select 2,30,'2008-05-12','百货'
declare @time datetime
set @time='2009-5-12'
select [Shop_Id],
[今天销售额]=max(case when datediff(day,[Write_time],@time)=0 then [DailySales_Profit] else 0 end),
[去年今天销售额]=max(case when datediff(year,[Write_time],@time)=1 then [DailySales_Profit] else 0 end),
[今年累计]=sum(case when datediff(year,[Write_time],@time)=0 and datediff(day,[Write_time],@time)>=0 then [DailySales_Profit] else 0 end),
[去年累计]=sum(case when datediff(year,[Write_time],@time)=1 and datediff(day,[Write_time],dateadd(year,-1,@time))>=0 then [DailySales_Profit] else 0 end )
from @tb
group by [Shop_Id]
Shop_Id 今天销售额 去年今天销售额 今年累计 去年累计
----------- ----------- ----------- ----------- -----------
1 20 20 50 20
2 15 30 40 30
(2 行受影响)
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Shop_Id] int,[DailySales_Profit] int,[Write_time] datetime,[Shop_Industry] varchar(4))
insert [tb]
select 1,20,'2009-05-12','餐饮' union all
select 1,30,'2009-05-11','餐饮' union all
select 1,20,'2008-05-12','餐饮' union all
select 2,15,'2009-05-12','百货' union all
select 2,25,'2009-05-11','百货' union all
select 2,30,'2008-05-12','百货'
---查询---
declare @dt datetime
set @dt='2009-05-12'
select
[Shop_Id],
今天销售额=sum(DailySales_Profit),
去年今天销售额=(select sum(DailySales_Profit) from tb where Shop_Id=t.Shop_Id and year(Write_time)=year(t.Write_time)-1 and month(Write_time)=month(t.Write_time) and day(Write_time)=day(t.Write_time)),
今年累计=(select sum(DailySales_Profit) from tb where Shop_Id=t.Shop_Id and year(Write_time)=year(t.Write_time) and Write_time<=@dt),
去年累计=(select sum(DailySales_Profit) from tb where Shop_Id=t.Shop_Id and year(Write_time)=year(t.Write_time)-1 and Write_time<=dateadd(yy,-1,@dt))
from
tb t
where
datediff(dd,Write_time,@dt)=0
group by
[Shop_Id]
,Write_time
---结果---
Shop_Id 今天销售额 去年今天销售额 今年累计 去年累计
----------- ----------- ----------- ----------- -----------
1 20 20 50 20
2 15 30 40 30
(所影响的行数为 2 行)