Inserting end of month dates in table with variable # of months

  • Hi,

    I am relatively new to SQL programming and have the following issue:

    I want to insert a number of records in a table depending on the value in a field (numberofperiods) in my projects subtable.

    These records have several fields and one of the of the end-of-month date. The first record that has to be inserted will have the end date of the month from the startdate of my projects table.

    An example will make this more clear:

    My projects subtable (aliased "Np" below) has a field "numberofperiods". Let's say it has a value of 18.

    The start date is in field "initialstartdate" of my Projects-table (PRProject) and has a value of "2013-03-15".

    I would like to insert 18 rows in my table "ProjectTemp" (which is an actual table) with the following values:

    - Field "Projectno" -> Projectnumber from my Project table

    - Field "End of month" -> Enddate for all 18 months beginning with 2013-03-31 for the first row; 2013-04-30 for the second; 2013-05-31 for the third row, 2013-06-30 for fourth row etc. until all 18 rows are populated with the end date.

    - Field "Projectstatus" -> Status from my Project table.

    - Field "Amount" -> calculated value

    So below for the query I have made so far.

    The end-of-month date is now populated by getdate() because I don't know how to insert the end-of-month (eom) date dynamically.

    I have made a function with generates the number of periods and applies it to the query using CROSS APPLY.

    My point is how to insert the eom date for all (in this example) 18 rows in order to replace the getdate() that is in the query now.

    See below. Every record has now the getdate, but should be the eom-date.

    INSERT INTO ProjectTemp

    (

    Projectno,

    EndOfMonth,

    Status,

    Amount

    )

    (

    SELECT

    a.Projectnumber,

    GETDATE() AS EndOfMonth,

    p.Status AS Status,

    ROUND(Ap.SUBAmountRequested / Np.NumberOfPeriods, 2) AS Amount

    FROMAbsences a (nolock)

    LEFT OUTER JOIN PRProject p ON a.ProjectNumber = p.ProjectNr

    LEFT OUTER JOIN CSNobEntYesNoFields YNp ON p.ProjectNr = YNp.ProjectNr

    LEFT OUTER JOIN CSNobEntNumberFields Np ON p.ProjectNr = Np.ProjectNr

    LEFT OUTER JOIN CSNobEntAmountFields Ap ON p.ProjectNr = Ap.ProjectNr

    LEFT OUTER JOIN CSNobEntListFields Lp ON p.ProjectNr = Lp.ProjectNr

    CROSS APPLY dbo.CSNob_FN_GenerateLinesPaymentschema (ISNULL(CONVERT(INT, Np.SUBTerms), 12))

    WHEREa.ProjectNumber = 'SUB59'

    AND a.Type = 9011

    )

    The function dbo.CSNob_FN_GenerateLinesPaymentschema to generate the 18 terms looks like this:

    USE [Test]

    GO

    /****** Object: UserDefinedFunction [dbo].[CSNob_FN_GenerateLinesPaymentschema] Script Date: 10-2-2014 10:18:39 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER FUNCTION [dbo].[CSNob_FN_GenerateLinesPaymentschema](@quantity AS int )

    RETURNS @NumberLines TABLE (Number FLOAT) AS

    BEGIN

    DECLARE @NumberStart AS int

    SELECT @NumberStart = CASE WHEN @quantity < 0 THEN (@quantity*-1) ELSE @quantity END

    INSERT INTO @NumberLines (Number)

    VALUES (@NumberStart)

    BEGIN

    DECLARE @counter INT

    SET @counter = @NumberStart

    WHILE @counter <> 1

    BEGIN

    SET @counter = @counter - 1

    INSERT INTO @NumberLines (Number)

    VALUES (@counter)

    END

    END

    RETURN;

    END

    GO

    Thanks!

    Regards,

    Michiel

  • I'm not sure where to start here. I suppose I'll go with the function.

    The function you have won't have a good performance and you should replace it with a set based approach to get an inLine-Table Function. Here's one option that will return up to 10,000 rows.

    CREATE FUNCTION [dbo].[GenerateNumbers](@quantity AS int )

    RETURNS TABLE WITH SCHEMABINDING

    AS

    RETURN

    WITH E1(N) AS(

    SELECT N FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(N)

    ),

    E2(N) AS(

    SELECT a.N FROM E1 a, E1 b -- 10 * 10 = 100 lines

    ),

    E4(N) AS(

    SELECT a.N FROM E2 a, E2 b -- 100 * 100 = 10,000 lines

    ),

    cteTally(N) AS(

    SELECT TOP(@quantity) ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

    FROM E4

    )

    SELECT *

    FROM cteTally

    Note that your original function was working with integers but returning float values. That is wrong and should be avoided, especially if you only need integer values.

    For the date is easier, you just need to use the date functions.

    --Adds the number of months plus one between date zero(1900-01-01) and today

    --to the day before date zero(1989-12-31)

    SELECT DATEADD( MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, -1)

    If you have any questions, feel free to ask.

    PS. Maybe you could remove CSNobEntYesNoFields from your query.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • ;WITH MyData AS (

    SELECT SUBTerms = 2, initialstartdate = CAST('2013-03-15' AS DATE) UNION ALL SELECT 4, '2013-01-15' UNION ALL SELECT 6, '2013-05-15'

    )

    -- MyData is a mockup of your existing result set

    SELECT

    Np.SUBTerms, Np.initialstartdate,

    x.number,

    newdate = DATEADD(MONTH,number+DATEDIFF(MONTH,0,np.initialstartdate),-1)

    FROM MyData np

    CROSS APPLY (

    SELECT TOP(Np.SUBTerms)

    number = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))

    FROM

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),

    (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n)

    ) x

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • As a bit of a sidebar, I would never use Month END dates... not on a bet. I would always use the first of the next month as a non-inclusive cutoff date which is also the best way to structure temporal WHERE clauses, as well.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply