SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Function Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cwestern
Starting Member

7 Posts

Posted - 05/14/2013 :  09:37:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1644 Posts

Posted - 05/14/2013 :  11:36:25  Show Profile  Reply with Quote
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)
Go to Top of Page

cwestern
Starting Member

7 Posts

Posted - 05/14/2013 :  11:48:05  Show Profile  Reply with Quote
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

Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 05/14/2013 :  14:07:22  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 05/14/2013 :  15:31:48  Show Profile  Reply with Quote
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.
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1644 Posts

Posted - 05/14/2013 :  17:27:27  Show Profile  Reply with Quote
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)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000