add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
(No column name)
Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288) - 12.0.6372.1 (X64)
Dec 12 2019 15:14:11
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 17763: ) (Hypervisor)
2 rows affected
name start_date end_date
Joe 2021-04-20 2021-05-20
John 2021-05-01 2021-05-28
365 rows affected
tdate
2021-01-01
2021-01-02
2021-01-03
2021-01-04
2021-01-05
2021-01-06
2021-01-07
2021-01-08
2021-01-09
2021-01-10
2021-01-11
2021-01-12
2021-01-13
2021-01-14
2021-01-15
2021-01-16
2021-01-17
2021-01-18
2021-01-19
2021-01-20
2021-01-21
2021-01-22
2021-01-23
2021-01-24
2021-01-25
2021-01-26
2021-01-27
2021-01-28
2021-01-29
2021-01-30
2021-01-31
2021-02-01
2021-02-02
2021-02-03
2021-02-04
2021-02-05
2021-02-06
2021-02-07
2021-02-08
2021-02-09
2021-02-10
2021-02-11
2021-02-12
2021-02-13
2021-02-14
2021-02-15
2021-02-16
2021-02-17
2021-02-18
2021-02-19
2021-02-20
2021-02-21
2021-02-22
2021-02-23
2021-02-24
2021-02-25
2021-02-26
2021-02-27
2021-02-28
2021-03-01
2021-03-02
2021-03-03
2021-03-04
2021-03-05
2021-03-06
2021-03-07
2021-03-08
2021-03-09
2021-03-10
2021-03-11
2021-03-12
2021-03-13
2021-03-14
2021-03-15
2021-03-16
2021-03-17
2021-03-18
2021-03-19
2021-03-20
2021-03-21
2021-03-22
2021-03-23
2021-03-24
2021-03-25
2021-03-26
2021-03-27
2021-03-28
2021-03-29
2021-03-30
2021-03-31
2021-04-01
2021-04-02
2021-04-03
2021-04-04
2021-04-05
2021-04-06
2021-04-07
2021-04-08
2021-04-09
2021-04-10
2021-04-11
2021-04-12
2021-04-13
2021-04-14
2021-04-15
2021-04-16
2021-04-17
2021-04-18
2021-04-19
2021-04-20
2021-04-21
2021-04-22
2021-04-23
2021-04-24
2021-04-25
2021-04-26
2021-04-27
2021-04-28
2021-04-29
2021-04-30
2021-05-01
2021-05-02
2021-05-03
2021-05-04
2021-05-05
2021-05-06
2021-05-07
2021-05-08
2021-05-09
2021-05-10
2021-05-11
2021-05-12
2021-05-13
2021-05-14
2021-05-15
2021-05-16
2021-05-17
2021-05-18
2021-05-19
2021-05-20
2021-05-21
2021-05-22
2021-05-23
2021-05-24
2021-05-25
2021-05-26
2021-05-27
2021-05-28
2021-05-29
2021-05-30
2021-05-31
2021-06-01
2021-06-02
2021-06-03
2021-06-04
2021-06-05
2021-06-06
2021-06-07
2021-06-08
2021-06-09
2021-06-10
2021-06-11
2021-06-12
2021-06-13
2021-06-14
2021-06-15
2021-06-16
2021-06-17
2021-06-18
2021-06-19
2021-06-20
2021-06-21
2021-06-22
2021-06-23
2021-06-24
2021-06-25
2021-06-26
2021-06-27
2021-06-28
2021-06-29
2021-06-30
2021-07-01
2021-07-02
2021-07-03
2021-07-04
2021-07-05
2021-07-06
2021-07-07
2021-07-08
2021-07-09
2021-07-10
2021-07-11
2021-07-12
2021-07-13
2021-07-14
2021-07-15
2021-07-16
2021-07-17
2021-07-18
2021-07-19
2021-07-20
2021-07-21
2021-07-22
2021-07-23
2021-07-24
2021-07-25
2021-07-26
2021-07-27
2021-07-28
2021-07-29
2021-07-30
2021-07-31
2021-08-01
2021-08-02
2021-08-03
2021-08-04
2021-08-05
2021-08-06
2021-08-07
2021-08-08
2021-08-09
2021-08-10
2021-08-11
2021-08-12
2021-08-13
2021-08-14
2021-08-15
2021-08-16
2021-08-17
2021-08-18
2021-08-19
2021-08-20
2021-08-21
2021-08-22
2021-08-23
2021-08-24
2021-08-25
2021-08-26
2021-08-27
2021-08-28
2021-08-29
2021-08-30
2021-08-31
2021-09-01
2021-09-02
2021-09-03
2021-09-04
2021-09-05
2021-09-06
2021-09-07
2021-09-08
2021-09-09
2021-09-10
2021-09-11
2021-09-12
2021-09-13
2021-09-14
2021-09-15
2021-09-16
2021-09-17
2021-09-18
2021-09-19
2021-09-20
2021-09-21
2021-09-22
2021-09-23
2021-09-24
2021-09-25
2021-09-26
2021-09-27
2021-09-28
2021-09-29
2021-09-30
2021-10-01
2021-10-02
2021-10-03
2021-10-04
2021-10-05
2021-10-06
2021-10-07
2021-10-08
2021-10-09
2021-10-10
2021-10-11
2021-10-12
2021-10-13
2021-10-14
2021-10-15
2021-10-16
2021-10-17
2021-10-18
2021-10-19
2021-10-20
2021-10-21
2021-10-22
2021-10-23
2021-10-24
2021-10-25
2021-10-26
2021-10-27
2021-10-28
2021-10-29
2021-10-30
2021-10-31
2021-11-01
2021-11-02
2021-11-03
2021-11-04
2021-11-05
2021-11-06
2021-11-07
2021-11-08
2021-11-09
2021-11-10
2021-11-11
2021-11-12
2021-11-13
2021-11-14
2021-11-15
2021-11-16
2021-11-17
2021-11-18
2021-11-19
2021-11-20
2021-11-21
2021-11-22
2021-11-23
2021-11-24
2021-11-25
2021-11-26
2021-11-27
2021-11-28
2021-11-29
2021-11-30
2021-12-01
2021-12-02
2021-12-03
2021-12-04
2021-12-05
2021-12-06
2021-12-07
2021-12-08
2021-12-09
2021-12-10
2021-12-11
2021-12-12
2021-12-13
2021-12-14
2021-12-15
2021-12-16
2021-12-17
2021-12-18
2021-12-19
2021-12-20
2021-12-21
2021-12-22
2021-12-23
2021-12-24
2021-12-25
2021-12-26
2021-12-27
2021-12-28
2021-12-29
2021-12-30
2021-12-31
Month Name Count
4 April 11
5 May 48
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Month Name Count
4 April 11
5 May 48
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
2 1 SELECT
  DATEPART(mm, ad.tdate) AS "Month",
  DATENAME(MONTH, ad.tdate) AS "Name",
  COUNT(MONTH(ad.tdate)) AS "Count"
