Reputation: 4661
I have the following SQL query in oracle:
SELECT * FROM
(
SELECT s.singleid,s.titel,a.naam,s.taal,SUM(b.aantal) AS "AANTAL VERKOCHT"
FROM singles s
JOIN artiesten a on a.artiestid = s.artiestid
JOIN bestellingen b on b.singleid = s.singleid
GROUP BY s.singleid,s.titel,a.naam,s.taal,b.datum
ORDER BY sum(b.aantal) DESC
)
WHERE ROWNUM <= 5
This works, but I need to return only the records where b.datum is between the time now, and previous week.
How do I do this?
Upvotes: 1
Views: 281
Reputation: 37490
You should be able to add a BETWEEN clause to your where:
WHERE b.datum between SYSDATE - 6 AND SYSDATE
Upvotes: 2
Reputation: 8919
You would want to extract from the BESTELLINGEN table only those rows where [datum] is greater than or equal to (today at midnight minus 7 days) and less than or equal to 'now' (or less than tomorrow at midnight, according to your requirement). I would probably make this set of Bestellingen rows an inline view and join the other tables to it, and then do your grouping.
Upvotes: 0
Reputation: 1132
In SQL Server the syntax is:
AND (b.datum > DATEADD(week, -1, GetDate()) and b.datum < GetDate())
I would assume the syntax is the same, or very similar, in Oracle.
Upvotes: -1