# Insert Calender Date into Time Attend Table

• ### Question

• User1043260425 posted

I got two table one contain a calender date table another one is contain staff time attend information which only have the data when staff come to office only.

My question is how could i show both information of both information just like below.

Or some one can advise me need to use what method to solve this ?

Time Attend Table

 Date Name Time IN Time Out 2012-10-01 LEE 08:30 17:30 2012-10-02 LEE 08:30 18:30 2012-10-03 LEE 08:30 17:30 2012-10-04 LEE 08:30 18:30 2012-10-05 LEE 08:30 17:30 2012-10-08 LEE 08:30 18:30

Calender Table

 Date 2012-10-01 2012-10-02 2012-10-03 2012-10-04 2012-10-05 2012-10-06 2012-10-07 2012-10-08 2012-10-09 2012-10-10

Result :

 Date Name Time IN Time Out 2012-10-01 LEE 08:30 17:30 2012-10-02 LEE 08:30 18:30 2012-10-03 LEE 08:30 17:30 2012-10-04 LEE 08:30 18:30 2012-10-05 LEE 08:30 17:30 2012-10-06 LEE (--:--) (--:--) 2012-10-07 LEE (--:--) (--:--) 2012-10-08 LEE 08:30 18:30
Wednesday, October 17, 2012 12:22 AM

• User260886948 posted

Hi gingank,

Yes, we can use SQL statement to do the query about showing all name is LEE to indicate which date he didnt come.

Please refer to the following statement:

```  SELECT * INTO [aa]from TIMEATTEND where NAME='LEE';      --aa is the new table for LEE

SELECT CALENDER.DATE, aa.NAME, ISNULL(aa.TIMEIN, '(--:--)') as Time_IN, ISNULL(aa.TIMEOUT, '(--:--)') as Time_OUT
FROM CALENDER LEFT OUTER JOIN aa ON CALENDER.DATE = aa.DATE```

Regards,
Amy peng

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Thursday, October 18, 2012 9:39 PM

### All replies

• User-1401636618 posted

Here is the solution:

```SELECT CALENDER.DATE, NAME, ISNULL(TIMEIN, '(--:--)'), ISNULL(TIMEOUT, '(--:--)')
FROM CALENDER LEFT OUTER JOIN TIMEATTEND ON CALENDER.DATE = TIMEATTEND.DATE ```

Wednesday, October 17, 2012 1:17 AM
• User1043260425 posted

I try this query but is not the things i want, it didnt return the Name and aslo the time attend table got any name too...

Please kindly help me on this

Result using this query

`SELECT CALENDER.DATE, NAME, ISNULL(TIMEIN, '(--:--)'), ISNULL(TIMEOUT, '(--:--)')        FROM CALENDER LEFT OUTER JOIN TIMEATTEND ON CALENDER.DATE = TIMEATTEND.DATE`
 DATE NAME (No column name) (No column name) 2012-10-01 LEE 08:30 17:30 2012-10-02 LEE 08:30 18:30 2012-10-03 LEE 08:30 17:30 2012-10-04 LEE 08:30 18:30 2012-10-04 LEE 08:30 18:30 2012-10-05 LEE 08:30 19:30 2012-10-06 NULL (--:--) (--:--) 2012-10-07 NULL (--:--) (--:--) 2012-10-08 LEE 08:30 18:30 2012-10-09 NULL (--:--) (--:--) 2012-10-10 NULL (--:--) (--:--)
Wednesday, October 17, 2012 2:52 AM
• User1043260425 posted

I try this query but is not the things i want, it didnt return the Name and aslo the time attend table got others

name too...

Please kindly help me on this

Result using this query

