Querying data between two datetimes from previous day

n4nz

Hi,

I am trying to write a query to be used in view where I can obtain results on a daily basis without having to change the query this is what i have so far:

Expand|Select|Wrap|Line Numbers

 SELECT BATCH_TYPE,

SUM(CASE WHEN RECEIVED_DATE BETWEEN TO_DATE('11-NOV-07 16:30:00', 'DD-MON-YY HH24:HH:MI:SS') AND TO_DATE('12-NOV-07 18:00:00' 'DD-MMM-YY HH24:HH:MI:SS')

FROM TABLE1

GROUP BY BATCH_TYPE

What i need to do is keep the same time intervals however keep the date as generic to 1 day in the past.

So for example where the Received Date is yesterday between 16:30 and Received Date is today and 18:00

The query is alot larger than the above example however if this part is resolved then the rest will fall into place. I don't want to have to edit the query daily.

Any help would be appreciated

amitpatel66

Quote:

Originally Posted by n4nz

Hi,

I am trying to write a query to be used in view where I can obtain results on a daily basis without having to change the query this is what i have so far:

Expand|Select|Wrap|Line Numbers

 SELECT BATCH_TYPE,

SUM(CASE WHEN RECEIVED_DATE BETWEEN TO_DATE('11-NOV-07 16:30:00', 'DD-MON-YY HH24:HH:MI:SS') AND TO_DATE('12-NOV-07 18:00:00' 'DD-MMM-YY HH24:HH:MI:SS')

FROM TABLE1

GROUP BY BATCH_TYPE

What i need to do is keep the same time intervals however keep the date as generic to 1 day in the past.

So for example where the Received Date is yesterday between 16:30 and Received Date is today and 18:00

The query is alot larger than the above example however if this part is resolved then the rest will fall into place. I don't want to have to edit the query daily.

Any help would be appreciated

The dates can be made generic using SYSDATES, but please clarify one thing, is it mandatory to use the time in the dates?
I would suggest to truncate the dates and then do the comparison in order to achieve better results.

n4nz

Yes the dates are Mandatory, as I need to ensure jobs are completed each day to meet business requirements the window is between 16:30 on day one until 18:00 on day two for all jobs received between 16:30 day one and 16:30 day two.

Using sysdate was my first thought however not sure how to include the time unless I use something like:

Expand|Select|Wrap|Line Numbers

Between Trunc(sysdate) -7.5/24 and Trunc(sysdate) + 18/24

Hope this clarify's things.

Thanks in advance

amitpatel66

Quote:

Originally Posted by n4nz

Yes the dates are Mandatory, as I need to ensure jobs are completed each day to meet business requirements the window is between 16:30 on day one until 18:00 on day two for all jobs received between 16:30 day one and 16:30 day two.

Using sysdate was my first thought however not sure how to include the time unless I use something like:

Expand|Select|Wrap|Line Numbers

Between Trunc(sysdate) -7.5/24 and Trunc(sysdate) + 18/24

Hope this clarify's things.

Thanks in advance

Check if below query is useful to add time to the dates:

Expand|Select|Wrap|Line Numbers

  
select TO_CHAR(SYSDATE-1,'DD-MON-YYYY')||' 16:30:00',TO_CHAR(SYSDATE,'DD-MON-YYYY')||' 18:00:00' from dual

n4nz

Thanks for your help I have two solutions both work equally as well although Option 1 has a slight speed gain:

Option 1:

Expand|Select|Wrap|Line Numbers

 SELECT BATCH_TYPE,

SUM(CASE WHEN RECEIVED_DATE BETWEEN TRUNC(SYSDATE-2) -7.5/24 AND TRUNC(SYSDATE-2) + 18/24 THEN 1 ELSE 0 END) AS COL1

FROM TABLE1

GROUP BY BATCH_TYPE

Option 2:

Expand|Select|Wrap|Line Numbers

 SELECT BATCH_TYPE,

SUM(CASE WHEN RECEIVED_DATE BETWEEN TO_DATE(TO_CHAR(SYSDATE-2,'DD-MON-YYYY')|| '16:30:00' 'DD-MON-YYYYHH24:MI:SS') AND TO_DATE(TO_CHAR(SYSDATE-1,'DD-MON-YYYY')|| '18:00:00' 'DD-MON-YYYYHH24:MI:SS') THEN 1 ELSE 0 END) AS COL1

FROM TABLE1

GROUP BY BATCH_TYPE

amitpatel66

Quote:

Originally Posted by n4nz

Thanks for your help I have two solutions both work equally as well although Option 1 has a slight speed gain:

Option 1:

Expand|Select|Wrap|Line Numbers

 SELECT BATCH_TYPE,

SUM(CASE WHEN RECEIVED_DATE BETWEEN TRUNC(SYSDATE-2) -7.5/24 AND TRUNC(SYSDATE-2) + 18/24 THEN 1 ELSE 0 END) AS COL1

FROM TABLE1

GROUP BY BATCH_TYPE

Option 2:

Expand|Select|Wrap|Line Numbers

 SELECT BATCH_TYPE,

SUM(CASE WHEN RECEIVED_DATE BETWEEN TO_DATE(TO_CHAR(SYSDATE-2,'DD-MON-YYYY')|| '16:30:00' 'DD-MON-YYYYHH24:MI:SS') AND TO_DATE(TO_CHAR(SYSDATE-1,'DD-MON-YYYY')|| '18:00:00' 'DD-MON-YYYYHH24:MI:SS') THEN 1 ELSE 0 END) AS COL1

FROM TABLE1

GROUP BY BATCH_TYPE

We are glad to help you :)

Querying data between two datetimes from previous day

re: Querying data between two datetimes from previous day

re: Querying data between two datetimes from previous day

re: Querying data between two datetimes from previous day

re: Querying data between two datetimes from previous day

re: Querying data between two datetimes from previous day

Similar Oracle Database bytes

/bytes/development

/bytes/it

/bytes/about