April 8, 2014 at 8:21 am
Hello,
Here is my stored procedure;I am trying to use ReimbursementTypeID column in top select statement but its showing error like multipart identifier or invalid column name..any one please help me.
Select IsNull([34],0) as FreshFruitsAmount,IsNull([35],0) as LaborAmount,IsNull([36],0) as SmallSupplyAmount,IsNull([37],0) as AdminLabor,
IsNull([38],0) as LargeEquipment,IsNull([53],0) as Others,SystemID
from
(SELECT I.ReimbursementTypeID,
CAST (SUM(ISNULL(I.AMOUNT,0) - ISNULL(I.OtherAmount,0)) AS NUMERIC(18,2)) AS 'invoiceamt',G.SystemID
FROM grantsystem G
INNER JOIN vlookups L on I.ReimbursementTypeID = L.lookupid
WHERE G.fiscalyear=@FiscalYear AND G.SystemID = @SystemId AND I.ReimbursementTypeID IN(34,35,36,37,38,53) AND
GROUP BY fiscalyear, G.SystemID,I.ReimbursementTypeID ) AS SourceTable
PIVOT
(
SUM(invoiceamt) For ReimbursementTypeID in ([34],[35],[36],[37],[38],[53])
) as PivotTable
April 8, 2014 at 11:42 am
mcfarlandparkway (4/8/2014)
Hello,Here is my stored procedure;I am trying to use ReimbursementTypeID column in top select statement but its showing error like multipart identifier or invalid column name..any one please help me.
Select IsNull([34],0) as FreshFruitsAmount,IsNull([35],0) as LaborAmount,IsNull([36],0) as SmallSupplyAmount,IsNull([37],0) as AdminLabor,
IsNull([38],0) as LargeEquipment,IsNull([53],0) as Others,SystemID
from
(SELECT I.ReimbursementTypeID,
CAST (SUM(ISNULL(I.AMOUNT,0) - ISNULL(I.OtherAmount,0)) AS NUMERIC(18,2)) AS 'invoiceamt',G.SystemID
FROM grantsystem G
INNER JOIN vlookups L on I.ReimbursementTypeID = L.lookupid
WHERE G.fiscalyear=@FiscalYear AND G.SystemID = @SystemId AND I.ReimbursementTypeID IN(34,35,36,37,38,53) AND
GROUP BY fiscalyear, G.SystemID,I.ReimbursementTypeID ) AS SourceTable
PIVOT (
SUM(invoiceamt) For ReimbursementTypeID in ([34],[35],[36],[37],[38],[53])
) as PivotTable
When you pivot, the column you are pivoting on is replaced by new columns corresponding to the contents of the column. In your case those would be [34],[35],[36],[37],[38], and [53]. After pivoting, the ReimbursementTypeID is gone, but that's the whole point of pivoting in the fist place.
Gerald Britton, Pluralsight courses
April 8, 2014 at 12:35 pm
Hi,
Is there any way to sum two textboxes amount into one.
I am using two different tables with same dataset.I need total amounts which I am getting in textbox 45 and textbox 48
is there any to combine these two textboxes dollar amount into single using any variables ?
April 8, 2014 at 3:25 pm
YOu can't do the sum in a pivot by itself. However, you could do pre-aggregation in a CTE or subquery then pivot the results. That might just do it for you
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply