CREATE TABLE LOOP of dates

[missmis44]

I am trying to create a table that lists the date from the first date of a record in the system through the current date, a count at 8:00 AM Each day of how many inmates we have in the jail and a full day count of inmates in the jail. Once this table is created through today. I want to run a script daily to add the counts from the previous day at 8:00 am and for the full day. I cannot get this to work. Any help would be appreciated.
Eg.
DATE AM_COUNT FULL_DAY_COUNT
09/07/2001 08:00:00 201 215
09/08/2001 08:00:00 199 203
09/09/2001 08:00:00 202 205

CREATE TABLE CJI_DAILY_POPULATION(
POPULATION_DATE DATE NOT NULL,
AM_COUNT NUMBER (10),
FULL_DAY_COUNT NUMBER (10)0;
COMMIT;


DECLARE
POP_DATE DATE := TO_DATE('09/07/2001 08:00:00', 'MM/DD/YYYY HH24:MI:SS');
AM_CT NUMBER (10)
FULL_CT NUMBER (10)

BEGIN
WHILE POPULATION_DATE < SYSTIMESTAMP
LOOP
AM_CT = (SELECT COUNT(BOOK_ID)
FROM CJI_BOOK_RELEASE
WHERE TO_DATE(POP_DATE, 'MM/DD/YYYY HH24:MI:SS')
BETWEEN BOOKDATE AND RELEASEDATE
FULL_CT = (SELECT COUNT(BOOK_ID)
FROM CJI_BOOK_RELEASE
WHERE TO_DATE(POP_DATE, 'MM/DD/YYYY')

INSERT INTO CJI_DAILY_POPULALATION VALUES (POP_DATE, AM_CT, FULL_CT)

POP_DATE = POP_DATE + 1;
END LOOP;
END;

 

[spesknight]

When you do
FULL_CT = (SELECT COUNT(BOOK_ID)
FROM CJI_BOOK_RELEASE
WHERE TO_DATE(POP_DATE, 'MM/DD/YYYY')
full_ct will have the count at 00:00 AM. I think you need the count at 12:00 PM. Try this
FULL_CT = (SELECT COUNT(BOOK_ID)
FROM CJI_BOOK_RELEASE
WHERE TO_DATE(POP_DATE+1, 'MM/DD/YYYY')

 

[missmis44]

For the FULL_CT, I want to return how many people were in custody at any point in the day:
FULL_CT = SELECT COUNT(BOOK_ID)
FROM CJI_BOOK_RELEASE
WHERE TO_DATE(POP_DATE, 'MM/DD/YYYY')
BETWEEN BOOKDATE AND RELEASEDATE
REM I forgot to include the between

**Getting the FULL_CT is not the problem. I am getting the following Error when I run the script**

AM_CT = (SELECT COUNT(BOOK_ID)
*
ERROR at line 9:
ORA-06550: line 9, column 9:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
:= . ( @ % ;
ORA-06550: line 12, column 35:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
. ( ) * @ % & - + / at mod rem with <an exponent (**)> and or
group having intersect minus start union where connect ||

 

[SantaMufasa]

MissMIS,

I cannot get this to work.

It is always helpful to post the evidence that something "is not working": We must guess whether your problem is A) a syntax error, B) a run-time error, or C) a logic error.

In your case, you suffer from several issues:

1) Within the scope of your PL/SQL block, POPULATION_DATE is undefined.

...WHILE POPULATION_DATE < SYSTIMESTAMP

Instead, your code should read:

...WHILE POP_DATE < SYSDATE

Although SYSTIMESTAMP will work for you, I recommend SYSDATE since POP_DATE is of datatype DATE.

2) You are illegally attempting to convert a date (POP_DATE) to a DATE.

...WHERE TO_DATE(POP_DATE+1, 'MM/DD/YYYY')

To resolve this problem, just say,

...WHERE POP_DATE BETWEEN BOOKDATE AND RELEASEDATE

3) In PL/SQL, we assign values from SELECTs using the "INTO" syntax. Also, you have an incomplete WHERE clause for your attempted FULL_CT assignment.

...AM_CT = (SELECT COUNT(BOOK_ID)
FROM CJI_BOOK_RELEASE
WHERE TO_DATE(POP_DATE, 'MM/DD/YYYY HH24:MI:SS')
BETWEEN BOOKDATE AND RELEASEDATE
FULL_CT = (SELECT COUNT(BOOK_ID)
FROM CJI_BOOK_RELEASE
WHERE TO_DATE(POP_DATE, 'MM/DD/YYYY')

Your corrected code should read:

SELECT COUNT(BOOK_ID) INTO AM_CT
   FROM CJI_BOOK_RELEASE
   WHERE POP_DATE BETWEEN BOOKDATE AND RELEASEDATE;
SELECT COUNT(BOOK_ID) INTO FULL_CT
   FROM CJI_BOOK_RELEASE
   WHERE TRUNC(POP_DATE)
       BETWEEN TRUNC(BOOKDATE) and TRUNC(RELEASEDATE);

Let us know if these corrections help to produce the result you want.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[missmis44]

This is what I have so far but I am still getting an error.:
DECLARE
POP_DATE DATE := TO_DATE('09/07/2001 08:00:00', 'MM/DD/YYYY HH24:MI:SS');
AM_CT NUMBER (10);
FULL_CT NUMBER (10);

BEGIN
WHILE POP_DATE < SYSTIMESTAMP
LOOP
/*AM_CT SHOULD DISPLAY THE COUNT OF INMATES AT 8AM EACH DAY. I
WANT TO INCLUDE THE TIMESTAMP ON THIS SELECT.
*/
SELECT COUNT(BOOK_ID) INTO AM_CT
FROM CJI_BOOK_RELEASE
WHERE POP_DATE
BETWEEN BOOKDATE AND RELEASEDATE;
/*FULL_CT SHOULD DISPLAY THE COUNT OF INMATES IN CUSTODY FOR THE ENTIRE DAY.
I DO NOT WANT TO INCLUDE THE TIMESTAMP ON THIS SELECT. HOW DO I CREATE THIS
WITHOUT THE TIMESTAMP?
*/
SELECT COUNT(BOOK_ID) INTO FULL_CT
FROM CJI_BOOK_RELEASE
WHERE POP_DATE
BETWEEN BOOKDATE AND RELEASEDATE;

INSERT INTO CJI_DAILY_POPULALATION VALUES (POP_DATE, AM_CT, FULL_CT)

POP_DATE = POP_DATE + 1;
END LOOP;
END;

I am now getting the following error:
AM_CT NUMBER (10);
*
ERROR at line 3:
ORA-06550: line 27, column 2:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 26, column 1:
PL/SQL: SQL Statement ignored

 

[SantaMufasa]

Actually, MissMIS, the problem that Oracle lists is a "red herring"...there is nothing wrong with your code that reads, "AM_CT NUMBER (10);". There are 3 errors that occur on these 2 lines of code:

INSERT INTO CJI_DAILY_POPU[b]LA[/b]LATION VALUES (POP_DATE, AM_CT, FULL_CT)
POP_DATE = POP_DATE + 1;

The lines should instead read:

INSERT INTO CJI_DAILY_POPULATION VALUES (POP_DATE, AM_CT, FULL_CT)[b];[/b]
POP_DATE [b]:[/b]= POP_DATE + 1;

Errors:
1) "CJI_DAILY_POPULALATION" should read "CJI_DAILY_POPULATION".
2) Missing ";" to end the INSERT statement.
3) The PL/SQL assignment operator is ":=", not "=".

Make those corrections, and life should be good. Let us know.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[SantaMufasa]

Also, MissMIS, to resolve your other issues:

a) I WANT TO INCLUDE THE TIMESTAMP ON THIS SELECT.

b) I DO NOT WANT TO INCLUDE THE TIMESTAMP ON THIS SELECT. HOW DO I CREATE THIS WITHOUT THE TIMESTAMP?

First, every DATE and TIMESTAMP expression in Oracle includes the following informational components (following is for DATE expressions):
1) A.D/B.C. indicator (negative value = B.C.; positive = A.D.)
2) 1 byte for two-digit Century (e.g., "19", "20")
3) 1 byte for two-digit Year (e.g., "99", "05")
4) 1 byte for two-digit Month (e.g., "03", "12")
5) 1 byte for two-digit Day (e.g., "01", "31")
6) 1 byte for two-digit Hour (e.g., "00","23")
7) 1 byte for two-digit Minute (e.g., "00","59")
8) 1 byte for two-digit Second (e.g., "00","59")

TIMESTAMP expressions include component for timezone and for milliseconds.

Therefore, you cannot "suppress" the internal storage of either date or time components, but you can suppress the display of any time component via the TO_CHAR function. As an example, here is just the time component of the current time:

select to_char(sysdate,'hh24:mi:ss') from dual;

12:44:14

Let us know if this information clarifies things for you.



Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[missmis44]

The script ran but there is not any data in the table:
PL/SQL procedure successfully completed.

I still need to calculate my two counts How can I do this without formatting the POP_DATE with and without the timestamp:
-Count Each Day at 8AM: Where 09/07/2001 08:00:00 falls between BOOKDATE AND RELEASE DATE
-Count For Full Day (Excluding Timestamp): Where 09/07/2001
between BOOKDATE AND RELEASE DATE

 

[SantaMufasa]

MissMIS,

Your code should generate values for "INSERT INTO CJI_DAILY_POPULATION..." even if CJI_BOOK_RELEASE is empty.
Following are the format masks to create the output formats you requested:

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

2005-03-24 14:44:14

select to_char(sysdate,'yyyy-mm-dd') from dual;

2005-03-24

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.