CODEWITHSUNDEEP
sqldatepivot
Corey Gashlin
Corey Gashlin

Reputation: 21

Rows of date to columns of day-of-week

I have a table and I have users with dates...

| Employee_Name | Date       |
| CG            | 2019-04-22 |
| CG            | 2019-04-26 |
| CG            | 2019-04-24 |

I am trying to get results so when I query and say Date BETWEEN (@start AND @end) it would give me results like:

EMPLOYEE_NAME | Monday     | Tuesday | Wednesday  | Thursday | Friday     | Saturday | Sunday |
CG            | 2019-04-22 | NULL    | 2019-04-24 | NULL     | 2019-04-26 | NULL     | NULL   |

I am at a loss scratching my head honestly - I know i may be able to do it with a pivot.

Upvotes: 0

Views: 72

Answers (2)

Gen Wan
Gen Wan

Reputation: 2009

Here is the Pivot solution.

I added more sample data (one more employee and two dates) to test it out:

declare @weekday table (weekday varchar(10))
insert into @weekday (weekday) values 
('Monday'),
('Tuesday'),
('Wednesday'),
('Thursday'),
('Friday'),
('Saturday'), 
('Sunday')

select p.employee_name,[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday]
from (
select t.employee_name,t.date,w.weekday from (
select *, datename(weekday,date) as weekday
from #test) t
right join @weekday w
on w.weekday = t.weekday) s
pivot
(max(date)
for weekday in ([Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Sunday])
) as p
where p.employee_name is not null

Please change the table_name accordingly. (I used #test)

Test Result:

DB<>Fiddle

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272096

The following should work (perfectly if @d2 - @d1 < 7 otherwise it'll display the first date for each weekday):

SELECT Employee_Name
     , MIN(CASE WHEN DATENAME(dw, Date) = 'Sunday'    THEN Date END) AS Sunday
     , MIN(CASE WHEN DATENAME(dw, Date) = 'Monday'    THEN Date END) AS Monday
     , MIN(CASE WHEN DATENAME(dw, Date) = 'Tuesday'   THEN Date END) AS Tuesday
     , MIN(CASE WHEN DATENAME(dw, Date) = 'Wednesday' THEN Date END) AS Wednesday
     , MIN(CASE WHEN DATENAME(dw, Date) = 'Thursday'  THEN Date END) AS Thursday
     , MIN(CASE WHEN DATENAME(dw, Date) = 'Friday'    THEN Date END) AS Friday
     , MIN(CASE WHEN DATENAME(dw, Date) = 'Saturday'  THEN Date END) AS Saturday
FROM t
WHERE Date BETWEEN @d1 AND @d2
GROUP BY Employee_Name

Upvotes: 2

Related Questions