You select from the cte at the end, not the beginning:

with cte as

(

select 1 as one, 2 as two from oneTable

),

cte2 as

(

select one, two from cte

union select one +...

Can you give us an example that relates to the sample data you've posted?

If you mean you want to keep the original 20 records with extra summed columns, you can do this:

SELECT Work_Id,

Project_name,

country,

region,

Current_Actual_Saving,

Current_Forecast,

Current_Metric,

Prior_Actual_Saving,

Prior_Forecast,

Prior_Metric,

Prior_2_Actual_Forecast,...

You can roll up selected values and add them to each record like this:

SELECT Country,Region, Current_Forecast,

SUM(Current_Forecast) OVER (PARTITION BY Country, Region) as Sum_Current_Forecast

FROM My_Table

There's another one here you may not have seen:

http://www.sqlservercentral.com/Forums/Topic952029-391-2.aspx

Seems to go into a bit more depth - again not sure it if it will answer your question...

This has been covered recently - may be of some help:

http://www.sqlservercentral.com/Forums/Topic872069-1550-1.aspx

You can create a script of all stored procedures in a database & search through that. It's easy to see the context & skip from one proc to another.

If...

Hi

You could use sp_executesql like this:

Test data:

---------------------------------------------

-- Set up test data:

---------------------------------------------

IF OBJECT_ID('dbo.Vendors') IS NOT NULL

DROP TABLE dbo.Vendors;

IF OBJECT_ID('dbo.Invoices') IS NOT NULL

DROP TABLE dbo.Invoices;

CREATE TABLE dbo.Vendors

(

VendorID Int,

VendorName nvarchar(30)

);

CREATE TABLE dbo.Invoices

(

VendorID Int,

InvoiceNumber...

How about this:

IF OBJECT_ID('dbo.Sales1') IS NOT NULL

DROP TABLE dbo.Sales1;

CREATE TABLE dbo.sales1

(

Sales_saleID int,

Sales_customerID int,

Sales_status varchar(10)

);

INSERT INTO dbo.sales1 VALUES ( 1, 1, 'Pending' );

INSERT INTO dbo.sales1 VALUES ( 2, 1, 'Completed' );

INSERT...

This is a very old fashioned syntax.

You should use LEFT OUTER JOIN ON ......, RIGHT OUTER JOIN ON ...... etc.

It's sp_Executesql that's the problem. 1) Are you using the parameter? 2) The format doesn't look right - see:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/a8d68d72-0f4d-4ecb-ae86-1235b962f646.htm

It works as EXEC(@SQLQuery)

This has been recommended recently & might help...

http://www.sqlservercentral.com/articles/IsNumeric/71512/

Having looked at the article, it looks like the 'AS' clause is in the wrong place. It should be like this, I think

SELECT a.DayCount,

...

If you've got both 0 & 1 records missing, use the following:

CREATE TABLE ORDERID

(

OrderId Int,

Cost Numeric(9,2),

Indicator int,

[Count] Int

);

INSERT INTO ORDERID VALUES ( 1, 10000, 0, 2 );

INSERT INTO ORDERID VALUES...

Using a LEFT OUTER JOIN will handle that:

SELECT

ZERO.OrderId, ZERO.Cost, ZERO.[Count],

ONE_COST = ISNULL(ONE.Cost, 000.00), ONE_COUNT = ISNULL(ONE.[Count], 000.00)

FROM

ORDERID ZERO

LEFT OUTER JOIN

(

SELECT

OrderId,

Cost, [Count]

FROM

ORDERID

WHERE

Indicator = 1

) ONE ON ZERO.OrderId = ONE.OrderId

WHERE

ZERO.Indicator...