Riding JW's solution...

--Peso 3

SELECTp.Company,

p.[Year],

COALESCE(p.Amt1, 0.0) As [Q1 Amt],

CAST(COALESCE(p.Qty1, 0) AS INT) As [Q1 Qty],

COALESCE(p.Amt2, 0.0) As [Q2 Amt],

CAST(COALESCE(p.Qty2, 0) AS INT) As [Q2 Qty],

COALESCE(p.Amt3, 0.0) As [Q3 Amt],

CAST(COALESCE(p.Qty3, 0) AS...

SQL Profiler

CPU Dur Reads

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

Jeff ...

-- Peso 2

SELECTp.Company,

p.[Year],

COALESCE(p.Q1Amount, 0.0) AS [Q1 Amt],

CAST(COALESCE(p.Q1Quantity, 0) AS INT) AS [Q1 Amt],

COALESCE(p.Q2Amount, 0.0) AS [Q2 Amt],

CAST(COALESCE(p.Q2Quantity, 0) AS INT) AS [Q2 Amt],

COALESCE(p.Q3Amount, 0.0) AS [Q3 Amt],

CAST(COALESCE(p.Q3Quantity, 0) AS INT)...

You didn't dare to post the question on "the other forum"? 😀

Here is a single-query suggestion with only one PIVOT operator.

SELECTp.Company,

p.[Year],

COALESCE(p.[Q1 Amt], 0) AS [Q1 Amt],

CAST(COALESCE(p.[Q1 Qty], 0) AS INT)...

Not that goodlooking but it will do the job.

DECLARE@Items TABLE (RowID INT IDENTITY(1, 1), Item1 CHAR, Item2 CHAR)

INSERT@Items

SELECT'D', 'Q' UNION ALL

SELECT'A', 'E' UNION ALL

SELECT'B', 'F' UNION ALL

SELECT'C', 'A' UNION ALL

SELECT'C',...

What will then happen when you add the sample data

SELECT'D', 'Q' UNION ALL

and remove the sample data

SELECT'A', 'D' UNION ALL

The graph then looks like

A D...

Is this a correct interpretation of your original sample data?

A - D

/ E C - Q

|

...

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105157

SELECT@UAP = COALESCE(dbo.tlbUAP.[Percent], dbo.tlbUAP.UAPValue)

FROMdbo.tlbUAP

INNER JOINdbo.tlbChange ON dbo.tlbChange.ChangeID = dbo.tlbUAP.ChangeID

INNER JOINdbo.tlbWBS ON dbo.tlbWBS.ChangeID = dbo.tlbChange.ChangeID

INNER JOINdbo.ThreePointEstimate ON dbo.ThreePointEstimate.WBSID = dbo.tlbWBS.WBSID

WHEREdbo.tlbWBS.WBSID = @WBSID

SELECTa.ContractorCode,

a.ProjectID,

a.ProjectName,

d.ProductName,

d.ProductType,

b.ProjectID + b.ProductCode AS Item,

b.ProductCode

FROMProjects AS a

INNER JOINProductAllocation AS b ON b.ProjectID = a.ProjectID

INNER JOINUserProjectAccess AS c ON c.ProjectID = a.ProjectID

LEFT JOINProductMaster AS d ON d.ProductCode = b.ProductCode

WHEREa.StatusOpen =...

ALTER FUNCTION dbo.convertFloatDate

(

@date float

)

RETURNS DATETIME

AS

BEGIN

RETURN CAST(STUFF(STUFF(STUFF(STR(@date, 14), 13, 0, ':'), 11, 0, ':'), 9, 0, ' ') AS DATETIME)

END

ALTER FUNCTION dbo.convertFloatDate

(

@date float

)

RETURNS VARCHAR(20)

AS

BEGIN

RETURN STUFF(STUFF(STUFF(STR(@date, 14), 13, 0, ':'), 11, 0, ':'), 9, 0, ' ')

END

So if you want to keep dateserials, just add 2 to excels dateserial value and you're set.

Or convert excel dateserial back to date, export and convert back to dateserial.

Date "Zero" for SQL Server is 1900-01-01

Date "Zero" for MS Access is 1899-12-30

That's why there is a two day difference.

Since you seems to have the Enterprise Edition, go for the

CREATE PARTITION FUNCTION over your partitionNr column,

and have a job that runs nightly to update the partitionNr column...