代码之家 › 专栏 › 技术社区 › Christian V

当数据丢失时报告数据(即如何在给定的一周内为客户报告零活动)

reporting-services sql-server-2008 sql-server

1

Christian V · 技术社区 · 16 年前

我想创建一个报告,汇总每个客户每周的活动数。如果给定一周内该客户没有活动,则应显示0(即下面示例中的第3周和第4周)

客户活动周数
α4×1
α2×2
α0×3
α0×4
α1×5
B…
C…

问题是,如果没有活动,就没有数据可报告,因此下面样本中的第3周和第4周不在报告中。

解决这个问题的“最佳”方法是什么?

2 回复 | 直到 16 年前

1

Community Mohan Dere 9 年前

试试这个:

DECLARE @YourTable table (CUSTOMER char(1), ACTIVITIES int, WEEKNUMBER int)
INSERT @YourTable VALUES ('A' , 4 , 1)
INSERT @YourTable VALUES ('A' , 2 , 2)
INSERT @YourTable VALUES ('A' , 0 , 3)
INSERT @YourTable VALUES ('A' , 0 , 4)
INSERT @YourTable VALUES ('A' , 1 , 5)
INSERT @YourTable VALUES ('B' , 5 , 3)
INSERT @YourTable VALUES ('C' , 2 , 4)


DECLARE @StartNumber int
       ,@EndNumber   int
SELECT @StartNumber=1
      ,@EndNumber=5

;WITH AllNumbers AS
(
    SELECT @StartNumber AS Number
    UNION ALL
    SELECT Number+1
        FROM AllNumbers
        WHERE Number<@EndNumber
)
, AllCustomers AS
(
    SELECT DISTINCT CUSTOMER FROM @YourTable
)
SELECT
    n.Number AS WEEKNUMBER, c.CUSTOMER, CASE WHEN y.Customer IS NULL THEN 0 ELSE y.ACTIVITIES END AS ACTIVITIES
    FROM AllNumbers                   n
        CROSS JOIN AllCustomers       c
        LEFT OUTER JOIN @YourTable    y ON n.Number=y.WEEKNUMBER AND c.CUSTOMER=y.CUSTOMER

--OPTION (MAXRECURSION 500)

输出:

WEEKNUMBER  CUSTOMER ACTIVITIES
----------- -------- -----------
1           A        4
1           B        0
1           C        0
2           A        2
2           B        0
2           C        0
3           A        0
3           B        5
3           C        0
4           A        0
4           B        0
4           C        2
5           A        1
5           B        0
5           C        0

(15 row(s) affected)

我用CTE来建立一个数字表,但是你可以建立一个永久的,看看这个问题: What is the best way to create and populate a numbers table? . 您可以在不使用CTE的情况下编写查询(与上面的结果相同):

SELECT
    n.Number AS WEEKNUMBER, c.CUSTOMER, CASE WHEN y.Customer IS NULL THEN 0 ELSE y.ACTIVITIES END AS ACTIVITIES
    FROM Numbers n
        CROSS JOIN (SELECT DISTINCT 
                        CUSTOMER 
                        FROM @YourTable
                   ) c
        LEFT OUTER JOIN @YourTable y ON n.Number=y.WEEKNUMBER AND c.CUSTOMER=y.CUSTOMER
    WHERE n.Number>=1 AND n.Number<=5
    ORDER BY n.Number,c.CUSTOMER

2

0

Damir Sudarevic 16 年前

分别保存一张时间段表,然后 左外连接 它的活动。

像:

select *
from ReportingPeriod as p
left join Activities as a on a.ReportingPeriodId = p.ReportingPeriodId;