snipt

Ctrl+h for KB shortcuts

SQL

Find Missing Date in T-SQL

----  Using Table variable  ----
Declare @dateRange table(num INT NOT NULL PRIMARY KEY)
Declare @startDate datetime
Declare @endDate datetime
Declare @dateFromDB table(d datetime)

set @startDate = '2011-05-21'
set @endDate = '2011/6/4';

WITH numbers AS 
( 
 SELECT 1 AS num 
 UNION ALL 
 SELECT num + 1 FROM numbers  
     WHERE num < (SELECT DATEDIFF(dd, @startDate, @endDate + 1)) 
) 
INSERT INTO @dateRange 
    SELECT num FROM numbers

INSERT INTO @dateFromDB
	SELECT [InventoryDateTime]
	FROM [RpoPrTracker].[dbo].[DailyTankInventory]
	WHERE [RpoPrTracker].[dbo].[DailyTankInventory].[EquipmentCode] = 'T2701'
        AND [InventoryDateTime] >= @startDate AND [InventoryDateTime] <= @endDate

SELECT d
	FROM @dateRange AS a
	LEFT OUTER JOIN @dateFromDB AS b 
	ON DATEADD(dd, a.num , @startDate -1) = b.d

SELECT DATEADD(dd, num - 1, @startDate) missingDate 
   FROM @dateRange t1 LEFT OUTER JOIN @dateFromDB t2  
   ON CAST(DATEADD(dd, t1.num, @startDate -1) AS DATE)  = CAST(t2.d AS DATE) 
   WHERE t2.d IS NULL; 



----   Using Temp Table  ----
CREATE TABLE #dateRange (num INT NOT NULL PRIMARY KEY);
CREATE TABLE #dateFromDB (d datetime);
Declare @startDate datetime
Declare @endDate datetime
set @startDate = '6/1/2011'
set @endDate = '2011/6/4';

WITH numbers AS 
( 
 SELECT 1 AS num 
 UNION ALL 
 SELECT num + 1 FROM numbers  
     WHERE num < (SELECT DATEDIFF(dd, @startDate, @endDate + 1))
) 
INSERT INTO #dateRange 
    SELECT num FROM numbers;
	
INSERT INTO #dateFromDB
	SELECT [InventoryDateTime]
	FROM [RpoPrTracker].[dbo].[DailyTankInventory]
	WHERE [RpoPrTracker].[dbo].[DailyTankInventory].[EquipmentCode] = 'T5411';

SELECT DATEADD(dd, num - 1, @startDate) missingDate 
   FROM #dateRange t1 LEFT OUTER JOIN #dateFromDB t2  
   ON CAST(DATEADD(dd, t1.num, @startDate -1) AS DATE)  = CAST(t2.d AS DATE)
   WHERE t2.d IS NULL; 
   
