Scott - If you are not looking for the test results to be listed in a string, then I think this is what you are looking for.

            
November 29, 2020 at 6:32 pm
            
#3814927

            
            
                                                
        

That's really a mistake on your part to think so in...

This is the main test q I use to test query-writing skill.  It's not as trivial / obvious as it sounds at first.

A table contains 1, 2 or...

As a drastic example of what I've talking about, say you asked an applicant to list all the columns from the table.  Say the table has key_col, data_col1, data_col2.  No...

Scott - I agree with Jeff in the sense that this test is somewhat unrealistic because you have CTE's and sub queries at your disposal. However, if this is...

• This reply was modified 5 years, 1 months ago by ScottPletcher.

Except that single outer apply will be executed for each row in the Meter table - and the data must be sorted each time based on the filters from...

Pretty good overall, reasonably close.

But:

the middle score will not necessarily be the avg score (sum/count is same as avg);

if there are fewer than 3 scores, leave extra scores NULL.  For...

Just curious - but how are these solutions better than using multiple joins?  If this is a performance related issue - fix the use of the upper functions on...

Here's the table structure:

student_scores ( student_id int NOT NULL, assignment_number int NOT NULL, score int NOT NULL )

Typically, you'd want columns used in the WHERE condition to be in the index keys, for the reasons noted by Mr. Gale.

select *from SaleOrderLines where OrderID='value1' and itemid=value2' and status='open'

However,...

Yeah, sorry, adjust all the "d." from the query.  You didn't provide any usable sample data for us to test with, so you can't expect fully tested code :-).

            
November 27, 2020 at 3:12 pm
            
#3814475

            
            
                                                
        

You can use a single table scan / lookup and will often get better performance from doing so:

select t.*, s.Description as SubTypeCD_DESC, 
    d.AOD_METHOD_DESC,...

As a bit of a sidebar, your WHERE clauses for the start (>=) and end dates (<) are CORRECT.  Your calculation for end date is not.  You could miss...

For best overall performance, it's extremely likely that you need to change the clustering index on the table to be ( OrderID, ItemID ).  Especially if the table is now...

This is the main test q I use to test query-writing skill.  It's not as trivial / obvious as it sounds at first.

A table contains 1, 2 or 3 rows...

• This reply was modified 5 years, 1 months ago by ScottPletcher. Reason: Reworded the description of the table to make it clearer