;with tbl(dt, rfdl1, rfdl2, rfdl3, rfdl4) as
(
select '2014-10-10', 1, 2, 3, 4 union all
select '2014-10-11', 5, 6, 7 , 8 union all
select '2014-10-12', 9, 10, 11, 12 union all
select '2014-10-13', 13, 14, 15, 16 union all
select '2015-10-10', 5, 8, 10, 4 union all
select '2015-10-11', 3, 5, 15, 6 union all
select '2015-10-12', 4, 7, 5, 10 union all
select '2015-10-13', 5, 6, 7, 8
),
tbl2 as
(
select dt, [品名], [数量] from tbl
unpivot
(
[数量] for [品名] in([rfdl1],[rfdl2],[rfdl3],[rfdl4])
) as T
)
select
t1.[品名],
t1.[数量] as [本日], t2.[数量] as [日同期],
(t1.[数量] - t2.[数量]) as [日同比],
t3.[数量] as [本月], t4.[数量] as [月同期],
(t3.[数量] - t4.[数量]) as [月同比],
t5.[数量] as [本年], t6.[数量] as [年同期],
(t5.[数量] - t6.[数量]) as [年同比]
from
(
select [品名],[数量] from tbl2 where dt = convert(nvarchar(10), getdate(), 120)
) as t1 -- 本日
left join
(
select [品名],[数量] from tbl2 where dt = convert(nvarchar(10), dateadd(YEAR, -1, getdate()), 120)
) as t2 -- 日同期
on t1.[品名] = t2.[品名]
left join
(
select [品名],sum([数量]) as [数量] from tbl2
where dt >= convert(nvarchar(7), getdate(), 120) + '-01'
and dt < convert(nvarchar(10), dateadd(DAY, 1, getdate()), 120)
group by [品名]
) as t3 -- 本月
on t1.[品名] = t3.[品名]
left join
(
select [品名],sum([数量]) as [数量] from tbl2
where dt >= convert(nvarchar(7), dateadd(YEAR, -1, getdate()), 120) + '-01'
and dt < convert(nvarchar(10), dateadd(DAY, 1, dateadd(YEAR, -1, getdate())), 120)
group by [品名]
) as t4 -- 月同期
on t1.[品名] = t4.[品名]
left join
(
select [品名],sum([数量]) as [数量] from tbl2
where dt >= convert(nvarchar(4), getdate(), 120) + '-01-01'
and dt < convert(nvarchar(10), dateadd(DAY, 1, getdate()), 120)
group by [品名]
) as t5 -- 本年
on t1.[品名] = t5.[品名]
left join
(
select [品名],sum([数量]) as [数量] from tbl2
where dt >= convert(nvarchar(4), dateadd(YEAR, -1, getdate()), 120) + '-01-01'
and dt < convert(nvarchar(10), dateadd(DAY, 1, dateadd(YEAR, -1, getdate())), 120)
group by [品名]
) as t6 -- 年同期
on t1.[品名] = t6.[品名]
------------------------------------------------------------------------------------------------------------------
品名 本日 日同期 日同比 本月 月同期 月同比 本年 年同期 年同比
rfdl1 4 9 -5 12 15 -3 12 15 -3
rfdl2 7 10 -3 20 18 2 20 18 2
rfdl3 5 11 -6 30 21 9 30 21 9
rfdl4 10 12 -2 20 24 -4 20 24 -4
