rows of consecutive dates if the value is same everyday

[Pradeepa01]

Hi,

We are facing an issue with the JDBC query, I need help on.
I have 3 fields, UPC_ID, QTY,Date
I have to retrieve all the records for one UPC_ID whose QTY is same for last five days. The problem here is the QTY will not be inserted on sundays. When when we run the query on a Monday, we should consider the Qty for last wednesday to saturday and for monday.
I have written a query where I retrieve the data for last 6 days considering sunday to be in the middle, and group by QTY and count of the rows is>4 i retieve the data. But when i run this on saturday, It gives me a problem as, though the QTY was different on Tuesday, the count would be >4 and that record is retieved. Please help.

My query

select UPC_ID,QTY(select UPC_ID,QTY,count(1) as count rows where date>sysdate-6 group by UPC_ID, QTY)a1 group by QTY having sum(countrows)>4

which gives me a wrong output when ran on saturday.

[Pilgrim333]

Hi,

I think the following would work:

SELECT upc_id, qty
FROM table
WHERE date >= ( SELECT MIN(date)
FROM (SELECT date
FROM table
GROUP BY date
ORDER BY date DESC
)
WHERE rownum <= 5
)
This will give you all the inserted data from the last 5 inserted dates. This will work for the last 5 days if you are sure that EVERY day data will be inserted, except on sunday.

You will have to add the rest of the query yourself (getting the upc_id's with the same qty for the last 5 days) I don't know how this will perform, let me know.

Hope this will help you, otherwise let me know.

Pilgrim.