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.