FROM stay s
JOIN all_dates ad
  ON ad.tdate >= s.start_date AND ad.tdate <= s.end_date
GROUP BY DATEPART(mm, ad.tdate), DATENAME(MONTH, ad.tdate)
ORDER BY DATEPART(mm, ad.tdate)
1 1 0 null null null null 365 null null null 0.02621159 null null SELECT False null
0 0   |--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1009],0))) 1 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1009],0)) [Expr1005]=CONVERT_IMPLICIT(int,[Expr1009],0) 365 0 0.0004015 49 0.02621159 [Expr1003], [Expr1004], [Expr1005] null PLAN_ROW False 1
2 1        |--Stream Aggregate(GROUP BY:([Expr1003], [Expr1004]) DEFINE:([Expr1009]=COUNT([Expr1003]))) 1 3 2 Stream Aggregate Aggregate GROUP BY:([Expr1003], [Expr1004]) [Expr1009]=COUNT([Expr1003]) 365 0 0.0004015 49 0.02621159 [Expr1003], [Expr1004], [Expr1009] null PLAN_ROW False 1
59 1             |--Sort(ORDER BY:([Expr1003] ASC, [Expr1004] ASC)) 1 5 3 Sort Sort ORDER BY:([Expr1003] ASC, [Expr1004] ASC) null 365 0.01126126 0.004946641 49 0.02581009 [Expr1003], [Expr1004] null PLAN_ROW False 1
0 0                  |--Compute Scalar(DEFINE:([Expr1003]=datepart(month,[fiddle_a6776f28533847c0bf1fd114bba8eac6].[dbo].[all_dates].[tdate] as [ad].[tdate]), [Expr1004]=datename(month,[fiddle_a6776f28533847c0bf1fd114bba8eac6].[dbo].[all_dates].[tdate] as [ad].[tdate]), [Expr1006]=datepart(month,[fiddle_a6776f28533847c0bf1fd114bba8eac6].[dbo].[all_dates].[tdate] as [ad].[tdate]))) 1 6 5 Compute Scalar Compute Scalar DEFINE:([Expr1003]=datepart(month,[fiddle_a6776f28533847c0bf1fd114bba8eac6].[dbo].[all_dates].[tdate] as [ad].[tdate]), [Expr1004]=datename(month,[fiddle_a6776f28533847c0bf1fd114bba8eac6].[dbo].[all_dates].[tdate] as [ad].[tdate]), [Expr1006]=datepart(month,[fiddle_a6776f28533847c0bf1fd114bba8eac6].[dbo].[all_dates].[tdate] as [ad].[tdate])) [Expr1003]=datepart(month,[fiddle_a6776f28533847c0bf1fd114bba8eac6].[dbo].[all_dates].[tdate] as [ad].[tdate]), [Expr1004]=datename(month,[fiddle_a6776f28533847c0bf1fd114bba8eac6].[dbo].[all_dates].[tdate] as [ad].[tdate]), [Expr1006]=datepart(month,[fiddle_a6776f28533847c0bf1fd114bba8eac6].[dbo].[all_dates].[tdate] as [ad].[tdate]) 365 0 3.65E-05 49 0.009602191 [Expr1003], [Expr1004], [Expr1006] null PLAN_ROW False 1
59 1                       |--Nested Loops(Inner Join, OUTER REFERENCES:([s].[start_date], [s].[end_date])) 1 7 6 Nested Loops Inner Join OUTER REFERENCES:([s].[start_date], [s].[end_date]) null 365 0 0.0015257 10 0.009565691 [ad].[tdate] null PLAN_ROW False 1
2 1                            |--Table Scan(OBJECT:([fiddle_a6776f28533847c0bf1fd114bba8eac6].[dbo].[stay] AS [s])) 1 8 7 Table Scan Table Scan OBJECT:([fiddle_a6776f28533847c0bf1fd114bba8eac6].[dbo].[stay] AS [s]) [s].[start_date], [s].[end_date] 2 0.003125 0.0001592 13 0.0032842 [s].[start_date], [s].[end_date] null PLAN_ROW False 1
59 2                            |--Clustered Index Seek(OBJECT:([fiddle_a6776f28533847c0bf1fd114bba8eac6].[dbo].[all_dates].[PK__all_date__FDD41D3439FD97F5] AS [ad]), SEEK:([ad].[tdate] >= [fiddle_a6776f28533847c0bf1fd114bba8eac6].[dbo].[stay].[start_date] as [s].[start_date] AND [ad].[tdate] <= [fiddle_a6776f28533847c0bf1fd114bba8eac6].[dbo].[stay].[end_date] as [s].[end_date]) ORDERED FORWARD) 1 9 7 Clustered Index Seek Clustered Index Seek OBJECT:([fiddle_a6776f28533847c0bf1fd114bba8eac6].[dbo].[all_dates].[PK__all_date__FDD41D3439FD97F5] AS [ad]), SEEK:([ad].[tdate] >= [fiddle_a6776f28533847c0bf1fd114bba8eac6].[dbo].[stay].[start_date] as [s].[start_date] AND [ad].[tdate] <= [fiddle_a6776f28533847c0bf1fd114bba8eac6].[dbo].[stay].[end_date] as [s].[end_date]) ORDERED FORWARD [ad].[tdate] 182.5 0.003125 0.00035775 10 0.004755791 [ad].[tdate] null PLAN_ROW False 2
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'all_dates'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'stay'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.