April 29, 2016 at 9:41 am
hi Guys, Gals-
I have the following query which works well. However, changing this to reside inside a table function has been challenging. Any suggestions?
--===== Suppress the auto-display of rowcounts for appearance and speed
SET NOCOUNT ON
--===== Declare some local control variables
-- These could be parameters in a stored proc
DECLARE @Year VARCHAR(8)
DECLARE @Code VARCHAR(30)
--===== Change these to vary the output
SET @Year = 'ALL' --'ALL' returns all years or change to a 4 digit year here
SET @Code = 'ALL' --'ALL' returns all Codes or change to a valid Code name here
--===== Product the SUM of Usage by Code, year, and month report
SELECT Rate_Cat_Cd = CASE --Changes Rate_Cat_Cd to 'Grand Total' when appropriate
WHEN GROUPING(Rate_Cat_Cd) = 1
AND GROUPING([Year]) = 1
THEN 'GrandTotal'
ELSE Rate_Cat_Cd
END,
[Year] = CASE --Changes [Year] to 'SubTotal' or blank when appropriate
WHEN GROUPING(Rate_Cat_Cd) = 0
AND GROUPING([Year]) = 0
THEN [Year]
WHEN GROUPING(Rate_Cat_Cd) = 0
AND GROUPING([Year]) = 1
THEN 'SubTotal'
ELSE ' '
END,
' Jan' = STR(SUM(CASE WHEN [Month] = 01 THEN KWH ELSE 0 END),8,2),
' Feb' = STR(SUM(CASE WHEN [Month] = 02 THEN KWH ELSE 0 END),8,2),
' Mar' = STR(SUM(CASE WHEN [Month] = 03 THEN KWH ELSE 0 END),8,2),
' Apr' = STR(SUM(CASE WHEN [Month] = 04 THEN KWH ELSE 0 END),8,2),
' May' = STR(SUM(CASE WHEN [Month] = 05 THEN KWH ELSE 0 END),8,2),
' Jun' = STR(SUM(CASE WHEN [Month] = 06 THEN KWH ELSE 0 END),8,2),
' Jul' = STR(SUM(CASE WHEN [Month] = 07 THEN KWH ELSE 0 END),8,2),
' Aug' = STR(SUM(CASE WHEN [Month] = 08 THEN KWH ELSE 0 END),8,2),
' Sep' = STR(SUM(CASE WHEN [Month] = 09 THEN KWH ELSE 0 END),8,2),
' Oct' = STR(SUM(CASE WHEN [Month] = 10 THEN KWH ELSE 0 END),8,2),
' Nov' = STR(SUM(CASE WHEN [Month] = 11 THEN KWH ELSE 0 END),8,2),
' Dec' = STR(SUM(CASE WHEN [Month] = 12 THEN KWH ELSE 0 END),8,2),
' Total' = STR(SUM(KWH),8,2),
' ' = CASE WHEN GROUPING([Year])=1 THEN CHAR(13)+CHAR(13) ELSE '' END --Makes group breaks at sub-totals
FROM (--Derived table does all the necessary conversions and pre-totals
SELECT Rate_Cat_Cd,
[Year] = CAST(YEAR(Bill_Usage_Mth_Dt) AS CHAR(8)),
[Month] = MONTH(Bill_Usage_Mth_Dt),
KWH = SUM(Usage_Qty)
FROM dbo.Cust_Data
WHERE YEAR(Bill_Usage_Mth_Dt) = CASE
WHEN @Year = 'ALL' THEN STR(YEAR(Bill_Usage_Mth_Dt),8)
ELSE @Year
END
AND Rate_Cat_Cd = CASE
WHEN @Code = 'ALL'
THEN Rate_Cat_Cd
ELSE @Code
END
GROUP BY Rate_Cat_Cd, CAST(YEAR(Bill_Usage_Mth_Dt) AS CHAR(8)),MONTH(Bill_Usage_Mth_Dt)
) d --End derived table
GROUP BY Rate_Cat_Cd,[Year] WITH ROLLUP
ORDER BY GROUPING(Rate_Cat_Cd),Rate_Cat_Cd,GROUPING([Year]), [Year]
April 29, 2016 at 9:52 am
fergfamster (4/29/2016)
hi Guys, Gals-I have the following query which works well. However, changing this to reside inside a table function has been challenging. Any suggestions?
--===== Suppress the auto-display of rowcounts for appearance and speed
SET NOCOUNT ON
--===== Declare some local control variables
-- These could be parameters in a stored proc
DECLARE @Year VARCHAR(8)
DECLARE @Code VARCHAR(30)
--===== Change these to vary the output
SET @Year = 'ALL' --'ALL' returns all years or change to a 4 digit year here
SET @Code = 'ALL' --'ALL' returns all Codes or change to a valid Code name here
--===== Product the SUM of Usage by Code, year, and month report
SELECT Rate_Cat_Cd = CASE --Changes Rate_Cat_Cd to 'Grand Total' when appropriate
WHEN GROUPING(Rate_Cat_Cd) = 1
AND GROUPING([Year]) = 1
THEN 'GrandTotal'
ELSE Rate_Cat_Cd
END,
[Year] = CASE --Changes [Year] to 'SubTotal' or blank when appropriate
WHEN GROUPING(Rate_Cat_Cd) = 0
AND GROUPING([Year]) = 0
THEN [Year]
WHEN GROUPING(Rate_Cat_Cd) = 0
AND GROUPING([Year]) = 1
THEN 'SubTotal'
ELSE ' '
END,
' Jan' = STR(SUM(CASE WHEN [Month] = 01 THEN KWH ELSE 0 END),8,2),
' Feb' = STR(SUM(CASE WHEN [Month] = 02 THEN KWH ELSE 0 END),8,2),
' Mar' = STR(SUM(CASE WHEN [Month] = 03 THEN KWH ELSE 0 END),8,2),
' Apr' = STR(SUM(CASE WHEN [Month] = 04 THEN KWH ELSE 0 END),8,2),
' May' = STR(SUM(CASE WHEN [Month] = 05 THEN KWH ELSE 0 END),8,2),
' Jun' = STR(SUM(CASE WHEN [Month] = 06 THEN KWH ELSE 0 END),8,2),
' Jul' = STR(SUM(CASE WHEN [Month] = 07 THEN KWH ELSE 0 END),8,2),
' Aug' = STR(SUM(CASE WHEN [Month] = 08 THEN KWH ELSE 0 END),8,2),
' Sep' = STR(SUM(CASE WHEN [Month] = 09 THEN KWH ELSE 0 END),8,2),
' Oct' = STR(SUM(CASE WHEN [Month] = 10 THEN KWH ELSE 0 END),8,2),
' Nov' = STR(SUM(CASE WHEN [Month] = 11 THEN KWH ELSE 0 END),8,2),
' Dec' = STR(SUM(CASE WHEN [Month] = 12 THEN KWH ELSE 0 END),8,2),
' Total' = STR(SUM(KWH),8,2),
' ' = CASE WHEN GROUPING([Year])=1 THEN CHAR(13)+CHAR(13) ELSE '' END --Makes group breaks at sub-totals
FROM (--Derived table does all the necessary conversions and pre-totals
SELECT Rate_Cat_Cd,
[Year] = CAST(YEAR(Bill_Usage_Mth_Dt) AS CHAR(8)),
[Month] = MONTH(Bill_Usage_Mth_Dt),
KWH = SUM(Usage_Qty)
FROM dbo.Cust_Data
WHERE YEAR(Bill_Usage_Mth_Dt) = CASE
WHEN @Year = 'ALL' THEN STR(YEAR(Bill_Usage_Mth_Dt),8)
ELSE @Year
END
AND Rate_Cat_Cd = CASE
WHEN @Code = 'ALL'
THEN Rate_Cat_Cd
ELSE @Code
END
GROUP BY Rate_Cat_Cd, CAST(YEAR(Bill_Usage_Mth_Dt) AS CHAR(8)),MONTH(Bill_Usage_Mth_Dt)
) d --End derived table
GROUP BY Rate_Cat_Cd,[Year] WITH ROLLUP
ORDER BY GROUPING(Rate_Cat_Cd),Rate_Cat_Cd,GROUPING([Year]), [Year]
What's the challenge of adding the following header to your query?
CREATE FUNCTION FunctionName(
@Year VARCHAR(8),
@Code VARCHAR(30)
)
RETURNS TABLE AS
RETURN
EDIT: Hit the post button accidentally
April 29, 2016 at 9:53 am
Just FYI, this is the function I tried.
Create FUNCTION RateClassTotals ( @Year VARCHAR(8),@Code VARCHAR(30) )
RETURNS @RateClassTable TABLE
(
Rate_Cat_Cd varchar(10),
Year varchar(20),
Jan varchar(25),
Feb varchar(25),
March varchar(25),
April varchar(25),
May varchar(25),
June varchar(25),
July varchar(25),
August varchar(25),
September varchar(25),
October varchar(25),
November varchar(25),
December varchar(25),
Total varchar(25)
)
AS
BEGIN
--===== Product the SUM of Usage by Code, year, and month report
SELECT Rate_Cat_Cd = CASE --Changes Rate_Cat_Cd to 'Grand Total' when appropriate
WHEN GROUPING(Rate_Cat_Cd) = 1
AND GROUPING([Year]) = 1
THEN 'GrandTotal'
ELSE Rate_Cat_Cd
END,
[Year] = CASE --Changes [Year] to 'SubTotal' or blank when appropriate
WHEN GROUPING(Rate_Cat_Cd) = 0
AND GROUPING([Year]) = 0
THEN [Year]
WHEN GROUPING(Rate_Cat_Cd) = 0
AND GROUPING([Year]) = 1
THEN 'SubTotal'
ELSE ' '
END,
' Jan' = STR(SUM(CASE WHEN [Month] = 01 THEN KWH ELSE 0 END),8,2),
' Feb' = STR(SUM(CASE WHEN [Month] = 02 THEN KWH ELSE 0 END),8,2),
' Mar' = STR(SUM(CASE WHEN [Month] = 03 THEN KWH ELSE 0 END),8,2),
' Apr' = STR(SUM(CASE WHEN [Month] = 04 THEN KWH ELSE 0 END),8,2),
' May' = STR(SUM(CASE WHEN [Month] = 05 THEN KWH ELSE 0 END),8,2),
' Jun' = STR(SUM(CASE WHEN [Month] = 06 THEN KWH ELSE 0 END),8,2),
' Jul' = STR(SUM(CASE WHEN [Month] = 07 THEN KWH ELSE 0 END),8,2),
' Aug' = STR(SUM(CASE WHEN [Month] = 08 THEN KWH ELSE 0 END),8,2),
' Sep' = STR(SUM(CASE WHEN [Month] = 09 THEN KWH ELSE 0 END),8,2),
' Oct' = STR(SUM(CASE WHEN [Month] = 10 THEN KWH ELSE 0 END),8,2),
' Nov' = STR(SUM(CASE WHEN [Month] = 11 THEN KWH ELSE 0 END),8,2),
' Dec' = STR(SUM(CASE WHEN [Month] = 12 THEN KWH ELSE 0 END),8,2),
' Total' = STR(SUM(KWH),8,2),
' ' = CASE WHEN GROUPING([Year])=1 THEN CHAR(13)+CHAR(13) ELSE '' END --Makes group breaks at sub-totals
FROM (--Derived table does all the necessary conversions and pre-totals
SELECT Rate_Cat_Cd,
[Year] = CAST(YEAR(Bill_Usage_Mth_Dt) AS CHAR(8)),
[Month] = MONTH(Bill_Usage_Mth_Dt),
KWH = SUM(Usage_Qty)
FROM dbo.Cust_Data
WHERE YEAR(Bill_Usage_Mth_Dt) = CASE
WHEN @Year = 'ALL' THEN STR(YEAR(Bill_Usage_Mth_Dt),8)
ELSE @Year
END
AND Rate_Cat_Cd = CASE
WHEN @Code = 'ALL'
THEN Rate_Cat_Cd
ELSE @Code
END
GROUP BY Rate_Cat_Cd, CAST(YEAR(Bill_Usage_Mth_Dt) AS CHAR(8)),MONTH(Bill_Usage_Mth_Dt)
) d --End derived table
GROUP BY Rate_Cat_Cd,[Year] WITH ROLLUP
ORDER BY GROUPING(Rate_Cat_Cd),Rate_Cat_Cd,GROUPING([Year]), [Year]
RETURN
END
April 29, 2016 at 9:56 am
fergfamster (4/29/2016)
Just FYI, this is the function I tried.
The function you tried has no RETURN. All functions need to return something.
Also, ORDER BY is not allowed in functions and views, unless it's used with the TOP clause. Even when allowed, the order is not guaranteed to be correct when being called.
April 29, 2016 at 9:58 am
Lol, well I guess nothing. I was assuming the returns could only be in the top header of what i was selecting from after the begins. thanks!
April 29, 2016 at 10:04 am
Also you have created a multi statement function so you need to insert into you table variable in order to return data. See below for the differences between and inline and multi statement
CREATE FUNCTION dbo.InlineStatement
(
)
RETURNS TABLE
AS
RETURN
(
SELECT Col FROM tbl
)
CREATE FUNCTION dbo.MultiStatement
(
)
RETURNS
@Tbl TABLE
(
Col1 int
)
AS
BEGIN
INSERT INTO @Tbl
(Col1)
SELECT col from tbl
RETURN
END
April 29, 2016 at 10:14 am
Glad you got it.
BEGIN...END is only needed when a function has multiple statements. Multiple statement table-valued functions are bad for performance, while inLine table-valued functions won't affect performance and would work as parametrized views.
April 29, 2016 at 10:19 am
Thanks Louis!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply