CODEWITHSUNDEEP
sqlsql-server-2005
billy the kids
billy the kids

Reputation: 25

Select all table in 2 table with null value

I Have 2 Table in SQL SERVER 2005:

first table :

Tanggal
2015/01/01
2015/01/02
2015/01/03
2015/01/04
2015/01/05
2015/01/06

Second Table :

Tanggal               Jam_Masuk          Jam_Pulang.    Id_user
2015/01/01        08:00:00                17:00:00         00600320 
2015/01/03        08:00:00                17:00:00         00600320 
2015/01/05        08:00:00                17:00:00         00600320 
2015/01/06        08:00:00                17:00:00         00600320

I want select like this :

Tanggal               Jam_Masuk          Jam_Pulang           Total              Status      Id_user
2015/01/01        08:00:00                17:00:00               09:00:00        OK          00600320   
2015/01/02        --:--:--                --:--:--               --:--:--        ALPHA/IZIN  00600320 
2015/01/03        08:00:00                17:00:00               09:00:00        OK          00600320   
2015/01/04        --:--:--                --:-- :--              --:--:--        ALPHA/IZIN  00600320 
2015/01/05        08:00:00                17:00:00               09:00:00        OK          00600320   
2015/01/06        08:00:00                17:00:00               09:00:00        OK          00600320

Note : Field Total (hh:mm:ss)= Jam_Pulang - Jam_Masuk

Thanks Before

Upvotes: 1

Views: 25

Answers (1)

Matt
Matt

Reputation: 15061

LEFT JOIN the two tables, Use a CASE statement for the Status and use the DATEDIFF function for the total. Also use CONVERT for the datestamp Tanggal with style 111 for your expected output of YYYY/MM/DD.

SELECT CONVERT(VARCHAR(50),a.Tanggal,111) AS Tanggal, 
b.Jam_Masuk, 
b.Jam_Pulang, 
DATEDIFF(hour, Jam_Masuk, Jam_Pulang) AS Total,
b.Id_user, 
CASE WHEN b.Id_user IS NOT NULL THEN 'OK' ELSE NULL END AS Status
FROM tab1 a
LEFT JOIN tab12 b ON a.Tanggal = b.Tanggal

OUTPUT

Tanggal     Jam_Masuk   Jam_Pulang  Total   Id_user Status
2015/01/01  08:00:00    17:00:00    9       600320  OK
2015/01/02  (null)      (null)      (null)  (null)  (null)
2015/01/03  08:00:00    17:00:00    9       600320  OK
2015/01/04  (null)      (null)      (null)  (null)  (null)
2015/01/05  08:00:00    17:00:00    9       600320  OK
2015/01/06  08:00:00    17:00:00    9       600320  OK

Upvotes: 1

Related Questions