需要帮助编写日期敏感的T-SQL查询 [英] need help writing a date sensitive T-SQL query

问题描述

我需要帮助编写一个T-SQL查询，从每个特许经营52周的序列中通常会包含差距的表格中，每个特许经营权产生52行数据（即特许经营可能每两周或每周报告一次数据

I need help writing a T-SQL query that will generate 52 rows of data per franchise from a table that will often contain gaps in the 52 week sequence per franchise (i.e., the franchise may have reported data bi-weekly or has not been in business for a full year).

我正在查询的表格如下所示：

The table I'm querying against looks something like this:

FranchiseId |日期| ContractHours | PrivateHours

，我需要加入到与此类似的表格中：

and I need to join it to a table similar to this:

FranchiseId |名称

查询的输出需要如下所示：

The output of the query needs to look like this:

Name | Date       | ContractHours | PrivateHours
----   ----------   -------------   ------------
AZ1    08-02-2011             292            897
AZ1    07-26-2011               0              0 -- default to 0's for gaps in sequence
...
AZ1    08-03-2010              45            125 -- row 52 for AZ1
AZ2    08-02-2011             382            239
...
AZ2    07-26-2011               0              0 -- row 52 for AZ2

我需要为每个特许经营的这种风格的输出，即52行数据，默认行为52周序列中的任何空白，单个结果组。因此，如果有100个特许经营，结果集应该是5200行。

I need this style of output for every franchise, i.e., 52 rows of data with default rows for any gaps in the 52 week sequence, in a single result set. Thus, if there are 100 franchises, the result set should be 5200 rows.

我试过的

我尝试了以下典型建议：

I've tried the typical suggestions of:

• 创建一个包含所有可能日期的表


• LEFT OUTER加入到所需数据表中

我遇到的问题是1）确保每个特许经营权是52行
，2）填补专利名称和0个小时的差距，我不能在结果集中具有以下内容：

The problems I'm running into are 1) ensuring that for every franchise their are 52 rows and 2) filling in gaps with the franchise name and 0 for hours, I can't have the following in the result set:

Name | Date       | ContractHours | PrivateHours
----   ----------   -------------   ------------
NULL   08-02-2011            NULL           NULL

我不知道从哪里去？是否有一种有效的方式来编写将产生所需输出的T-SQL查询？

I don't know where to go from here? Is there an efficient way to write a T-SQL query that will produce the required output?

推荐答案

裸骨是这个

• 生成52周范围


• 与加盟商交叉加入


• LEFT JOIN实际日期


• ISNULL替换零

• Generate 52 week ranges
• Cross join with Franchise
• LEFT JOIN the actual date
• ISNULL to substitute zeroes

所以，像这样，未经测试

So, like this, untested

;WITH cDATE AS
(
    SELECT
        CAST('20100101' AS date /*smalldatetime*/) AS StartOfWeek, 
        CAST('20100101' AS date /*smalldatetime*/) + 6 AS EndOfWeek
    UNION ALL
    SELECT StartOfWeek + 7, EndOfWeek + 7
    FROM cDATE WHERE StartOfWeek + 7 < '20110101'
), Possibles AS
(
    SELECT
        StartOfWeek, FranchiseID
    FROM
        cDATE CROSS JOIN Franchise
)
SELECT
    P.FranchiseID,
    P.StartOfWeek, 
    ISNULL(SUM(O.ContractHours), 0), 
    ISNULL(SUM(O.PrivateHours), 0)
FROM
    Possibles P
    LEFT JOIN
    TheOtherTable O ON P.FranchiseID = O.FranchiseID AND 
                    O.Date BETWEEN P.StartOfWeek AND P.EndOfWeek
GROUP BY
    P.FranchiseID

这篇关于需要帮助编写日期敏感的T-SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！