December 16, 2015 at 11:58 am
Hello,
This is a bit more complex than I can handle, maybe someone can figure this out:
I am trying to CROSS JOIN or even UNION ALL a table (#TableOfRigTableRigRowRigInputActionValues) with a function (ufnPerYear_1). The trick is that the function requires 4 column values from the table.
Here is what I have, but I can't figure out how to get the column values in there:
Create Table #TableOfRigTableRigRowRigInputActionValues
(
ID int identity(1,1) not null,
RigsVolTable varchar(10),
RigsVolRow varchar(10),
RigsInputAction varchar(20),
Volume numeric(17,2),
RegStartQtr varchar(2),
RegStartYear varchar(4),
RegEndQtr varchar(2),
RegEndYear varchar(4)
,RigsDNOTXTSH varchar(5)
)
INSERT INTO #TableOfRigTableRigRowRigInputActionValues
(RigsVolTable
, RigsVolRow
, RigsInputAction
, Volume
, RegStartQtr
, RegStartYear
, RegEndQtr
, RegEndYear
, RigsDNOTXTSH)
Select [RIGS Vol Table]
, [RIGS Vol Row]
, CUM.[Input Action]
, Sum(CAST(CUD.VolumeExecutionFactor AS numeric(17,2))) * ISNULL(SUC.[TargetValue],1)
AS Volume
,CAS.RegulatoryQuarterNumberInYear RegStartQtr
,LEFT(CAS.RegulatoryYearName,4) RegStartYear
,CAE.RegulatoryQuarterNumberInYear RegEndQtr
,LEFT(CAE.RegulatoryYearName,4) RegEndYear
, DN.TXTSH
From [DWH].[CUMappingTable] CUM
LEFT JOIN [DWH].[StaticUnitConversion] SUC ON SUC.[TargetUnit] = CUM.[UOM]
INNER JOIN DWH.DimCompatibleUnit CU
ON CUM.[Input Asset Type] = CU.[AssetType]
AND CUM.[Input Voltage] = CU.OperatingVolt
AND UPPER (ISNULL(SUC.[SourceUnit], CUM.[UOM])) = UPPER (CASE WHEN SUC.[SourceUnit] IS NULL THEN CUM.[UOM] ELSE CU.UOM END)
AND CUM.[Input Action] = CU.ACTION
AND CU.CostType ='DIRECT'
AND CU.[InvestmentDriver] = CUM.[Input INVESTMENT DRIVER]
INNER JOIN DWH.DimCUDetails CUD
ON CU.DW_CompatibleUnit_key = CUD.CompatibleUnitKey
LEFT JOIN [DWH].[DimCalendar] CAS
ON ISNULL(CAST(CUD.[QuarterVolume] AS VARCHAR(10)),ISNULL(CAST(CUD.[YearStart] AS VARCHAR(10)),CUD.[StartDate])) = CASE WHEN CUD.[QuarterVolume] IS NULL THEN CAST(CAST(CAS.[DayTimeStamp] AS DATE) AS VARCHAR(10)) ELSE CAS.[QuarterName] END
LEFT JOIN [DWH].[DimCalendar] CAE
ON ISNULL(CAST(CUD.[YearVolume]AS VARCHAR(10)),ISNULL(CAST(CUD.[YearStart]AS VARCHAR(10)),CUD.[EndDate])) = CAST(CASE WHEN CUD.[YearVolume] IS NULL THEN CAST(CAST(CAE.[DayTimeStamp]AS DATE) AS VARCHAR(10)) ELSE CAE.[YearName] END AS VARCHAR(10))
LEFT JOIN [DWH].[vwDimCompatibleUnit] CU1
ON CU1.AssetType = CUM.[Input Asset Type]
AND CU1.OperatingVolt = CUM.[Input Voltage]
AND CU1.CostType = 'DIRECT'
LEFT JOIN [DWH].[DimDNO] DN
ON DN.TXTSH = CU1.[DNODesc]
Group by [RIGS Vol Table]
, [RIGS Vol Row]
, CUM.[Input Action]
,CAS.RegulatoryQuarterNumberInYear
,LEFT(CAS.RegulatoryYearName,4)
,CAE.RegulatoryQuarterNumberInYear
,LEFT(CAE.RegulatoryYearName,4)
, DN.TXTSH
, CUM.[UOM]
, CU.[UOM]
, SUC.[TargetValue]
Order by [RIGS Vol Table]
, [RIGS Vol Row]
, CUM.[Input Action]
, RegStartYear
, RegStartQtr
, RegEndYear
, RegEndQtr
, DN.TXTSH
-----------------------------------------
Create Table #TableFinalAlt
(
RigsVolTable varchar(10),
RigsVolRow float,
RigsInputAction varchar(20),
Volume numeric(17,2),
RegStartQtr varchar(2),
RegStartYear varchar(4),
RegEndQtr varchar(2),
RegEndYear varchar(4),
RigsDNOTXTSH varchar(10),
PlnVol1 numeric (17,4) null,
PlnVol2 numeric (17,4) null,
PlnVol3 numeric (17,4) null,
PlnVol4 numeric (17,4) null,
PlnVol5 numeric (17,4) null,
PlnVol6 numeric (17,4) null,
PlnVol7 numeric (17,4) null,
PlnVol8 numeric (17,4) null
)
INSERT INTO #TableFinalAlt
(
RigsVolTable
, RigsVolRow
, RigsInputAction
, Volume
, RegStartQtr
, RegStartYear
, RegEndQtr
, RegEndYear
, RigsDNOTXTSH
, PlnVol1
, PlnVol2
, PlnVol3
, PlnVol4
, PlnVol5
, PlnVol6
, PlnVol7
, PlnVol8
)
SELECTRigsVolTable
, Try_convert(float, RigsVolRow)
, RigsInputAction
, Volume
, RegStartQtr
, RegStartYear
, RegEndQtr
, RegEndYear
, RigsDNOTXTSH
FROM #TableOfRigTableRigRowRigInputActionValues
CROSS JOIN
SELECT * FROM [DWH].[ufnGetPerYear_1] (Volume, RegStartQtr, RegStartYear, RegEndQtr, RegEndYear)
Thank you so very much for your time and attention.
Most sincerely,
Matt
December 16, 2015 at 12:03 pm
Can you post the function definition?
December 16, 2015 at 12:42 pm
IF you want to pass columns to a table valued function like that you need to use APPLY. I suspect that this will work for you:
SELECT RigsVolTable
, Try_convert(float, RigsVolRow)
, RigsInputAction
, Volume
, RegStartQtr
, RegStartYear
, RegEndQtr
, RegEndYear
, RigsDNOTXTSH
FROM #TableOfRigTableRigRowRigInputActionValues
CROSS APPLY
[DWH].[ufnGetPerYear_1] (Volume, RegStartQtr, RegStartYear, RegEndQtr, RegEndYear)
-- Itzik Ben-Gan 2001
December 17, 2015 at 2:08 pm
ufnGetPerYear_1 function definition:
USE [SAPBW_DataWarehouse_R3]
GO
/****** Object: UserDefinedFunction [DWH].[ufnGetPerYear_1] Script Date: 17/12/2015 21:06:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [DWH].[ufnGetPerYear_1]
(@CnV numeric(17,4), @StrQtr int, @StrYr int, @EndQtr int, @EndYr int)
RETURNS
@temptable TABLE (
[Year] int,
YearShare numeric(17,4)
)
AS
BEGIN
DECLARE @QtrCount int = 4
DECLARE @YearCount int = 0
DECLARE @YearDiff int = @EndYr - @StrYr;
-- Below Line modified by Rakesh
--DECLARE @YearDiff int = CASE WHEN @EndQtr >= @StrQtr THEN (@EndYr - @StrYr) ELSE (@EndYr - @StrYr-1) END;
--DECLARE @QtrDiff int = CASE WHEN @EndQtr > @StrQtr THEN (@EndQtr - @StrQtr) ELSE (@StrQtr - @EndQtr) END + (@YearDiff * 4)
-- Below Line modified by Rakesh
DECLARE @QtrDiff int = (@EndQtr - @StrQtr)+ (@YearDiff * 4) + 1
DECLARE @PerQtrValue Numeric(17,4) = @CnV /CASE WHEN @QtrDiff = 0 THEN 1 ELSE @QtrDiff END
SET @YearCount = @YearDiff
WHILE @YearCount >= 0
BEGIN
IF @YearCount = @YearDiff
Begin
SET @QtrCount = @EndQtr
End
ELSE IF @YearCount = 0
BEGIN
SET @QtrCount = 5-@StrQtr
END
ELSE
BEGIN
SET @QtrCount = 4
END
INSERT INTO @temptable
Select @EndYr [Year], @PerQtrValue * @QtrCount YearShare
SET @EndYr = @EndYr - 1
SET @YearCount = @YearCount - 1
END
--While @QtrDiff >0
--BEGIN
--IF @YearDiff <=1
--BEGIN
--SET @QtrCount = @EndQtr
--END
--IF @QtrDiff < @QtrCount
-- SET @QtrCount = @QtrDiff
--INSERT INTO @temptable
--Select @EndYr [Year], @PerQtrValue * @QtrCount YearShare
--SET @EndYr = @EndYr - 1
--SET @QtrDiff = @QtrDiff - @QtrCount
--END
RETURN
end
Thank you so very much for your time and attention.
Most sincerely,
Matt Warren
December 17, 2015 at 2:09 pm
Hell Alan,
Thank you so very much for taking the time to help me.
I am going to try this now.
Most sincerely,
Matt
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply