sql sum with separate column for each day

Question

My current code looks like this:

declare @start datetime
declare @end datetime

set @start = '2/16/2020'
set @end = '2/19/2020'

select 

s.location, s.department, s.position, SUM(s.hours)/60
from SCHEDULES s where SCHDATE between @start and @end
group by s.location, s.department, s.position

It yields the following results (which is correct):

   loc dep pos hrs
    2   2   7   96
    3   2   11  96
    2   2   13  192
    3   2   5   96
    3   1   4   228

How do I break this out by day so that the format looks like below: 'start' is the @start variable and 'start+1' is simply that plus 1 day, etc.

loc dep pos start start+1 start+2 start+3
2   2   7   24     24      24      24
3   2   11  24     24      24      24
2   2   13  48     48      48      48
3   2   5   24     24      24      24
3   1   4   57     57      57      57

thanks

Why do you need four columns with the same values? Why not just divide by (60 * 4)? — Gordon Linoff, Mar 03 '20 at 18:22
that was just an example. It could be split up totally randomly — Green, Mar 03 '20 at 18:25

downernn · Answer 1 · 2020-03-03T19:07:26.963

0

Sounds like you want to do a pivot:

SELECT  *
    FROM SCHEDULES s 
PIVOT(
    SUM(hours) 
    FOR SCHDATE IN (
        [2020-2-16],
        [2020-2-17], 
        [2020-2-18], 
        [2020-2-19])
) AS pivot_table;

Hopefully the dates you want to work with are fixed and known. If you need to pivot on calculated columns, things seem to get a lot more complicated. For example, see this thread.

edited Mar 03 '20 at 19:07

answered Mar 03 '20 at 19:00

downernn

176
5

sql sum with separate column for each day

1 Answers1