locked
Entries for all dates in a range RRS feed

  • 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

Answers

  • 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
       
    declare @ColumnHeaders NVARCHAR(4000) ;
    ;with dates as (
    Select dateadd(day,n ,@startdate ) dt 
    from(values(0),(1),(2),(3),(4),(5),(6),(7)) D(n)
    Where dateadd(day,n ,@startdate )<=@enddate)
    
    
    --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, '');
    
     --print @ColumnHeaders
    
    
    ;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 Pragati Sharma 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 (
    Select dateadd(day,n ,@startdate ) dt 
    from(values(0),(1),(2),(3),(4),(5),(6),(7)) D(n)
    Where dateadd(day,n ,@startdate )<=@enddate)
    ,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 (
    Select dateadd(day,n ,@startdate ) dt 
    from(values(0),(1),(2),(3),(4),(5),(6),(7)) D(n)
    Where dateadd(day,n ,@startdate )<=@enddate)
    ,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
       
    declare @ColumnHeaders NVARCHAR(4000) ;
    ;with dates as (
    Select dateadd(day,n ,@startdate ) dt 
    from(values(0),(1),(2),(3),(4),(5),(6),(7)) D(n)
    Where dateadd(day,n ,@startdate )<=@enddate)
    
    
    --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, '');
    
     --print @ColumnHeaders
    
    
    ;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 Pragati Sharma 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