SQL GROUPING QUESTION ISSUE

corey gashlin
corey gashlin used Ask the Experts™
on
SQL View to group employees and show days for range.
I have my SQL View which is showing me the data that I want however it is not formatting how I would want it and I am at a brick wall with it -

Here is my sql
SELECT DISTINCT 
                          dbo.EMPLOYEELIST.EMPLOYEE_NAME,
                        
						 dbo.EMPLOYEELIST.ACTIVE, 
						 CASE WHEN cast(dbo.TimeEntered.Monday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Monday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Monday  as date)
						 END AS MONDAY,
						  CASE WHEN cast(dbo.TimeEntered.Tuesday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Tuesday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Tuesday  as date)
						 END AS Tuesday,
						 	  CASE WHEN cast(dbo.TimeEntered.Wed  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Wed  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Wed  as date)
						 END AS Wednesday,
						 	 	  CASE WHEN cast(dbo.TimeEntered.Thursday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Thursday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Thursday  as date)
						 END AS Thursday,
						 	 	 	  CASE WHEN cast(dbo.TimeEntered.Friday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Friday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Friday  as date)
						 END AS Friday,
						 	 	 	 	  CASE WHEN cast(dbo.TimeEntered.Saturday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Saturday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Saturday  as date)
						 END AS Saturday,
						 	 	 	 	  CASE WHEN cast(dbo.TimeEntered.Sunday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Sunday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Sunday  as date)
						 END AS Sunday

FROM            dbo.EMPLOYEELIST LEFT OUTER JOIN
                         dbo.TimeEntered ON dbo.EMPLOYEELIST.EMPLOYEE_CODE = dbo.TimeEntered.Userid

Where cast(dbo.TimeEntered.datetime  as date) between '04/22/2019' and '04/24/2019' and Active='Y' 

Open in new window


here are my results -


Examples

EMPLOYEE_NAME      ACTIVE      MONDAY      Tuesday      Wednesday      Thursday      Friday      Saturday      Sunday
Adam McElhaney      Y      NULL      NULL      NULL      NULL      2019-04-19      NULL      NULL
Adam McElhaney      Y      NULL      NULL      NULL      NULL      2019-04-26      NULL      NULL
Adam McElhaney      Y      NULL      NULL      2019-04-24      NULL      NULL      NULL      NULL
Adam McElhaney      Y      NULL      2019-04-23      NULL      NULL      NULL      NULL      NULL
Adam McElhaney      Y      NULL      2019-04-23      2019-04-24      2019-04-25      NULL      NULL      NULL
Adam McElhaney      Y      2019-04-22      NULL      NULL      NULL      NULL      NULL      NULL
Adam McElhaney      Y      2019-04-22      2019-04-23      NULL      NULL      NULL      NULL      NULL
Adam McElhaney      Y      2019-04-22      2019-04-23      2019-04-24      NULL      NULL      NULL      NULL
Adam McElhaney      Y      2019-04-22      2019-04-23      2019-04-24      2019-04-25      NULL      NULL      NULL
Alan Moran      Y      NULL      NULL      2019-04-17      2019-04-18      2019-04-19      NULL      NULL
Alan Moran      Y      NULL      2019-04-23      NULL      NULL      NULL      NULL      NULL
Alan Moran      Y      2019-04-22      2019-04-23      NULL      NULL      NULL      NULL      NULL

What I am looking for is grouping essentially -

I want to choose a week (7 day range) and have it format so that I just have 1 username and that username then shows me dates in the coresponding days of the week. A null where they have no put in any time...

So lay out would be something like

Adam McElhaney | Y | 2019-04-22| 2019-04-23 | NULL | NULL | 2019-04-26| NULL|

Instead of the output above...
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Quick and dirty way is this one:
SELECT e.EMPLOYEE_NAME, e.ACTIVE, 
       MAX(e.MONDAY) AS MONDAY,
       MAX(e.Tuesday) AS Tuesday, 
       MAX(e.Wednesday) AS Wednesday, 
       MAX(e.Thursday) AS Thursday, 
       MAX(e.Friday) AS Friday, 
       MAX(e.Saturday) AS Saturday, 
       MAX(e.Sunday) AS Sunday
  FROM (
         SELECT DISTINCT 
                         dbo.EMPLOYEELIST.EMPLOYEE_NAME,
                        
						 dbo.EMPLOYEELIST.ACTIVE, 
						 CASE WHEN cast(dbo.TimeEntered.Monday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Monday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Monday  as date)
						 END AS MONDAY,
						  CASE WHEN cast(dbo.TimeEntered.Tuesday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Tuesday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Tuesday  as date)
						 END AS Tuesday,
						 	  CASE WHEN cast(dbo.TimeEntered.Wed  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Wed  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Wed  as date)
						 END AS Wednesday,
						 	 	  CASE WHEN cast(dbo.TimeEntered.Thursday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Thursday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Thursday  as date)
						 END AS Thursday,
						 	 	 	  CASE WHEN cast(dbo.TimeEntered.Friday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Friday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Friday  as date)
						 END AS Friday,
						 	 	 	 	  CASE WHEN cast(dbo.TimeEntered.Saturday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Saturday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Saturday  as date)
						 END AS Saturday,
						 	 	 	 	  CASE WHEN cast(dbo.TimeEntered.Sunday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Sunday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Sunday  as date)
						 END AS Sunday
         FROM     dbo.EMPLOYEELIST LEFT OUTER JOIN
                  dbo.TimeEntered ON dbo.EMPLOYEELIST.EMPLOYEE_CODE = dbo.TimeEntered.Userid
        Where cast(dbo.TimeEntered.datetime  as date) between '04/22/2019' and '04/24/2019' and Active='Y'   ) e
