如何使用BUILTIN.PERIOD

Question

我要查询最近七天的发票价值。这是我最好的尝试:

SELECT T.* 
FROM Transaction AS T
WHERE T.type IN('CustInvc') AND trandate IN(BUILTIN.PERIOD('SDLW', 'START', 'ALL', '>'))

---

我已经阅读了文档，但是我错过了一些东西。我相信SDLW代表SAME_DAY_LAST_WEEK。我甚至试过(但没能)注册一个账户来查看这个答案。我将相关文档剪切为下面的HTML。

body {
font-family: 'Oracle Sans', -apple-system, BlinkMacSystemFont, "Segoe UI", "Helvetica Neue", Arial, sans-serif;
}

<tr>
<td style="padding:3.6px;width:17.5%;">
<p style="font-size:1em !important;margin:0px;">
<code>PERIOD</code>
</p>
</td>
<td style="padding:3.6px;width:23.4%;">
<p style="font-size:1em !important;margin:0px;">Returns the contents of the IN predicate for a relative date range as a subselection</p>
</td>
<td style="padding:3.6px;width:36.5%;">
<ul>
<li>
<p style="font-size:1em !important;margin:0px;">Range ID</p>
<p style="font-size:1em !important;margin:0px;">This parameter accepts the same property values listed in the <a href="section_1544111773.html" class="o-hf">query.RelativeDateRange</a> enum in the N/query module (for example, <code>'LFY'</code> to represent a range starting or ending last fiscal
year).
</p>
</li>
<li>
<p style="font-size:1em !important;margin:0px;">Range type</p>
<p style="font-size:1em !important;margin:0px;">This parameter accepts the following values (including the single quotation marks):</p>
<ul>
<li>
<p style="font-size:1em !important;margin:0px;">
<code>'START'</code>
</p>
</li>
<li>
<p style="font-size:1em !important;margin:0px;">
<code>'END'</code>
</p>
</li>
</ul>
</li>
<li>
<p style="font-size:1em !important;margin:0px;">Adjustment</p>
<p style="font-size:1em !important;margin:0px;">This parameter accepts the following values (including the single quotation marks):</p>
<ul>
<li>
<p style="font-size:1em !important;margin:0px;">
<code>'NOT_LAST'</code>
</p>
</li>
<li>
<p style="font-size:1em !important;margin:0px;">
<code>'ALL'</code>
</p>
</li>
</ul>
</li>
<li>
<p style="font-size:1em !important;margin:0px;">Operator</p>
<p style="font-size:1em !important;margin:0px;">This parameter accepts the following values (including the single quotation marks):</p>
<ul>
<li>
<p style="font-size:1em !important;margin:0px;">
<code>'BETWEEN'</code>
</p>
</li>
<li>
<p style="font-size:1em !important;margin:0px;">
<code>'NOT BETWEEN'</code>
</p>
</li>
<li>
<p style="font-size:1em !important;margin:0px;">
<code>'&lt;'</code>
</p>
</li>
<li>
<p style="font-size:1em !important;margin:0px;">
<code>'&lt;='</code>
</p>
</li>
<li>
<p style="font-size:1em !important;margin:0px;">
<code>'&gt;'</code>
</p>
</li>
<li>
<p style="font-size:1em !important;margin:0px;">
<code>'&gt;='</code>
</p>
</li>
</ul>
</li>
</ul>
</td>
<td style="padding:3.6px;width:22.4%;">
<pre width="370.7472" class="line-numbers precomplete" tabindex="0">                    <code class="language-sql hljs">BUILTIN.PERIOD('LFY', '<span class="hljs-operator"><span class="hljs-keyword">END</span><span class="hljs-string">', '</span>NOT_LAST<span class="hljs-string">', '</span>&gt;<span class="hljs-string">') 
</span></span></code>
</pre>
<pre width="376.0704" class="line-numbers precomplete" tabindex="0">                    <code class="language-sql hljs">BUILTIN.PERIOD('LFY', '<span class="hljs-operator"><span class="hljs-keyword">START</span><span class="hljs-string">', '</span><span class="hljs-keyword">ALL</span><span class="hljs-string">', '</span><span class="hljs-keyword">BETWEEN</span><span class="hljs-string">') 
</span></span></code>
</pre>
</td>
</tr>

Answer 1

使用RELATIVE_RANGES和>运算符

SELECT * FROM transaction
WHERE type IN('CustInvc')
AND trandate > (BUILTIN.RELATIVE_RANGES('SDLW', 'START'))

或者，如果您想包含上周的同一天，请使用>=操作符。

SELECT * FROM transaction
WHERE type IN('CustInvc')
AND trandate >= (BUILTIN.RELATIVE_RANGES('SDLW', 'START'))