locked
Insert Calender Date into Time Attend Table RRS feed

  • 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

Answers

  • 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 Anonymous 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,

    Please try the following statement:

    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 Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 18, 2012 9:39 PM