February 3, 2012 at 4:26 pm
Hello All,
I'd like this code to be re-useable from various procedures.
If a function can only return one value do I need two functions?
Any guidance would be would be appreciated.
Critiques of my date logic would also be welcome.
Thanks...
DECLARE @Period varchar(50)
DECLARE @End_Date DATETIME
DECLARE @Start_Date DATETIME
--SET @Period= 'MostRecentYearToDate'
--SET @Period= 'MostRecenCompletedYear'
--SET @Period= 'MostRecentThreeMonthPeriod'
--SET @Period= 'MostRecentThreeMonthPeriod'
SET @Period= 'MostRecenCompleteQuarter'
IF @Period = 'MostRecentYearToDate'
BEGIN
SET @End_Date = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1
--SET @Start_Date =DATEADD(yy,DATEDIFF(yy,0,@End_Date),0)
SET @Start_Date =DATEADD(yy,-1,@End_Date)
PRINT @Start_Date
PRINT @End_Date
END
IF @Period = 'MostRecenCompletedYear'
BEGIN
SET @End_Date = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1
SET @Start_Date = DATEADD(yy,-1,@End_Date)
PRINT @Start_Date
PRINT @End_Date
END
IF @Period = 'MostRecentThreeMonthPeriod'
BEGIN
SET @End_Date = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1
SET @Start_Date = DATEADD(mm,-3,@End_Date)
PRINT @Start_Date
PRINT @End_Date
END
IF @Period = 'MostRecenCompleteQuarter'
BEGIN
SET @End_Date =
(
SELECT
CASE
WHEN MONTH(GETDATE()) IN (1,2,3) THEN CAST(YEAR(GETDATE()) - 1 AS VARCHAR) + '1231'
WHEN MONTH(GETDATE()) IN (4,5,6) THEN CAST(YEAR(GETDATE()) AS VARCHAR) + '0331'
WHEN MONTH(GETDATE()) IN (7,8,9) THEN CAST(YEAR(GETDATE()) AS VARCHAR) + '0630'
WHEN MONTH(GETDATE()) IN (10,11,12) THEN CAST(YEAR(GETDATE()) AS VARCHAR) + '0930'
END)
SET @Start_Date = DATEADD(mm,-3,@End_Date)
PRINT @Start_Date
PRINT @End_Date
END
February 3, 2012 at 5:44 pm
You could return the two calculated values via parameters by using the "OUTPUT" keyword.
February 5, 2012 at 1:21 am
Hi Chrissy,
You can always use an iTVF or Inline Table Valued Function for such a thing. They're very fast. This one will look a bit odd to you but think of it as nothing more than a hidden CASE statement on steroids. Here's the function (I'm not sure that your two yearly functions are correct but I left them as you had them).
CREATE FUNCTION dbo.GetTimePeriod
(
@Period VARCHAR(50)
)
RETURNS TABLE AS
RETURN
SELECT Start_Date = DATEADD(yy,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1),
End_Date = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1
WHERE @Period = 'MostRecentYearToDate'
UNION ALL
SELECT Start_Date = DATEADD(yy,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1),
End_Date = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1
WHERE @Period = 'MostRecentCompletedYear'
UNION ALL
SELECT Start_Date = DATEADD(mm,-3,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1),
End_Date = DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1
WHERE @Period = 'MostRecentThreeMonthPeriod'
UNION ALL
SELECT Start_Date = DATEADD(qq,DATEDIFF(qq,0,DATEADD(mm,-3,GETDATE())),0),
End_Date = DATEADD(qq,DATEDIFF(qq,0,GETDATE()),-1)
WHERE @Period = 'MostRecentCompleteQuarter'
;
And, yes, I did redo your "MostRecentCompleteQuarter" function.
Here's one way to use it...
DECLARE @End_Date DATETIME,
@Start_Date DATETIME
;
SELECT @Start_Date = Start_Date,
@End_Date = End_Date
FROM dbo.GetTimePeriod('MostRecentCompleteQuarter')
;
PRINT @Start_Date;
PRINT @End_Date;
... or you could just use it like any table and join to it.
--Jeff Moden
February 6, 2012 at 7:01 pm
Jeff,
Thanks for the code and education on Inline Table Valued Functions! That should work.
I considerd a calendar/date period table but since there was no business days or holiday requirements I did not use one although I am still considering Mr. Celko's response...
Thanks to all for their responses.
February 7, 2012 at 4:34 am
A table-valued UDF is a function that accepts parameters and returns the results in the form of a table. This type of function is special because it returns a table that you can query the results of and join with other tables. In SQL Server 2005, field values from other tables may be passed into the function during a join operation to return a record based result. To accomplish this, you must use SQL Server 2005’s APPLY operator.
It can be difficult to know when it is appropriate to use a VIEW vs. when it is appropriate to use a table-valued UDF. VIEWs are a great tool for data abstraction, combining data, and logically using subsets of data. I like to use table-valued UDFs when I need to use one or more values from different tables in a join operation where some type of calculation needs to be done and an aggregation returned.
Scalar-valued functions
A scalar-valued UDF accepts parameters and, ultimately, returns a single, atomic value. There are seven reasons why these types of functions are different than stored procedures in the database engine.
* You cannot modify data inside of a UDF.
* A scalar-valued UDF returns only one value, where a stored procedure can have numerous OUTPUT parameters.
* You can use scalar-valued UDFs as the default value for a column in a table.
* Scalar-valued UDFs are an easy way to define constant values to use in your database environment.
* You can pass field values as parameters into UDFs.
* You can nest scalar function calls. This means that you can pass a call to a scalar-valued function to another function or stored procedure.
* You can use the results from scalar-valued UDFs as criteria in a WHERE statement. Although you can do it, this is typically not a good idea. (Later in the article, I’ll explain why I try to avoid this common practice.)
There are two types of scalar-valued UDFs: deterministic and non-deterministic. Recognizing the determinism of the functions that are created is important. An example of the importance is the creation of indexed views. One of the many restrictions of creating an index on a view is that the view definition cannot use a non-deterministic function.
Deterministic
A deterministic UDF always returns the same result with the same set of input parameters. Some examples of deterministic functions are the system functions MONTH(), YEAR(), and ISNULL().
Non-deterministic
A non-deterministic UDF is can potentially return a different value each time it is called with the same set of input parameters. Some examples of non-deterministic functions are the system functions GETDATE(), NEWID(), and @@CONNECTIONS.
Two examples of UDFs
Before presenting the examples, I will set up my SalesHistory table and load data into it:
IF OBJECT_ID('SalesHistory')>0
DROP TABLE SalesHistory;
CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
DECLARE @i SMALLINT
SET @i = 1
WHILE (@i <=1000)
BEGIN
INSERT INTO SalesHistory(Product, SaleDate, SalePrice)
VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))
INSERT INTO SalesHistory(Product, SaleDate, SalePrice)
VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))
INSERT INTO SalesHistory(Product, SaleDate, SalePrice)
VALUES('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))
SET @i = @i + 1
END
GO
The first UDF I will look at is the scalar-valued UDF. The script below defines a function named dbo.udf_GetProductSales that accepts three parameters and returns a MONEY value. The function uses the three input parameters as criteria in calculating the total sales from the SalesHistory table.
CREATE FUNCTION dbo.udf_GetProductSales
(
@Product VARCHAR(10),
@BeginDate DATETIME,
@EndDate DATETIME
)
RETURNS MONEY
AS
BEGIN
DECLARE @Sales MONEY
SELECT @Sales = SUM(SalePrice)
FROM SalesHistory
WHERE
Product = @Product AND
SaleDate BETWEEN @BeginDate AND @EndDate
RETURN(@Sales)
END
The script below calls the UDF created in the above script. Note: The schema the function belongs to must be used in the call. In this case, the function belongs to the dbo schema.
SELECT dbo.udf_GetProductSales('PoolTable', '1/1/1990', '1/1/2000')
I usually discourage using scalar-valued UDFs in a WHERE criteria statement because, for every record considered in the query, the scalar-valued function will be called. This means that a function used in the WHERE criteria will cause a scan of the values being searched, which is going to be slower than if an index is able to be used. (I will provide more details on this concept in a future article.)
Although the use of a correlated sub-query is sometimes confusing and complicated, the use of them can help solve some of the more challenging query problems. While using these special queries is useful, they only return one column of data. You can use the upgraded table-valued UDFs in SQL Server 2005 to overcome this shortcoming. I’ll show you how to use the APPLY operator to accept column values from a table and return a table-result of correlated values.
CREATE FUNCTION dbo.udf_GetProductSalesTable
(
@Product VARCHAR(10),
@SaleID INT
)
RETURNS @SalesTable TABLE
(
SalesTotal MONEY,
SalesCount INT
)
BEGIN
INSERT INTO @SalesTable(SalesTotal, SalesCount)
SELECT
SUM(SalePrice), COUNT(SaleID)
FROM
SalesHistory
WHERE
Product = @Product AND
SaleID <= @SaleID
RETURN
END
GO
The above function accepts the particular product for which we were searching, along with the SaleID from the SalesHistory table. From the function definition, you can see that the function returns a table named @SalesTable that contains two columns: SalesTotal and SalesCount. The body of the function inserts aggregate values into the @SalesTable table variable based upon the input parameters.
The following code uses the APPLY operator to invoke the table-valued function with the values from the SalesHistory table. (Note: Logically, you may want to use a JOIN operator here, but it is not necessary. The APPLY operator essentially does the “JOIN” for us by applying the values from the SalesHistory table to the table-valued function. In a sense, this code works the same way a correlated sub-query does, except that it can return multiple correlated values.)
SELECT * FROM SalesHistory sh
CROSS APPLY dbo.udf_GetProductSalesTable(sh.Product, sh.SaleID)
ORDER BY sh.SaleID ASC
http://stackoverflow.com/questions/7789939/how-do-i-use-rowcount-in-function-in-sqlserver
http://www.devx.com/getHelpOn/10MinuteSolution/16499/1954
Search Engine Optimizing | Search Engine Marketing | Social Media Marketing | Pay Per Clicks
February 7, 2012 at 8:58 am
Chrissy321 (2/6/2012)
Jeff,Thanks for the code and education on Inline Table Valued Functions! That should work.
I considerd a calendar/date period table but since there was no business days or holiday requirements I did not use one although I am still considering Mr. Celko's response...
Thanks to all for their responses.
You bet, Chrissy. Thanks for the feedback.
On the subject of Calendar tables... They're good for a whole lot of things and not so good for other things. People tend to make them too wide and fill them with columns that are a whole lot easier to do with just a couple of simple formulas like you did... especially when simple scalar results are all that's needed.
When building and using a Calendar table, my recommendation is to be a bit conservative as to what you actually put in it. Think about what you'll be using it for and what kind of lookups you need rather than trying to build a "panacea".
--Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply