Tracking of Accounts data and Stored procedures
-
Please find my sample db here:https://drive.google.com/file/d/1_8JWxOrAdKEz1dxKITnqW3HZr1GTBgB9/view
- I have a DB that I would like to track account movements(I created an "Accounts" table)
- I also want to track "AccountsCommisonHistory"(I also created this table).
- I created an "Accounts_daily_snapshot" and "CommisionGroups" table, but it contains no records as in the above tables. Where is the error in my code/tables that does not give me the desired results?
Please Note: My day starts at midnight and ends at 23:59. I am open to any suggestions and improvements in my code etc.
--NEXT DAY AND EVERY DAY
DECLARE @EffectiveDate DATE = '2023-02-02',
@FutureEndDate DATE = '9999-12-31' -
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
-
I did attempt to import your bacpac, but I don't think it's configured correctly:
Could not load file or assembly 'System.Resources.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040) (mscorlib)
Can you explain further what it is that you're trying to do and how you're trying to do it? You're talking about creating tables, but you don't talk about what code puts data into those tables. I just can't answer based on the information you've provided. It's unclear where the data is coming from, how it's supposed to move betwen the tables, and how it is that creating another table is going to magically get filled from data from the first two tables... also, why are you creating multiple data stores for the same data. Can't you just query it to get what you need?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - Grant Fritchey wrote:
I did attempt to import your bacpac, ...
I'll likely never make the attempt on these forums because it's not permanent to this forum. If it goes away, any answer is close to useless to others in the future.
--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 wrote:Grant Fritchey wrote:
I did attempt to import your bacpac, ...
I'll likely never make the attempt on these forums because it's not permanent to this forum. If it goes away, any answer is close to useless to others in the future.
if I could have seen what was going on (and I'll bet I couldn't see it, I think info is missing here), I'd have posted the code. However, no arguments on my side.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Thank you for assisting.
I have the test data available in re-created bacpac() and Excel format() and attached.
Besides the requirements below, you are welcome to make changes or recommend changes so I can meet the requirements below:
Dimensional table and procedure that tracks Primary Accounts’ group movements:
Determine the necessary fields for the table, including primary account identifier, group identifier, start and end date, and unique identifier.
Create the dimensional table with the necessary fields.
Determine the process for populating the table with data, such as extracting data from source systems or receiving manual input.
Create a procedure that runs daily and appends the previous day's data to the table.
Test the procedure to ensure data is accurately and consistently captured in the table.
Daily aggregated table or data view along with the procedure that tracks the opening and closing balances of accounts along with debited and credited amounts:
Determine the necessary fields for the table or data view, including account identifier, date, opening balance, closing balance, debited amount, and credited amount.
-
This sounds like homework. Is this homework? In which case, I'll help where you have problems with the work you've done. Please note the tricky phrase, the work you've done. I'm not doing your homework because I won't get graded.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - Grant Fritchey wrote:
This sounds like homework. Is this homework? In which case, I'll help where you have problems with the work you've done. Please note the tricky phrase, the work you've done. I'm not doing your homework because I won't get graded.
I never asked you in the first place.
-
Posting DDL & insert scripts rather than links to download is more likely to get you help.
Not getting snippy with a generous expert like Grant is another way to increase your chances of receiving help.
-
What does this insert script look like?
- yrstruly wrote:
What does this insert script look like?
have you even bothered reading the link that ratbak provided? your reply clearly indicates you didn't.
-
I have created:
USE [Kazang]
GO
-- Create the dimensional table for group tracking
CREATE TABLE [dbo].[PrimaryAccountGroupTracking](
[id] [int] IDENTITY(1,1) PRIMARY KEY,
[primary_account_id] [int] NOT NULL,
[group_id] [int] NOT NULL,
[start_date] [date] NOT NULL,
[end_date] [date] NULL,
[unique_id] [varchar](50) NOT NULL
)
USE [Kazang]
GO
-- Create the procedure for appending data daily
CREATE PROCEDURE [dbo].[AppendPrimaryAccountGroupTracking]
AS
BEGIN
-- Insert new data into the dimensional table for today's date
INSERT INTO [dbo].[PrimaryAccountGroupTracking] ([primary_account_id], [group_id], [start_date], [unique_id])
SELECT [primary_account_id], [group_id], GETDATE(), NEWID()
FROM [dbo].[Transactions]
WHERE [Transaction_Date] = CAST(GETDATE() AS DATE)
-- Update end_date for previous day's data if necessary
UPDATE [dbo].[PrimaryAccountGroupTracking]
SET [end_date] = DATEADD(DAY, -1, GETDATE())
WHERE [end_date] IS NULL
AND [start_date] < CAST(GETDATE() AS DATE)
ENDI ran the stored procedure and still nothing in the table "PrimaryAccountGroupTracking"
Still no data in this table.
-
This code:
USE [Kazang]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[AppendPrimaryAccountGroupTracking]
@TransactionDate = '2023-01-01'
SELECT'Return Value' = @return_value
GOgives me an output of:
- yrstruly wrote:
I ran the stored procedure and still nothing in the table "PrimaryAccountGroupTracking"
Still no data in this table.
That would suggest that there is no data in your table
[dbo].[Transactions]for the date you ran the query. We don't have the definition of that table, nor any sample data, so it's difficult for us to replicate the problem you're having without guess work on the data you have.Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply