I was thinking along the same lines as Jeff, but did things in a slightly different order.  By uniquifying first, I'm able to use the current price for the previous/next...

It's not clear what you are trying to accomplish here.  And based on best guesses there are several issues.

• It looks like you want to match Table1.Col3 to Table11.Col1, but...

This is a much shorter template and is thus easier to understand.  It's also probably more efficient, because it will abort as soon as it finds ANY row that contains...

SELECT Student_ID
FROM dbo.table_name
GROUP BY Student_ID
HAVING MAX(CASE WHEN Student_Status = '2' THEN 1 ELSE 0 END) = 1 AND
    MAX(CASE WHEN Student_Status <> '2' THEN 1...

The fields listed in the SELECT clause are a subset of the fields in the GROUP BY clause.  What you said about DISTINCT is only necessarily true if...

Why are you using DISTINCT and GROUP BY in your first query out of interest? GROUP BY already puts your data into distinct groups, so the DISTINCT in the...

Both tables contain a chain id, store id, supplier id, and product id.  I would expect that you would want to include EACH of those columns in your join criteria.

I...

something along these lines - it assumes that none of the duplicates already contain a row that is 1 hour ahead

if object_id('tempdb..#keys') is not null
 ...

• This reply was modified 2 years, 5 months ago by drew.allen.

Instead of the wordy

If(OBJECT_ID('tempdb..#TBL_Orders ') Is Not Null)
Begin
    Drop Table #TBL_Orders 
End

I recommend switching to the shorter and more intuitive

DROP TABLE...

I think you are looking for something like the following:

WITH Server_Databases AS
(
    select a.Server,d.dbname [DB_name], COUNT(d.dbname) OVER(PARTITION BY a.Server) AS cnt
   ...

First, when you post code, you should use the {;} Code button to format the code.

The simplest (but not necessarily most efficient) method is to use nested queries.

            
March 23, 2023 at 4:00 pm
            
#4164227

            
            
                                                
        

Instead of dividing by 100 try dividing by 100.0.  With division the implicit type conversion is to INT unless the denominator is non-INT, either NUMERIC or FLOAT.  Also, imo...

You might also check the encoding of the old and new versions.  I know  that Notepad++ will show you the encoding.

Drew

I don't use Jeff's string splitter, but this is the broad outline.

1. Convert the item number to zero-based instead of one-based (if necessary).

   1. That is, subtract one from the item...

You're doing integer division, which is why you're getting 0's.  Instead of specifying 1 (integer), specify 1.0 (decimal).

Also, you don't need the ISNULL() if you specify the CASE expression correctly.