SQL Server : count() for each group, but only groups having 1+ element like... AND all elements like -

SQL Server : count() for each group, but only groups having 1+ element like... AND all elements like -

there tables(and columns) like:

clients (clientid) houses (houseid) visits (clientid, houseid, visit_date) contracts (contractid, houseid, clientid, rentdate_from, rentdate_end)

i have problem writing sql query of kind:

how many visits houses did each client, before renting 1 of them?

it's easy count total number of visits each client, listing visits + grouping clientid , selecting count(*) each group.

let's select_1, , select_2 listing contracts clients.

select_1 not answer, because count must performed on groups, which:

have @ to the lowest degree 1 row "like" row in select_2 (it means @ to the lowest degree 1 of visited houses rented, because can happen client visited few houses, rented other, not visited house). thought comparing select_1 , select_2 with:

where s1.clientid = s2.clientid , s1.houseid = s2.houseid

each grouping must have rows(visits) date of same day or before contract date

maybe: datediff(day, s1.visit_date, s2.rentdate_from) >= 0

(why after pressing come in there 1 empty line on forum?)

a having clause serves filter groups aggregate query. similar where clause, applies aggregate result. in particular case, turns out want utilize same query both form initial result rows , filter groups; sql server, with clause allows perform query 1 time , reuse it. thus, might end this:

with previousvisits(clientid, houseid_visited, houseid_rented) select visits.clientid, visits.houseid, contracts.houseid visits bring together contracts on visits.clientid = contracts.clientid visits.visit_date <= contracts.rentdate_from select clientid, houseid_rented, count(*) visitcount previousvisits pv1 grouping clientid, houseid_rented having houseid_rented in ( select houseid_visited previousvisits pv2 pv2.clientid = clientid , pv2.houseid_rented = houseid_rented )

note should consider whether want in case same client rents 2 different houses. written, each house rented count houses visited before rental. there potential issue if same client rents same house more once.

sql-server