Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Shumaila
Starting Member
21 Posts |
 Posted - 2001-10-19 : 15:32:05
|
| I have written a Query and it is not giving the expected result whereas i think logically it should. There might be a small problem in it. The senario is some thing like ... there is a customer order table which has orderid, customer id, product id, datetime, NameofProduct ....etcNow Order_id is unique and never repeated. Customer_id's are corresponding to customer and product_id represent products. Each customer could order many product and he could also order a single product many times, only differece would be date (i.e., whenever previous product finishes he can reorder it with different order #)We need to retrieve record of all those products which a customer had ordered more than a yr ago. i.e., if Customer C1 had ordered P1 about 13 months ago but never after that then we would retrieve this record. If C1 had order P2 13 month ago then 6 month ago then we would not retrieve this record.I wrote this query Select * from Order join( Select max(dateTimeStamp),CID,PID from Order group by CID, PID having datediff(m,max(dateTimeStamp),getdate()) >12) A on(Order.cID = A.CID and Order.PID = A.PID)Now the inner query gives all the cid, pid with max datetimestamp corresponding to that group of cid and pid but when i join it with outside select (as done here) to get the full rows of each result it returns me whole set of those grouped cid (which are qualified for more than 12 month perios), pid along with max date instead of just the row with the max dateFor testing purpose i tried using only number instead of dates. and the date wasTimeDate Cid Pid Name ------ ---------- ---------- ---------- 1 C1 P1 Name 1 2 C1 P1 Name 2 3 C3 P3 Name 3 4 C4 P4 Name 4 5 C3 P3 Name 5 6 C4 P4 Name 6 7 C7 P7 Name 7 8 C3 P3 Name 8 Edited by - shumaila on 10/19/2001 15:35:38 |
|
|
|
|
|
|
|
Join query problem