[Solved] How to create a query for this scenario?

by


I think you can try this to obtain a list for all the users (left join with table user in this case is not enough – see my sample data below):

SELECT C.USERNAME, A.CALENDARDATE
      , CASE WHEN B.USERNAME IS NULL THEN 'Absent' ELSE 'Present' END AS STATUS
      , CAST(B.WDT AS DATE) AS WDT, CAST(B.WDT AS TIME) AS TIME
FROM CALENDAR A
CROSS JOIN (SELECT DISTINCT USERNAME FROM US) C
LEFT JOIN US B ON A.CALENDARDATE = CAST(WDT AS DATE) AND C.USERNAME=B.USERNAME;

Sample data:

SET DATEFORMAT ymd

CREATE TABLE CALENDAR (CALENDARDATE DATE);
INSERT INTO CALENDAR VALUES ('2017-01-01');
INSERT INTO CALENDAR VALUES ('2017-01-02');
INSERT INTO CALENDAR VALUES ('2017-01-03');
INSERT INTO CALENDAR VALUES ('2017-01-04');

CREATE TABLE US (USERNAME VARCHAR(10), WDT DATETIME);
INSERT INTO US VALUES ('User1', '2017-01-01 08:00');
INSERT INTO US VALUES ('User1', '2017-01-02 08:05');
INSERT INTO US VALUES ('User2', '2017-01-04 08:10');

Output:

   +----------+--------------+---------+------------+------------------+
| USERNAME | CALENDARDATE | STATUS  |    WDT     |       TIME       |
+----------+--------------+---------+------------+------------------+
| User1    | 2017-01-01   | Present | 2017-01-01 | 08:00:00.0000000 |
| User1    | 2017-01-02   | Present | 2017-01-02 | 08:05:00.0000000 |
| User1    | 2017-01-03   | Absent  | NULL       | NULL             |
| User1    | 2017-01-04   | Absent  | NULL       | NULL             |
| User2    | 2017-01-01   | Absent  | NULL       | NULL             |
| User2    | 2017-01-02   | Absent  | NULL       | NULL             |
| User2    | 2017-01-03   | Absent  | NULL       | NULL             |
| User2    | 2017-01-04   | Present | 2017-01-04 | 08:10:00.0000000 |
+----------+--------------+---------+------------+------------------+

0

solved How to create a query for this scenario?