Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Inverse of a SQL Function

Posted on 2016-08-12
Medium Priority
106 Views
Last Modified: 2016-08-12
I'm trying to find a way to create a formula to produce the inverse of this function. When writing the answer can you also write down what happens at each step? I tried to break down the formula to see what happens when we do (@StartQtr/10*10) since it looks like a redundant statement but I couldnt see the answer.

``````GO

/****** Object:  UserDefinedFunction [dbo].[FutureQtr_fn]    Script Date: 08/12/2016 11:52:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- This function takes a qtr (yyyyq), and adds the number of qtrs, and returns the future qtr
--e.g. FutureQtr (20154,7) returns 20173
CREATE FUNCTION [dbo].[FutureQtr_fn] (@StartQtr int,@AddlQtrs int)

Returns int
AS BEGIN
RETURN
(@StartQtr/10*10)+ -- Current Year (yyyy)
((@StartQtr%(@StartQtr/10*10) +@AddlQtrs - 1)/4)*10 -- add'l years (n0)
+(@StartQtr%(@StartQtr/10*10) +@AddlQtrs-1)%4  + 1 -- new qtr (n)
END

GO
``````

Thanks
0
Question by:endurance
• 3
• 3
6 Comments

LVL 70

Expert Comment

ID: 41754377
What do you mean by "inverse"?  Subtract qtrs?
0

Author Comment

ID: 41754380
Yes
0

LVL 70

Assisted Solution

Scott Pletcher earned 2000 total points
ID: 41754384
Personally I'd adjust the code's method so that you can just pass in a negative number of qtrs when you want to back up:

SELECT dbo.FutureQtr_fn(20154,7), dbo.FutureQtr_fn(20173,-7)

ALTER FUNCTION [dbo].[FutureQtr_fn]
(
@StartQtr int,
@AddlQtrs int
)
RETURNS int
AS
BEGIN
RETURN (
SELECT (total_qtrs / 4 * 10) + (total_qtrs % 4 + 1)
FROM (
SELECT ((@StartQtr/10*4)+(@StartQtr%10)+(@AddlQtrs-1)) AS total_qtrs
) AS calc_new_qtr
)
END /*FUNCTION*/
0

Author Comment

ID: 41754442
that doesn't really work the way I need. I'll be creating a new function call it dbo.PastQtr_fn and when I write select dbo.PastQtr_fn(20154,7) I should get 20141.
0

LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 41754468
CREATE FUNCTION [dbo].[PastQtr_fn]
(
@StartQtr int,
@PastQtrs int
)
RETURNS int
AS
BEGIN
RETURN (
SELECT (total_qtrs / 4 * 10) + (total_qtrs % 4 + 1)
FROM (
SELECT ((@StartQtr/10*4)+(@StartQtr%10)-(@PastQtrs+1)) AS total_qtrs
) AS calc_new_qtr
)
END /*FUNCTION*/
0

Author Closing Comment

ID: 41754476
Thank you so much.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
###### Suggested Courses
Course of the Month10 days, 3 hours left to enroll

#### 571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.