Piet, we all have days where we battle with the task at hand.  Once the solution has...

The code can be re-written with 1 less CROSS APLY

SELECT  invoiceID      = SUBSTRING(t.invoiceID, x.StartPos, 6)
      ,...

The following code appears to fulfill your requirements

SELECT  invoiceID      = SUBSTRING(t.invoiceID, sq.StartPos, 6)
      , t.amount
 ...

I haven't done this for many years.  But I recall needing double path separators

'C:\\Users\\Administrator\\OneDrive - NAme of Company\\SQLBackup\\'

Dear Everyone I wanted to know the statement below will it delete everything in the last 30 days or anything before the last 30 days? DELETE FROM tb_LogIP WHERE...

Below is the dynamic SQL version of the equivalent CrossTab query.  I would suggest a performance test against your data.

DECLARE @sql NVARCHAR(MAX);
DECLARE @Columns NVARCHAR(MAX) = N'';

SELECT ...

1. Your ProjectID must be NOT NULL, as it is the PK
2. You need to remove the comma before each of the DEFAULTs.

You need to only GROUP BY STRXREFCODE

  Yes, I can totally do that, but I'm asking if it's possible to do it a single query.   My production query does a Dynamic Pivot using Dynamic...

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

Sorry for the silly question, but if you do everything in a stored procedure, what's the difference? Maybe you have a CTE with two pivots in it, and then...

You might be better off using a cross-tab query

SELECT 
  p1.DataLabel
, [CAT1 Quantity] = MAX(CASE WHEN p1.DataCategory = 'CAT1' THEN p1.Quantity END)
, [CAT2 Quantity] = MAX(CASE...

You would be much better off storing your values in the DB with the correct data types.  Then you can format the values any way you want in the presentation...

You are counting the total number of unique records.

You need to get a count per unique set of values

SELECT    MainHoldingID, OfferingElementID, [Count] = COUNT(*)
FROM...

You cold always try TABLESAMPLE

SELECT *
FROM SchemaName.TableName TABLESAMPLE (100000 ROWS)

Perhaps this code will point you in the right direction

WeekNum = DATEDIFF(dd, '1753', [YourDate]) /7 *7 -- Always works for week start on Monday