none
Frequency Based Records

    Question

  • Hi All

    A little overview

    we have a intake system where users submit requests and we process them . Some of our requests are recurring which means after we get the original request we create manual requests for that recurring request and process them until the original request expires

    Lets assume all original requests are stored in Table A,Table B and Table C

    Manual requests are going to be steored in Table D.

    We have created a process to generate automatic requests in Table D based on original Request information from Tables A,B,C . I was using a cursor logic which was working fine for generating requests where the recurring frequency selected was Monthly and Quaterly. I needed help in the query to generate records when the original requests came with frequency selected as Weekly or Bi weekly and got help from a differant thread where it was suggested to create a calendar table and then start using the calendar table to generate records irrespective of the frequency and it worked with sample data.

    Below is the Query

    Declare @Sample Table(Requestid int, Receiveddate smalldatetime, frequency varchar(10), assignedanalystid int, requesttypeid int, CustomerName varchar(50), CompletedDate smalldatetime, ExpirationDate smalldatetime)
    Insert @Sample(Requestid, Receiveddate, frequency, assignedanalystid, requesttypeid, CustomerName, CompletedDate, ExpirationDate)
    Select 123456, '12/31/2013', 'Monthly', 123, 34, 'Testing Company', '1/15/2014', '12/31/2014'
    Union All Select 123654, '12/31/2013', 'Weekly', 123, 34, 'Testing Company', '1/15/2014', '12/31/2014'
    Union All Select 125000, '12/31/2013', 'Bi-Weekly', 123, 34, 'Testing Company', '1/15/2014', '12/31/2014';
    
    Select s.Requestid + Row_Number() Over(Partition By s.Requestid Order By c.dt) As Requestid,
      Cast(c.dt As smalldatetime) As ProductionMonth,
      s.assignedanalystid As AssignedAnalyst,
      s.frequency As freqcd,
      Cast(c.dt As smalldatetime) As EnteredDate,
      s.requesttypeid As rptdesc,
      s.CustomerName As groupname,
      s.Requestid As originalrequestid
    From @Sample s
    Inner Join dbo.Calendar c On c.dt Between s.CompletedDate And DateAdd(month, 1, s.ExpirationDate)
    Where (s.frequency = 'Monthly' And c.D = 1)
      Or (s.frequency = 'Weekly' And DateDiff(day, s.CompletedDate, c.dt) % 7 = 0 And c.dt > s.CompletedDate And c.dt <= DateAdd(week, 1, s.ExpirationDate))
      Or (s.frequency = 'Bi-Weekly' And DateDiff(day, s.CompletedDate, c.dt) % 14 = 0 And c.dt > s.CompletedDate And c.dt <= DateAdd(week, 2, s.ExpirationDate))
    Order By originalrequestid, Requestid;

    Original Thread

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/430bcb7d-1ee9-400b-b29a-9db8c2672cf1/generating-records-based-on-frequency?forum=transactsql

    How should i modify the above so that i use a requestid from Table A,Table B,Table C and generate its relevant requests in Table D

    My Original Post

    SET NOCOUNT ON

    GO

    declare @num_of_times int

    declare @count int

    declare @frequency varchar(10)

    declare @num_of_times1 int

    DECLARE @oldrequestid varchar(50),@newrequestid varchar(50)

    DECLARE db_cursor CURSOR FOR

    SELECT Requestid from Request_Customer where requestid in (149016)

    OPEN db_cursor  

    FETCH NEXT FROM db_cursor INTO @oldrequestid

    WHILE @@FETCH_STATUS = 0  

    BEGIN 

      --do work here   

     SET @num_of_times = NULL

    select @num_of_times=datediff(month, receiveddate,expirationdate) from Request_Customer where requestid in (@oldrequestid)

    SET @num_of_times1 = @num_of_times+1

    set @count=0

    WHILE @count < @num_of_times1

    BEGIN

     update table_keys

    set key_id = key_id + 1

    where table_name = 'adhoc'

    Select @newrequestid = key_id from table_keys where table_name = 'adhoc'

    INSERT INTO [dbo].[renoffcyc]

               ([roc_id]

               ,[prodmth]

               ,[opa_id]

               ,[freqcd]

               ,[entereddt]

               ,[rptdesc]

               ,[groupname]

               ,[origrequestid]

               )

      SELECT @newrequestid

               ,convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate))

               ,SR.Assignto

               ,RR.defineschedule

               ,convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate))

    ,SR.rptdesc

               ,RR.CTNAME

               ,@oldrequestid

       FROM dbo].[Request_Customer] RR INNER JOIN SELECTED_CUSTOMER SR

    ON RR.requestid = SR.requestid

    where RR.requestid = @oldrequestid

    set @count=@count+1

    END

    FETCH NEXT FROM db_cursor INTO @oldrequestid

    END  -- Cursor loop


    Example:

    So for example a request comes in Jan 2014 with frequency selected as monthly and the expiration date is Dec 2014. we have to send the user monthly reports until Jan 2015 for which my monthly logic submits requests and one of our team member uses that automatic request generated by the query to process them monthly.

    Sample Data
    Original Request

    Requestid Receiveddate frequency assignedanalystid requesttypeid CustomerName CompletedDate ExpirationDate
    123456 12/31/2013 Monthly 123 34 Testing Company 1/15/2014 12/31/2014

    Monthly Offcycles for original request 123456
    Roc_id ProductionMonth AssignedAnalystid freqcd entereddt rptdesc groupname origrequestid
    123457 2/1/2014 123 Monthly 2/1/2014 34 Testing Company 123456
    123458 3/1/2014 123 Monthly 3/1/2014 34 Testing Company 123456
    123459 4/1/2014 123 Monthly 4/1/2014 34 Testing Company 123456
    123460 5/1/2014 123 Monthly 5/1/2014 34 Testing Company 123456
    123461 6/1/2014 123 Monthly 6/1/2014 34 Testing Company 123456
    123462 7/1/2014 123 Monthly 7/1/2014 34 Testing Company 123456
    123463 8/1/2014 123 Monthly 8/1/2014 34 Testing Company 123456
    123464 9/1/2014 123 Monthly 9/1/2014 34 Testing Company 123456
    123465 10/1/2014 123 Monthly 10/1/2014 34 Testing Company 123456
    123466 11/1/2014 123 Monthly 11/1/2014 34 Testing Company 123456
    123467 12/1/2014 123 Monthly 12/1/2014 34 Testing Company 123456
    123468 1/1/2015 123 Monthly 1/1/2015 34 Testing Company 123456

    Below is sample data for original request and the off cycles that I would like help to generate weekly offcycles and Bi weekly Offcycles

    Original Request
    Requestid Receiveddate frequency assignedanalystid requesttypeid CustomerName CompletedDate ExpirationDate
    123654 12/31/2013 Weekly 123 34 Testing Company 1/15/2014 12/31/2014

    Weekly Offcycles for original request 123456
    Roc_id ProductionMonth AssignedAnalystid freqcd entereddt rptdesc groupname origrequestid
    123655 1/22/2014 123 Weekly 1/22/2014 34 Testing Company 123654
    123656 1/29/014 123 Weekly 1/29/014 34 Testing Company 123654
    123657 2/4/2014 123 Weekly 2/4/2014 34 Testing Company 123654
    123658 2/11/2014 123 Weekly 2/11/2014 34 Testing Company 123654
    123659 2/18/2014 123 Weekly 2/18/2014 34 Testing Company 123654
    123660 2/25/2014 123 Weekly 2/25/2014 34 Testing Company 123654
    123661 3/4/2014 123 Weekly 3/4/2014 34 Testing Company 123654
    123662 3/10/014 123 Weekly 3/10/014 34 Testing Company 123654
    123663 3/17/2014 123 Weekly 3/17/2014 34 Testing Company 123654
    123664 3/24/2014 123 Weekly 3/24/2014 34 Testing Company 123654
    123665 3/31/2014 123 Weekly 3/31/2014 34 Testing Company 123654
    123666 4/7/2014 123 Weekly 4/7/2014 34 Testing Company 123654
    123667 4/11/2014 123 Weekly 4/11/2014 34 Testing Company 123654
    123668 4/18/2014 123 Weekly 4/18/2014 34 Testing Company 123654
    123669 4/25/2014 123 Weekly 4/25/2014 34 Testing Company 123654<//span>

    Thanks


    Vamsi

    Thursday, March 27, 2014 7:06 PM

All replies

  • Just a comment for anyone who works on this who did not see the original post - Vamsi's system uses SQL 2005.

    Tom


    • Edited by Tom Cooper Thursday, March 27, 2014 9:39 PM
    Thursday, March 27, 2014 8:51 PM