GROUP BY e.EMPLOYEE_NAME, e.ACTIVE

Open in new window

Of course, you may add MAX() and GROUP BY directly to your query (and remove DISTINCT).
Kyle AbrahamsSenior .Net Developer

Commented:
You could get just the dates you wanted and then pivot off of that.

https://blogs.msdn.microsoft.com/spike/2009/03/03/pivot-tables-in-sql-server-a-simple-sample/

Author

Commented:
pcbella looks good however, I am having issues now with getting all of my employee_names to show up... I tried seeing what may be causing that but nothing - Basically I need all my employees names to show up regardless of if they have any time or not in the system... I thought left outer join would work but when combined with all this it seems to not work.

Please give me some guidence :-)

Author

Commented:
I have narrowed it down to the where clause is limiting the outer join back to inner join.... I need those names to show even if no data or just displayts null for the days.
Left-join is correct, but the where-clause contains filters on the left-table, which may not exist.  Change the word WHERE to AND, now it's part of the on-clause.
Yes, John pointed it out. Either change the FROM - WHERE part to
         FROM     dbo.EMPLOYEELIST LEFT OUTER JOIN
                  dbo.TimeEntered ON dbo.EMPLOYEELIST.EMPLOYEE_CODE = dbo.TimeEntered.Userid
        Where dbo.EMPLOYEELIST.Active = 'Y' AND
              (dbo.TimeEntered.Userid IS NULL OR cast(dbo.TimeEntered.datetime  as date) between '04/22/2019' and '04/24/2019')  ) e

Open in new window

or
         FROM     dbo.EMPLOYEELIST LEFT OUTER JOIN
                  dbo.TimeEntered ON dbo.EMPLOYEELIST.EMPLOYEE_CODE = dbo.TimeEntered.Userid AND cast(dbo.TimeEntered.datetime  as date) between '04/22/2019' and '04/24/2019')
        Where dbo.EMPLOYEELIST.Active = 'Y' ) e

Open in new window


And one more note: The CAST conversion in WHERE or in JOIN expression could significantly slow the query down. It is better to adjust the constants to the data type of the column.

… dbo.TimeEntered.datetime between '04/22/2019 00:00' and '04/24/2019 23:59:59.999'
or
… dbo.TimeEntered.datetime >= '04/22/2019' AND dbo.TimeEntered.datetime < '04/25/2019'  -- the time part is added by SQL engine here notice the date + 1
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Please don't use "between" for date ranges. It is just so unnecessary to take the risk that data will fall into a crack (and the query result be wrong) instead use the second form as suggested by pcelba above form, it is 100% accurate no matter what time precision applies to the data. i.e.

   dbo.TimeEntered.datetime >= '20190422' AND dbo.TimeEntered.datetime < '20190425'  -- notice the date + 1

Also note that mm/dd/yyyy is NOT a "safe" date format to use in queries.
YYYYMMDD is the only 100% safe date literal style for sql server.
YYYY-MM-DD is almost as safe (but not quite as good as YYYYMMDD).

Here is a somewhat different approach to the query that uses arithmetic to align data into days of the week. Using datediff from the zero date we can reliably figure out the days of the week (0 = Monday) (using remainder of division by 7) without needing to rely on language or datefirst settings. The cross join simply enforces that each day of week is represented against all employees, then the time entered is left joined to the cartesian join of employees and days of week numbers.
declare @from as date
set @from = '20190429'

select
      e.employee_code
     , max(case when cj.d = 0 and t.d is not null then t.dt_string end) as Monday
     , max(case when cj.d = 1 and t.d is not null then t.dt_string end) as Tuesday
     , max(case when cj.d = 2 and t.d is not null then t.dt_string end) as Wednesday
     , max(case when cj.d = 3 and t.d is not null then t.dt_string end) as Thursday
     , max(case when cj.d = 4 and t.d is not null then t.dt_string end) as Friday
     , max(case when cj.d = 5 and t.d is not null then t.dt_string end) as Saturday
     , max(case when cj.d = 6 and t.d is not null then t.dt_string end) as Sunday
from employeelist e
cross join (
    select 0 as d union all
    select 1 as d union all
    select 2 as d union all
    select 3 as d union all
    select 4 as d union all
    select 5 as d union all
    select 6 as d
    ) cj
left join (
    select
          employee_code
        , datediff(day,0,TimeEntered) % 7 as d
        , convert(varchar(10),TimeEntered,121) as dt_string
    from mytable
    where TimeEntered >= @from
    and TimeEntered < dateadd(day,7,@from)
    ) t on e.employee_code = t.employee_code and cj.d = t.d
group by
      e.employee_code

Open in new window

Using the following sample data:
CREATE TABLE mytable(
   EMPLOYEE_CODE VARCHAR(1) NOT NULL
  ,TimeEntered   DATE  NOT NULL
);
INSERT INTO mytable(EMPLOYEE_CODE,TimeEntered) VALUES ('a','2019-04-29');
INSERT INTO mytable(EMPLOYEE_CODE,TimeEntered) VALUES ('a','2019-04-30');
INSERT INTO mytable(EMPLOYEE_CODE,TimeEntered) VALUES ('a','2019-05-01');
INSERT INTO mytable(EMPLOYEE_CODE,TimeEntered) VALUES ('a','2019-05-03');
INSERT INTO mytable(EMPLOYEE_CODE,TimeEntered) VALUES ('a','2019-05-06');
INSERT INTO mytable(EMPLOYEE_CODE,TimeEntered) VALUES ('a','2019-05-09');
INSERT INTO mytable(EMPLOYEE_CODE,TimeEntered) VALUES ('a','2019-05-10');

CREATE TABLE EMPLOYEELIST(
   EMPLOYEE_CODE VARCHAR(1) NOT NULL PRIMARY KEY
);
INSERT INTO EMPLOYEELIST(EMPLOYEE_CODE) VALUES ('a');
INSERT INTO EMPLOYEELIST(EMPLOYEE_CODE) VALUES ('b');

Open in new window

Produces this result:
+---------------+------------+------------+------------+----------+------------+----------+--------+
| employee_code |   Monday   |  Tuesday   | Wednesday  | Thursday |   Friday   | Saturday | Sunday |
+---------------+------------+------------+------------+----------+------------+----------+--------+
| a             | 2019-04-29 | 2019-04-30 | 2019-05-01 | NULL     | 2019-05-03 | NULL     | NULL   |
| b             | NULL       | NULL       | NULL       | NULL     | NULL       | NULL     | NULL   |
+---------------+------------+------------+------------+----------+------------+----------+--------+

Open in new window

The US date format is safe under US locales, Europe and Japan should not use it...

The whole query can be rather simple:
SELECT dbo.EMPLOYEELIST.EMPLOYEE_NAME,
       dbo.EMPLOYEELIST.ACTIVE, 
       CAST(MAX(dbo.TimeEntered.Monday) AS date) Monday, 
       CAST(MAX(dbo.TimeEntered.Tuesday) AS date) Tuesday, 
       CAST(MAX(dbo.TimeEntered.Wed) AS date) Wednesday, 
       CAST(MAX(dbo.TimeEntered.Thursday) AS date) Thursday, 
       CAST(MAX(dbo.TimeEntered.Friday) AS date) Friday, 
       CAST(MAX(dbo.TimeEntered.Saturday) AS date) Saturday, 
       CAST(MAX(dbo.TimeEntered.Sunday) AS date) Sunday
  FROM dbo.EMPLOYEELIST
  LEFT JOIN dbo.TimeEntered ON dbo.EMPLOYEELIST.EMPLOYEE_CODE = dbo.TimeEntered.Userid 
            AND dbo.TimeEntered.datetime >= '04/22/2019' AND dbo.TimeEntered.datetime < '04/29/2019' 
 WHERE dbo.EMPLOYEELIST.Active='Y'
 GROUP BY dbo.EMPLOYEELIST.EMPLOYEE_NAME, dbo.EMPLOYEELIST.ACTIVE

Open in new window

Paul's query is also good it just does not use the existing data structures. I would also recommend to use DATEPART(dw, ...) function instead of  "datediff(day,0,TimeEntered) % 7"  expression.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
YYYYMMDD is safe regardless of server settings, that is the point really.

The same is true of using  "datediff(day,0,TimeEntered) % 7" instead of language based approaches such as English day names.

Author

Commented:
So I have tried them all and the following apepars to be the closes except something is not quite right -  Since I am trying to find Monday that starts with 4/22/2019 I only want Tuesday-Sunday to show dates if they have those dates that correspond so 4/23,4/24 etc.. if not then just null.  

I have results showing where future dates are populating where they should not be in -


SELECT e.EMPLOYEE_NAME,
       MAX(e.MONDAY) AS MONDAY,
       MAX(e.Tuesday) AS Tuesday, 
       MAX(e.Wednesday) AS Wednesday, 
       MAX(e.Thursday) AS Thursday, 
       MAX(e.Friday) AS Friday, 
       MAX(e.Saturday) AS Saturday, 
       MAX(e.Sunday) AS Sunday
  FROM (
         SELECT 
                         dbo.EMPLOYEELIST.EMPLOYEE_NAME,
                        
						 
						 CASE WHEN cast(dbo.TimeEntered.Monday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Monday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Monday  as date)
						 END AS MONDAY,
						  CASE WHEN cast(dbo.TimeEntered.Tuesday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Tuesday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Tuesday  as date)
						 END AS Tuesday,
						 	  CASE WHEN cast(dbo.TimeEntered.Wed  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Wed  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Wed  as date)
						 END AS Wednesday,
						 	 	  CASE WHEN cast(dbo.TimeEntered.Thursday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Thursday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Thursday  as date)
						 END AS Thursday,
						 	 	 	  CASE WHEN cast(dbo.TimeEntered.Friday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Friday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Friday  as date)
						 END AS Friday,
						 	 	 	 	  CASE WHEN cast(dbo.TimeEntered.Saturday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Saturday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Saturday  as date)
						 END AS Saturday,
						 	 	 	 	  CASE WHEN cast(dbo.TimeEntered.Sunday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Sunday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Sunday  as date)
						 END AS Sunday
         FROM     dbo.EMPLOYEELIST LEFT Outer JOIN
                  dbo.TimeEntered ON dbo.EMPLOYEELIST.EMPLOYEE_CODE = dbo.TimeEntered.Userid
        and cast(dbo.TimeEntered.Monday  as date) = '04/22/2019' and Active='Y' and Proj_id <> '1'   ) e
GROUP BY e.EMPLOYEE_NAME

Open in new window




Results
Gabriel Coello      2019-04-22      2019-04-23      2019-05-01      NULL      2019-05-03      NULL      NULL
This does not sound too believable because your query contains condition "cast(dbo.TimeEntered.Monday  as date) = '04/22/2019'"...
This would mean when the Monday column contains  '04/22/2019' then the same row contains values in Tuesday, Wed, and Friday columns?
If there are future values dated in May does it mean we are working with some plans? What exactly contains t.datetime column?

Could you please show results for this query?
SELECT t.userid, t.datetime, t.Monday, t.Tuesday, t.Wed, t.Thursday, t.Friday, t.Saturday, t.Sunday
  FROM dbo.TimeEntered t
 WHERE cast(t.Monday  as date) = '04/22/2019'

Open in new window

It could also be good to know userID for Gabriel Coello and if the Proj_ID column is in the above table then include it in above query. We have to know the data meaning then we may continue with the query design.

The best you can do is to post sample data containing CREATE TABLE and INSERT commands, then your query which provides incorrect results and also expected results. We need to know what data are you working with. Good example of such piece of SQL code is in Paul's answer above. Of course, we don't care what are real names of employees or other sensitive data.

Author

Commented:
Sorry - So the table that its pulling from has tons of dates - I just have it all going into a view using pivot to move dates that fall on specific dates into the corresponding columns.... The end result that I am trying to get on here does not show correctly. Becuase there may be specific dates that are in the future that fall on wednesday..

It should only show me dates that are Monday-Sunday for the given week but its not.

Author

Commented:
So this si my results

Adam McElhaney      2019-04-29      2019-04-30      2019-05-01      2019-05-02      2019-05-03      NULL      NULL
Afshin  Amiri      NULL      NULL      NULL      NULL      NULL      NULL      NULL
Alan Moran      2019-04-29      2019-04-30      2019-05-01      2019-05-02      2019-05-03      NULL      NULL
Albert Georgi      2019-04-29      2019-04-30      2019-05-01      2019-05-02      2019-04-26      NULL      NULL
Alisan Smith      NULL      NULL      NULL      NULL      NULL      NULL      NULL
Amir Kazeminia      NULL      NULL      NULL      NULL      NULL      NULL      NULL
Anton Kreisl      2019-04-29      2019-04-30      2019-05-01      2019-05-02      2019-05-03      NULL      NULL


This is my query -
declare @from as date
set @from = '04/29/2019'

SELECT e.EMPLOYEE_NAME,
       MAX(e.MONDAY) AS MONDAY,
       MAX(e.Tuesday) AS Tuesday, 
       MAX(e.Wednesday) AS Wednesday, 
       MAX(e.Thursday) AS Thursday, 
       MAX(e.Friday) AS Friday, 
       MAX(e.Saturday) AS Saturday, 
       MAX(e.Sunday) AS Sunday
  FROM (
         SELECT 
                         dbo.EMPLOYEELIST.EMPLOYEE_NAME,
                        
						 
						 CASE WHEN cast(dbo.TimeEntered.Monday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Monday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Monday  as date)
						 END AS MONDAY,
						  CASE WHEN cast(dbo.TimeEntered.Tuesday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Tuesday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Tuesday  as date)
						 END AS Tuesday,
						 	  CASE WHEN cast(dbo.TimeEntered.Wed  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Wed  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Wed  as date)
						 END AS Wednesday,
						 	 	  CASE WHEN cast(dbo.TimeEntered.Thursday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Thursday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Thursday  as date)
						 END AS Thursday,
						 	 	 	  CASE WHEN cast(dbo.TimeEntered.Friday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Friday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Friday  as date)
						 END AS Friday,
						 	 	 	 	  CASE WHEN cast(dbo.TimeEntered.Saturday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Saturday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Saturday  as date)
						 END AS Saturday,
						 	 	 	 	  CASE WHEN cast(dbo.TimeEntered.Sunday  as time) = '00:00:00.0000000' then NULL
						 WHEN cast(dbo.TimeEntered.Sunday  as time) <> '00:00:00.0000000' then cast(dbo.TimeEntered.Sunday  as date)
						 END AS Sunday
         FROM     dbo.EMPLOYEELIST LEFT Outer JOIN
                  dbo.TimeEntered ON dbo.EMPLOYEELIST.EMPLOYEE_CODE = dbo.TimeEntered.Userid
        and cast(dbo.TimeEntered.datetime  as date) between @from and dateadd(DAY, 6, @from) and Active='Y'   ) e
GROUP BY e.EMPLOYEE_NAME

Open in new window

Your view should have the possibility to select dates from the given week. If there is no such filter column then the pivoting is not necessary at all and it would  be better to query the original table.

Author

Commented:
My original table is as follows -

unique_id	Client_ID	Proj_id	Userid	Task	Work	datetime	Monday	Tuesday	Wed	Thursday	Friday	Saturday	Sunday
73085	NULL	36	1210	2		2012-10-09 06:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73086	NULL	98	1210	1		2012-10-12 01:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73087	NULL	1	1210	3	Meeting in Immokalee w CCPS	2012-10-11 04:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73088	NULL	288	1210	1		2012-10-12 05:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73089	NULL	288	1210	1		2012-10-10 02:30:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73090	NULL	288	1210	1		2012-10-09 01:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73091	NULL	230	1210	1		2012-10-08 02:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73092	NULL	230	1210	1		2012-10-09 01:30:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73093	NULL	230	1210	1		2012-10-10 01:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73094	NULL	230	1210	1		2012-10-12 02:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73095	NULL	93	1210	1		2012-10-08 01:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73096	NULL	97	1210	2		2012-10-10 00:30:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73097	NULL	97	1210	2		2012-10-08 05:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73098	NULL	36	1210	2		2012-10-10 04:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73099	NULL	36	1210	2		2012-10-11 04:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73100	NULL	208	1115	1	as-built drawings reviewed	2012-10-08 00:20:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73101	NULL	208	1115	1	as-built drawings reviewed	2012-10-09 00:20:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73102	NULL	231	1115	1	ASI drawings printed and out	2012-10-08 00:20:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73103	NULL	92	1115	2	specs	2012-10-11 00:45:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73104	NULL	207	1208	1	Meeting prep and travel Submittal Review	2012-10-11 06:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73105	NULL	207	1208	1	Meeting prep and travel Submittal Review	2012-10-10 02:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73106	NULL	207	1208	1	Meeting prep and travel Submittal Review	2012-10-08 01:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73107	NULL	101	1130	1	Meeting In Miami	2012-10-10 12:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73108	NULL	301	1130	2		2012-10-11 08:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73109	NULL	301	1130	2		2012-10-12 04:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73110	NULL	201	1603	1	T&B review	2012-10-08 02:30:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73111	NULL	201	1603	1	T&B review	2012-10-12 02:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73112	NULL	62	1603	1	T& review	2012-10-11 02:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73113	NULL	120	1603	2	Review Comments	2012-10-08 01:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73114	NULL	120	1603	2	Review Comments	2012-10-09 03:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73117	NULL	280	1133	6		2012-10-11 02:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73118	NULL	280	1133	6		2012-10-12 03:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73119	NULL	280	1133	2		2012-10-12 05:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73120	NULL	301	1402	2		2012-10-09 07:30:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73121	NULL	301	1402	2		2012-10-11 04:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73122	NULL	301	1402	2		2012-10-12 04:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73123	NULL	280	1402	2		2012-10-10 07:30:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73124	NULL	280	1402	2		2012-10-11 04:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73125	NULL	280	1402	2		2012-10-12 04:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73126	NULL	231	1140	1	RFI	2012-10-10 01:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73127	NULL	244	1140	1	RFI	2012-10-08 03:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73128	NULL	244	1140	1	RFI	2012-10-10 02:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73129	NULL	244	1140	1	RFI	2012-10-11 01:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73130	NULL	81	1140	1	Project Manager Meeting	2012-10-10 04:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73131	NULL	342	1140	2	Drawing Review	2012-10-11 01:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73132	NULL	357	1140	2	Site Visit	2012-10-10 02:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73133	NULL	148	1140	1	RFI	2012-10-11 02:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73134	NULL	148	1140	1	RFI	2012-10-09 02:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73135	NULL	148	1140	1	RFI	2012-10-08 03:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73136	NULL	306	1140	2		2012-10-11 02:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73137	NULL	1	1122	3	Margaritaville	2012-10-09 02:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73138	NULL	1	1122	3	Margaritaville	2012-10-08 01:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73139	NULL	188	1122	1	Submittals RFI	2012-10-08 02:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73140	NULL	58	1122	2	Garage Ventilation Revs	2012-10-11 01:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73141	NULL	58	1122	2	Garage Ventilation Revs	2012-10-10 03:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73142	NULL	356	1122	2		2012-10-12 06:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73143	NULL	356	1122	2		2012-10-11 04:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73144	NULL	356	1122	2		2012-10-10 03:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73145	NULL	356	1122	2		2012-10-09 02:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73146	NULL	356	1122	2		2012-10-08 02:30:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73147	NULL	345	1122	2		2012-10-12 01:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73148	NULL	345	1122	2		2012-10-11 02:30:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73149	NULL	345	1122	2		2012-10-09 01:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73150	NULL	345	1122	2		2012-10-08 01:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73151	NULL	195	1123	1		2012-10-09 02:30:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73152	NULL	43	1123	1		2012-10-08 03:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73153	NULL	83	1126	2		2012-10-15 05:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73154	NULL	357	1126	2		2012-10-16 02:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73155	NULL	372	1126	2		2012-10-16 02:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73156	NULL	125	1602	0		2012-10-18 04:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73230	NULL	371	1133	2		2012-10-16 03:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73231	NULL	380	1133	2		2012-10-22 05:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73232	NULL	371	1133	2		2012-10-22 03:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73233	NULL	280	1133	2		2012-10-17 07:30:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73234	NULL	1	1133	13	vacation	2012-10-15 08:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
109948	NULL	1	1407	11		2017-08-17 16:37:17.397	2017-08-14 04:00:00.000	2017-08-15 02:00:00.000	2017-08-16 00:00:00.000	2017-08-17 02:00:00.000	2017-08-18 00:00:00.000	2017-08-19 00:00:00.000	2017-08-20 00:00:00.000
109957	NULL	1	1141	8	Shoab computer remote migrate	2017-08-18 11:20:39.430	2017-08-14 00:00:00.000	2017-08-15 00:00:00.000	2017-08-16 00:00:00.000	2017-08-17 00:00:00.000	2017-08-18 02:00:00.000	2017-08-19 00:00:00.000	2017-08-20 00:00:00.000
109967	NULL	642	1405	11		2017-08-18 15:38:15.653	2017-08-14 00:00:00.000	2017-08-15 00:00:00.000	2017-08-16 02:00:00.000	2017-08-17 03:00:00.000	2017-08-18 01:00:00.000	2017-08-19 00:00:00.000	2017-08-20 00:00:00.000
109969	NULL	1	1901	8	Update software/ debug CAD issues	2017-08-18 17:07:27.850	2017-08-14 00:00:00.000	2017-08-15 00:00:00.000	2017-08-16 00:00:00.000	2017-08-17 05:00:00.000	2017-08-18 00:00:00.000	2017-08-19 00:00:00.000	2017-08-20 00:00:00.000
109984	NULL	1560	1604	3		2017-08-21 13:22:15.103	2017-08-21 03:00:00.000	2017-08-22 00:00:00.000	2017-08-23 00:00:00.000	2017-08-24 02:00:00.000	2017-08-25 00:00:00.000	2017-08-26 00:00:00.000	2017-08-27 00:00:00.000
109995	NULL	1552	1106	2	REV-16	2017-08-21 16:09:31.510	2017-08-21 00:30:00.000	2017-08-22 00:00:00.000	2017-08-23 00:00:00.000	2017-08-24 00:00:00.000	2017-08-25 00:00:00.000	2017-08-26 00:00:00.000	2017-08-27 00:00:00.000
110005	NULL	802	1144	2		2017-08-21 16:59:12.673	2017-08-14 00:00:00.000	2017-08-15 00:00:00.000	2017-08-16 00:00:00.000	2017-08-17 01:00:00.000	2017-08-18 00:00:00.000	2017-08-19 00:00:00.000	2017-08-20 00:00:00.000
73246	NULL	305	1115	2	specs	2012-10-15 01:30:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73247	NULL	92	1115	2	specs	2012-10-15 00:30:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73248	NULL	1	1115	9	Email schedule and coordination	2012-10-15 06:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73249	NULL	1	1115	9	Email schedule and coordination	2012-10-25 01:50:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73250	NULL	1	1115	9	Email schedule and coordination	2012-10-24 07:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73251	NULL	369	1208	2	Services Order with Joe	2012-10-23 01:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73252	NULL	369	1208	2	Services Order with Joe	2012-10-24 03:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73253	NULL	203	1208	1	Site Inspection and Travel	2012-10-25 05:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73254	NULL	201	1208	2	Wireless Handoff and closeout	2012-10-23 02:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73255	NULL	201	1208	2	Wireless Handoff and closeout	2012-10-24 01:30:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73256	NULL	201	1208	2	Wireless Handoff and closeout	2012-10-25 00:30:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73257	NULL	201	1208	2	Wireless Handoff and closeout	2012-10-26 01:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73258	NULL	231	1208	1	Data Center Fiber Pricing & Travel	2012-10-22 08:30:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73259	NULL	231	1208	1	Data Center Fiber Pricing & Travel	2012-10-23 02:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73260	NULL	231	1208	1	Data Center Fiber Pricing & Travel	2012-10-24 01:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73261	NULL	125	1602	0		2012-10-25 05:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73262	NULL	250	1203	2		2012-10-23 04:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73263	NULL	250	1203	2		2012-10-22 04:00:00.000	NULL	NULL	NULL	NULL	NULL	NULL	NULL

Open in new window



I need it to show sequencial order and if nothing has been entered for a date i need null -


So Monday =

Name   Monday   Tuesday  Wednesday  Thursday  Friday  Saturday


the datetime column should be used... I only need the date as long as the time stamp is greater than 00:00:00.000

Author

Commented:
I found this idea -
DECLARE @dt DATE = '20190506';

-- make sure Sunday is the "beginning" of the week:
SET DATEFIRST 7;

-- roll it back to Sunday
SET @dt = DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @dt), '19050101');

;WITH dt AS
(
  SELECT EMPLOYEE_NAME, dw = DATEPART(WEEKDAY, [datetime])
  FROM dbo.OCI_JSMITH_WEEKVIEW where 
  [datetime] >= @dt AND [datetime] < DATEADD(DAY, 7, @dt)
),
x AS
(
  SELECT EMPLOYEE_NAME, dw = COALESCE(dw, 8),  
    c = CONVERT(VARCHAR(11), COUNT(*))
  FROM dt 
  GROUP BY GROUPING SETS((EMPLOYEE_NAME),(EMPLOYEE_NAME,dw))
)
SELECT EMPLOYEE_NAME, 
  [Sunday] = COALESCE([1],'-'),
  [Monday] = COALESCE([2],'-'),
  [Tuesday] = COALESCE([3],'-'),
  [Wednesday] = COALESCE([4],'-'),
  [Thursday] = COALESCE([5],'-'),
  [Friday] = COALESCE([6],'-'),
  [Saturday] = COALESCE([7],'-'),
  [Total Number of Entries] = [8]
FROM x 
PIVOT (MAX(c) FOR dw IN ([1],[2],[3],[4],[5],[6],[7],[8])) AS pvt order by EMPLOYEE_NAME

Open in new window



However again it does not display all of my users if they do not exists I know its the where clause but i can not change to and it will not let me.
The above query lists employees having defined datetime. If you need all employees then add the above query to the LEFT JOIN part of your query for all users.

Author

Commented:
What?

Author

Commented:
This is the final part of the query and I will post once complete. I just can not figure out the left join of the employees on this pivot.... Any examples?

Author

Commented:
I have tried this with left join to no avail -

DECLARE @dt DATE = '20190506';

-- make sure Sunday is the "beginning" of the week:
SET DATEFIRST 7;

-- roll it back to Sunday
SET @dt = DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @dt), '19050101');

;WITH dt AS
(
  SELECT Userid, dw = DATEPART(WEEKDAY, [datetime])
  FROM dbo.OCI_JSMITH_WEEKVIEW
  WHERE [datetime] >= @dt AND [datetime] < DATEADD(DAY, 7, @dt)
),
x AS
(
  SELECT userid, dw = COALESCE(dw, 8),  
    c = CONVERT(VARCHAR(11), COUNT(*))
  FROM dt 

  GROUP BY GROUPING SETS(( userid),(userid,dw))
)
SELECT dbo.EMPLOYEELIST.EMPLOYEE_NAME ,userid,
  [Sunday] = COALESCE([1],'-'),
  [Monday] = COALESCE([2],'-'),
  [Tuesday] = COALESCE([3],'-'),
  [Wednesday] = COALESCE([4],'-'),
  [Thursday] = COALESCE([5],'-'),
  [Friday] = COALESCE([6],'-'),
  [Saturday] = COALESCE([7],'-'),
  [Total Number of Entries] = [8]
FROM x 
PIVOT (MAX(c) FOR dw IN ([1],[2],[3],[4],[5],[6],[7],[8])) AS pvt left outer join EMPLOYEELIST on Userid = EMPLOYEELIST.EMPLOYEE_CODE  
  
order by EMPLOYEE_NAME

Open in new window

Author

Commented:
I think i got it!!. Runs slow as hell

DECLARE @dt DATE = '20190506';

-- make sure Sunday is the "beginning" of the week:
SET DATEFIRST 7;

-- roll it back to Sunday
SET @dt = DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @dt), '19050101');

;WITH dt AS
(
  SELECT Userid, dw = DATEPART(WEEKDAY, [datetime])
  FROM dbo.OCI_JSMITH_WEEKVIEW
  WHERE [datetime] >= @dt AND [datetime] < DATEADD(DAY, 7, @dt)
),
x AS
(
  SELECT userid, dw = COALESCE(dw, 8),  
    c = CONVERT(VARCHAR(11), COUNT(*))
  FROM dt 

  GROUP BY GROUPING SETS(( userid),(userid,dw))
)
SELECT dbo.EMPLOYEELIST.EMPLOYEE_NAME ,userid,
  [Sunday] = COALESCE([1],'-'),
  [Monday] = COALESCE([2],'-'),
  [Tuesday] = COALESCE([3],'-'),
  [Wednesday] = COALESCE([4],'-'),
  [Thursday] = COALESCE([5],'-'),
  [Friday] = COALESCE([6],'-'),
  [Saturday] = COALESCE([7],'-'),
  [Total Number of Entries] = COALESCE([8],'NONE')
FROM EMPLOYEELIST left outer join   x 
PIVOT (MAX(c) FOR dw IN ([1],[2],[3],[4],[5],[6],[7],[8])) AS pvt  on Userid = EMPLOYEELIST.EMPLOYEE_CODE  
  
order by EMPLOYEE_NAME

Open in new window

Solution
DECLARE @dt DATE = @newdate

-- make sure Sunday is the "beginning" of the week:
SET DATEFIRST 7;

-- roll it back to Sunday
SET @dt = DATEADD(WEEK, DATEDIFF(WEEK, '19050101', @dt), '19050101');

;WITH dt AS
(
  SELECT Userid, dw = DATEPART(WEEKDAY, [datetime])
  FROM dbo.OCI_JSMITH_WEEKVIEW
  WHERE [datetime] >= @dt AND [datetime] < DATEADD(DAY, 7, @dt) 
),
x AS
(
  SELECT userid, dw = COALESCE(dw, 8),  
    c = CONVERT(VARCHAR(11), COUNT(*))
  FROM dt 

  GROUP BY GROUPING SETS(( userid),(userid,dw))
)
SELECT dbo.OCI_ACTIVE_EMPLOYEES.EMPLOYEE_NAME ,
  [Sunday] = COALESCE([1],'-'),
  [Monday] = COALESCE([2],'-'),
  [Tuesday] = COALESCE([3],'-'),
  [Wednesday] = COALESCE([4],'-'),
  [Thursday] = COALESCE([5],'-'),
  [Friday] = COALESCE([6],'-'),
  [Saturday] = COALESCE([7],'-'),
  [Total Number of Entries] = COALESCE([8],'NONE')
FROM OCI_ACTIVE_EMPLOYEES left outer join   x 
PIVOT (MAX(c) FOR dw IN ([1],[2],[3],[4],[5],[6],[7],[8])) AS pvt  on Userid = OCI_ACTIVE_EMPLOYEES.EMPLOYEE_CODE  
  
order by EMPLOYEE_NAME

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial