locked
Custom Function to calculate days RRS feed

  • Question

  • I have 2 tables Documents and Process.  Each document can have 1 to many process. Ex:

    Doc1 

    1. Rcvd 1/9/2010
    2. Sent for Info 1/15/2010
    3. Back to the office 1/30/2010
    4. Completed  1/31/2010

    The days between step 2 and 3 should not be counted, so the number of days between 1 and 2 is 6 plus number of days between 3 and 4 is 1 = Total number to process = 7 DAYS.  I have 2 custom code formulas that calculates this and it is working, but is not working if the document came and was never sent out for information, example:

    Doc 2

    1. Rcvd 1/9/2010
    2. Completed 1/19/2010

    In this case the number of days to process is 10 days.  Below are the formulas that I created and I am totally lost on how to make it work for Document number 2.  PLEASE HELP ME.  I AM TOTALLY LOST!!! 

    Function SentTo(RcvdDate as date, DateAction as date, ActionNr as integer)as integer

    If ActionNr = 2

    Return datediff("d", RcvdDate, DateAction)

    ELSE

    RETURN 0

    End if

    End Function

    Function BackFrom( DateAction as date, ComplDate as date,ActionNr as integer)as integer
     If ActionNr = 3
      Return datediff("d", DateAction, ComplDate)
     ELSE
      RETURN 0
     End if
    End Function

     

           


    Angie Rivera
    • Moved by Cathy Kong Thursday, December 16, 2010 9:46 AM (From:Team Foundation Server - Reporting & Warehouse)
    Tuesday, December 14, 2010 10:44 PM

