January 31, 2023 at 9:12 pm
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
January 31, 2023 at 9:13 pm
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
January 31, 2023 at 9:54 pm
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];
February 1, 2023 at 5:51 am
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);
February 1, 2023 at 6:14 am
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
February 1, 2023 at 3:59 pm
that column needs to be in the group by. That is what the error means
February 1, 2023 at 4:06 pm
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';
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 1, 2023 at 4:26 pm
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
February 2, 2023 at 12:08 pm
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."
February 2, 2023 at 12:13 pm
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
February 2, 2023 at 12:28 pm
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:
February 2, 2023 at 2:41 pm
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