i have grown in my t-sql stature: i used cross-apply twice in the last week.
There are a number of relevant articles about apply\cross apply\outer apply. I particularly like what Rob Farley says about apply: “APPLY, in both its inner (CROSS APPLY) and outer (OUTER APPLY) forms, allows a correlated sub-query or table-valued function to be part of the FROM clause.” This means that you can have a subquery that returns values keyed to the same value in the outer query. It is commonly said that you run the apply function for each row of the outer function.
I have used it twice: one when i had to fix the first value (max) for a selection to add to a table. Patient A, diagnosis 1, diagnosis 2, diagnosis 3, diagnosis 4 (actually both the idc9 codes and the name or description. Create the outer table with a select into statement the columns for patient and all the other details. Include a select column for diagnosis1.code and diagnosis2.name, and four times. Obviously, we don’t know what diagnosis1 is, so the query will fail, or rather, work if we comment out the four\eight columns.
For each of the diagnoses, create a outer-apply code block immediately after the joins (and or groups) but before any final where predicates. In my case, it looks like:
outer apply (select sd.diagnosis_code_id as code, dc.description as name FROM Service_Diagnoses sd join Diagnosis_codes dc on dc.Diagnosis_Code_ID = sd.Diagnosis_Code_ID where ser.Service_ID = service_id order by service_diagnosis_id offset 0 rows fetch first 1 rows only ) as diagnosis1
Notice what is happening. For each diagnosis_code_id, i get the diagnosis code and the name for that service – which is passed in from the main table. Next – they are ordered and only the first one is returned to the outside table as diagnosis1.code and diagnosis1.name.
Next, the same code for diagnosis2 -except that i return the SECOND value in the list with
offset 1 rows fetch first 1 rows only
and similarly with three and four.
The second case was somewhat similar – vouchers are paid by a carrier (or two), and we wanted to know the number of services on that voucher. Shouldn’t be an issue, but as i built out the voucher and carrier component, each time i tried to add count(services) into the join, the count of records exploded. And the counts were all 1 instead of the counts for each voucher, which could be numerous. I truly think that i was doing something wrong with the group-bys that i had to do to put the count function into the big table, but i never got it to work.
What DID work was to write the table as before, include a column in the initial section that was “a.acount”. The cross apply select was created “as a” and i selected the count of services for each voucher as acount. It didn’t work at first – i kept getting 5 million instead of 12 until i realized that in the cross apply, i joined vouchers to services as a JOIN and not just on the join conditions. This is
cross apply (select count(ser.service_id) as acount from pm.services ser join pm.vouchers vou on vou.voucher_id = ser.voucher_id) as a
when it SHOULD BE
cross apply (select count(ser.service_id) as acount from pm.services ser where vou.voucher_id = ser.voucher_id) as a
Voucher (vou) isn’t even defined in the cross-apply – it gets passed in and a.acount gets passed out when the process is run.