Trying to understand parentheses placements

Question

Trying to understand parentheses placements

Hall of Fame

Points: 3501

August 21, 2021 at 11:57 am

Hi all, for a long time I've struggled with code that had multiple consecutive right parentheses, as shown below, and I finally want to master them, 2 I can usually manage, but the end of this code has three, I'm struggling to find the corresponding left counterpart for each right parenthesis - I hope you guys can talk me through them, let me know what this is called in SQL (Nesting?) and maybe also point me to a tutorial that I can practice with

with
max_min_cte(t_min_dt, t_max_dt) as (
    select min(dateadd(hour, datediff(hour, 0, start_dt), 0)),
           max(dateadd(hour, datediff(hour, 0, end_dt), 0))
    from #t),    
dt_cte(dt) as (
    select dateadd(hour, fn.n, mm.t_min_dt)
    from max_min_cte mm
         cross apply dbo.fnTally(0, datediff(hour, mm.t_min_dt, mm.t_max_dt)) fn),
exp_cte(cnt, dt) as (
    select count(*), hr_calc.dt
    from #t t
         cross apply (values (dateadd(hour, datediff(hour, 0, t.start_dt), 0),
                              dateadd(hour, datediff(hour, 0, t.end_dt), 0))) conv_hr(hr_start_dt, hr_end_dt)
         cross apply dbo.fnTally(0, datediff(hour, conv_hr.hr_start_dt, conv_hr.hr_end_dt)) hr_fn
         cross apply (values (dateadd(hour, hr_fn.n, conv_hr.hr_start_dt)))

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

maybe this will help?

if you paste code into https://www.dpriver.com/pp/sqlformat.htm (Instant SQL Formatter) by default it breaks the brackets out so it is easy to follow...other formaters are available <grin>

maybe this will help?

   exp_cte(cnt, dt) AS
           (  --  first
            SELECT      count(*),
                        hr_calc.dt
            FROM        #t t
            CROSS apply (VALUES
                        (
                          dateadd(hour, datediff(hour, 0, t.start_dt), 0),
                          dateadd(hour, datediff(hour, 0, t.end_dt), 0)
                        )
                        ) conv_hr(hr_start_dt, hr_end_dt)
            CROSS apply dbo.fntally(0, datediff(hour, conv_hr.hr_start_dt, conv_hr.hr_end_dt)) hr_fn
            CROSS apply (VALUES
                        (
                          dateadd(hour, hr_fn.n, conv_hr.hr_start_dt)
                        )
            )  -- last

This reply was modified 3 years, 3 months ago by J Livingston SQL.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

One great aid for me is built into SSMS itself. Let's first make sure that you're setup for it. The setup only needs to be done once.

Step 1: From the menu at the top of SSMS, click on {Tools} and the following menu will pop up.

Step 2: Click on {Options} in the menu above and the following window will pop up.

Step 3: Referring to the above, make sure that you select {Fonts and Colors} in the left pane and {Brace Matching (Highlight)} in the [Display items:] pane. Set the [Item foreground:] to Black if it's not the default of Black and put a checkmark in the [Bold] checkbox.

Step 4: Referring to the below, in the currently displayed [Display items:] pane, click on the {Brace Matching (Rectangle)} entry. Then follow your nose in a similar fashion as before to select the indicated [Item background:] and check the [Bold] box.

Step 5: Click on the {OK} button in the window above.

Usage:

In your code, do one of the following things:

Put the cursor to the right of a left parenthesis and press the left arrow button to move the cursor.
Put the cursor to the left of a right parenthesis and press the right arrow button to move the cursor.

In either case, the matching parentheses will be highlighted in bright green like the example below.

This also means you don't have to reformat your code using a 3rd party tool.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

Never knew about that Jeff......thanks muchly