Thanks for posting the alternate solution, which seems to have a better execution plan than the one that I posted.

Aint that the truth

I'm sorry, but this amused me...

The following code is based on a solution previously provided by Drew Allen

https://www.sqlservercentral.com/Forums/FindPost2025579.aspx

WITH cteStart AS (
  SELECT  aa.account_id, aa.active_date
     ...

You can use a CASE statement

CREATE VIEW [dbo].[GCDFview]
AS
SELECT p.FirstName, p.LastName, c.city, p.instructorInt as 'Instructor'
, s.stateAbbrveation AS 'State', 'USA' AS Country, 'GCDF' AS Certifications
, p.PeopleID + CASE WHEN...

Use this pattern to pad your values

DECLARE @PaddingChar char(1) = 'x'; -- Change your padding character here
DECLARE @RequiredLen int = 7; -- Change the padded length here

SELECT...

• This reply was modified 6 years, 5 months ago by DesNorton. Reason: Added use cases for the function

You can add padding to the start/end of the value, then use left/right to get the required length

DECLARE @AccountNumber2 varchar(20) = '8063#018375';
DECLARE @PaddingChar char(1) = 'x'; --...

In the absence of better code, this is what I came up with.  I'm assuming you have a table of holidays like #Holidays table. Then you just check for...

Do you guys run EVERYTHING that goes into production through QA/UAT, or do you trust some users if they have a proven track record of high scores, and only...

At a guess, this *MIGHT* have improved performance.

WITH cteData AS (
  SELECT      p.party_id, p.consolidation_ind
  FROM     ...

Thanks for your help. I want to show only the columns City and Company Name. I dont want to show  column City with a count of the Cities per...

The temp...

With a small structure change

ALTER TABLE #TItems ADD
  AutoReorder  bit
, isFavorite   bit
, IsControlled bit;

UPDATE i
SET AutoReorder  = a.AutoReorder
  , isFavorite ...

Perfect, suits my needs perfectly thanks. Just to expand for anyone reading this in future: Whilst in my example the result of ROW_NUMBER can not be greater than the...

This is a classic (Gaps and) Islands problem.  The following will not give you sequential group numbers, but it will give you unique group numbers.

GroupNum = SequenceNumber...

• This reply was modified 6 years, 6 months ago by DesNorton.
• This reply was modified 6 years, 6 months ago by DesNorton.
• This reply was modified 6 years, 6 months ago by DesNorton.