locked
Different Sums on each column RRS feed

  • Question

  • I'm trying to make a dashboard that helps us see planned workload this will help us not over book what we have. 

    SumSumSum




    • Edited by Mr. Adam Thursday, September 12, 2019 5:10 PM
    Thursday, September 12, 2019 4:45 PM

Answers

  • Hello Mr. Adam,

    Please check if this work for you!

    create  table timecontrol (
    	id int identity primary key,
    	Assign varchar(50),
    	hour_prep decimal(5,2),
    	hour_work decimal(5,2),
    	schd_date datetime)
    
    
    insert into timecontrol values ('Chris', 0.5, 2, '2019-09-13 09:39:00')
    insert into timecontrol values ('Dave', 0.25, 1.5, '2019-09-12 09:39:00')
    insert into timecontrol values ('Abe', 0.5, 2, '2019-09-14 09:39:00')
    insert into timecontrol values ('Abe', 0.8, 3.5, '2019-09-12 09:39:00')
    insert into timecontrol values ('Chris', 0.9, 7, '2019-09-14 09:39:00')
    insert into timecontrol values ('Bill', 1.5, 8, '2019-09-14 09:39:00')
    
    
    select
     x.Assign,
     sum(x.Today) as 'SumToday',
     sum(x.Tomorrow) as 'SumTomorrow',
     sum(x.AfterTomorrow) as 'AfterTomorrow'
    from
    (
    	select 
    		Assign,
    		case when (datediff(day, getdate(), schd_date)) = 0 then (hour_prep+hour_work) else 0 end as 'Today',
    		case when (datediff(day, getdate(), schd_date)) = 1 then (hour_prep+hour_work) else 0 end as 'Tomorrow',	
    		case when (datediff(day, getdate(), schd_date)) = 2 then (hour_prep+hour_work) else 0 end as 'AfterTomorrow'
    	from timecontrol) x
    group by 
    	x.Assign

    Regards,

    Rafael

    • Marked as answer by Mr. Adam Thursday, September 12, 2019 7:04 PM
    Thursday, September 12, 2019 6:43 PM

All replies

  • I figure it would look something like this (but it doesn't work)


    Select

     Person,

    (Select  Person,

    Sum (HOURS_TO_PREP + HOURS_OF_WORK)

    from PLAN_LINEUP

    where SCHED_START_DATE between

    DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate())) -- Midnight last night

    and

    DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate())+1) -- Midnight tomorrow

    group by PERSON) As 'Today'

    ,

    (Select  person, Sum (HOURS_TO_PREP + HOURS_OF_WORK)

    from PLAN_LINEUP where SCHED_START_DATE between

    DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate())+1) -- Midnight last night

    and

    DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate())+2) -- Midnight in tomorrow

    group by PERSON) As 'Tomorrow'

    ,

    (Select person, Sum (HOURS_TO_PREP + HOURS_OF_WORK)

    from PLAN_LINEUP where SCHED_START_DATE between

    DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate())+2) -- Midnight 2 days from Now

    and

    DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate())+3) -- Midnight 3 days from Now

    group by PERSON)


    From Plan_lineup

    --Group by PERSON ??




    • Edited by Mr. Adam Thursday, September 12, 2019 6:17 PM
    Thursday, September 12, 2019 6:15 PM
  • Hello Mr. Adam,

    Please check if this work for you!

    create  table timecontrol (
    	id int identity primary key,
    	Assign varchar(50),
    	hour_prep decimal(5,2),
    	hour_work decimal(5,2),
    	schd_date datetime)
    
    
    insert into timecontrol values ('Chris', 0.5, 2, '2019-09-13 09:39:00')
    insert into timecontrol values ('Dave', 0.25, 1.5, '2019-09-12 09:39:00')
    insert into timecontrol values ('Abe', 0.5, 2, '2019-09-14 09:39:00')
    insert into timecontrol values ('Abe', 0.8, 3.5, '2019-09-12 09:39:00')
    insert into timecontrol values ('Chris', 0.9, 7, '2019-09-14 09:39:00')
    insert into timecontrol values ('Bill', 1.5, 8, '2019-09-14 09:39:00')
    
    
    select
     x.Assign,
     sum(x.Today) as 'SumToday',
     sum(x.Tomorrow) as 'SumTomorrow',
     sum(x.AfterTomorrow) as 'AfterTomorrow'
    from
    (
    	select 
    		Assign,
    		case when (datediff(day, getdate(), schd_date)) = 0 then (hour_prep+hour_work) else 0 end as 'Today',
    		case when (datediff(day, getdate(), schd_date)) = 1 then (hour_prep+hour_work) else 0 end as 'Tomorrow',	
    		case when (datediff(day, getdate(), schd_date)) = 2 then (hour_prep+hour_work) else 0 end as 'AfterTomorrow'
    	from timecontrol) x
    group by 
    	x.Assign

    Regards,

    Rafael

    • Marked as answer by Mr. Adam Thursday, September 12, 2019 7:04 PM
    Thursday, September 12, 2019 6:43 PM
  • Incredible!!!

    Thank you.

    I was able to do this without creating the new table but rather retooling your code to look in the right places in the existing table.

    A++ 

    THANKYOU


    Dude!

    Thursday, September 12, 2019 7:04 PM