I have a table like this
SELECT V_EP_ID,V_START_DATE,V_END_DATE
FROM PGATEINTRA.dbo.EMPLOYEE_VACATIONS
ORDER BY V_EP_ID ASC
output of the above query is
V_EP_ID V_START_DATE V_END_DATE
------------------------------------------------------
28 2010-06-21 00:00:00 2010-06-21 00:00:00
103 2011-06-27 00:00:00 2011-06-27 00:00:00
103 2011-08-12 00:00:00 2011-08-12 00:00:00
104 2011-03-24 00:00:00 2011-03-24 00:00:00
104 2011-05-06 00:00:00 2011-05-06 00:00:00
104 2010-06-07 00:00:00 2010-06-08 00:00:00
104 2011-02-23 00:00:00 2011-02-25 00:00:00
...
I wrote the statement like this
SELECT * FROM PGATEINTRA.dbo.EMPLOYEE_VACATIONS
WHERE V_START_DATE >='2010-06-21 00:00:00'
AND V_END_DATE <= '2010-06-21 00:00:00'
by passing v_start_date and v_end_date from PGATEINTRA.dbo.EMPLOYEE_VACATIONS
and I am getting the output as
V_ID V_TYPE V_EP_ID V_REASON V_START_DATE V_END_DATE
----------------------------------------------------------------------------
3 c 28 Took leave 2010-06-21 00:00:00 2010-06-21 00:00:00
7 c 109 Took leave 2010-06-21 00:00:00 2010-06-21 00:00:00
I want output to get all the V_EP_ID FROM PGATEINTRA.dbo.EMPLOYEE_VACATIONS between V_START_DATE and V_END_DATE dynamically.
I don't know where cursors come into play here, but I think what you need is the DATEDIFF
function:
WHERE DATEDIFF(DAY, V_START_DATE, V_END_DATE) = 0