0

I have this piece of code written by someone that helped me and it took me a while to figure out the code being new to SQL/TSQL, but I don't understand how to interpret the logic of the CROSS APPLY calling the other one in the example below .

I know that a CROSS APPLY is a process to be applied to each row from the left table but I don't see how this CROSS APPLY calls another one and applies this calculation to each row.. ?

I would like to understand how to interpret the logic of this double CROSS APPLY statement, as in understanding how it behaves against the table that will be returned by this SELECT statement, as well as how come a CROSS APPLY can call another one and how to understand what it does.

SELECT
    P.Name,
    CAST(DT.LocalTime AS CHAR(5)) AS LocalTime,
    DT.LocalWeekdayName,
    CASE WHEN O.IsOpen = 1 THEN 'Open' ELSE 'Closed' END AS OpenOrClosed
FROM Place AS P
JOIN Timezone TZ ON TZ.TimezoneId = P.TimezoneId

CROSS APPLY (
    SELECT
        @UtcNow AT TIME ZONE 'UTC' AT TIME ZONE TZ.Name AS LocalDateTime
) AS LT

-- THAT MEANS APPLY THE LOCAL WEEKDAY AND LOCAL TIME TO EACH ROW OF THE PLACE TABLE ?
CROSS APPLY (
    SELECT
        -- It's using LT's CROSS APPLY, can a CROSS APPLY mention another CROSS APPLY ?
        DATENAME(weekday, LT.LocalDateTime) AS LocalWeekdayName,
        CAST(LT.LocalDateTime AS TIME) AS LocalTime
) AS DT
JOIN DayOfTheWeek DOW ON DOW.DayName = DT.LocalWeekdayName

I have tried to look up for an answer online because I haven't seen anything like this in my TSQL book but I couldn't find anything.

Dale K
  • 25,246
  • 15
  • 42
  • 71
CodingIsFunYouShouldTryIt
  • 65
  • 6
  • 2
    `CROSS APPLY` is one method of DRY coding i.e. you can write a calculation once and not have to repeat it. And pretty much as you have explained, the first `CROSS APPLY` calculates a value per row, in this case converting a date to UTC, then the second `CROSS APPLY` uses the result of that calculation, twice, to calculate another 2 values per row. – Dale K May 02 '23 at 02:53
  • You could duplicate the calculation in the first `CROSS APPLY` into both instances of `LT.LocalDateTime` but then you've had to repeat that calculation. – Dale K May 02 '23 at 02:56
  • 1
    Yes it does work, that's how `APPLY` works: it *applies* the results per row so it can use outer references in previously mentioned table and applys. This technique is used pretty heavily to avoid repeating code. See also the documentation https://learn.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver16#left_table_source--cross--outer--apply-right_table_source – Charlieface May 02 '23 at 02:57
  • Oh I see, so you mean that the 1st CROSS APPLY is almost being used like a method by the 2nd one in order to respect the DRY principle ? – CodingIsFunYouShouldTryIt May 02 '23 at 17:49

0 Answers0