我有一个查询,可查询36个月的单位销售历史记录。它按原样工作,但我想知道是否有一种方法可以减少它的冗长和繁琐。
就查询而言,它分为两部分-标头和数据。
这是标题的内容:
SELECT
DATEADD(m, -36, GETDATE()-day(GETDATE()-1)) AS "MM36"
, DATEADD(m, -35, GETDATE()-day(GETDATE()-1)) AS "MM35"
, DATEADD(m, -34, GETDATE()-day(GETDATE()-1)) AS "MM34"
截至今天,这是2011年9月-2011年11月。
, DATEADD(m, -03, GETDATE()-day(GETDATE()-1)) AS "MM03"
, DATEADD(m, -02, GETDATE()-day(GETDATE()-1)) AS "MM02"
, DATEADD(m, -01, GETDATE()-day(GETDATE()-1)) AS "MM01"
2014年6月-2014年8月。36行代码。
拉取单位销售数据的语句如下:
, SUM (CASE WHEN "SalesTable"."SalesDate" BETWEEN DATEADD(m, -36, GETDATE()-day(GETDATE()-1)) AND DATEADD(m, -35, GETDATE()-day(GETDATE()-1))-1 THEN "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" ELSE NULL END) AS "NetQty36"
, SUM (CASE WHEN "SalesTable"."SalesDate" BETWEEN DATEADD(m, -35, GETDATE()-day(GETDATE()-1)) AND DATEADD(m, -34, GETDATE()-day(GETDATE()-1))-1 THEN "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" ELSE NULL END) AS "NetQty35"
, SUM (CASE WHEN "SalesTable"."SalesDate" BETWEEN DATEADD(m, -34, GETDATE()-day(GETDATE()-1)) AND DATEADD(m, -33, GETDATE()-day(GETDATE()-1))-1 THEN "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" ELSE NULL END) AS "NetQty34"
截至今天,这是2011年9月-2011年11月。
, SUM (CASE WHEN "SalesTable"."SalesDate" BETWEEN DATEADD(m, -03, GETDATE()-day(GETDATE()-1)) AND DATEADD(m, -02, GETDATE()-day(GETDATE()-1))-1 THEN "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" ELSE NULL END) AS "NetQty03"
, SUM (CASE WHEN "SalesTable"."SalesDate" BETWEEN DATEADD(m, -02, GETDATE()-day(GETDATE()-1)) AND DATEADD(m, -01, GETDATE()-day(GETDATE()-1))-1 THEN "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" ELSE NULL END) AS "NetQty02"
, SUM (CASE WHEN "SalesTable"."SalesDate" BETWEEN DATEADD(m, -01, GETDATE()-day(GETDATE()-1)) AND DATEADD(m, -00, GETDATE()-day(GETDATE()-1))-1 THEN "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" ELSE NULL END) AS "NetQty01"
2014年6月至8月。
在完成此报告之前,我将要添加更多内容,因此我希望使事情保持尽可能整洁。
谢谢,
编辑
下面的示例显示,使用上面的代码,我可以得到想要的(没有代表张贴图像,因此我必须做一个链接)。它还显示,在数据透视表中,我缺少商品编号和滚动月份。如何添加这两件事?
我尝试按以下方式放入“ ItemTable”。“ ItemNum”字段,但该字段未显示在我的Crystal Report中。在其他尝试放置的地方,都会出现“无法绑定”错误。
select
[0] MM00
, [1] MM01
, [2] MM02
, ...
from (
select
"ItemTable"."ItemNum",DateDiff(m, "SalesTable"."SalesDate", GetDate()) months_ago
, "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" NetQty
from your_table
) as source
pivot
(
sum(NetQty) For months_ago in ([0], [1], [2], [3], ...)
) as PivotTable
再次感谢。
对于数据透视表,这种情况似乎已经成熟。
遵循以下原则
select
[0] MM00
, [1] MM01
, [2] MM02
, ...
from (
select
DateDiff(m, "SalesTable"."SalesDate", GetDate()) months_ago
, "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" NetQty
from your_table
) as source
pivot
(
sum(NetQty) For months_ago in ([0], [1], [2], [3], ...)
) as PivotTable
编辑
改进示例:包括项目编号是相当简单的;只需将其包括在select语句中即可。
select
"ItemTable"."ItemNum"
, [0] MM00
, [1] MM01
, [2] MM02
, ...
from (
select
"ItemTable"."ItemNum"
, DateDiff(m, "SalesTable"."SalesDate", GetDate()) months_ago
, "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" NetQty
from your_table
) as source
pivot
(
sum(NetQty) For months_ago in ([0], [1], [2], [3], ...)
) as PivotTable
为透视列指定动态名称要困难得多。通常的想法是必须将SQL构建为字符串并动态执行。Google搜索会产生许多结果(例如SQL Hints博客)。一些不完整的代码可以为您提供总体思路。
SET @DynamicPivotQuery =
N'select
"ItemTable"."ItemNum"
, [' + @Name1 + ']
, [' + @Name2 + ']
, [' + @NameN + ']
from (
select
"ItemTable"."ItemNum"
/* search MSDN for the best date formatting algorithm */
, CONVERT_TO_STRING("SalesTable"."SalesDate") month
, "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" NetQty
from your_table
) as source
pivot
(
sum(NetQty) For month in (
[' + @Name1 + ']
, [' + @Name2 + ']
, [' + @NameN + ']
)
) as PivotTable';
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句