PL/SQL Questions

[shagymoe]

I already have a query which looks like this:
------------------------------------------------------
SET PAGESIZE 100
SET LINESIZE 200

SPOOL C:\PKCNT2ND

ACCEPT CREATION_DATE DATE PROMPT "Enter the date in MM-DD-YY format please, and then press enter " FORMAT mm-dd-yy
ACCEPT NEXT_DAY DATE PROMPT "Enter the next day's date in MM-DD-YY format please, and then press enter " FORMAT mm-dd-yy

SELECT login, COUNT(DISTINCT sequence)
FROM history
WHERE creation_time BETWEEN to_date('16:00 &&creation_date', 'HH24:MI MM-DD-YY')
AND to_date('03:30 &&next_day', 'HH24:MI MM-DD-YY')
GROUP BY login
ORDER BY COUNT(DISTINCT sequence) DESC
/
QUIT
/

---------------------------------------------------------------------
This is invoked by an NT script and prints out the report automatically and quits the sqlplus application. Basically, it gives a history of scanning transactions for the 2nd shift which is from 16:00 to 03:30. What I don't like about this is that they have to put in two dates because 2nd shift spans two different days. For the life of me, I cannot figure out how to create a variable which just adds one day to the day they input (creation_time) and insert this into the query. I had thought of something like this, but it always fails, no matter how I change it.
--------------------------------------------------------------------
ACCEPT CREATION_DATE DATE PROMPT "Enter the date in MM-DD-YY format please, and then press enter " FORMAT mm-dd-yy

DECLARE
v_next_day DATE := '&&creation_date + 1';

SELECT login, COUNT(DISTINCT sequence) count
FROM history
WHERE creation_time BETWEEN to_date('16:00 &&creation_date', 'HH24:MI MM-DD-YY')
AND to_date('03:30 v_next_day', 'HH24:MI MM-DD-YY')
GROUP BY login
ORDER BY COUNT(DISTINCT sequence) DESC;
count_record count_cursor%ROWTYPE;

BEGIN
OPEN count_cursor;
LOOP
FETCH count_cursor INTO count_record;
EXIT WHEN count_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('Sequencer ' || count_record.login || 'picked ' || count_record.count || 'sequences today')
END LOOP;
END;
/
----------------------------------------------------------------------
I'm sure there is more than one reason why this won't work, but I can't figure out what the MAIN problem is...how do I use a variable to add one day to what they input and use it in the query??

Thanks for the help.

[jrpm]

Going after your original query

AND to_date('03:30 &&next_day', 'HH24:MI MM-DD-YY')
goes to

AND to_date('03:30 '||to_char(to_date(&&creation_date)+1),'HH24:MI MM-DD-YY')

at least something like this should work.

[jmodic]

In your original script, change the query to:

SELECT login, COUNT(DISTINCT sequence)
FROM history
WHERE creation_time BETWEEN to_date('16:00 &&creation_date', 'HH24:MI MM-DD-YY')
AND to_date('03:30 &&creation_day', 'HH24:MI MM-DD-YY')+1
GROUP BY login
ORDER BY COUNT(DISTINCT sequence) DESC;

That way you don't need the second date entered.

In your second script, you should change the declaration part to:

DECLARE
v_next_day DATE := TO_DATE('&&creation_date,'MM-DD-YY') + 1;

[shagymoe]

Thanks guys...why is it that it always seems so darn simple once you know the answer?