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
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.
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.
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())`
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.
19 People are following this question.
