DesNorton,

I see what you are saying now.  I was reading it wrong.  But what about my second post where SQL says 'CK_isActive' is not a constraint?  I don't understand...

Remove the where clause.  then it does all products

This is a simple SUM with GROUP BY

SELECT    t.BoughtDate
        , Apples  = SUM( t.BoughtApples )
 ...

Edit:  I ran this part of the code only to see if I can isolate the problem:

ALTER TABLE Costpoint.ProjectCodes
DROP CONSTRAINT CK_isActive;

and I got this error message:

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