`SELECT CALENDER.DATE, NAME, ISNULL(TIMEIN, '(--:--)'), ISNULL(TIMEOUT, '(--:--)')        FROM CALENDER LEFT OUTER JOIN TIMEATTEND ON CALENDER.DATE = TIMEATTEND.DATE`
 DATE NAME (No column name) (No column name) 2012-10-01 LEE 08:30 17:30 2012-10-02 LEE 08:30 18:30 2012-10-03 LEE 08:30 17:30 2012-10-04 LEE 08:30 18:30 2012-10-04 LEE 08:30 18:30 2012-10-05 LEE 08:30 19:30 2012-10-06 NULL (--:--) (--:--) 2012-10-07 NULL (--:--) (--:--) 2012-10-08 LEE 08:30 18:30 2012-10-09 NULL (--:--) (--:--) 2012-10-10 NULL (--:--) (--:--)
Wednesday, October 17, 2012 2:52 AM
• User-2082239438 posted

I got two table one contain a calender date table another one is contain staff time attend information which only have the data when staff come to office only.

My question is how could i show both information of both information just like below.

Or some one can advise me need to use what method to solve this ?

Time Attend Table

 Date Name Time IN Time Out 2012-10-01 LEE 08:30 17:30 2012-10-02 LEE 08:30 18:30 2012-10-03 LEE 08:30 17:30 2012-10-04 LEE 08:30 18:30 2012-10-05 LEE 08:30 17:30 2012-10-08 LEE 08:30 18:30

Calender Table

 Date 2012-10-01 2012-10-02 2012-10-03 2012-10-04 2012-10-05 2012-10-06 2012-10-07 2012-10-08 2012-10-09 2012-10-10

Result :

 Date Name Time IN Time Out 2012-10-01 LEE 08:30 17:30 2012-10-02 LEE 08:30 18:30 2012-10-03 LEE 08:30 17:30 2012-10-04 LEE 08:30 18:30 2012-10-05 LEE 08:30 17:30 2012-10-06 LEE (--:--) (--:--) 2012-10-07 LEE (--:--) (--:--) 2012-10-08 LEE 08:30 18:30

First of all tell, how you can determine that calender table record of 9 & 10 will not come into your result set.?

Provide details how you can fetch your result set? what is your condition to check in calender control?

all above query posted by other member are correct one. You just need to add your condition.

Wednesday, October 17, 2012 3:24 AM
• User-1597870976 posted
`SELECT CALENDER.DATE, NAME, ISNULL(TIMEIN, '(--:--)'), ISNULL(TIMEOUT, '(--:--)')        FROM CALENDER LEFT OUTER JOIN TIMEATTEND ON CALENDER.DATE = TIMEATTEND.DATE`
Wednesday, October 17, 2012 3:29 AM
• User260886948 posted

Hi gingank,

```SELECT CALENDER.DATE, NAME, ISNULL(TIMEIN, '(--:--)') as Time_IN, ISNULL(TIMEOUT, '(--:--)') as Time_OUT
FROM CALENDER LEFT OUTER JOIN TIMEATTEND ON CALENDER.DATE = TIMEATTEND.DATE```

Here is the result which I uesd this select statement:

Regards,
Amy Peng

Wednesday, October 17, 2012 11:08 PM
• User1043260425 posted

But the name also show Null. If i wan to show all name is LEE to indicate which date he didnt come. Can it be done or not using the sql method ?

Thursday, October 18, 2012 5:55 AM
• User260886948 posted

Hi gingank,

Yes, we can use SQL statement to do the query about showing all name is LEE to indicate which date he didnt come.

Please refer to the following statement:

```  SELECT * INTO [aa]from TIMEATTEND where NAME='LEE';      --aa is the new table for LEE

SELECT CALENDER.DATE, aa.NAME, ISNULL(aa.TIMEIN, '(--:--)') as Time_IN, ISNULL(aa.TIMEOUT, '(--:--)') as Time_OUT
FROM CALENDER LEFT OUTER JOIN aa ON CALENDER.DATE = aa.DATE```

Regards,
Amy peng

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Thursday, October 18, 2012 9:39 PM