Getting every day data into "AccountCommissionHistory"
-
Please find sample data in bacpac format. I would like my data to be loaded daily with a start and end time/date. This SQL code, I filter on a specific Primary Account ID. It gives me the transactions for that date. How do I ensure that every transaction has a start and end date?
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (1000) [transaction_id]
,[Transaction_Date]
,[primary_account_id]
,[parent_account_id]
,[provider_account_id]
,[icp_account_id]
,[group_id]
,[primary_type]
,[transaction_amount]
,[stock_cost]
,[vat]
,[superdealer_vat_reg]
,[vendor_vat_reg]
,[vendor_comm]
,[superdealer_comm]
,[icp_comm]
,[psitek_comm]
,[vendor_rbc_cost]
,[bank_charge]
,[bank_charge_owner]
,[detail_type_name]
,[detail_object_id]
,[primary_account_balance]
,[content_type]
,[reversal_id]
FROM [Kazang].[dbo].[Transactions]
where primary_account_id = 314715I checked AccountCommissionHistory and nothing is in table:
-
What defines the start and end dates of a transaction?
Do you store that data anywhere else in the database?
What are you actually trying to accomplish here as the question is far from coherent to form a formative answer.
Please also ensure you follow https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help on posting code, as people wont download files and bacpacs are not allowed to be uploaded anyway.
-
What defines the start and end dates of a transaction? Start of a new day would be the start date and end of day would be the end date.
--Initialise Table
DECLARE @EffectiveDate DATE = '2023-01-01',
@FutureEndDate DATE = '9999-12-31'
INSERT dbo.AccountCommissionHistory (AccountID, GroupID, StartDate, EndDate)
SELECT a.AccountID, a.CurrentGroupID, @EffectiveDate, @FutureEndDate
FROM dbo.Accounts AS A
GO
--NEXT DAY AND EVERY DAY
DECLARE @EffectiveDate DATE = '2023-02-02',
@FutureEndDate DATE = '9999-12-31'
--Expire current rows not in source
UPDATE a
SET a.EndDate = DATEADD(DAY, -1, @EffectiveDate)
FROM dbo.AccountCommissionHistory AS a
WHERE a.EndDate = @FutureEndDate
AND NOT EXISTS (SELECT 1
FROM dbo.Accounts AS b
WHERE b.AccountID = a.AccountID
AND b.CurrentGroupID = a.GroupID)
-- Insert new rows
INSERT dbo.AccountCommissionHistory (AccountID, GroupID, StartDate, EndDate)
SELECT a.AccountID, a.CurrentGroupID, @EffectiveDate, @FutureEndDate
FROM dbo.Accounts AS a
WHERE NOT EXISTS (SELECT 1
FROM dbo.AccountCommissionHistory AS b
WHERE b.AccountID = a.AccountID
AND b.GroupID = a.CurrentGroupID
AND b.EndDate = @FutureEndDate)
--------------
DROP TABLE IF EXISTS dbo.account_daily_snapshot
CREATE TABLE dbo.account_daily_snapshot
( primary_account_id INT NOT NULL,
Transaction_Date DATE NOT NULL,
debit_amount DECIMAL(18,2),
credit_amount DECIMAL(18,2),
CONSTRAINT PKaccount_daily_snapshot PRIMARY KEY CLUSTERED (primary_account_id, Transaction_Date)
)
INSERT dbo.account_daily_snapshot (primary_account_id, Transaction_Date, debit_amount, credit_amount)
SELECT a.primary_account_id, b.Transaction_Date,
ISNULL(c.debit_amount,0) AS debit_amount,
ISNULL(c.credit_amount,0) AS credit_amount
FROM
(
SELECT primary_account_id, MIN(Transaction_Date) AS MinDate
FROM dbo.Transactions
GROUP BY primary_account_id
) AS a
JOIN (
SELECT DISTINCT Transaction_Date
FROM dbo.Transactions
) AS b ON b.Transaction_Date >= a.MinDate
LEFT OUTER JOIN
(
SELECT x.primary_account_id, x.Transaction_Date,
SUM(CASE WHEN y.transaction_code = 1 THEN x.transaction_amount ELSE 0 END) AS debit_amount,
SUM(CASE WHEN y.transaction_code = 2 THEN x.transaction_amount ELSE 0 END) AS credit_amount
FROM dbo.Transactions AS x
JOIN dbo.transaction_type AS y ON x.primary_type = y.transaction_code
GROUP BY primary_account_id, Transaction_Date
) AS c ON a.primary_account_id = c.primary_account_id AND b.Transaction_Date = c.Transaction_Date
SELECT primary_account_id,
Transaction_Date,
SUM(credit_amount + debit_amount) OVER
( PARTITION BY primary_account_id ORDER BY Transaction_Date
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS opening_balance,
SUM(credit_amount + debit_amount) OVER
( PARTITION BY primary_account_id ORDER BY Transaction_Date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS closing_balance
FROM dbo.account_daily_snapshot - yrstruly wrote:
What defines the start and end dates of a transaction? Start of a new day would be the start date and end of day would be the end date.
So the "transaction_date" column in transactions is going to be both the startdate and the enddate?
SELECT
parent_account_id,
group_id,
convert(date,transaction_date) as start_date,
convert(date,transaction_date) as end_date
from transactions
group by parent_account_id, group_id, convert(date, transaction_date)I'm not following the logic or question here.
Post the question you need an answer too along with consumable data and expected outcome and I hope it makes more sense to me.
-
How do i post data when you guys don't want to get it from a link?
-
You post data by providing tested/working insert scripts that insert test data into the relevant tables for which you have provided DDL scripts.
That has been mentioned before -- e.g., https://www.sqlservercentral.com/forums/topic/dimensional-table-and-stored-procedure#post-4145269
-
Also follow the forum etiquette post I linked earlier
-
USE [Kazang]
GO
INSERT INTO [dbo].[daily_aggregated_view]
([primary_account_id]
,[Transaction_Date]
,[debit_amount]
,[credit_amount]
,[opening_balance]
,[closing_balance])
VALUES
(<primary_account_id, int,>
,<Transaction_Date, date,>
,<debit_amount, decimal(38,2),>
,<credit_amount, decimal(38,2),>
,<opening_balance, decimal(38,2),>
,<closing_balance, decimal(38,2),>)
GO
--------------
USE [Kazang]
GO
INSERT INTO [dbo].[Transactions]
([transaction_id]
,[Transaction_Date]
,[primary_account_id]
,[parent_account_id]
,[provider_account_id]
,[icp_account_id]
,[group_id]
,[primary_type]
,[transaction_amount]
,[stock_cost]
,[vat]
,[superdealer_vat_reg]
,[vendor_vat_reg]
,[vendor_comm]
,[superdealer_comm]
,[icp_comm]
,[psitek_comm]
,[vendor_rbc_cost]
,[bank_charge]
,[bank_charge_owner]
,[detail_type_name]
,[detail_object_id]
,[primary_account_balance]
,[content_type]
,[reversal_id])
VALUES
(<transaction_id, bigint,>
,<Transaction_Date, date,>
,<primary_account_id, int,>
,<parent_account_id, int,>
,<provider_account_id, int,>
,<icp_account_id, int,>
,<group_id, int,>
,<primary_type, int,>
,<transaction_amount, decimal(18,2),>
,<stock_cost, decimal(18,2),>
,<vat, decimal(18,2),>
,<superdealer_vat_reg, decimal(18,2),>
,<vendor_vat_reg, int,>
,<vendor_comm, decimal(18,2),>
,<superdealer_comm, decimal(18,2),>
,<icp_comm, decimal(18,2),>
,<psitek_comm, decimal(18,2),>
,<vendor_rbc_cost, decimal(18,2),>
,<bank_charge, decimal(18,2),>
,<bank_charge_owner, decimal(18,2),>
,<detail_type_name, varchar(50),>
,<detail_object_id, bigint,>
,<primary_account_balance, decimal(18,2),>
,<content_type, int,>
,<reversal_id, bigint,>)
GO
-------------------
USE [Kazang]
GO
INSERT INTO [dbo].[account_daily_snapshot]
([primary_account_id]
,[Transaction_Date]
,[debit_amount]
,[credit_amount])
VALUES
(<primary_account_id, int,>
,<Transaction_Date, date,>
,<debit_amount, decimal(18,2),>
,<credit_amount, decimal(18,2),>)
GO
-------------------
USE [Kazang]
GO
INSERT INTO [dbo].[AccountCommissionHistory]
([ID]
,[AccountID]
,[GroupID]
,[StartDate]
,[EndDate])
VALUES
(<ID, int,>
,<AccountID, int,>
,<GroupID, int,>
,<StartDate, date,>
,<EndDate, date,>)
GO -
LOL.
No, the point of that is so that people trying to help can have some data to work with. That's just template code... doesn't actually insert any records.
-
OK, so you have given us a BACPAC
Now explain clearly what it is that you need help with, based on this sample data.
As based on your original logic, I still don't understand what it is you need.
Note that the BACPAC doesn't contain any "accounts" the account table is empty, also Commission Groups and AccountCommissionHistory are empty too.
-
Does the dataset meet these requirements?: "Dimensional table and procedure that tracks Primary Accounts’ group movements. An
account moves to different groups that are associated with levels of commission paid.
Analysts needs to follow how they moved over time. Accounts can move back and forth
between groups but can only be assigned to one group within a certain time period. The
Table needs to contain start and end date. A unique Id needs to be created to link to the
records associated to that time and group. Procedure runs daily and appends the previous
days data.
4. 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. The data view should be able to generate the following graph:"
-
@Ant-Green did you have a chance to test the DB according to the requirements?
-
No I didn't, I don't fully understand your requirements.
Please generate what data is needed to be in the "final result set" based on the data in the BACPAC and it may become more clear.
Sample data we have.
Final result we don't have.
-
I provided data in the form of a bacpac file. I am not able to provide data in any other format. The backpack file contains the data as it is in my database with the views and procedures. It is basically just on this forum that I find that the people that assist on here are very difficult in not wanting to download data or a file from a URL. Don't we do that on a daily basis?
- Check the procedure that tracks Primary Accounts group movements and test if this newly created table does contain data in it. I believe there is no data currently in it. The sample data is minimal in terms of the number of days. But I think this procedure should populate this table in question with the daily required data.
- I have created a View and it contains data. Please test the Daily aggregated table or data view along with the procedure that tracts the opening and closingbalances 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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply