Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Inverse of a SQL Function

Posted on 2016-08-12
Medium Priority
79 Views
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  + 1 -- new qtr (n)
END

GO
``````

Thanks
0
Question by:endurance
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3

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,
)
RETURNS int
AS
BEGIN
RETURN (
SELECT (total_qtrs / 4 * 10) + (total_qtrs % 4 + 1)
FROM (
) 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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
###### Suggested Courses
Course of the Month10 days, 6 hours left to enroll