From: michael@gurfinkel.us   
      
   Solved - I should have check for the date within ON clause, and not in the   
   WHERE   
   correct version looks like this :   
      
   select ...   
   left join	@SCHEDULED S	on E.EMPL_ID=S.EMPL_ID   
        and S.WORK_DATE = '20150107';   
      
      
   On Thursday, February 26, 2015 at 3:51:12 PM UTC-8, M.G. wrote:   
   > I need to show list of employees along with their scheduled work hours for a   
   given date. If employee was not scheduled to work, then the hours should be   
   empty on his/her line.   
   >   
   > Here is my setup (SQL 2008)   
   > declare @EMPLOYEES	table (   
   > 	EMPL_ID		int		NOT NULL   
   > ,	EMPL_NAME	varchar(64)   
   > )   
   > insert into @EMPLOYEES(EMPL_ID,EMPL_NAME) values   
   > ( 100,	'JOHN'),   
   > ( 200,	'BOB'),   
   > ( 300,	'MARY');   
   >   
   >   
   > declare @SCHEDULED	table (   
   > 	EMPL_ID		int		NOT NULL   
   > ,	WORK_DATE	date	NOT NULL   
   > ,	WORK_HOURS	int		NOT NULL   
   > )   
   > insert into @SCHEDULED(EMPL_ID, WORK_DATE, WORK_HOURS) values   
   > ( 100,	'20150106',	8),   
   > ( 100,	'20150107',	8),   
   > ( 100,	'20150108',	8),   
   > ( 200,	'20150107',	6);   
   >   
   > So, I expect to get three records in my report regardless of the day   
   specified.   
   >   
   >   
   >   
   > This is my SQL statement:   
   >   
   > select E.EMPL_ID, E.EMPL_NAME, S.WORK_DATE, S.WORK_HOURS   
   > from	   
   > 		@EMPLOYEES E   
   > left join	@SCHEDULED S	on E.EMPL_ID=S.EMPL_ID   
   >   
   > where		S.WORK_DATE = '20150107' or S.EMPL_ID IS NULL;   
   >   
   > It results in :   
   >   
   > EMPL_ID	EMPL_NAME WORK_DATE WORK_HOURS   
   > ======= ======= ========== =======   
   > 100	JOHN	2015-01-07	8   
   > 200	BOB	2015-01-07	6   
   > 300	MARY	NULL	NULL   
   >   
   > Which is correct.   
   >   
   > If I use 20150206, my result is:   
   >   
   > 100	JOHN	2015-01-06	8   
   > 300	MARY	NULL	NULL   
   >   
   > So, I am missing record for BOB, as I would expect to se BOB with NULLS in   
   the report   
   >   
   > If I use date of 20150131 I expect to get three lines with all hours being   
   NULLS, but my result is only one record:   
   >   
   > 300	MARY	NULL	NULL   
   >   
   > Please help me with this situation, any ideas would be appreciated.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)