CODEWITHSUNDEEP
sqloracle-database
Robby Smet
Robby Smet

Reputation: 4661

Return records between now and previous week

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

Answers (3)

chris
chris

Reputation: 37490

You should be able to add a BETWEEN clause to your where:

WHERE b.datum between SYSDATE - 6 AND SYSDATE

Upvotes: 2

Tim
Tim

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

paulH
paulH

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

Related Questions