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 |
| n | tdate |
|---|---|
| 0 | 2021-04-20 |
| 1 | 2021-04-21 |
| 2 | 2021-04-22 |
| 3 | 2021-04-23 |
| 4 | 2021-04-24 |
| 5 | 2021-04-25 |
| 6 | 2021-04-26 |
| 7 | 2021-04-27 |
| 8 | 2021-04-28 |
| 9 | 2021-04-29 |
| 10 | 2021-04-30 |
| 11 | 2021-05-01 |
| 12 | 2021-05-02 |
| 13 | 2021-05-03 |
| 14 | 2021-05-04 |
| 15 | 2021-05-05 |
| 16 | 2021-05-06 |
| 17 | 2021-05-07 |
| 18 | 2021-05-08 |
| 19 | 2021-05-09 |
| 20 | 2021-05-10 |
| 21 | 2021-05-11 |
| 22 | 2021-05-12 |
| 23 | 2021-05-13 |
| 24 | 2021-05-14 |
| 25 | 2021-05-15 |
| 26 | 2021-05-16 |
| 27 | 2021-05-17 |
| 28 | 2021-05-18 |
| 29 | 2021-05-19 |
| 30 | 2021-05-20 |
| 31 | 2021-05-21 |
| 32 | 2021-05-22 |
| 33 | 2021-05-23 |
| 34 | 2021-05-24 |
| 35 | 2021-05-25 |
| 36 | 2021-05-26 |
| 37 | 2021-05-27 |
| 38 | 2021-05-28 |
| 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 = 15 ms, elapsed time = 15 ms.
| Rows | Executes | StmtText | StmtId | NodeId | Parent | PhysicalOp | LogicalOp | Argument | DefinedValues | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | OutputList | Warnings | Type | Parallel | EstimateExecutions |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2 | 1 | WITH stays (min_sd, no_days) AS ( SELECT MIN(start_date) AS min_sd, -- MAX(end_date) AS max_ed, DATEDIFF(DAY, MIN(start_date), MAX(end_date)) AS no_days FROM stay ), all_dates(n, tdate) AS ( SELECT 0, (SELECT min_sd FROM stays) UNION ALL SELECT n + 1, DATEADD(DAY, n + 1, (SELECT min_sd FROM stays)) FROM all_dates WHERE n < (SELECT no_days FROM stays) ) 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) OPTION (MAXRECURSION 0) |
1 | 1 | 0 | null | null | null | null | 2 | null | null | null | 0.02548643 | null | null | SELECT | False | null |
| 0 | 0 | |--Compute Scalar(DEFINE:([Expr1033]=CONVERT_IMPLICIT(int,[Expr1044],0))) | 1 | 2 | 1 | Compute Scalar | Compute Scalar | DEFINE:([Expr1033]=CONVERT_IMPLICIT(int,[Expr1044],0)) | [Expr1033]=CONVERT_IMPLICIT(int,[Expr1044],0) | 2 | 0 | 3.4E-06 | 49 | 0.02548643 | [Expr1031], [Expr1032], [Expr1033] | null | PLAN_ROW | False | 1 |
| 2 | 1 | |--Stream Aggregate(GROUP BY:([Expr1031], [Expr1032]) DEFINE:([Expr1044]=COUNT(datepart(month,[Recr1030])))) | 1 | 3 | 2 | Stream Aggregate | Aggregate | GROUP BY:([Expr1031], [Expr1032]) | [Expr1044]=COUNT(datepart(month,[Recr1030])) | 2 | 0 | 3.4E-06 | 49 | 0.02548643 | [Expr1031], [Expr1032], [Expr1044] | null | PLAN_ROW | False | 1 |
| 59 | 1 | |--Sort(ORDER BY:([Expr1031] ASC, [Expr1032] ASC)) | 1 | 4 | 3 | Sort | Sort | ORDER BY:([Expr1031] ASC, [Expr1032] ASC) | null | 4 | 0.01126126 | 0.000112528 | 48 | 0.02548303 | [Recr1030], [Expr1031], [Expr1032] | null | PLAN_ROW | False | 1 |
| 0 | 0 | |--Compute Scalar(DEFINE:([Expr1031]=datepart(month,[Recr1030]), [Expr1032]=datename(month,[Recr1030]))) | 1 | 5 | 4 | Compute Scalar | Compute Scalar | DEFINE:([Expr1031]=datepart(month,[Recr1030]), [Expr1032]=datename(month,[Recr1030])) | [Expr1031]=datepart(month,[Recr1030]), [Expr1032]=datename(month,[Recr1030]) | 4 | 0 | 4E-07 | 48 | 0.01410924 | [Recr1030], [Expr1031], [Expr1032] | null | PLAN_ROW | False | 1 |
| 59 | 1 | |--Nested Loops(Inner Join, WHERE:([Recr1030]>=[fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[start_date] as [s].[start_date] AND [Recr1030]<=[fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[end_date] as [s].[end_date])) | 1 | 6 | 5 | Nested Loops | Inner Join | WHERE:([Recr1030]>=[fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[start_date] as [s].[start_date] AND [Recr1030]<=[fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[end_date] as [s].[end_date]) | null | 4 | 0 | 3.344E-05 | 10 | 0.01410884 | [Recr1030] | null | PLAN_ROW | False | 1 |
| 39 | 1 | |--Index Spool(WITH STACK) | 1 | 7 | 6 | Index Spool | Lazy Spool | WITH STACK | null | 4 | 0 | 1E-08 | 10 | 0.0105435 | [Expr1043], [Recr1029], [Recr1030] | null | PLAN_ROW | False | 1 |
| 39 | 1 | | |--Concatenation | 1 | 8 | 7 | Concatenation | Concatenation | null | [Expr1043] = ([Expr1040], [Expr1042]), [Recr1029] = ([Expr1002], [Expr1020]), [Recr1030] = ([Expr1010], [Expr1028]) | 4 | 0 | 2E-09 | 10 | 0.01037343 | [Expr1043], [Recr1029], [Recr1030] | null | PLAN_ROW | False | 1 |
| 0 | 0 | | |--Compute Scalar(DEFINE:([Expr1040]=(0))) | 1 | 9 | 8 | Compute Scalar | Compute Scalar | DEFINE:([Expr1040]=(0)) | [Expr1040]=(0) | 1 | 0 | 2E-08 | 10 | 2E-08 | [Expr1040], [Expr1002], [Expr1010] | null | PLAN_ROW | False | 5 |
| 0 | 0 | | | |--Compute Scalar(DEFINE:([Expr1002]=(0), [Expr1010]=[Expr1006])) | 1 | 10 | 9 | Compute Scalar | Compute Scalar | DEFINE:([Expr1002]=(0), [Expr1010]=[Expr1006]) | [Expr1002]=(0), [Expr1010]=[Expr1006] | 1 | 0 | 1E-07 | 14 | 0.003286 | [Expr1002], [Expr1010] | null | PLAN_ROW | False | 1 |
| 1 | 1 | | | |--Stream Aggregate(DEFINE:([Expr1006]=MIN([fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[start_date]))) | 1 | 11 | 10 | Stream Aggregate | Aggregate | null | [Expr1006]=MIN([fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[start_date]) | 1 | 0 | 1.7E-06 | 10 | 0.0032859 | [Expr1006] | null | PLAN_ROW | False | 1 |
| 2 | 1 | | | |--Table Scan(OBJECT:([fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay])) | 1 | 12 | 11 | Table Scan | Table Scan | OBJECT:([fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay]) | [fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[start_date] | 2 | 0.003125 | 0.0001592 | 10 | 0.0032842 | [fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[start_date] | null | PLAN_ROW | False | 1 |
| 38 | 1 | | |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1042], [Recr1011], [Recr1012])) | 1 | 24 | 8 | Nested Loops | Inner Join | OUTER REFERENCES:([Expr1042], [Recr1011], [Recr1012]) | null | 4 | 0 | 1.68E-07 | 10 | 0.007087428 | [Expr1042], [Expr1020], [Expr1028] | null | PLAN_ROW | False | 5 |
| 0 | 0 | | |--Compute Scalar(DEFINE:([Expr1042]=[Expr1041]+(1))) | 1 | 25 | 24 | Compute Scalar | Compute Scalar | DEFINE:([Expr1042]=[Expr1041]+(1)) | [Expr1042]=[Expr1041]+(1) | 1 | 0 | 2E-08 | 10 | 2E-08 | [Expr1042], [Recr1011], [Recr1012] | null | PLAN_ROW | False | 5 |
| 39 | 1 | | | |--Table Spool(WITH STACK) | 1 | 26 | 25 | Table Spool | Lazy Spool | WITH STACK | null | 1 | 0 | 2E-08 | 10 | 2E-08 | [Expr1041], [Recr1011], [Recr1012] | null | PLAN_ROW | False | 5 |
| 0 | 0 | | |--Compute Scalar(DEFINE:([Expr1020]=[Recr1011]+(1), [Expr1028]=dateadd(day,[Recr1011]+(1),[Expr1024]))) | 1 | 30 | 24 | Compute Scalar | Compute Scalar | DEFINE:([Expr1020]=[Recr1011]+(1), [Expr1028]=dateadd(day,[Recr1011]+(1),[Expr1024])) | [Expr1020]=[Recr1011]+(1), [Expr1028]=dateadd(day,[Recr1011]+(1),[Expr1024]) | 1 | 0 | 1E-07 | 14 | 0.00708724 | [Expr1020], [Expr1028] | null | PLAN_ROW | False | 4 |
| 38 | 39 | | |--Nested Loops(Inner Join) | 1 | 31 | 30 | Nested Loops | Inner Join | null | null | 1 | 0 | 4.18E-06 | 10 | 0.00708684 | [Expr1024] | null | PLAN_ROW | False | 4 |
| 38 | 39 | | |--Filter(WHERE:([Recr1011]<datediff(day,CONVERT_IMPLICIT(datetimeoffset(7),[Expr1016],0),CONVERT_IMPLICIT(datetimeoffset(7),[Expr1017],0)))) | 1 | 32 | 31 | Filter | Filter | WHERE:([Recr1011]<datediff(day,CONVERT_IMPLICIT(datetimeoffset(7),[Expr1016],0),CONVERT_IMPLICIT(datetimeoffset(7),[Expr1017],0))) | null | 1 | 0 | 9.8E-07 | 9 | 0.00353702 | null | null | PLAN_ROW | False | 4 |
| 39 | 39 | | | |--Stream Aggregate(DEFINE:([Expr1016]=MIN([fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[start_date]), [Expr1017]=MAX([fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[end_date]))) | 1 | 33 | 32 | Stream Aggregate | Aggregate | null | [Expr1016]=MIN([fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[start_date]), [Expr1017]=MAX([fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[end_date]) | 1 | 0 | 1.7E-06 | 13 | 0.0035331 | [Expr1016], [Expr1017] | null | PLAN_ROW | False | 4 |
| 78 | 39 | | | |--Table Scan(OBJECT:([fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay])) | 1 | 34 | 33 | Table Scan | Table Scan | OBJECT:([fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay]) | [fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[start_date], [fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[end_date] | 2 | 0.0032035 | 8.07E-05 | 13 | 0.0035263 | [fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[start_date], [fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[end_date] | null | PLAN_ROW | False | 4 |
| 38 | 38 | | |--Stream Aggregate(DEFINE:([Expr1024]=MIN([fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[start_date]))) | 1 | 41 | 31 | Stream Aggregate | Aggregate | null | [Expr1024]=MIN([fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[start_date]) | 1 | 0 | 1.7E-06 | 10 | 0.0035331 | [Expr1024] | null | PLAN_ROW | False | 4 |
| 76 | 38 | | |--Table Scan(OBJECT:([fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay])) | 1 | 42 | 41 | Table Scan | Table Scan | OBJECT:([fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay]) | [fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[start_date] | 2 | 0.0032035 | 8.07E-05 | 10 | 0.0035263 | [fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay].[start_date] | null | PLAN_ROW | False | 4 |
| 78 | 39 | |--Table Scan(OBJECT:([fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay] AS [s])) | 1 | 51 | 6 | Table Scan | Table Scan | OBJECT:([fiddle_edc4ea9745d340e382f91fd7161a3a79].[dbo].[stay] AS [s]) | [s].[start_date], [s].[end_date] | 2 | 0.0032035 | 8.07E-05 | 13 | 0.0035263 | [s].[start_date], [s].[end_date] | null | PLAN_ROW | False | 4 |
Table 'Worktable'. Scan count 2, logical reads 235, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'stay'. Scan count 79, logical reads 117, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.