# Entries for all dates in a range

• ### Question

• I need to generate  a capacity report for all the team members for a sprint range:

For example:

Capacity table entries

Name Value

A           6

B           6

C           5

Completed table entries

Name     Date       Value

A          18 jan      5

A          19 Jan     3

B           17 Jan    4

B           19 Jan    4

B           20Jan     2

and so on…

Here for D we do not have capacity defined and for few there is no completed value.

Sprint range is : 17 Jan 2017 to 20 Jan 2017

 Name 17 18 19 20 Capacity completed Capacity completed Capacity completed Capacity completed A 6 0 6 5 6 3 6 0 B 6 4 6 0 6 4 6 2 C 5 3 5 8 5 5 5 3 D 0 3 0 0 0 2 0 0

How can I produce a list of values for all employees against each date in the particular sprint range.

I have tried Cross apply but couldn’t make it perfectly work for all the Employees.

Best Regards,

Pragati

Tuesday, January 17, 2017 1:46 PM

• Here is the dynamic version:

```Create table Capacity(Name char(1),Value int)
Insert into Capacity values('A',6),('B',6),('C',5)
Create table Completed (Name char(1), Date date, Value int)
Insert into Completed values('A','18 jan,2017', 5)
,('A','19 jan,2017', 3)
,('B','17 jan,2017', 4)
,('B','17 jan,2017', 4)
,('B','20 jan,2017', 2)
,('D', '20 jan,2017', 2)

declare @startdate date='17 Jan 2017'
declare @enddate date= '20 Jan 2017'

DECLARE @Sql NVARCHAR(4000) =null

;with dates as (
from(values(0),(1),(2),(3),(4),(5),(6),(7)) D(n)

--load date list to a temp table
Select dt
into tempdates
from dates

Select @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'isnull(max(CASE WHEN dt =' + quotename(dt,'''') + ' THEN  value1  else null end ),0) as '
+ quotename(Cast(datepart(day,dt) as varchar(2))+'Capacity','[')  + char(10)+char(13)
+
',' + 'isnull(max(CASE WHEN dt =' + quotename(dt,'''') + ' THEN  value2  else null end ),0) as '
+ quotename(Cast(datepart(day,dt) as varchar(2))+'Complete','[') + char(10)+char(13)
FROM  tempdates
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');

;with  names as (

Select distinct Name from Capacity
union
Select distinct Name from Completed
)
,combs as (
Select Name, dt from names,tempdates)

,finaldataset as (
Select c0.name,c0.dt,c1.value as value1, c2.value as value2 from combs  c0 left join Capacity c1 on c0.name=c1.name
left join  Completed c2 on c0.name=c2.name and c0.dt=c2.Date
)

Select * into finaldataset2
from finaldataset

Select @sql  =N' Select  name, '+   @ColumnHeaders + ' from  finaldataset2 Group by name';

--print @sql
EXEC(@SQL)

--cleanup
Drop table finaldataset2,tempdates

drop table Completed,Capacity```

• Marked as answer by Tuesday, January 17, 2017 2:50 PM
Tuesday, January 17, 2017 2:45 PM

### All replies

• ```Create table Capacity(Name char(1),Value int)
Insert into Capacity values('A',6),('B',6),('C',5)
Create table Completed (Name char(1), Date date, Value int)
Insert into Completed values('A','18 jan,2017', 5)
,('A','19 jan,2017', 3)
,('B','17 jan,2017', 4)
,('B','17 jan,2017', 4)
,('B','20 jan,2017', 2)

declare @startdate date='17 Jan 2017'
declare @enddate date= '20 Jan 2017'

;with dates as (
from(values(0),(1),(2),(3),(4),(5),(6),(7)) D(n)
,names as (
Select distinct Name from Capacity)

, combs as (
Select Name, dt from names,dates)

,finaldataset as (
Select c0.name,c0.dt,c1.value as value1, c2.value as value2 from combs  c0 join Capacity c1 on c0.name=c1.name
left join  Completed c2 on c0.name=c2.name and c0.dt=c2.Date
)

Select Name
, max(Case when dt='2017-01-17' then Value1 end) '17 Capacity'
, ISNULL(max(Case when dt='2017-01-17' then Value2 end),0) '17 Complete'
, max( Case when dt='2017-01-18' then Value1 end) '18 Capacity'
, ISNULL(max(Case when dt='2017-01-18' then Value2 end),0) '18 Complete'
,  max(Case when dt='2017-01-19' then Value1 end) '19 Capacity'
, ISNULL(max(Case when dt='2017-01-19' then Value2 end),0) '19 Complete'
,  max(Case when dt='2017-01-20' then Value1 end) '20 Capacity'
,ISNULL( max(Case when dt='2017-01-20' then Value2 end),0) '20 Complete'

from finaldataset
Group by name

drop table Completed,Capacity```

