question

halfblood2010 avatar image
halfblood2010 asked

Select customers having age between date range

How can I do this task? List all orders made in 1/2015, by male customers between 30 and 40 years old. ![alt text][1] [1]: /storage/temp/3841-untitled.png
sqlmysql
untitled.png (6.4 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Homework? What have you tried so far? The schema you're showing there doesn't include anything that allows us to determine gender of customer.
0 Likes 0 ·
Saji avatar image
Saji answered
SELECT OP.ORDERDATE,C.CUSTOMERID,PO.ORDERID,P.NAME,P.PRICE,PO.QUANTITY FROM CUSTOMER C INNER JOIN ORDER_PRODUCT OP ON (C.CUSTOMERID = OP.CUSTOEMRID) INNER JOIN PRODUCT_ORDERED PO ON (OP.ORDERID = PO.ORDERID) INNER JOIN PRODUCT P ON (PO.PRODUCTID = P.PRODUCTID) WHERE DATEDIFF(YY,C.DOB,GETDATE()) BETWEEN 30 AND 40 AND C.GENDER = 'MALE' AND DATEPART(YY,OP.ORDERDATE) = '2015' AND DATEPART(MM,OP.ORDERDATE) = '01' Note: Gender field is not available in customer table. I have included it in my query.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Be careful using DATEDIFF to determine age - it actually counts how many date boundaries are crossed - so in your example someone born on 25 Dec 2000 will be classed as being 16, when they are not quite yet (today is 21 Dec) `select datediff(yy,'25 Dec 2000',getdate())`
1 Like 1 ·
Saji avatar image
Saji answered
Hi, SELECT OP.ORDERDATE,C.CUSTOMERID,PO.ORDERID,P.NAME,P.PRICE,PO.QUANTITY FROM CUSTOMER C INNER JOIN ORDER_PRODUCT OP ON (C.CUSTOMERID = OP.CUSTOEMRID) INNER JOIN PRODUCT_ORDERED PO ON (OP.ORDERID = PO.ORDERID) INNER JOIN PRODUCT P ON (PO.PRODUCTID = P.PRODUCTID) WHERE DATEDIFF(YY,C.DOB,GETDATE()) BETWEEN 30 AND 40 AND C.GENDER = 'MALE' AND DATEPART(YY,OP.ORDERDATE) = '2015' AND DATEPART(MM,OP.ORDERDATE) = '01' Note: I haven't seen any gender column in customer table. I have added it in my query.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.