0

I am new in SQL and I want to add a row in query result between date range if any date is missing between the date range.

Here is my query

SELECT CHECKINOUT.USERID, USERINFO.USERID,
        CAST(CHECKTIME AS DATE) DATE,
        MAX(CAST(CHECKTIME AS TIME)) as MAX_TIME,
        MIN(CAST(CHECKTIME AS TIME)) as MIN_TIME
        FROM CHECKINOUT
        LEFT JOIN USERINFO
        ON CHECKINOUT.USERID = USERINFO.USERID
        WHERE CHECKTIME BETWEEN '2020-02-01' AND '2020-02-10'
        GROUP BY CHECKINOUT.USERID,CAST(CHECKTIME AS DATE), USERINFO.USERID, USERINFO.NAME
         ORDER by CAST(CHECKTIME AS DATE), USERINFO.NAME ASC

Output of this query

USERID  USERID  DATE    MAX_TIME    MIN_TIME
59  59  2/3/2020    4:11:48 PM  8:47:02 AM
59  59  2/4/2020    6:22:38 PM  8:45:50 AM
59  59  2/6/2020    2:33:43 PM  1:57:55 PM
59  59  2/7/2020    4:29:40 PM  8:39:38 AM
60  60  2/3/2020    4:11:48 PM  8:47:02 AM
60  60  2/4/2020    6:22:38 PM  8:45:50 AM
60  60  2/6/2020    2:33:43 PM  1:57:55 PM
60  60  2/7/2020    4:29:40 PM  8:39:38 AM
61  61  2/3/2020    4:11:48 PM  8:47:02 AM
61  61  2/4/2020    6:22:38 PM  8:45:50 AM
61  61  2/6/2020    2:33:43 PM  1:57:55 PM
61  61  2/7/2020    4:29:40 PM  8:39:38 AM

And My expected Output

    USERID  USERID  DATE    MAX_TIME    MIN_TIME
59  59  2/1/2020    NULL    NULL
59  59  2/2/2020    NULL    NULL
59  59  2/3/2020    4:11:48 PM  8:47:02 AM
59  59  2/4/2020    6:22:38 PM  8:45:50 AM
59  59  2/6/2020    2:33:43 PM  1:57:55 PM
59  59  2/7/2020    4:29:40 PM  8:39:38 AM
59  59  2/8/2020    NULL    NULL
59  59  2/9/2020    NULL    NULL
59  59  2/10/2020   NULL    NULL
60  60  2/1/2020    NULL    NULL
60  60  2/2/2020    NULL    NULL
60  60  2/3/2020    4:11:48 PM  8:47:02 AM
60  60  2/4/2020    6:22:38 PM  8:45:50 AM
60  60  2/6/2020    2:33:43 PM  1:57:55 PM
60  60  2/7/2020    4:29:40 PM  8:39:38 AM
60  60  2/8/2020    NULL    NULL
60  60  2/9/2020    NULL    NULL
60  60  2/10/2020   NULL    NULL
61  61  2/1/2020    NULL    NULL
61  61  2/2/2020    NULL    NULL
61  61  2/3/2020    4:11:48 PM  8:47:02 AM
61  61  2/4/2020    6:22:38 PM  8:45:50 AM
61  61  2/6/2020    2:33:43 PM  1:57:55 PM
61  61  2/7/2020    4:29:40 PM  8:39:38 AM
61  61  2/8/2020    NULL    NULL
61  61  2/9/2020    NULL    NULL
61  61  2/10/2020   NULL    NULL

Can anyone please help me?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
sunny
  • 1,511
  • 5
  • 20
  • 57

2 Answers2

0

Mysql cannot itself return rows for non existing data, you have to workaround it yourself making it creating those empty rows.

I hade a similar question, you can refer to this answer to achieve what you are looking for.

TLDR

You basically use a table with enough rows to provide the quantity of empty rows you need (the answer i linked above use information_schema.columns) combined with a variable to generate rows with an incrementing value (m,...,n), then you can use those rows and those values to generate the missing data you need, like dates for your use case, and in the end join those default-value rows with your real-data rows.

fudo
  • 2,254
  • 4
  • 22
  • 44
0

You need to generate the list of dates. You can do this in multiple ways:

  • Explicitly.
  • Using a numbers/tally/calendar table.
  • Using a recursive CTE.

Then use a CROSS JOIN to generate the rows that you want. And finally a LEFT JOIN to bring in the data you have already calculated.

Here is an example:

WITH uc AS (
      SELECT c.USERID, CAST(CHECKTIME AS DATE) as DATE,
             MAX(CAST(CHECKTIME AS TIME)) as MAX_TIME,
             MIN(CAST(CHECKTIME AS TIME)) as MIN_TIME
      FROM CHECKINOUT c LEFT JOIN
           USERINFO u 
           ON i.USERID = u.USERID
      WHERE c.CHECKTIME BETWEEN '2020-02-01' AND '2020-02-10'
      GROUP BY c.USERID, CAST(CHECKTIME AS DATE), u.USERID, u.NAME
     )
SELECT u.USERID, d.dte, uc.min_time, uc.max_time
FROM (SELECT DISTINCT USERID FROM uc) u CROSS JOIN
     (SELECT CAST('2020-02-01' as DATE) as dte UNION ALL
      SELECT CAST('2020-02-02' as DATE) as dte UNION ALL
      . . .
      SELECT CAST('2020-02-10' as DATE) as dte UNION ALL
     ) d LEFT JOIN
     cu
     ON cu.USERID = u.USERID AND cu.DATE = d.dte
ORDER by d.dte, NAME ASC
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Can you please write complete query for me. It's difficult for me – sunny Feb 27 '20 at 12:06
  • @sunny . . . This answer has the complete query. – Gordon Linoff Feb 27 '20 at 12:06
  • Actually it showing me error `Msg 195, Level 15, State 10, Line 13 'DATE' is not a recognized built-in function name. Msg 195, Level 15, State 10, Line 14 'DATE' is not a recognized built-in function name. Msg 195, Level 15, State 10, Line 15 'DATE' is not a recognized built-in function name.` – sunny Feb 27 '20 at 12:09
  • @sunny . . . Use whatever logic is appropriate for your database. You haven't specified what database you are using. – Gordon Linoff Feb 27 '20 at 12:14
  • I am using MS SQL – sunny Feb 27 '20 at 12:15