Quote:
|
... between a given date range.
|
How would you give the "given date range"?
(1) If you gave it by the list of dates, like
CREATE TABLE date_range
( given_date DATE NOT NULL UNIQUE );
Try this...
Example 1:
Code:
SELECT r.given_date
, e.employee_id
, e.employee_name
FROM date_range r
CROSS JOIN
employee_tbl e
LEFT OUTER JOIN
time_tbl t
ON t.time_date = r.given_date
AND t.employee_id = e.employee_id
AND t.time_start IS NOT NULL
AND t.time_end IS NOT NULL
AND t.time_details IS NOT NULL
WHERE t.employee_id IS NULL
ORDER BY
r.given_date
, e.employee_id
;
(2) If you gave it by a pair of(begin_date , end_date),
make list of given_date like in (1), then use Example 1.
It should be different by RDBMS products to make list of given_date from a pair of(begin_date , end_date).
Sorry, I don't know so much about MS SQL Server.
Here is an example on DB2.
Example 2:
Note: Although a column "k" seems to be redundant(and not necessary),
it was used to prevent a warning message, like...
SQL0347W The recursive common table expression "DB2ADMIN.DATE_RANGE" may
contain an infinite loop. SQLSTATE=01605
Code:
------------------------------ Commands Entered ------------------------------
WITH
duration(begin_date , end_date) AS (
VALUES
( DATE('2012-01-29') , DATE('2012-02-04') )
)
, date_range(given_date , end_date , k) AS (
SELECT begin_date
, end_date
, 1
FROM duration
UNION ALL
SELECT given_date + 1 DAY
, end_date
, k + 1
FROM date_range
WHERE k < 10000
AND given_date < end_date
)
SELECT given_date FROM date_range
;
------------------------------------------------------------------------------
GIVEN_DATE
----------
2012-01-29
2012-01-30
2012-01-31
2012-02-01
2012-02-02
2012-02-03
2012-02-04
7 record(s) selected.
dBforums
>
Get Time defaulters list
02-15-12, 01:26
Linear Mode