Answers

  • This is getting a little complicated for me as I am NO SQL expert (more like a beginner).   What you have to do is combine all the data lines into 1 line with all the dates that you have for each document number.  You can then pass them all (what's there) to the function as shown earlier.

    You have to do something like the following:

    Select Docx, Max(DateR), Max(DateS), Max(DateB), Max(DateC)
    from (Select Docx, RecTx, 
              Case when RecTx = 'R' then Datex else NULL end as DateR,
              Case when RecTx = 'S' then Datex else NULL end as DateS,
              Case when RecTx = 'B' then Datex else NULL end as DateB,
              Case when RecTx = 'C' then Datex else NULL end as DateC
              from <table_name>  
    ) as work
    group by Docx

    This should give you a line similiar to the below. If you do not have a specific record type the date column should be NULL

    Document#     DateReceived     DateSent     DateBack     Date Completed

    1                    1/1/2011          1/5/2011     1/10/2011    1/20/2011

    2                    1/5/2011          NULL           NULL           1/15/2011

    This is the best I can do hope it helps.  Good Luck.

     

     

    • Marked as answer by sunnyplace Thursday, January 13, 2011 1:11 PM
    Wednesday, January 12, 2011 10:47 PM

All replies

  • Hi Angie,

    Thanks for posting your question here!

    Could you tell me what kind of document. I think this issue seems not related with Team Foundation Server, could you tell me what the issue belongs to so I can move this thread to the related forum.

    Thanks for your understanding and support.


    Cathy Kong [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, December 15, 2010 8:33 AM
  • I am using SSRS 2005
    Angie Rivera
    Wednesday, December 15, 2010 7:16 PM
  • Hi Angie,

    We can add one more function to calculate the case the document comes and is never sent out for information. In the function, if the DataAction is null, calculate the date difference between the Rcvd and Compleled directly:

    Function GetDays(RcvdDate as date, DateAction as date, DateAction2 as date, ComplDate as date)as integer
    If IsNothing(DateAction) AND IsNothing(DateAction2) 
    Return datediff("d", RcvdDate, ComplDate )
    ELSE
    Return SentTo(RcvdDate, DateAction, 2) + BackFrom(DateAction2, ComplDate, 3)
    End IF
    End Function
    Function SentTo(RcvdDate as date, DateAction as date, ActionNr as integer)as integer
    If ActionNr = 2
    Return datediff("d", RcvdDate, DateAction)
    ELSE
    RETURN 0
    End if
    End Function
    Function BackFrom( DateAction as date, ComplDate as date,ActionNr as integer)as integer
     If ActionNr = 3
     Return datediff("d", DateAction, ComplDate) 
     ELSE
     RETURN 0
     End if
    End Function
    

    Then, please use the new function GetDays to calculate the date difference.

    If there is anything unclear, please feel free to ask.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    • Proposed as answer by pplcallmepsycho Friday, December 17, 2010 5:23 AM
    • Unproposed as answer by sunnyplace Friday, December 17, 2010 7:19 PM
    Friday, December 17, 2010 3:36 AM
  • Jin Chen:

    The formula GetDays that you created, does not work it always calculates the number of dayS from Received Date to Completion Date, it completely ignores the time the document went out for more information. The function should count only the days between option 1 and option 2 and between option 3 and option 4, it should not count the time lapsed between option 2 and option 3.
    Document # 1
    1.Rcvd 1/9/2010
    2.Sent for Info 1/15/2010 ............................6 days
    3.Back to the office 1/30/2010
    4.Completed  1/31/2010 .............................. 1 day

    TOTAL number of days to process is 7 days

    Document # 2

    1.Rcvd 1/9/2010
    2.Completed 1/19/2010 ................................10 days

    TOTAL number of days for Document 2 = 10 days

    PLEASE HELP ME!!!!!!!!!!!!!!!!!!!!!


    Angie Rivera
    Friday, December 17, 2010 7:19 PM
  • It all depend supn how many of the dates wiil you have.  1, 2 3 or all 4.    I would do something like this.

    Function GetDays(d1 as date, d2 as date, d3 as date , d4 as date) as integer

    Count as integer

    If IsNothing(d1) then Count = Count  + 0 else Count = Count + 1).

    If InNothing(d2) then Count = Count + 0 else Count = Count + 2).

    If IsNothing(d3) then Count = Count + 0 else Count = Count + 4).

    If IsNothing(d4) then Count = Count + 0 else Count = Count + 8).

    By using a binary value count you will always have a unique sum.  Just test the sum and it tells you what values you have and can do the necessary calculations within each IF statement

    examples:

    If Count = 0  <-- no values were passed (return error)

    If Count = 1  then d1 only (return error)

    If Count = 7 then d1, d2 and d3 (return datediff(d1,d2))

    If Count = 15 then d1, d2, d3, d4 (return datediff(d1, d2) + datediff(d3, d4)

    Determine all the error values and code them first and then you just have to write code for each valid value

     

     

     

     

     

     

    Sunday, December 19, 2010 10:46 PM
  • JHAB/ OR ANYBODY -  PLEASE HELP!!!:

    I am totally lost and desesperate to make this report work, I tried your function but I do not know how to make it work.  the function that I created is below and this is the way I called it from the report: =code.GetDays(Fields!ActionDate.Value, Fields!ActionDate.Value, Fields!ActionDate.Value, Fields!ActionDate.Value) but I get 0 as a result.  I think I do not know how to write the code for each valid and related to the fields in the table.
    I was able to create 3 functions and I am able to calculate the days using a a SWITCH fornula that is calculating the days in my second group (document) but when I tried to calculate the total days for my first group (employee) it does not work. HOW CAN I SUM that text field, I tried =sum(reportitems!textbox9.Value,"table1_MailType"), textbox 54 uses a scope parameter for an agregate function.  I HAVE TRIED EVERYTHING, I DO NOT KNOW WHAT ELSE TO DO.  I AM SO FRUSTRATED!!!  I did this report in MSACCESS and it was a breeze!!!  are we going backward in technology.  I missed Crystal report, MsAccess....

    Function GetDays(D1 AS date, D2 AS date, D3 AS date, D4 AS date) as integer
    DIM Count as integer

     If  IsNothing(D1) then Count = Count + 0 else Count = Count + 1
     If  IsNothing(D2) then Count = Count + 0 else Count = Count + 2
     If  IsNothing(D3) then Count = Count + 0 else Count = Count + 4
     If  IsNothing(D4) then Count = Count + 0 else Count =Count + 8

     If Count = 0 then return  0
     If Count = 1 then return 0
     If Count = 7 then return datediff("d", D1, D2)
     If Count = 15 then return datediff("d", D1, D2) + datediff("d", d3, d4)
    End Function


    Angie Rivera
    Tuesday, December 21, 2010 3:43 PM
  • Code is below.   However, you need to add a few if's.   There are 16 (0-15) possible combination of dates:   receveived and nothing else, Sent and nothing else, Returned and nothing else, all 4, etc, etc,   See Count = 13.  You have a received, returned and complete.  Since you do not have a sent and do not calculate  returned/complete if seems you have to take the # days between received and complete (this is the same as if you had all 4 dates).

    I've tried to make the code as simple to follow as I can.  Good Luck.

    IF the return value = 0 then you have an error as defined by those date combinations (count value) as defined by you.

    =code.GetDays(Fields!Received.Value, Fields!sent.Value, Fields!Retured.Value, Fields!Completed.Value)

     Function GetDays(D1, D2,  D3 , D4 ) as integer

    ' set error return

    Dim Count as Integer = 0

     

    If IsDate(D1) = True then

       Count = Count + 1

    End if

     

    If IsDate(D2) = True then

       Count = Count + 2

    End if

     

    If IsDate(D3) = True then

       Count = Count + 4

    End if

     

    If IsDate(D4) = True then

       Count = Count + 8

    End if

     

    ' Received, Send, Returned, Complete

    If Count = 15 then

       Count = DateDiff("d", D1, D2) + DateDiff("d", D3, D4)

    End if

     

    ' Received, Returned, Complete

    If Count = 13 then

       Count = DateDiff("d", D1, D4)

    End if

     

    'Received, Complete

    If Count = 9 then

       Count = DateDiff("d", D1, D4)

    End if

    <insert other COUNT = X here>

    Return Count

    End Function

     

    Thursday, December 23, 2010 2:21 PM
  • These are all the values/date combinations you can test for.

    Value (Valid Date)

    1=Received (D1)

    2=Sent (D2)

    4=Returned (D3)

    8=Completed (D4)

     

    0 = no valid dates

    1 = received only

    2=sent only

    3=received and sent

    4=returned only

    5=received and returned

    6=sent and returned

    7=received, sent, returned

    8=completed

    9=received and completed

    10=sent and completed

    11=received, sent, completed

    12=returned and completed

    13=received, returned and completed

    14=send, returned and completed

    15=received, sent, returned, completed

     

     

    Thursday, December 23, 2010 7:41 PM
  • I don't have a lot of experience writing functions, so I can't help there but if you're willing to push the functionality back to SQL Server (assuming you're using a SQL Server back end...) it's actually pretty easy.

    Here's a quick example...

    ---------------------------------------------------------
    -- Create a temp table to hold the sample values
    -- and then populate it
    ---------------------------------------------------------
    IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp
    CREATE TABLE #Temp (DocNum INT, DocStatus VarChar(50), StatusDate DateTime)
    INSERT INTO #Temp Values(1, 'Rcvd', '1/9/2010')
    INSERT INTO #Temp Values(1, 'Sent for Info', '1/15/2010')
    INSERT INTO #Temp Values(1, 'Back to the office', '1/30/2010 ')
    INSERT INTO #Temp Values(1, 'Completed', '1/31/2010')
    INSERT INTO #Temp Values(2, 'Rcvd', '1/9/2010')
    INSERT INTO #Temp Values(2, 'Completed', '1/19/2010')
    
    ---------------------------------------------------------
    -- Final SELECT statment, see inline comments
    ---------------------------------------------------------
    SELECT
    t1.DocNum,
    t1.DocStatus,
    t1.StatusDate AS StatusBegDate,
      ------------------------------------------------------------------------
    CASE WHEN t1.DocStatus = 'Completed' THEN t1.StatusDate --If DocStatus = completed then enddate is same as begdate
       WHEN t2.StatusDate IS NULL THEN GetDate() --If enddate is still null, use the current date
       ELSE t2.StatusDate END AS StatusEndDate, -- otherwise use the enddate as it's provided.
      -------------------------------------------------------------------------
    CASE WHEN t1.DocStatus IN ('Rcvd', 'Back to the office') --If the DocStatus is Rcvd or Back to the office then calc the
       THEN DateDiff(dd, t1.StatusDate, t2.StatusDate) ELSE NULL END AS StatusDays --the days between beg and end, if not, then null
      --------------------------------------------------------------------------
    FROM (
      SELECT
      ROW_NUMBER() OVER (PARTITION BY DocNum ORDER BY StatusDate) AS RN, -- creates an artificial row number for the statuses
      *
      FROM #Temp) AS t1
    LEFT OUTER JOIN (
      SELECT
      ROW_NUMBER() OVER (PARTITION BY DocNum ORDER BY StatusDate) AS RN, -- creates an artificial row number for the statuses
      *
      FROM #Temp) AS t2
    ON t1.DocNum = t2.DocNum AND t1.RN = (t2.RN -1) --shifts the t2 version up a row number so that it's statusdate can be used as the enddate in the main query
    

    HTH,

    Jason


    Jason Long
    Thursday, December 23, 2010 9:06 PM
  • Jhab:

    I tried this but it will not work because I do not have different values for the dates, I have only one table called PROCESS with 2 fields ActionDate, ActionName, so when I called this function from the report this is how the code.getdays looks:

    =code.GetDays(Fields!ActionDate.Value, Fields!ActionDate.Value, Fields!ActionDate.Value, Fields!ActionDate.Value)

    in your code you have different values for received date, sent date, returned date, completed date, how can I get those values from your function.  I am very confused, and I can not make it work.  When I run the code.getdays from the report I get 0 for all the dates.  Below is your code.

    =code.GetDays(Fields!Received.Value, Fields!sent.Value, Fields!Retured.Value, Fields!Completed.Value)

     

     

     


    Angie Rivera
    Monday, January 10, 2011 2:50 PM
  • Angie, If you don't already have an "end date" in in that table, you want to create one by using the date field of the next record. Doing this in a report designer can be tough. They tend not to like putting data from one row on another row. That said, it's easy if you do it on the database side. If you take a look at the SQL sample I provided in my last post, I'm doing exaxctly that. If you copy the sql in to SSMS or into a data set of a test report, you'll see that it yields the the following data.
    DocNum DocStatus StatusBegDate StatusEndDate StatusDays
    1 Rcvd 2010:01:09 00:00:00 2010:01:15 00:00:00 6
    1 Sent for Info 2010:01:15 00:00:00 2010:01:30 00:00:00 NULL
    1 Back to the office 2010:01:30 00:00:00 2010:01:31 00:00:00 1
    1 Completed 2010:01:31 00:00:00 2010:01:31 00:00:00 NULL
    2 Rcvd 2010:01:09 00:00:00 2010:01:19 00:00:00 10
    2 Completed 2010:01:19 00:00:00 2010:01:19 00:00:00 NULL
             

    Jason Long
    Monday, January 10, 2011 3:35 PM
  • This is getting a little complicated for me as I am NO SQL expert (more like a beginner).   What you have to do is combine all the data lines into 1 line with all the dates that you have for each document number.  You can then pass them all (what's there) to the function as shown earlier.

    You have to do something like the following:

    Select Docx, Max(DateR), Max(DateS), Max(DateB), Max(DateC)
    from (Select Docx, RecTx, 
              Case when RecTx = 'R' then Datex else NULL end as DateR,
              Case when RecTx = 'S' then Datex else NULL end as DateS,
              Case when RecTx = 'B' then Datex else NULL end as DateB,
              Case when RecTx = 'C' then Datex else NULL end as DateC
              from <table_name>  
    ) as work
    group by Docx

    This should give you a line similiar to the below. If you do not have a specific record type the date column should be NULL

    Document#     DateReceived     DateSent     DateBack     Date Completed

    1                    1/1/2011          1/5/2011     1/10/2011    1/20/2011

    2                    1/5/2011          NULL           NULL           1/15/2011

    This is the best I can do hope it helps.  Good Luck.

     

     

    • Marked as answer by sunnyplace Thursday, January 13, 2011 1:11 PM
    Wednesday, January 12, 2011 10:47 PM
  • Jhab:

     Finally I got working.

    Thanks a lot for all your help.

     

     


    Angie Rivera
    Thursday, January 13, 2011 1:11 PM