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 |
|
asdf777
Starting Member
1 Post |
 Posted - 2014-10-17 : 11:01:48
|
| There are tables(and columns) like: 'Clients'(clientID) 'Houses' (houseID) 'Visits' (clientID, houseID, visit_date) 'Contracts'(contractID, houseID, clientID, rentDate_from, rentDate_end) I have problem with writing MS SQL query of this kind:how many visits to houses did each client, before renting one of them?Its easy to count total number of Visits for each client, listing all visits + group by clientID and selecting count(*) for each group.Lets say this is select_1, and select_2 is listing all Contracts for all clients.Select_1 is not answer, because count must be performed only on groups, which:-have at least 1 row "like" row in select_2 (it means that at least one of visited houses was rented, because it can happen that client visited few houses, but rented other, not visited house). my idea for this is comparing select_1 and select_2 with:"where s1.clientID=s2.clientID and s1.houseID=s2.houseID"-each group must have all rows(visits) with date of same day or earlier than contract date maybe: "datediff(day, s1.visit_date, s2.rentDate_from) >= 0" |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-10-17 : 11:19:14
|
| You need a HAVING clause. |
 |
|
|
|
|
|
|
|
count() for each group, but only groups having 1+