DROP TABLE #dateRange;
DROP TABLE #dateFromDB;
https://snipt.net/embed/a0363cba57130631661036ebd8e0649b/
/raw/a0363cba57130631661036ebd8e0649b/
a0363cba57130631661036ebd8e0649b
sql
SQL
67
2019-06-16T18:51:47
True
False
False
/api/public/snipt/38627/
find-missing-date-in-t-sql
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1"> 1</a> <a href="#L-2"> 2</a> <a href="#L-3"> 3</a> <a href="#L-4"> 4</a> <a href="#L-5"> 5</a> <a href="#L-6"> 6</a> <a href="#L-7"> 7</a> <a href="#L-8"> 8</a> <a href="#L-9"> 9</a> <a href="#L-10">10</a> <a href="#L-11">11</a> <a href="#L-12">12</a> <a href="#L-13">13</a> <a href="#L-14">14</a> <a href="#L-15">15</a> <a href="#L-16">16</a> <a href="#L-17">17</a> <a href="#L-18">18</a> <a href="#L-19">19</a> <a href="#L-20">20</a> <a href="#L-21">21</a> <a href="#L-22">22</a> <a href="#L-23">23</a> <a href="#L-24">24</a> <a href="#L-25">25</a> <a href="#L-26">26</a> <a href="#L-27">27</a> <a href="#L-28">28</a> <a href="#L-29">29</a> <a href="#L-30">30</a> <a href="#L-31">31</a> <a href="#L-32">32</a> <a href="#L-33">33</a> <a href="#L-34">34</a> <a href="#L-35">35</a> <a href="#L-36">36</a> <a href="#L-37">37</a> <a href="#L-38">38</a> <a href="#L-39">39</a> <a href="#L-40">40</a> <a href="#L-41">41</a> <a href="#L-42">42</a> <a href="#L-43">43</a> <a href="#L-44">44</a> <a href="#L-45">45</a> <a href="#L-46">46</a> <a href="#L-47">47</a> <a href="#L-48">48</a> <a href="#L-49">49</a> <a href="#L-50">50</a> <a href="#L-51">51</a> <a href="#L-52">52</a> <a href="#L-53">53</a> <a href="#L-54">54</a> <a href="#L-55">55</a> <a href="#L-56">56</a> <a href="#L-57">57</a> <a href="#L-58">58</a> <a href="#L-59">59</a> <a href="#L-60">60</a> <a href="#L-61">61</a> <a href="#L-62">62</a> <a href="#L-63">63</a> <a href="#L-64">64</a> <a href="#L-65">65</a> <a href="#L-66">66</a> <a href="#L-67">67</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="c1">---- Using Table variable ----</span> </span><span id="L-2"><a name="L-2"></a><span class="k">Declare</span> <span class="o">@</span><span class="n">dateRange</span> <span class="k">table</span><span class="p">(</span><span class="n">num</span> <span class="nb">INT</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">)</span> </span><span id="L-3"><a name="L-3"></a><span class="k">Declare</span> <span class="o">@</span><span class="n">startDate</span> <span class="n">datetime</span> </span><span id="L-4"><a name="L-4"></a><span class="k">Declare</span> <span class="o">@</span><span class="n">endDate</span> <span class="n">datetime</span> </span><span id="L-5"><a name="L-5"></a><span class="k">Declare</span> <span class="o">@</span><span class="n">dateFromDB</span> <span class="k">table</span><span class="p">(</span><span class="n">d</span> <span class="n">datetime</span><span class="p">)</span> </span><span id="L-6"><a name="L-6"></a> </span><span id="L-7"><a name="L-7"></a><span class="k">set</span> <span class="o">@</span><span class="n">startDate</span> <span class="o">=</span> <span class="s1">&#39;2011-05-21&#39;</span> </span><span id="L-8"><a name="L-8"></a><span class="k">set</span> <span class="o">@</span><span class="n">endDate</span> <span class="o">=</span> <span class="s1">&#39;2011/6/4&#39;</span><span class="p">;</span> </span><span id="L-9"><a name="L-9"></a> </span><span id="L-10"><a name="L-10"></a><span class="k">WITH</span> <span class="n">numbers</span> <span class="k">AS</span> </span><span id="L-11"><a name="L-11"></a><span class="p">(</span> </span><span id="L-12"><a name="L-12"></a> <span class="k">SELECT</span> <span class="mi">1</span> <span class="k">AS</span> <span class="n">num</span> </span><span id="L-13"><a name="L-13"></a> <span class="k">UNION</span> <span class="k">ALL</span> </span><span id="L-14"><a name="L-14"></a> <span class="k">SELECT</span> <span class="n">num</span> <span class="o">+</span> <span class="mi">1</span> <span class="k">FROM</span> <span class="n">numbers</span> </span><span id="L-15"><a name="L-15"></a> <span class="k">WHERE</span> <span class="n">num</span> <span class="o">&lt;</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">DATEDIFF</span><span class="p">(</span><span class="n">dd</span><span class="p">,</span> <span class="o">@</span><span class="n">startDate</span><span class="p">,</span> <span class="o">@</span><span class="n">endDate</span> <span class="o">+</span> <span class="mi">1</span><span class="p">))</span> </span><span id="L-16"><a name="L-16"></a><span class="p">)</span> </span><span id="L-17"><a name="L-17"></a><span class="k">INSERT</span> <span class="k">INTO</span> <span class="o">@</span><span class="n">dateRange</span> </span><span id="L-18"><a name="L-18"></a> <span class="k">SELECT</span> <span class="n">num</span> <span class="k">FROM</span> <span class="n">numbers</span> </span><span id="L-19"><a name="L-19"></a> </span><span id="L-20"><a name="L-20"></a><span class="k">INSERT</span> <span class="k">INTO</span> <span class="o">@</span><span class="n">dateFromDB</span> </span><span id="L-21"><a name="L-21"></a> <span class="k">SELECT</span> <span class="p">[</span><span class="n">InventoryDateTime</span><span class="p">]</span> </span><span id="L-22"><a name="L-22"></a> <span class="k">FROM</span> <span class="p">[</span><span class="n">RpoPrTracker</span><span class="p">].[</span><span class="n">dbo</span><span class="p">].[</span><span class="n">DailyTankInventory</span><span class="p">]</span> </span><span id="L-23"><a name="L-23"></a> <span class="k">WHERE</span> <span class="p">[</span><span class="n">RpoPrTracker</span><span class="p">].[</span><span class="n">dbo</span><span class="p">].[</span><span class="n">DailyTankInventory</span><span class="p">].[</span><span class="n">EquipmentCode</span><span class="p">]</span> <span class="o">=</span> <span class="s1">&#39;T2701&#39;</span> </span><span id="L-24"><a name="L-24"></a> <span class="k">AND</span> <span class="p">[</span><span class="n">InventoryDateTime</span><span class="p">]</span> <span class="o">&gt;=</span> <span class="o">@</span><span class="n">startDate</span> <span class="k">AND</span> <span class="p">[</span><span class="n">InventoryDateTime</span><span class="p">]</span> <span class="o">&lt;=</span> <span class="o">@</span><span class="n">endDate</span> </span><span id="L-25"><a name="L-25"></a> </span><span id="L-26"><a name="L-26"></a><span class="k">SELECT</span> <span class="n">d</span> </span><span id="L-27"><a name="L-27"></a> <span class="k">FROM</span> <span class="o">@</span><span class="n">dateRange</span> <span class="k">AS</span> <span class="n">a</span> </span><span id="L-28"><a name="L-28"></a> <span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="o">@</span><span class="n">dateFromDB</span> <span class="k">AS</span> <span class="n">b</span> </span><span id="L-29"><a name="L-29"></a> <span class="k">ON</span> <span class="n">DATEADD</span><span class="p">(</span><span class="n">dd</span><span class="p">,</span> <span class="n">a</span><span class="p">.</span><span class="n">num</span> <span class="p">,</span> <span class="o">@</span><span class="n">startDate</span> <span class="o">-</span><span class="mi">1</span><span class="p">)</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">d</span> </span><span id="L-30"><a name="L-30"></a> </span><span id="L-31"><a name="L-31"></a><span class="k">SELECT</span> <span class="n">DATEADD</span><span class="p">(</span><span class="n">dd</span><span class="p">,</span> <span class="n">num</span> <span class="o">-</span> <span class="mi">1</span><span class="p">,</span> <span class="o">@</span><span class="n">startDate</span><span class="p">)</span> <span class="n">missingDate</span> </span><span id="L-32"><a name="L-32"></a> <span class="k">FROM</span> <span class="o">@</span><span class="n">dateRange</span> <span class="n">t1</span> <span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="o">@</span><span class="n">dateFromDB</span> <span class="n">t2</span> </span><span id="L-33"><a name="L-33"></a> <span class="k">ON</span> <span class="k">CAST</span><span class="p">(</span><span class="n">DATEADD</span><span class="p">(</span><span class="n">dd</span><span class="p">,</span> <span class="n">t1</span><span class="p">.</span><span class="n">num</span><span class="p">,</span> <span class="o">@</span><span class="n">startDate</span> <span class="o">-</span><span class="mi">1</span><span class="p">)</span> <span class="k">AS</span> <span class="nb">DATE</span><span class="p">)</span> <span class="o">=</span> <span class="k">CAST</span><span class="p">(</span><span class="n">t2</span><span class="p">.</span><span class="n">d</span> <span class="k">AS</span> <span class="nb">DATE</span><span class="p">)</span> </span><span id="L-34"><a name="L-34"></a> <span class="k">WHERE</span> <span class="n">t2</span><span class="p">.</span><span class="n">d</span> <span class="k">IS</span> <span class="k">NULL</span><span class="p">;</span> </span><span id="L-35"><a name="L-35"></a> </span><span id="L-36"><a name="L-36"></a> </span><span id="L-37"><a name="L-37"></a> </span><span id="L-38"><a name="L-38"></a><span class="c1">---- Using Temp Table ----</span> </span><span id="L-39"><a name="L-39"></a><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="o">#</span><span class="n">dateRange</span> <span class="p">(</span><span class="n">num</span> <span class="nb">INT</span> <span class="k">NOT</span> <span class="k">NULL</span> <span class="k">PRIMARY</span> <span class="k">KEY</span><span class="p">);</span> </span><span id="L-40"><a name="L-40"></a><span class="k">CREATE</span> <span class="k">TABLE</span> <span class="o">#</span><span class="n">dateFromDB</span> <span class="p">(</span><span class="n">d</span> <span class="n">datetime</span><span class="p">);</span> </span><span id="L-41"><a name="L-41"></a><span class="k">Declare</span> <span class="o">@</span><span class="n">startDate</span> <span class="n">datetime</span> </span><span id="L-42"><a name="L-42"></a><span class="k">Declare</span> <span class="o">@</span><span class="n">endDate</span> <span class="n">datetime</span> </span><span id="L-43"><a name="L-43"></a><span class="k">set</span> <span class="o">@</span><span class="n">startDate</span> <span class="o">=</span> <span class="s1">&#39;6/1/2011&#39;</span> </span><span id="L-44"><a name="L-44"></a><span class="k">set</span> <span class="o">@</span><span class="n">endDate</span> <span class="o">=</span> <span class="s1">&#39;2011/6/4&#39;</span><span class="p">;</span> </span><span id="L-45"><a name="L-45"></a> </span><span id="L-46"><a name="L-46"></a><span class="k">WITH</span> <span class="n">numbers</span> <span class="k">AS</span> </span><span id="L-47"><a name="L-47"></a><span class="p">(</span> </span><span id="L-48"><a name="L-48"></a> <span class="k">SELECT</span> <span class="mi">1</span> <span class="k">AS</span> <span class="n">num</span> </span><span id="L-49"><a name="L-49"></a> <span class="k">UNION</span> <span class="k">ALL</span> </span><span id="L-50"><a name="L-50"></a> <span class="k">SELECT</span> <span class="n">num</span> <span class="o">+</span> <span class="mi">1</span> <span class="k">FROM</span> <span class="n">numbers</span> </span><span id="L-51"><a name="L-51"></a> <span class="k">WHERE</span> <span class="n">num</span> <span class="o">&lt;</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">DATEDIFF</span><span class="p">(</span><span class="n">dd</span><span class="p">,</span> <span class="o">@</span><span class="n">startDate</span><span class="p">,</span> <span class="o">@</span><span class="n">endDate</span> <span class="o">+</span> <span class="mi">1</span><span class="p">))</span> </span><span id="L-52"><a name="L-52"></a><span class="p">)</span> </span><span id="L-53"><a name="L-53"></a><span class="k">INSERT</span> <span class="k">INTO</span> <span class="o">#</span><span class="n">dateRange</span> </span><span id="L-54"><a name="L-54"></a> <span class="k">SELECT</span> <span class="n">num</span> <span class="k">FROM</span> <span class="n">numbers</span><span class="p">;</span> </span><span id="L-55"><a name="L-55"></a> </span><span id="L-56"><a name="L-56"></a><span class="k">INSERT</span> <span class="k">INTO</span> <span class="o">#</span><span class="n">dateFromDB</span> </span><span id="L-57"><a name="L-57"></a> <span class="k">SELECT</span> <span class="p">[</span><span class="n">InventoryDateTime</span><span class="p">]</span> </span><span id="L-58"><a name="L-58"></a> <span class="k">FROM</span> <span class="p">[</span><span class="n">RpoPrTracker</span><span class="p">].[</span><span class="n">dbo</span><span class="p">].[</span><span class="n">DailyTankInventory</span><span class="p">]</span> </span><span id="L-59"><a name="L-59"></a> <span class="k">WHERE</span> <span class="p">[</span><span class="n">RpoPrTracker</span><span class="p">].[</span><span class="n">dbo</span><span class="p">].[</span><span class="n">DailyTankInventory</span><span class="p">].[</span><span class="n">EquipmentCode</span><span class="p">]</span> <span class="o">=</span> <span class="s1">&#39;T5411&#39;</span><span class="p">;</span> </span><span id="L-60"><a name="L-60"></a> </span><span id="L-61"><a name="L-61"></a><span class="k">SELECT</span> <span class="n">DATEADD</span><span class="p">(</span><span class="n">dd</span><span class="p">,</span> <span class="n">num</span> <span class="o">-</span> <span class="mi">1</span><span class="p">,</span> <span class="o">@</span><span class="n">startDate</span><span class="p">)</span> <span class="n">missingDate</span> </span><span id="L-62"><a name="L-62"></a> <span class="k">FROM</span> <span class="o">#</span><span class="n">dateRange</span> <span class="n">t1</span> <span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="o">#</span><span class="n">dateFromDB</span> <span class="n">t2</span> </span><span id="L-63"><a name="L-63"></a> <span class="k">ON</span> <span class="k">CAST</span><span class="p">(</span><span class="n">DATEADD</span><span class="p">(</span><span class="n">dd</span><span class="p">,</span> <span class="n">t1</span><span class="p">.</span><span class="n">num</span><span class="p">,</span> <span class="o">@</span><span class="n">startDate</span> <span class="o">-</span><span class="mi">1</span><span class="p">)</span> <span class="k">AS</span> <span class="nb">DATE</span><span class="p">)</span> <span class="o">=</span> <span class="k">CAST</span><span class="p">(</span><span class="n">t2</span><span class="p">.</span><span class="n">d</span> <span class="k">AS</span> <span class="nb">DATE</span><span class="p">)</span> </span><span id="L-64"><a name="L-64"></a> <span class="k">WHERE</span> <span class="n">t2</span><span class="p">.</span><span class="n">d</span> <span class="k">IS</span> <span class="k">NULL</span><span class="p">;</span> </span><span id="L-65"><a name="L-65"></a> </span><span id="L-66"><a name="L-66"></a><span class="k">DROP</span> <span class="k">TABLE</span> <span class="o">#</span><span class="n">dateRange</span><span class="p">;</span> </span><span id="L-67"><a name="L-67"></a><span class="k">DROP</span> <span class="k">TABLE</span> <span class="o">#</span><span class="n">dateFromDB</span><span class="p">;</span> </span></pre></div> </td></tr></table>
T-SQL