SQL Server : Insert blank line in query

Question

This may be so last year but I'm using SQL Server 2005

stmpdate      intime
----------------------
2014-10-08   08:04:43
2014-10-09   07:57:13
2014-10-10   07:57:14
2014-10-16   07:79:56
2014-10-17   07:45:56

I have this table. It keeps check-in time of the employee, but this employee didn't check-in everyday in the month. So what I want it to be is something like this

    stmpdate    intime
1   2014-10-01  
2   2014-10-02  
3   2014-10-03  
4   2014-10-04  
5   2014-10-05  
6   2014-10-06  
7   2014-10-07  
8   2014-10-08  08:04:43
9   2014-10-09  07:57:13
10  2014-10-10  07:57:14
11  2014-10-11  
12  2014-10-12  
13  2014-10-13  
14  2014-10-14  
15  2014-10-15  
16  2014-10-16  07:59:56
17  2014-10-17  07:45:56
18  2014-10-18  
19  2014-10-19  
20  2014-10-20  
21  2014-10-21  
22  2014-10-22  
23  2014-10-23  
24  2014-10-24  
25  2014-10-25  
26  2014-10-26  
27  2014-10-27  
28  2014-10-28  
29  2014-10-29  
30  2014-10-30  
31  2014-10-31  

I tried to make a temp table which contains every date in the month, and then left join it with the first table I mentioned, but it seemed to not work.

declare @datetemp table (
stmpdate varchar(10)
);
insert into @datetemp 
SELECT '2014-10-01'
UNION ALL
SELECT '2014-10-02'
UNION ALL
SELECT '2014-10-03'
....

and

SELECT dtt.stmpdate, intime
FROM @datetemp dtt left join v_dayTimesheet
on dtt.stmpdate=v_dayTimesheet.stmpdate
WHERE (emp_no = '001234567')

here is the result of query above

stmpdate    intime
2014-10-08  08:04:43
2014-10-09  07:57:13
2014-10-10  07:57:14
2014-10-16  07:59:56
2014-10-17  07:45:56

and here is the result of select * from @datetemp

2014-10-01
2014-10-02
2014-10-03
2014-10-04
2014-10-05
2014-10-06
2014-10-07
2014-10-08
2014-10-09
2014-10-10
2014-10-11
2014-10-12
2014-10-13
2014-10-14
2014-10-15
2014-10-16
2014-10-17
2014-10-18
2014-10-19
2014-10-20
2014-10-21
2014-10-22
2014-10-23
2014-10-24
2014-10-25
2014-10-26
2014-10-27
2014-10-28
2014-10-29
2014-10-30
2014-10-31

Answer 1

SQL Query: SQLFIDDLEExample

SELECT t2.dt,
       isnull(t1.intime, '') intime
FROM 
(
    SELECT DATEADD(day,number,'2014-10-01') dt
    FROM master..spt_values 
    WHERE Type = 'P' 
    AND DATEADD(day,number,'2014-10-01') >= '2014-10-01'
    AND DATEADD(day,number,'2014-10-01') < '2014-11-01'
 ) t2
LEFT JOIN Table1 t1
ON t1.stmpdate = t2.dt

Result:

|                             DT |   INTIME |
|--------------------------------|----------|
| October, 01 2014 00:00:00+0000 |          |
| October, 02 2014 00:00:00+0000 |          |
| October, 03 2014 00:00:00+0000 |          |
| October, 04 2014 00:00:00+0000 |          |
| October, 05 2014 00:00:00+0000 |          |
| October, 06 2014 00:00:00+0000 |          |
| October, 07 2014 00:00:00+0000 |          |
| October, 08 2014 00:00:00+0000 | 08:04:43 |
| October, 09 2014 00:00:00+0000 | 07:57:13 |
| October, 10 2014 00:00:00+0000 | 07:57:14 |
| October, 11 2014 00:00:00+0000 |          |
| October, 12 2014 00:00:00+0000 |          |
| October, 13 2014 00:00:00+0000 |          |
| October, 14 2014 00:00:00+0000 |          |
| October, 15 2014 00:00:00+0000 |          |
| October, 16 2014 00:00:00+0000 | 07:79:56 |
| October, 17 2014 00:00:00+0000 | 07:45:56 |
| October, 18 2014 00:00:00+0000 |          |
| October, 19 2014 00:00:00+0000 |          |
| October, 20 2014 00:00:00+0000 |          |
| October, 21 2014 00:00:00+0000 |          |
| October, 22 2014 00:00:00+0000 |          |
| October, 23 2014 00:00:00+0000 |          |
| October, 24 2014 00:00:00+0000 |          |
| October, 25 2014 00:00:00+0000 |          |
| October, 26 2014 00:00:00+0000 |          |
| October, 27 2014 00:00:00+0000 |          |
| October, 28 2014 00:00:00+0000 |          |
| October, 29 2014 00:00:00+0000 |          |
| October, 30 2014 00:00:00+0000 |          |
| October, 31 2014 00:00:00+0000 |          |

Answer 2

I got my answer!!

SELECT dtt.stmpdate, intime
FROM @datetemp dtt left join 
(
    SELECT stmpdate, intime
    FROM v_dayTimesheet 
    WHERE (emp_no = '001234567') 
) as vdayTimesheet
 on sparedate.stmpdate=vdayTimesheet.stampdate
ORDER BY stmpdate

this is what I want, thanks everyone

Answer 3

you're filtering for only where emp_no has a value. if they didn't check in, it won't return on that row because you just have date info and no employee number. so you have to allow for equal or null.

SELECT  dtt.stmpdate, intime

FROM    @datetemp dtt 

        left outer join v_dayTimesheet
           on dtt.stmpdate=v_dayTimesheet.stmpdate

WHERE   emp_no = '001234567' or emp_no is null

also, for your dates... check this out: http://www.sqlservercurry.com/2010/03/generate-start-and-end-date-range-using.html

DECLARE
@StartDate datetime = '2010-01-01',
@EndDate   datetime = '2010-03-01'

;WITH datetemp as
(
       SELECT @StartDate as stmpdate
       UNION ALL
       SELECT DATEADD(day, 1, stmpdate)
       FROM   datetemp 
       WHERE  DATEADD(day, 1, stmpdate) <= @EndDate    
)

SELECT stmpdate   
FROM   datetemp; 

you would then select from datetemp as a normal table. beware, though, a common table expression can only be used once and immediately following the with statement.

---

just trust me on this one... run this query and see how your blank lines occur:

SELECT  dtt.stmpdate, intime, emp_no 

FROM    @datetemp dtt 

        left outer join v_dayTimesheet
           on dtt.stmpdate=v_dayTimesheet.stmpdate

WHERE   emp_no = '001234567' or emp_no is null

all these lines will return with emp_no = 001234567

stmpdate    intime
2014-10-08  08:04:43
2014-10-09  07:57:13
2014-10-10  07:57:14
2014-10-16  07:59:56
2014-10-17  07:45:56

and all your blank lines will have null as emp_no.