Tuesday, January 17, 2017 2:18 PM
• Hi

As per your question, I can understand that Capacity and completed tables will not have value for few of the name. If that is the case then you will be missing those values sometimes. To avoid that, it would be better to maintain all the names (A to Z) in a separate table so that you can make that table as a reference can join other 2 tables (Capacity & Completed) with Left or right outer join.

Thanks

Hari

Tuesday, January 17, 2017 2:25 PM
• Thanks for your prompt response.

I could see that you always have capacity ..In my case there are chances that there is no capacity. However, a person completed some hours Or vice versa.

So I do not have a main table which have all the Employees information ..I need to somehow get the distinct employees from Capacity and completed and then generate the rows for each date combination.

so in case you have

Insert into Completed values('A','18 jan,2017', 5)
,('A','19 jan,2017', 3)
,('B','17 jan,2017', 4)
,('B','17 jan,2017', 4)
,('B','20 jan,2017', 2)
,('D', '20 jan,2017', 2)

I want to see the values for D as well.

Thank you

Tuesday, January 17, 2017 2:35 PM
• ```Create table Capacity(Name char(1),Value int)
Insert into Capacity values('A',6),('B',6),('C',5)
Create table Completed (Name char(1), Date date, Value int)
Insert into Completed values('A','18 jan,2017', 5)
,('A','19 jan,2017', 3)
,('B','17 jan,2017', 4)
,('B','17 jan,2017', 4)
,('B','20 jan,2017', 2)
,('D', '20 jan,2017', 2)

declare @startdate date='17 Jan 2017'
declare @enddate date= '20 Jan 2017'

;with dates as (
from(values(0),(1),(2),(3),(4),(5),(6),(7)) D(n)
,names as (
Select distinct Name from Capacity
union
Select distinct Name from Completed

)

, combs as (
Select Name, dt from names,dates)

,finaldataset as (
Select c0.name,c0.dt,c1.value as value1, c2.value as value2 from combs  c0 left join Capacity c1 on c0.name=c1.name
left join  Completed c2 on c0.name=c2.name and c0.dt=c2.Date
)

Select Name
, ISNULL(max(Case when dt='2017-01-17' then Value1 end),0) '17 Capacity'
, ISNULL(max(Case when dt='2017-01-17' then Value2 end),0) '17 Complete'
, ISNULL(max( Case when dt='2017-01-18' then Value1 end),0) '18 Capacity'
, ISNULL(max(Case when dt='2017-01-18' then Value2 end),0) '18 Complete'
,  ISNULL(max(Case when dt='2017-01-19' then Value1 end),0) '19 Capacity'
, ISNULL(max(Case when dt='2017-01-19' then Value2 end),0) '19 Complete'
,  ISNULL(max(Case when dt='2017-01-20' then Value1 end),0) '20 Capacity'
,ISNULL( max(Case when dt='2017-01-20' then Value2 end),0) '20 Complete'

from finaldataset
Group by name

drop table Completed,Capacity```

Tuesday, January 17, 2017 2:42 PM
• Here is the dynamic version:

