'DATE' is not a recognized built-in function name

  • yrstruly wrote:

    DDL of Transactions table and ransactions_type below. I tried changing the column name.

    What do you mean by this - 'I tried changing the column name'.  If you have control over that table and can change the column name in the table you should change it to 'Transaction_Date' instead of just [Date].

    And as I suspected - there is indeed another table involved.  Adding table aliases to each table and referencing your columns using the table alias is going to make it much easier later on when you have to debug this code.

    Looking at your table definitions - I would guess that the columns 'primary_type' and 'content_type' are related to the table transaction_type01 table.  But I am probably incorrect in that assumption.

     

    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

  • Adding another entry to get to second page...

    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

  • Does this get you any closer?

    Edit: the date column is already a date so no need to convert it.

    SELECT  a.[DATE] AS TransactionDate,
    SUM(CASE WHEN b.transaction_type = 'Opening Balance' THEN a.transaction_amount ELSE 0 END) AS OpeningBalance,
    SUM(CASE WHEN b.transaction_type = 'Closing Balance' THEN a.transaction_amount ELSE 0 END) AS ClosingBalance,
    SUM(CASE WHEN b.transaction_type = 'Debit' THEN a.transaction_amount ELSE 0 END) AS DebitedAmount,
    SUM(CASE WHEN b.transaction_type = 'Credit' THEN a.transaction_amount ELSE 0 END) AS CreditedAmount
    FROM dbo.Transactions AS a
    INNER JOIN dbo.transaction_type01 AS b ON a.primary_type = b.transaction_type
    GROUP BY a.[DATE];

    • This reply was modified 1 year, 3 months ago by  Ed B.
  • I'm still not getting anywhere even when I changed the columns.

    It is all tied to this question(https://www.sqlservercentral.com/forums/topic/view-or-stored-procedure-3)

    I have also rreated sql table(s) that would allow analysts to track the movement of accounts between different commission groups over time, with the ability to see the start and end dates for each group assignment and link it to the relevant records.

     

     

    CREATE TABLE CommissionGroups (

    GroupID INT PRIMARY KEY,

    GroupName VARCHAR(255) NOT NULL

    );

    CREATE TABLE AccountCommissionHistory (

    AccountID INT NOT NULL,

    GroupID INT NOT NULL,

    StartDate DATE NOT NULL,

    EndDate DATE,

    FOREIGN KEY (GroupID) REFERENCES CommissionGroups(GroupID)

    );

    CREATE TABLE Accounts (

    AccountID INT PRIMARY KEY,

    AccountName VARCHAR(255) NOT NULL,

    CurrentGroupID INT,

    FOREIGN KEY (CurrentGroupID) REFERENCES CommissionGroups(GroupID

    :

    CREATE VIEW DailyAccountBalances AS
    SELECT
    CONVERT(DATE, Transaction_Date) AS Date,
    SUM(CASE WHEN = 'Opening Balance' THEN Amount ELSE 0 END) AS OpeningBalance,
    SUM(CASE WHEN detail_type_name = 'Closing Balance' THEN Amount ELSE 0 END) AS ClosingBalance,
    SUM(CASE WHEN detail_type_name = 'Debit' THEN Amount ELSE 0 END) AS DebitedAmount,
    SUM(CASE WHEN detail_type_name = 'Credit' THEN Amount ELSE 0 END) AS CreditedAmount
    FROM Transactions
    GROUP BY CONVERT(DATE, Transaction_Date);

    • This reply was modified 1 year, 3 months ago by  yrstruly.
    • This reply was modified 1 year, 3 months ago by  yrstruly.
    Attachments:
    You must be logged in to view attached files.
  • I re-wrote the SQL to join transactions and transaction type, I am getting this error:


    CREATE VIEW daily_aggregated_view AS
    SELECT
    primary_account_id,
    Transaction_Date,
    SUM(CASE WHEN transaction_type01.transaction_code = 1 THEN transaction_amount ELSE 0 END) AS debit_amount,
    SUM(CASE WHEN transaction_type01.transaction_code = 2 THEN transaction_amount ELSE 0 END) AS credit_amount,
    SUM(transaction_amount) OVER (PARTITION BY primary_account_id ORDER BY Transaction_Date) AS running_balance
    FROM
    Transactions
    INNER JOIN transaction_type01 ON Transactions.primary_type = transaction_type01.transaction_code
    GROUP BY
    primary_account_id,
    Transaction_Date

    • This reply was modified 1 year, 3 months ago by  yrstruly.
    Attachments:
    You must be logged in to view attached files.
  • that column needs to be in the group by. That is what the error means

  • yrstruly wrote:

    DDL of Transactions table and ransactions_type below. I tried changing the column name.

    Neither of those tables have atransactiondate column though, so the reason you are getting the error is clear; the column does not exist in that table. The column is called date. Honestly, I would suggest renaming that column, as date isn't the most descriptive of names. Don't do this blindly, as changing a column's name can (will) break objects referencing it, but you can rename it with:

    EXEC sys.sp_rename N'dbo.Transactions.date',N'TransactionDate','COLUMN';

     

    • This reply was modified 1 year, 3 months ago by  Thom A.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • My guess is that in reality it's the fact that you have a window function that sums transaction_amount that the engine is barking about. Mixing a group by and a window function in one go is not allowed.

    Try a new rewrite like this (or something similar):

    CREATE VIEW daily_aggregated_view AS
    WITH trans as (
    select
    primary_account_id,
    Transaction_Date,
    transaction_amount,
    primary_type,
    SUM(transaction_amount) OVER (PARTITION BY primary_account_id ORDER BY Transaction_Date) AS running_balance
    from Transactions
    )
    SELECT
    t.primary_account_id,
    t.Transaction_Date,
    SUM(CASE WHEN tt.transaction_code = 1 THEN t.transaction_amount ELSE 0 END) AS debit_amount,
    SUM(CASE WHEN tt.transaction_code = 2 THEN t.transaction_amount ELSE 0 END) AS credit_amount,
    t.running_balance
    FROM
    trans AS t
    INNER JOIN transaction_type01 AS tt
    ON t.primary_type = tt.transaction_code
    GROUP BY
    t.primary_account_id,
    t.Transaction_Date,
    t.running_balance
  • Thank you for the assistance. Do you mean it must be part of the Group By statement/columns, like below(but a new error appears)?

    "Msg 2714, Level 16, State 3, Procedure daily_aggregated_view, Line 2 [Batch Start Line 2]

    There is already an object named 'daily_aggregated_view' in the database."

     

    Attachments:
    You must be logged in to view attached files.
  • yrstruly wrote:

    Thank you for the assistance. Do you mean it must be part of the Group By statement/columns, like below(but a new error appears)?

    "Msg 2714, Level 16, State 3, Procedure daily_aggregated_view, Line 2 [Batch Start Line 2] There is already an object named 'daily_aggregated_view' in the database."

    You can't CREATE an object that already exists; the error is informing you of the problem. You need to ALTER it. As you're on a recent version of SQL Server, just get into the habit of using CREATE OR ALTER.

    I must admit, I get the impression that you're in a position where you're out of your depth. That isn't an issue but you need to take the step yourself to attempt to debug and learn about the problem you are facing. Reading the error is the first step, and many of the errors you've received and posted here are telling you the issue.

    If you actually have no familiarity with (T-)SQL (and based on this post I would hazard a guess you have little with it), then get your workplace to send you on a training course, as it seems you do need that knowledge to do your job; any good employer will be happy to provide you with training on a skill that they require you to obtain.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Sorry. I misread the code. I tried it and it works. Please tell me, the code @kaj shared with me also works. Does both of these codes give me the same results? I want to rest assured both codes will lead to the same results.


    CREATE VIEW daily_aggregated_view AS
    SELECT
    primary_account_id,
    Transaction_Date,
    SUM(CASE WHEN transaction_type01.transaction_code = 1 THEN transaction_amount ELSE 0 END) AS debit_amount,
    SUM(CASE WHEN transaction_type01.transaction_code = 2 THEN transaction_amount ELSE 0 END) AS credit_amount,
    SUM(transaction_amount) OVER (PARTITION BY primary_account_id ORDER BY Transaction_Date) AS running_balance
    FROM
    Transactions
    INNER JOIN transaction_type01 ON Transactions.primary_type = transaction_type01.transaction_code
    GROUP BY
    primary_account_id,
    Transaction_Date

    VS

    CREATE VIEW daily_aggregated_view AS
    WITH trans as (
    select
    primary_account_id,
    Transaction_Date,
    transaction_amount,
    primary_type,
    SUM(transaction_amount) OVER (PARTITION BY primary_account_id ORDER BY Transaction_Date) AS running_balance
    from Transactions
    )
    SELECT
    t.primary_account_id,
    t.Transaction_Date,
    SUM(CASE WHEN tt.transaction_code = 1 THEN t.transaction_amount ELSE 0 END) AS debit_amount,
    SUM(CASE WHEN tt.transaction_code = 2 THEN t.transaction_amount ELSE 0 END) AS credit_amount,
    t.running_balance
    FROM
    trans AS t
    INNER JOIN transaction_type01 AS tt
    ON t.primary_type = tt.transaction_code
    GROUP BY
    t.primary_account_id,
    t.Transaction_Date,
    t.running_balance

    Thank you All for your assistance:

  • I had to implement some changes

    "Create daily aggregated table or data view along with procedure that tracts the opening and closing

    balances of accounts along with debited and credited amounts. The data view needs to be

    able to roll up to a daily level that will be used to track the total opening and closing balance

    of ALL accounts. The accounts should have an end-of-day balance every day from their first

    transaction, even if they didn’t transact that day. Procedure runs daily and appends the

    previous days data. "

     

    I came up with these SQL code which are working. I just want to know if all you pro's agree?


    CREATE PROCEDURE daily_aggregate_procedure AS
    BEGIN
    INSERT INTO daily_aggregated_view
    SELECT
    primary_account_id,
    Transaction_Date,
    SUM(CASE WHEN transaction_type01.transaction_code = 1 THEN transaction_amount ELSE 0 END) AS debit_amount,
    SUM(CASE WHEN transaction_type01.transaction_code = 2 THEN transaction_amount ELSE 0 END) AS credit_amount,
    SUM(CASE WHEN transaction_type01.transaction_code = 1 THEN transaction_amount ELSE 0 END -
    CASE WHEN transaction_type01.transaction_code = 2 THEN transaction_amount ELSE 0 END)
    OVER (PARTITION BY primary_account_id ORDER BY Transaction_Date) AS opening_balance,
    SUM(CASE WHEN transaction_type01.transaction_code = 1 THEN transaction_amount ELSE 0 END -
    CASE WHEN transaction_type01.transaction_code = 2 THEN transaction_amount ELSE 0 END) +
    SUM(transaction_amount)
    OVER (PARTITION BY primary_account_id ORDER BY Transaction_Date) AS closing_balance
    FROM
    Transactions
    INNER JOIN transaction_type01 ON Transactions.primary_type = transaction_type01.transaction_code
    WHERE
    Transaction_Date = CAST(GETDATE() AS DATE)
    GROUP BY
    primary_account_id,
    Transaction_Date, transaction_code,transaction_amount;
    END;



    CREATE VIEW daily_aggregated_view AS
    SELECT
    primary_account_id,
    Transaction_Date,
    SUM(CASE WHEN transaction_type01.transaction_code = 1 THEN transaction_amount ELSE 0 END) AS debit_amount,
    SUM(CASE WHEN transaction_type01.transaction_code = 2 THEN transaction_amount ELSE 0 END) AS credit_amount,
    SUM(CASE WHEN transaction_type01.transaction_code = 1 THEN transaction_amount ELSE 0 END -
    CASE WHEN transaction_type01.transaction_code = 2 THEN transaction_amount ELSE 0 END)
    OVER (PARTITION BY primary_account_id ORDER BY Transaction_Date) AS opening_balance,
    SUM(CASE WHEN transaction_type01.transaction_code = 1 THEN transaction_amount ELSE 0 END -
    CASE WHEN transaction_type01.transaction_code = 2 THEN transaction_amount ELSE 0 END) +
    SUM(transaction_amount)
    OVER (PARTITION BY primary_account_id ORDER BY Transaction_Date) AS closing_balance
    FROM
    Transactions
    INNER JOIN transaction_type01 ON Transactions.primary_type = transaction_type01.transaction_code
    GROUP BY
    primary_account_id,
    Transaction_Date;

Viewing 12 posts - 16 through 26 (of 26 total)

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