I have the following table BOOKSALES. Some Background about the attributes - BookID - The unique ID of the book SaleDate - Date on which the book was sold RegisterDate - Date on which sale was registered in accounts books The table looks like this - Now I want to execute the query that gives me the count of book

Execute the outer query for each date obtained from inner query

I have the following table BOOKSALES. Some Background about the attributes –

BookID – The unique ID of the book
SaleDate – Date on which the book was sold
RegisterDate – Date on which sale was registered in accounts books

The table looks like this –

+--------+------------+--------------+
| BOOKID | SALEDATE   | REGISTERDATE |
+--------+------------+--------------+
| A1     | 2021-03-01 | 2021-03-01   |
| A2     | 2021-03-01 | 2021-03-02   |
| A3     | 2021-03-01 | 2021-03-03   |
| A4     | 2021-03-02 | 2021-03-02   |
| A5     | 2021-03-02 | 2021-03-03   |
| A6     | 2021-03-03 | 2021-03-03   |
| A7     | 2021-04-01 | 2021-04-01   |
| A8     | 2021-04-01 | 2021-04-02   |
+--------+------------+--------------+

Now I want to execute the query that gives me the count of book ids for each sale date, but the register date is less than the sale date. For example- If I want to execute the query for 2nd March, the query will look like-

SELECT COUNT(BOOKID) FROM BOOKSALES WHERE SALEDATE>='2021-03-01' AND SALEDATE<='2021-03-02' AND REGISTERDATE<='2021-03-02';

I want to execute the above query for each date in March . So I tried to rephrase the query as follows

SELECT SALEDATE,COUNT(BOOKID) FROM BOOKSALES WHERE SALEDATE>='2021-03-01' AND (SALEDATE AND REGISTERDATE)<=(SELECT DISTINCT(SALEDATE) FROM BOOKSALES WHERE SALEDATE>='2021-03-01' AND SALEDATE<='2021-03-31')  ORDER BY SALEDATE GROUP BY SALEDATE;

But the above query doesn’t seem to work. Can somebody suggest how to write the above query correctly?

The eventual output should look like –

+------------+---------------+
| SALEDATE   | COUNT(BOOKID) |
+------------+---------------+
| 2021-03-01 |             1 |
+------------+---------------+
| 2021-03-02 |             3 |
+------------+---------------+
| 2021-03-03 |             6 |
+------------+---------------+

Answer

The goal of the task is a bit unclear, can’t see what are you trying to achieve. Anyways, your desired output can be returned with this query:

SELECT A.SALEDATE, COUNT(*)
FROM 
(SELECT SALEDATE
FROM BOOKSALES
WHERE SALEDATE BETWEEN '2021-03-01' AND '2021-03-31'
GROUP BY SALEDATE
) AS A
INNER JOIN BOOKSALES AS B
ON A.SALEDATE >= B.REGISTRATION_DATE
GROUP BY A.SALEDATE

Advertisement

Answer