```Create table Capacity(Name char(1),Value int)
Insert into Capacity values('A',6),('B',6),('C',5)
Create table Completed (Name char(1), Date date, Value int)
Insert into Completed values('A','18 jan,2017', 5)
,('A','19 jan,2017', 3)
,('B','17 jan,2017', 4)
,('B','17 jan,2017', 4)
,('B','20 jan,2017', 2)
,('D', '20 jan,2017', 2)

declare @startdate date='17 Jan 2017'
declare @enddate date= '20 Jan 2017'

DECLARE @Sql NVARCHAR(4000) =null

;with dates as (
from(values(0),(1),(2),(3),(4),(5),(6),(7)) D(n)

--load date list to a temp table
Select dt
into tempdates
from dates

Select @ColumnHeaders = STUFF( (SELECT DISTINCT ',' + 'isnull(max(CASE WHEN dt =' + quotename(dt,'''') + ' THEN  value1  else null end ),0) as '
+ quotename(Cast(datepart(day,dt) as varchar(2))+'Capacity','[')  + char(10)+char(13)
+
',' + 'isnull(max(CASE WHEN dt =' + quotename(dt,'''') + ' THEN  value2  else null end ),0) as '
+ quotename(Cast(datepart(day,dt) as varchar(2))+'Complete','[') + char(10)+char(13)
FROM  tempdates
FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');

;with  names as (

Select distinct Name from Capacity
union
Select distinct Name from Completed
)
,combs as (
Select Name, dt from names,tempdates)

,finaldataset as (
Select c0.name,c0.dt,c1.value as value1, c2.value as value2 from combs  c0 left join Capacity c1 on c0.name=c1.name
left join  Completed c2 on c0.name=c2.name and c0.dt=c2.Date
)

Select * into finaldataset2
from finaldataset

Select @sql  =N' Select  name, '+   @ColumnHeaders + ' from  finaldataset2 Group by name';

--print @sql
EXEC(@SQL)

--cleanup
Drop table finaldataset2,tempdates

drop table Completed,Capacity```

• Marked as answer by Tuesday, January 17, 2017 2:50 PM
Tuesday, January 17, 2017 2:45 PM
• Perfect Thank you :)
Tuesday, January 17, 2017 2:51 PM
• >> I need to generate  a capacity report for all the team members for a sprint range: <<

Would you please follow forum rules and post DDL along with sample data? We now have to transcribe text from your ASCII picture for you. Why do you think things like “name”, “date” or “value” are valid data element names? It would also help if you knew how to write dates (please read the ISO standards).

CREATE TABLE Foobar
(employee_name CHAR(1) NOT NULL PRIMARY KEY,
foobar_score INTEGER NOT NULL);

INSERT INTO Foobar
VALUES (‘A’, 6),(‘B’, 6),(‘C’, 5);

Please look at the above DDL. A table has to have a key, by definition. We need to know of columns can be null or not. Data elements should use the ISO 11179 naming conventions <attribute>_<attribute property>.

Here is a second guess at the other table.

CREATE TABLE Floob
(employee_name CHAR(1) NOT NULL,
generic_date DATE NOT NULL,
PRIMARY KEY (employee_name, generic_date),
foobar_score INTEGER NOT NULL);

INSERT INTO Floob
VALUES
(‘A’, ‘2016-01-18’, 5),
(‘A’, ‘2016-01-19’, 3),
(‘B’, ‘2016-01-17’, 4),
(‘B’, ‘2016-01-19’, 4),
(‘B’, ‘2016-01-20’, 2);

>> and so on… <<

There is no “and so on..” Operator in SQL.

>> Here for D we do not have capacity defined and for few there is no completed value. <<

Does that mean it is shown as a zero or is shown as NULL? This is why we require DDL; we cannot read your mind. You also missed a fundamental concept of modern programming; Your trying to use the database tier in a tiered architecture to format a report! In SQL, we passed the result set to a presentation layer that does all of that ASCII pretty printing. The result set ought to be a simple outer join against the calendar table.

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Tuesday, January 17, 2017 5:05 PM