Insert Date from integer month and year columns in the same table

  • Hi,
    Please consider the following tables. Invoices are issued when products are sent to customer. for each customer could be one or more invoices issued each month. At the end of the month, statement is created and customer pays for monthly statement. I would like to add StatementDate column to #Statements table and use MonthValue and YearValue for month & year and also last day of each month for day of date to insert into StatementDate when inserting data into #Statements.
    Thank you.

    --======= If the first test table already exists, drop it
        IF OBJECT_ID('TempDB..#Invoices', 'U') IS NOT NULL
            DROP TABLE #Invoices

    --======= Create the first test table with
    CREATE TABLE #Invoices
        (
        ID                INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        InvoiceDate        DATETIME,
        Price            DECIMAL(18,2),
        CustomerID        INT
        )

    --======= Setup any special required conditions specifically where dates are concerned
        SET DATEFORMAT DMY

    --======= Allow Inserts into IDENTITY column
        SET IDENTITY_INSERT #Invoices ON

    --======= Insert the test data into first test table
    INSERT INTO #Invoices
        (ID, InvoiceDate, Price, CustomerID)
    SELECT 1, 'JUL 3 2018 12:00AM', 185.00, 3 UNION ALL
    SELECT 3, 'JUL 5 2018 12:00AM', 98.00, 5 UNION ALL
    SELECT 8, 'JUL 5 2018 12:00AM', 125.00, 7 UNION ALL
    SELECT 9, 'JUL 7 2018 12:00AM', 90.00, 3 UNION ALL
    SELECT 12, 'JUL 10 2018 12:00AM', 205.00, 5 UNION ALL
    SELECT 23, 'JUL 10 2018 12:00AM', 165.00, 3 UNION ALL
    SELECT 27, 'AUG 8 2018 12:00AM', 385.00, 5 UNION ALL
    SELECT 32, 'AUG 9 2018 12:00AM', 222.00, 3 UNION ALL
    SELECT 35, 'SEP 4 2018 12:00AM', 402.00, 3

    --======= Do not allow IDENTITY insert
        SET IDENTITY_INSERT #Invoices OFF

    --===================================================================
    --======= If the second test table alrady exists, drop it
        IF OBJECT_ID('TempDB..#Statements', 'U') IS NOT NULL
            DROP TABLE #Statements

    --======= Create the second test table
    CREATE TABLE #Statements
        (
        StatementID            INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        StatementSum        DECIMAL(18,2),
        Paid                BIT DEFAULT (0),
        MonthValue            INT,
        YearValue            INT,
        CustomerID            INT
        --, StatementDate        DATETIME
        )
        
    --====== Insert the test data using #Invoices table into second test table
    INSERT INTO #Statements
        (StatementSum, MonthValue, YearValue, CustomerID)

        SELECT DISTINCT
            SUM(Price) OVER(PARTITION BY CustomerID),
            MONTH(InvoiceDate),
            YEAR(InvoiceDate),
            CustomerID
        FROM #Invoices
        WHERE MONTH(InvoiceDate)=7
        ORDER BY CustomerID

    --====== Display test table data
    SELECT * FROM #Invoices
    SELECT * FROM #Statements

  • rahimazer - Monday, November 5, 2018 11:15 AM

    Hi,
    Please consider the following tables. Invoices are issued when products are sent to customer. for each customer could be one or more invoices issued each month. At the end of the month, statement is created and customer pays for monthly statement. I would like to add StatementDate column to #Statements table and use MonthValue and YearValue for month & year and also last day of each month for day of date to insert into StatementDate when inserting data into #Statements.
    Thank you.

    --======= If the first test table already exists, drop it
        IF OBJECT_ID('TempDB..#Invoices', 'U') IS NOT NULL
            DROP TABLE #Invoices

    --======= Create the first test table with
    CREATE TABLE #Invoices
        (
        ID                INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        InvoiceDate        DATETIME,
        Price            DECIMAL(18,2),
        CustomerID        INT
        )

    --======= Setup any special required conditions specifically where dates are concerned
        SET DATEFORMAT DMY

    --======= Allow Inserts into IDENTITY column
        SET IDENTITY_INSERT #Invoices ON

    --======= Insert the test data into first test table
    INSERT INTO #Invoices
        (ID, InvoiceDate, Price, CustomerID)
    SELECT 1, 'JUL 3 2018 12:00AM', 185.00, 3 UNION ALL
    SELECT 3, 'JUL 5 2018 12:00AM', 98.00, 5 UNION ALL
    SELECT 8, 'JUL 5 2018 12:00AM', 125.00, 7 UNION ALL
    SELECT 9, 'JUL 7 2018 12:00AM', 90.00, 3 UNION ALL
    SELECT 12, 'JUL 10 2018 12:00AM', 205.00, 5 UNION ALL
    SELECT 23, 'JUL 10 2018 12:00AM', 165.00, 3 UNION ALL
    SELECT 27, 'AUG 8 2018 12:00AM', 385.00, 5 UNION ALL
    SELECT 32, 'AUG 9 2018 12:00AM', 222.00, 3 UNION ALL
    SELECT 35, 'SEP 4 2018 12:00AM', 402.00, 3

    --======= Do not allow IDENTITY insert
        SET IDENTITY_INSERT #Invoices OFF

    --===================================================================
    --======= If the second test table alrady exists, drop it
        IF OBJECT_ID('TempDB..#Statements', 'U') IS NOT NULL
            DROP TABLE #Statements

    --======= Create the second test table
    CREATE TABLE #Statements
        (
        StatementID            INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        StatementSum        DECIMAL(18,2),
        Paid                BIT DEFAULT (0),
        MonthValue            INT,
        YearValue            INT,
        CustomerID            INT
        --, StatementDate        DATETIME
        )
        
    --====== Insert the test data using #Invoices table into second test table
    INSERT INTO #Statements
        (StatementSum, MonthValue, YearValue, CustomerID)

        SELECT DISTINCT
            SUM(Price) OVER(PARTITION BY CustomerID),
            MONTH(InvoiceDate),
            YEAR(InvoiceDate),
            CustomerID
        FROM #Invoices
        WHERE MONTH(InvoiceDate)=7
        ORDER BY CustomerID

    --====== Display test table data
    SELECT * FROM #Invoices
    SELECT * FROM #Statements

    You just need to learn some date math to calculate the last day of the month.

    WITH cteInvoices AS(
      SELECT CustomerID,
       DATEADD( MM, DATEDIFF( MM, -1, InvoiceDate), -1) AS StatementDate,
       Price
      FROM #Invoices
    WHERE InvoiceDate >= '20180701'
    AND InvoiceDate < '20180801'
    )
    INSERT INTO #Statements
      (StatementSum, MonthValue, YearValue, CustomerID, StatementDate)
    SELECT
      SUM(Price),
      MONTH(StatementDate),
      YEAR(StatementDate),
      CustomerID,
      StatementDate
    FROM cteInvoices
    GROUP BY CustomerID,
       StatementDate;

    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
  • rahimazer - Monday, November 5, 2018 11:15 AM

    Hi,
    Please consider the following tables. Invoices are issued when products are sent to customer. for each customer could be one or more invoices issued each month. At the end of the month, statement is created and customer pays for monthly statement. I would like to add StatementDate column to #Statements table and use MonthValue and YearValue for month & year and also last day of each month for day of date to insert into StatementDate when inserting data into #Statements.
    Thank you.

    --======= If the first test table already exists, drop it
        IF OBJECT_ID('TempDB..#Invoices', 'U') IS NOT NULL
            DROP TABLE #Invoices

    --======= Create the first test table with
    CREATE TABLE #Invoices
        (
        ID                INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        InvoiceDate        DATETIME,
        Price            DECIMAL(18,2),
        CustomerID        INT
        )

    --======= Setup any special required conditions specifically where dates are concerned
        SET DATEFORMAT DMY

    --======= Allow Inserts into IDENTITY column
        SET IDENTITY_INSERT #Invoices ON

    --======= Insert the test data into first test table
    INSERT INTO #Invoices
        (ID, InvoiceDate, Price, CustomerID)
    SELECT 1, 'JUL 3 2018 12:00AM', 185.00, 3 UNION ALL
    SELECT 3, 'JUL 5 2018 12:00AM', 98.00, 5 UNION ALL
    SELECT 8, 'JUL 5 2018 12:00AM', 125.00, 7 UNION ALL
    SELECT 9, 'JUL 7 2018 12:00AM', 90.00, 3 UNION ALL
    SELECT 12, 'JUL 10 2018 12:00AM', 205.00, 5 UNION ALL
    SELECT 23, 'JUL 10 2018 12:00AM', 165.00, 3 UNION ALL
    SELECT 27, 'AUG 8 2018 12:00AM', 385.00, 5 UNION ALL
    SELECT 32, 'AUG 9 2018 12:00AM', 222.00, 3 UNION ALL
    SELECT 35, 'SEP 4 2018 12:00AM', 402.00, 3

    --======= Do not allow IDENTITY insert
        SET IDENTITY_INSERT #Invoices OFF

    --===================================================================
    --======= If the second test table alrady exists, drop it
        IF OBJECT_ID('TempDB..#Statements', 'U') IS NOT NULL
            DROP TABLE #Statements

    --======= Create the second test table
    CREATE TABLE #Statements
        (
        StatementID            INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        StatementSum        DECIMAL(18,2),
        Paid                BIT DEFAULT (0),
        MonthValue            INT,
        YearValue            INT,
        CustomerID            INT
        --, StatementDate        DATETIME
        )
        
    --====== Insert the test data using #Invoices table into second test table
    INSERT INTO #Statements
        (StatementSum, MonthValue, YearValue, CustomerID)

        SELECT DISTINCT
            SUM(Price) OVER(PARTITION BY CustomerID),
            MONTH(InvoiceDate),
            YEAR(InvoiceDate),
            CustomerID
        FROM #Invoices
        WHERE MONTH(InvoiceDate)=7
        ORDER BY CustomerID

    --====== Display test table data
    SELECT * FROM #Invoices
    SELECT * FROM #Statements

    How is this:

    --======= If the first test table already exists, drop it
      IF OBJECT_ID('TempDB..#Invoices', 'U') IS NOT NULL
       DROP TABLE #Invoices;
    go
    --======= Create the first test table with
    CREATE TABLE #Invoices
      (
      ID      INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
      InvoiceDate   DATETIME,
      Price    DECIMAL(18,2),
      CustomerID   INT
      );
    go
    --======= Allow Inserts into IDENTITY column
      SET IDENTITY_INSERT #Invoices on;
    go
    INSERT INTO #Invoices
      (ID, InvoiceDate, Price, CustomerID)
    VALUES -- using ISO standard for date/time values
    (1, '20180703 00:00:00.000', 185.00, 3)
    ,(3, '20180705 00:00:00.000', 98.00, 5)
    ,(8, '20180705 00:00:00.000', 125.00, 7)
    ,(9, '20180707 00:00:00.000', 90.00, 3)
    ,(12, '20180710 00:00:00.000', 205.00, 5)
    ,(23, '20180710 00:00:00.000', 165.00, 3)
    ,(27, '20180808 00:00:00.000', 385.00, 5)
    ,(32, '20180809 00:00:00.000', 222.00, 3)
    ,(35, '20180904 00:00:00.000', 402.00, 3);
    go
    --======= Do not allow IDENTITY insert
      SET IDENTITY_INSERT #Invoices off;
    go
    select * from [#Invoices];
    go
    --======= If the second test table alrady exists, drop it
      IF OBJECT_ID('TempDB..#Statements', 'U') IS NOT NULL
       DROP TABLE #Statements;
    go
    --======= Create the second test table
    CREATE TABLE #Statements
      (
      StatementID    INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
      StatementSum   DECIMAL(18,2),
      Paid      BIT DEFAULT (0),
      MonthValue    INT,
      YearValue    INT,
      CustomerID    INT
      , StatementDate   DATETIME
      );
    go
    --====== Insert the test data using #Invoices table into second test table
    insert into [#Statements]
    (
      [StatementSum]
    , [MonthValue]
    , [YearValue]
    , [CustomerID]
    , [StatementDate]
    )
    select distinct
      sum([Price]) over (partition by [CustomerID])
    , month([InvoiceDate])
    , year([InvoiceDate])
    , [CustomerID]
    , dateadd(month, datediff(month,0,[InvoiceDate]) + 1,-1)
    from [#Invoices]
    where [InvoiceDate] >= '20180701'
      and [InvoiceDate] < '20180801'
    order by [CustomerID];
    go
    --====== Display test table data
    SELECT * FROM #Invoices
    SELECT * FROM #Statements

  • How about EOMONTH(InvoiceDate)?  Probably won't notice any performance issues with this - but if you do then revert to the DATEADD methods above.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams 3188 - Monday, November 5, 2018 3:51 PM

    How about EOMONTH(InvoiceDate)?  Probably won't notice any performance issues with this - but if you do then revert to the DATEADD methods above.

    Just a personal opinion but why pick up a bad habit if it can be a performance issue?

    --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)

  • Thank you all for taking your time and contributing to this. I struggled with this for about 2 weeks now. Luis Cazares and Lynn Pettis Thank you so much.
    Regards,
    Savalan

  • Jeff Moden - Monday, November 5, 2018 8:25 PM

    Jeffrey Williams 3188 - Monday, November 5, 2018 3:51 PM

    How about EOMONTH(InvoiceDate)?  Probably won't notice any performance issues with this - but if you do then revert to the DATEADD methods above.

    Just a personal opinion but why pick up a bad habit if it can be a performance issue?

    The EOMONTH function outperforms all other methods for calculating the end of month, with datediff months to 0 date coming next, the latter is more than 10% slower than the former.
    😎

  • Eirikur Eiriksson - Monday, November 5, 2018 10:59 PM

    Jeff Moden - Monday, November 5, 2018 8:25 PM

    Jeffrey Williams 3188 - Monday, November 5, 2018 3:51 PM

    How about EOMONTH(InvoiceDate)?  Probably won't notice any performance issues with this - but if you do then revert to the DATEADD methods above.

    Just a personal opinion but why pick up a bad habit if it can be a performance issue?

    The EOMONTH function outperforms all other methods for calculating the end of month, with datediff months to 0 date coming next, the latter is more than 10% slower than the former.
    😎

    True. Actually I was just playing with dates yesterday, creating billing cycles for an AR system that I am building. Built in functions are generally highly optimized and nearly never NOT the way to go 🙂 I can't think of a case where not using a built in function would not be the most efficient but I am sure there is some example of this.

    <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

  • Jeffery Williams - Tuesday, November 6, 2018 6:35 AM

    Eirikur Eiriksson - Monday, November 5, 2018 10:59 PM

    Jeff Moden - Monday, November 5, 2018 8:25 PM

    Jeffrey Williams 3188 - Monday, November 5, 2018 3:51 PM

    How about EOMONTH(InvoiceDate)?  Probably won't notice any performance issues with this - but if you do then revert to the DATEADD methods above.

    Just a personal opinion but why pick up a bad habit if it can be a performance issue?

    The EOMONTH function outperforms all other methods for calculating the end of month, with datediff months to 0 date coming next, the latter is more than 10% slower than the former.
    😎

    True. Actually I was just playing with dates yesterday, creating billing cycles for an AR system that I am building. Built in functions are generally highly optimized and nearly never NOT the way to go 🙂 I can't think of a case where not using a built in function would not be the most efficient but I am sure there is some example of this.

    I have tested 15 different methods and the EOMONTH leaves the competition behind every time.
    😎

    There are quite few posts out there stating that EOMONTH does not perform well, just wondering what kind of T#@mp tweeted that fake news😀

  • Eirikur Eiriksson - Tuesday, November 6, 2018 6:51 AM

    Jeffery Williams - Tuesday, November 6, 2018 6:35 AM

    Eirikur Eiriksson - Monday, November 5, 2018 10:59 PM

    Jeff Moden - Monday, November 5, 2018 8:25 PM

    Jeffrey Williams 3188 - Monday, November 5, 2018 3:51 PM

    How about EOMONTH(InvoiceDate)?  Probably won't notice any performance issues with this - but if you do then revert to the DATEADD methods above.

    Just a personal opinion but why pick up a bad habit if it can be a performance issue?

    The EOMONTH function outperforms all other methods for calculating the end of month, with datediff months to 0 date coming next, the latter is more than 10% slower than the former.
    😎

    True. Actually I was just playing with dates yesterday, creating billing cycles for an AR system that I am building. Built in functions are generally highly optimized and nearly never NOT the way to go 🙂 I can't think of a case where not using a built in function would not be the most efficient but I am sure there is some example of this.

    I have tested 15 different methods and the EOMONTH leaves the competition behind every time.
    😎

    There are quite few posts out there stating that EOMONTH does not perform well, just wondering what kind of T#@mp tweeted that fake news😀

    So, not a Trump fan.  Too bad, I have more money in my pocket since his tax cuts.
    FORMAT, a do not use function until they fix it.

  • Eirikur Eiriksson - Monday, November 5, 2018 10:59 PM

    Jeff Moden - Monday, November 5, 2018 8:25 PM

    Jeffrey Williams 3188 - Monday, November 5, 2018 3:51 PM

    How about EOMONTH(InvoiceDate)?  Probably won't notice any performance issues with this - but if you do then revert to the DATEADD methods above.

    Just a personal opinion but why pick up a bad habit if it can be a performance issue?

    The EOMONTH function outperforms all other methods for calculating the end of month, with datediff months to 0 date coming next, the latter is more than 10% slower than the former.
    😎

    Hmmmm... haven't actually testing it myself.  Since I typically use Closed/Open criteria for dates, I'll have to test for EOMONTH+1 to see if it's still faster than the "traditional" DATEADD/DATEDIFF method for finding the first of the next month.

    --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)

  • Jeff Moden - Tuesday, November 6, 2018 5:43 PM

    Eirikur Eiriksson - Monday, November 5, 2018 10:59 PM

    Jeff Moden - Monday, November 5, 2018 8:25 PM

    Jeffrey Williams 3188 - Monday, November 5, 2018 3:51 PM

    How about EOMONTH(InvoiceDate)?  Probably won't notice any performance issues with this - but if you do then revert to the DATEADD methods above.

    Just a personal opinion but why pick up a bad habit if it can be a performance issue?

    The EOMONTH function outperforms all other methods for calculating the end of month, with datediff months to 0 date coming next, the latter is more than 10% slower than the former.
    😎

    Hmmmm... haven't actually testing it myself.  Since I typically use Closed/Open criteria for dates, I'll have to test for EOMONTH+1 to see if it's still faster than the "traditional" DATEADD/DATEDIFF method for finding the first of the next month.

    What about DATEFROMPARTS(yearvalue, monthvalue, 1) ?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • sgmunson - Monday, November 12, 2018 6:20 AM

    Jeff Moden - Tuesday, November 6, 2018 5:43 PM

    Eirikur Eiriksson - Monday, November 5, 2018 10:59 PM

    Jeff Moden - Monday, November 5, 2018 8:25 PM

    Jeffrey Williams 3188 - Monday, November 5, 2018 3:51 PM

    How about EOMONTH(InvoiceDate)?  Probably won't notice any performance issues with this - but if you do then revert to the DATEADD methods above.

    Just a personal opinion but why pick up a bad habit if it can be a performance issue?

    The EOMONTH function outperforms all other methods for calculating the end of month, with datediff months to 0 date coming next, the latter is more than 10% slower than the former.
    😎

    Hmmmm... haven't actually testing it myself.  Since I typically use Closed/Open criteria for dates, I'll have to test for EOMONTH+1 to see if it's still faster than the "traditional" DATEADD/DATEDIFF method for finding the first of the next month.

    What about DATEFROMPARTS(yearvalue, monthvalue, 1) ?

    The same, faster than all other methods I've tested.
    😎

Viewing 13 posts - 1 through 12 (of 12 total)

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