Solved

Inverse of a SQL Function

Posted on 2016-08-12
6
42 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

Open in new window


Thanks
0
Comment
Question by:endurance
  • 3
  • 3
6 Comments
 
LVL 69

Expert Comment

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

Author Comment

by:endurance
ID: 41754380
Yes
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 500 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:endurance
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 69

Accepted Solution

by:
ScottPletcher earned 500 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

by:endurance
ID: 41754476
Thank you so much.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now