none
Proc to Project Expected Payments

    Question

  • I'm not sure what you would call what I need to accomplish.  I know I can do it with cursors, but I think there is probably a better way.

    Test data:

    	DECLARE @Contracts TABLE (
    		VendorName		VARCHAR(100)	NOT NULL,
    		ScheduleID		INT				NOT NULL,
    		Occurs			VARCHAR(250)	NOT NULL,
    		Amount			DECIMAL(18, 2)	NOT NULL,
    		StartDate		DATE			NOT NULL,
    		EndDate			DATE			NOT NULL
    	);
    
    	INSERT INTO @Contracts
    	VALUES	('Pension Group', 3, 'Quarterly: March, June, September, December, on the 1st', 3149.00, '2013-11-08', '2014-05-08'),
    			('Security Corp.', 2, 'Monthly on the 5th', 18000.00, '2013-11-08', '2014-05-08'),
    			('UPS', 1, 'Weekly on Mondays', 1000.00, '2013-11-08', '2014-05-08');
    
    	SELECT	*
    	FROM @Contracts
    	ORDER BY VendorName;
    

    What I need to calculate out rows for each expected payment.  Ignoring the day (Monday, or the 5th, or whatever - I figure as I get closer I'll get that specific), what the right output would be one row per applicable hit between the start and stop dates.  In other words, for UPS there would be about 26 rows, would hit for December and March, and there would be 6 recs for the Security group.  I should probably mention that I have a date table that I will probably need to use.

    As I said above, I can walk these rows by ScheduleID - that tells me weekly, monthly, quarterly - and adds rows for each in probably a cursor,  in a cursor, but it feels like there is better approach.  For the life of me I can't think of it, though.  How would you guys approach it?  If you just have ideas and no code, that's fine.  I'm not looking for anyone to write my app for me.

    Thanks, mates.


    Thanks,
    Corey Furman @ Facebook
    ______________________________________________________
    Please mark posts as answer or helpful when they are.

    Friday, November 08, 2013 9:14 PM

All replies

  • To do this, I would first create a calendar table.  They are very useful for lots of things and will help with this.  See

    http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

    for what a calendar table is, how to create one, and some of the many uses of that table.

    Second, you can't let just any user enter any description of what the payment schedule is.  (Users will misspell words, and you'll never be able to write a piece of code that can process whatever a user might enter.  Instead create a table of allowed payment schedules, which has the allowed payment schedules and a column containing the SQL needed to select the required dates from the calendar table.  (for example since the day of the month in the calendar table (assuming you build one that has the columns built in the above link).  Then all the user needs to do is to select the appropriate schedule from a drop down list when entering a new row in the Contracts table.  Of course, if you get a schedule that you've never seen before, you will need to add a new row to the schedule table.

    Using your sample data, the schedule and contracts tables could look like

    Create Table TestPaymentSchedules(
      PaymentScheduleID int primary key,
      PaymentScheduleName varchar(250) unique not null,
      PaymentScheduleSQL nvarchar(max) not null
    );
    Insert TestPaymentSchedules(PaymentScheduleID, PaymentScheduleName, PaymentScheduleSQL) Values
    (1, 'Quarterly: March, June, September, December, on the 1st', 'AND D = 1 AND M IN (3, 6, 9, 12)'),
    (2, 'Monthly on the 5th', 'AND D = 5'),
    (3, 'Weekly on Mondays', 'AND DW = 2');
    
    Create Table TestContracts(
    	VendorName		VARCHAR(100)	NOT NULL,
    	ScheduleID		INT				NOT NULL,
    	PaymentScheduleID	INT	NOT NULL REFERENCES TestPaymentSchedules(PaymentScheduleID),
    	Amount			DECIMAL(18, 2)	NOT NULL,
    	StartDate		DATE			NOT NULL,
    	EndDate			DATE			NOT NULL
    );
    
    
    INSERT INTO TestContracts
    VALUES	('Pension Group', 3, 1, 3149.00, '2013-11-08', '2014-05-08'),
    		('Security Corp.', 2, 2, 18000.00, '2013-11-08', '2014-05-08'),
    		('UPS', 1, 3, 1000.00, '2013-11-08', '2014-05-08');

    Having done this preliminary work, generating all the payment dates is a single select to build the dynamic sql to do the work and then executing that dynamic sql

    Declare @SQL nvarchar(max);
    
    SELECT @SQL = Stuff((SELECT 'UNION ALL SELECT	c.VendorName, c.ScheduleID, c.PaymentScheduleID, c.Amount, c.StartDate, c.EndDate, d.dt As PaymentDate, p.PaymentScheduleName
      FROM TestContracts c
      INNER JOIN TestPaymentSchedules p On c.PaymentScheduleID = p.PaymentScheduleID 
      And p.PaymentScheduleID = ' + Cast(p.PaymentScheduleID As varchar(11)) + '
      INNER JOIN Calendar d On d.dt Between c.StartDate And c.EndDate '
      + p.PaymentScheduleSQL + '
      '
    FROM TestContracts c
    INNER JOIN TestPaymentSchedules p On c.PaymentScheduleID = p.PaymentScheduleID
    For XML Path(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,10,'')
    
    --Select @SQL;
    Exec(@SQL);
    

    Tom

    Saturday, November 09, 2013 5:25 AM
  • I am away from where I can test out your idea, but I will look into it Monday morning.  I actually have a calendar table (I called it a date table in the OP) and I figured a more correct solution would involve that.

    I dummied up the sample data to give some the idea of what the output would look like.  In reality, there is no free form data entry.  In the my vendor screen, the user has the opportunity to pick a schedule - weekly, monthly, etc - and a date.  using these fields, I am able to project what the schedule should be.  A simplified version of that output looks like the sample provided.


    Thanks,
    Corey Furman @ Facebook
    ______________________________________________________
    Please mark posts as answer or helpful when they are.

    Saturday, November 09, 2013 6:00 PM