Here is a different approach.

WITH cteUniquify AS
(
    SELECT *, CASE WHEN ph.Price = LAG(ph.Price) OVER(ORDER BY SomeDt) THEN 0 ELSE 1 END AS PriceChange
...

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 3 years, 6 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.

J. Drew Allen

Business Intelligence Analyst

Philadelphia, PA