CODEWITHSUNDEEP
sqlsql-server
aas
aas

Reputation: 197

Create a view by day of week with the range of date list

I have a situation and i spend long day to figure out please help me

This is the table

5555    Alex    10:47   2013-11-09
6666    Joel    00:09   2013-11-10
6666    Joel    22:10   2013-11-11
5555    Alex    01:00   2013-11-11

Like this it will have a week of dates i need to create a view with all 7 days as a row assume

day1 date is 2013-11-09
day2 date is 2013-11-10
day3 date is 2013-11-11

I need this 

Emplid Name   Day1    Day2     Day3   Day4   Day5 Day6 Day7
555    Alex   10.47   --       01.00  --     --   --   --
666    Joel   --      00.09    22.10  --     --   --   --

if anybody can give a good solution other than this that will be very helpful

Thanks

I am getting this error

Msg 4109, Level 15, State 1, Line 1
Windowed functions cannot be used in the context of another windowed function or aggregate.

Upvotes: 2

Views: 555

Answers (1)

mucio
mucio

Reputation: 7119

you need something like:

    SELECT Emplid,
           Name,
           max(day1) day1,
           max(day2) day2,
           max(day3) day3,
           max(day4) day4,
           max(day5) day5,
           max(day6) day6,
           max(day7) day7
      FROM (
              SELECT Emplid,
                     Name,
                     CASE WHEN rank() over(partition by Emplid, Name order by date) = 1 THEN time ELSE NULL END Day1,
                     CASE WHEN rank() over(partition by Emplid, Name order by date) = 2 THEN time ELSE NULL END Day2,
                     CASE WHEN rank() over(partition by Emplid, Name order by date) = 3 THEN time ELSE NULL END Day3,
                     CASE WHEN rank() over(partition by Emplid, Name order by date) = 4 THEN time ELSE NULL END Day4,
                     CASE WHEN rank() over(partition by Emplid, Name order by date) = 5 THEN time ELSE NULL END Day5,
                     CASE WHEN rank() over(partition by Emplid, Name order by date) = 6 THEN time ELSE NULL END Day6,
                     CASE WHEN rank() over(partition by Emplid, Name order by date) = 7 THEN time ELSE NULL END Day7
                FROM your_table
           ) t
  GROUP BY Emplid,
           Name

You can check this demo on SQLFiddle

Upvotes: 1

Related Questions