none
current payment due date

    Question

  • I need help with finding the CURRENT_PAYMENT_DUE_DATE after a cutoff date.

    For example, say a CURRENT_PAYMENT_DUE_DATE was Wednesday, 7/31/2013. The cutoff date is 8/10/2013 and the payment frequency is 1 payment per week, due (in this case) on Wednesday. The weekly ones will always be on the same day of the week following the cutoff date. We know that the revised CURRENT_PAYMENT_DUE_DATE would be 8/14/2013, but how would I figure this out in TSQL?

    I also have to do the same thing for bi-weekly, monthly, and semi-monthly. I would appreciate any tips I could get on this, even if it isn't all 4 scenarios.

    Wednesday, August 21, 2013 8:20 PM

All replies

  • Could you please post some sample data with expected output and the version of SQL Server.

    Regards............


    Ione

    Wednesday, August 21, 2013 8:36 PM
  • SQL Server 2008 R2

    CURRENT_PAYMENT_DUE_DATE CutoffDate, NEW_CURRENT_PAYMENT_DUE_DATE

    09/15/2012, 08/10/2013, 08/17/2013

    08/01/2011, 08/10/2013, 08/12/2013

    07/31/2011, 08/10/2013, 08/14/2013

    02/27/2013, 08/10/2013, 08/14/2013

    02/26/2013, 08/10/2013, 08/13/2013

    These are for weekly payment frequency.

    =========================

    CURRENT_PAYMENT_DUE_DATE CutoffDate, NEW_CURRENT_PAYMENT_DUE_DATE

    09/15/2012, 08/10/2013, 08/15/2013

    08/01/2011, 08/10/2013, 09/01/2013

    07/31/2011, 08/10/2013, 08/31/2013

    02/27/2013, 08/10/2013, 08/27/2013

    02/26/2013, 08/10/2013, 08/26/2013

    These are for monthly payment frequency. If the month doesn't have enough days, the last day of month would work.

    Semi-monthly and bi-weekly are trickier, but I at least want to get started.

    • Edited by duanewilson Wednesday, August 21, 2013 8:54 PM
    Wednesday, August 21, 2013 8:48 PM
  • Short answer: calendar table. 

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats (you screwed up!). Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. We will now move your name to the "rude posters" list and pass around. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Sarat Babu (SS) Thursday, August 22, 2013 1:25 AM
    • Unproposed as answer by duanewilson Thursday, August 22, 2013 5:03 PM
    Thursday, August 22, 2013 1:19 AM
  • I have actually seen you as to be quite rude on many posts, far more than I ever have. So look in the mirror. In fact, if you look on many of my posts, I have put in detailed scripts. I was only answering the last poster's question to post some sample data. My question is very general in that there are only 2 variables that could be ANY sample dates and a new date. Any cutoff date, any current payment due date, and what would be the new one. You could even use a random generator. I don't care. And my posts made that quite clear. If there is anything more I could have done, I would have. But I don't have anything except random data. If you don't want to help, then don't. But don't criticize every post that doesn't come with a script. If one is indicated, I will be the first one to post it. Your proposed answer is not an answer, and I have unchecked it. You have written many good books and have a lot of knowledge, but your attitude and way of dealing with people doesn't match it.
    Thursday, August 22, 2013 5:10 PM
  • >> My question is very general in that there are only 2 variables that could be ANY sample dates and a new date. Any cutoff date, any current payment due date, and what would be the new one. <<

    Did you notice that other people also ask that you follow minimal Netiquette? 

    The reason for DDL is that 80-95% of the work in SQL is done in the DDL. Bad DDL (or no DDL at all) lead to assumptions and kludges. Why was it so hard for you to do this minimal polite behavior? I can think of five ways to design a table from your vague narrative. 


    CREATE TABLE Billing_Cycles
    (payment_plan_type VARCHAR(10) NOT NULL
      CHECK (payment_plan_type
           IN ('week', 'bi-week', 'month', 'semi-month')),
     payment_due_date DATE NOT NULL,
     PRIMARY KEY (payment_plan_type, payment_due_date), 
     payment_cutoff_date DATE NOT NULL,
     CHECK (payment_due_date < cutoff_date));

    See the constraint to help the optimizer? See the key that makes this a table and not a pile of punch cards written in SQL? 

    Since SQL is a data base language, we do not compute things; we look them up in tables! Load this table with 100 years of data. That is less than 150,000 row. Make the primary key index INCLUDE  payment_cutoff_date and put it in ASC order, so the most recent dates will be read first. 

    >> If you don't want to help, then don't. But don't criticize every post that doesn't come with a script. <<

    I am trying to help you in spite of your arrogance, ignorance and lack of basic forum manners. The rule that you post a script goes back over 30 years on SQL forums; remember CompuServe? 

    The use of ISO-8601 date formats has been part of the ANSI/ISO Standards since we added temporal date types! Not using ISO-8601 tells the world that you have no idea how IT works; it is embedded in every ISO and ANSI standard, not just SQL. What would you think of an engineer who does not know the Metric System? 

    >> You have written many good books and have a lot of knowledge, but your attitude and way of dealing with people doesn't match it. <<

    I am blunt, precise  and treat people like they are adults with serious questions and not whiny children who care more about their “self-esteem” and “feelings” than a good answer. Look up “Keisaku”; you are about 2-4 years away from being an SQL programmer. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Thursday, August 22, 2013 8:49 PM