SQL Function Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Function Help

Author

Topic

cwestern
Starting Member

7 Posts

Posted - 2013-05-14 : 09:37:12

I'm writing a sql function that takes in two parameters @QuarterAudited and @AuditDate. Our fiscal year here starts in May and ends in April. I'm trying to evaluate the @QuarterAudited and @AuditDate to see if something is Past Due(P) according to the @QuarterAudited. If's is complete it will get a (C).

Am I writing this the best way? I don't think it's correct because it's now Fiscal Year 14' and things are showing up as (P) Past Due when they aren't. My logic may be wrong.




USE [TSSWork]
GO

CREATE FUNCTION [dbo].[IsAssetAudited]
    (
      @QuarterAudited CHAR(30)
    , @AuditDate DATETIME
    )
RETURNS CHAR(1)
AS 
    BEGIN 

        DECLARE @1stQtrStartDate DATETIME
          , @2ndQtrStartDate DATETIME
          , @3rdQtrStartDate DATETIME
          , @4thQtrStartDate DATETIME
          , @New1stQtrStartDate DATETIME
          , @ReturnValue CHAR(1)
    
        SELECT  @1stQtrStartDate = CAST('05/01/'
                + CAST(DATEPART(yy, GETDATE()) AS VARCHAR(10)) AS DATETIME)
                - 365
              , @2ndQtrStartDate = CAST('08/01/'
                + CAST(DATEPART(yy, GETDATE()) AS VARCHAR(10)) AS DATETIME)
                - 365
              , @3rdQtrStartDate = CAST('11/01/'
                + CAST(DATEPART(yy, GETDATE()) AS VARCHAR(10)) AS DATETIME)
                - 365
              , @4thQtrStartDate = CAST('02/01/'
                + CAST(DATEPART(yy, GETDATE()) AS VARCHAR(10)) AS DATETIME)
              , @New1stQtrStartDate = CAST('05/01/'
                + CAST(DATEPART(yy, GETDATE()) AS VARCHAR(10)) AS DATETIME)


        SELECT  @ReturnValue = CASE WHEN @QuarterAudited = '1ST QUARTER'
                                         AND @AuditDate NOT BETWEEN @1stQtrStartDate
                                                        AND   @2ndQtrStartDate
                                         OR @QuarterAudited = '1ST QUARTER'
                                         AND @AuditDate < @1stQtrStartDate
                                         OR @QuarterAudited = '1ST QUARTER'
                                         AND @AuditDate IS NULL THEN 'P'
                                    WHEN @QuarterAudited = '2ND QUARTER'
                                         AND @AuditDate NOT BETWEEN @2ndQtrStartDate
                                                        AND   @3rdQtrStartDate
                                         OR @QuarterAudited = '2ND QUARTER'
                                         AND @AuditDate < @2ndQtrStartDate
                                         OR @QuarterAudited = '2ND QUARTER'
                                         AND @AuditDate IS NULL THEN 'P'
                                    WHEN @QuarterAudited = '3RD QUARTER'
                                         AND @AuditDate NOT BETWEEN @3rdQtrStartDate
                                                        AND   @4thQtrStartDate
                                         OR @QuarterAudited = '3RD QUARTER'
                                         AND @AuditDate < @3rdQtrStartDate
                                         OR @QuarterAudited = '3RD QUARTER'
                                         AND @AuditDate IS NULL THEN 'P'
                                    WHEN @QuarterAudited = '4TH QUARTER'
                                         AND @AuditDate NOT BETWEEN @4thQtrStartDate
                                                        AND   @New1stQtrStartDate
                                         OR @QuarterAudited = '4TH QUARTER'
                                         AND @AuditDate < @4thQtrStartDate
                                         OR @QuarterAudited = '4TH QUARTER'
                                         AND @AuditDate IS NULL THEN 'P'
                                    WHEN @QuarterAudited = 'NOT ASSIGNED'
                                    THEN 'U'
                                    ELSE 'C'
                               END 


        RETURN @ReturnValue

    END

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-05-14 : 11:36:25

In re: "My logic may be wrong" - If you are not getting the results you want, the logic is wrong ;-)

1) Subtracting 365 does not take into account leap years. Why not use DateDiff to subtract one year instead.
2) If we are already in May, do you want to go back one year (i.e., previous fiscal year)?
3) Some sample data and expected results might allow us to better help you.

=================================================
I am not one of those who in expressing opinions confine themselves to facts. (Mark Twain)

cwestern
Starting Member

7 Posts

Posted - 2013-05-14 : 11:48:05

Well our Fiscal Year starts in May. So its May 2013 now but we'll call this Fiscal Year 14'.

For Example if you enter the date 05/07/13 and 1ST QUARTER it will return P which is incorrect.

Qtr 1 - May, June, July
Qtr 2 - Aug, Sept, Oct
Qtr 3 - Nov, Dec, Jan
Qtr 4 - Feb, Mar, Apr

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-05-14 : 14:07:22

If you run this function today, are these your Quarters?
Q1: 2014-05-01 -> 2014-07-31
Q2: 2014-08-01 -> 2014-10-31
Q3: 2014-11-01 -> 2015-01-31
Q4: 2015-02-01 -> 2015-04-30

On what function run date would the quarters switch to the next year?
Q1: 2015-05-01 -> 2015-07-31
Q2: 2015-08-01 -> 2015-10-31
Q3: 2016-11-01 -> 2016-01-31
Q4: 2016-02-01 -> 2016-04-30

Be One with the Optimizer
TG

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-05-14 : 15:31:48

If I was you I'd create a table that I can join to rather that trying to do all that in a function. However, without knowing what your logic should be, you might want to try wrapping the logic in parenthises to make it clear how you want the ANDs and ORs applied.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-05-14 : 17:27:27

I ran your script and output the quarters being generated by your first SELECT. Are these the values you expect?
Q1 Q2 Q3 Q4 Q1New
2012-05-01 2012-08-01 2012-11-01 2013-02-01 2013-05-01

If these are right then the logic dutifully reports that we are in the 1st quarter and the Audit Date is not between 2012-05-01 and 2012-08-01 so the answer is 'P'.

=================================================
I am not one of those who in expressing opinions confine themselves to facts. (Mark Twain)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources