Tracking of Accounts data and Stored procedures

Question

Tracking of Accounts data and Stored procedures

April 24, 2023 at 6:56 am

This was removed by the editor as SPAM

Viewing 9 posts - 16 through 23 (of 23 total)

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

Thom A SSC Guru Points: 99133 More actions · Answer 1

I'm not going anywhere near a stranger's Google Drive; I've no idea what sort of payload could be in there. Plus the domain is blocked at the office so strictly that just me quoting your post (which contains a Google Drive URL) and trying to submit it triggered the firewall block and my manager go an email telling them I was trying to access it. If you want to supply sample data, please use DDL and DML.

Thom~

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

yrstruly Hall of Fame Points: 3475 More actions · Answer 2

USE [Kazang]
GO

/****** Object:  StoredProcedure [dbo].[AppendPrimaryAccountGroupTracking]    Script Date: 2023/04/18 18:16:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- Create the stored procedure
CREATE PROCEDURE [dbo].[AppendPrimaryAccountGroupTracking]
    @TransactionDate DATE
AS
BEGIN
    -- Declare variables
    DECLARE @primary_account_id INT;
    DECLARE @group_id INT;
    DECLARE @unique_id UNIQUEIDENTIFIER; -- Added unique_id variable
    
    -- Get the primary account id from Transactions table
    SELECT @primary_account_id = primary_account_id
    FROM [dbo].[Transactions]
    WHERE Transaction_Date = @TransactionDate;
    
    -- Set default value for group_id if not found
    SET @group_id = 0; -- Update this with your desired default value or business logic
    
    -- Check if the primary account id is not null
    IF @primary_account_id IS NOT NULL
    BEGIN
        -- Generate unique_id
        SET @unique_id = NEWID();
        
        -- Insert data into PrimaryAccountGroup table
        INSERT INTO [dbo].[PrimaryAccountGroup] (unique_id, primary_account_id, group_id, start_date, end_date)
        VALUES (@unique_id, @primary_account_id, @group_id, @TransactionDate, NULL);
        
        -- Print success message
        PRINT 'Data inserted into PrimaryAccountGroup table successfully.';
    END
    ELSE
    BEGIN
        -- Print error message
        PRINT 'Error: primary_account_id is null. Data not inserted into PrimaryAccountGroup table.';
    END
END
GO



----------
USE [Kazang]
GO

/****** Object:  StoredProcedure [dbo].[Track_Group_Movements]    Script Date: 2023/04/18 18:17:03 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


  CREATE PROCEDURE [dbo].[Track_Group_Movements]
AS
BEGIN
  DECLARE @PrevDate DATE = DATEADD(DAY, -1, GETDATE()); -- Get previous day's date

  -- Update existing records
  UPDATE Group_Movement
  SET End_Date = @PrevDate
  WHERE Account_ID IN (
    SELECT Account_ID
    FROM Primary_Accounts
    WHERE Move_Date = @PrevDate -- Retrieve accounts that moved on previous day
  ) AND End_Date IS NULL; -- Check if there is an existing record with End Date as NULL

  -- Insert new records
  INSERT INTO Group_Movement (Account_ID, Group_ID, Start_Date, Unique_ID)
  SELECT Account_ID, Group_ID, @PrevDate, NEWID() -- Insert new record with previous day's date and new unique ID
  FROM Primary_Accounts
  WHERE Move_Date = @PrevDate; -- Retrieve accounts that moved on previous day
END;

GO

===========

USE [Kazang]
GO

/****** Object:  StoredProcedure [dbo].[usp_AppendDailyDataToAggregatedView]    Script Date: 2023/04/18 18:17:28 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- Create the stored procedure
CREATE PROCEDURE [dbo].[usp_AppendDailyDataToAggregatedView]
AS
BEGIN
    -- Insert previous day's data into the Transactions table
    INSERT INTO Transactions (Transaction_Date, primary_account_id, primary_type, transaction_amount, primary_account_balance)
    SELECT DATEADD(day, -1, GETDATE()), primary_account_id, primary_type, 0, primary_account_balance
    FROM Transactions
    GROUP BY primary_account_id, primary_type, primary_account_balance

    -- Update the transaction_amount for the previous day's data
    UPDATE Transactions
    SET transaction_amount = (SELECT SUM(CASE WHEN primary_type = 1 THEN transaction_amount ELSE -transaction_amount END)
                               FROM Transactions t2
                               WHERE t2.primary_account_id = Transactions.primary_account_id
                               AND t2.Transaction_Date = DATEADD(day, -1, GETDATE()))
    WHERE Transaction_Date = DATEADD(day, -1, GETDATE())

    -- Refresh the daily_aggregated_view
    EXEC sp_refreshview 'dbo.daily_aggregated_view'
END
GO


====

USE [Kazang]
GO

/****** Object:  Table [dbo].[PrimaryAccountGroup]    Script Date: 2023/04/18 18:17:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PrimaryAccountGroup](
[id] [int] IDENTITY(1,1) NOT NULL,
[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,
PRIMARY KEY CLUSTERED 
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

frederico_fonseca SSCoach Points: 16164 More actions · Answer 3

YOU (not us) need to debug your code - this means going step by step through it and seeing if each step has the data you are expecting.

not going to give you instructions for all of them, but the simple one

AppendPrimaryAccountGroupTracking -

you are executing it as

EXEC@return_value = [dbo].[AppendPrimaryAccountGroupTracking]
@TransactionDate = '2023-01-01'

your code does

SELECT @primary_account_id = primary_account_id
FROM [dbo].[Transactions]
WHERE Transaction_Date = @TransactionDate;

so your first step of debugging is

SELECT primary_account_id
FROM [dbo].[Transactions]
WHERE Transaction_Date = '2023-01-01' ';

(do note that if transactions contains more then 1 row for a date, and if there are multiple primary_account_id you have your first error.)

This reply was modified 1 year, 8 months ago by frederico_fonseca.

pietlinden SSC Guru Points: 63506 More actions · Answer 4

Wow. Just wow.

I've heard of "biting the hand that feeds me" but of all the hands to bite, that's perhaps the worst one.

Don't you got no home training??

yrstruly Hall of Fame Points: 3475 More actions · Answer 5

If your intention is not to help me, keep your negative comments and let those who want to help me, assist.

Dont, understand why the "trolls" on here want to chip in when clearly they have other intentions than assisting.

I DONT NEED IT.

Thom A SSC Guru Points: 99133 More actions · Answer 6

yrstruly wrote:

If your intention is not to help me, keep your negative comments and let those who want to help me, assist.
Dont, understand why the "trolls" on here want to chip in when clearly they have other intentions than assisting.
I DONT NEED IT.

The fact that we've been responding, and asking for additional details shows that we do (or probably more accurately now, did) want to help, however, we've been lacking the information to do so. We still are, as we're still lacking the DDL and DML for some of your objects, such as dbo.Transactions, meaning we can't run your procedure to see it returning no data when it (presumably) does contain data for that date.

Thom~

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

yrstruly Hall of Fame Points: 3475 More actions · Answer 7

Thank you for the assistance. Here follows the DDL n DML

-- Create Transactions Table
CREATE TABLE [dbo].[Transactions](
[id] [int] IDENTITY(1,1) NOT NULL,
[transaction_date] [datetime] NOT NULL,
[primary_account_id] [int] NOT NULL,
[group_id] [int] NULL,
[transaction_amount] [decimal](18, 2) NULL,
PRIMARY KEY CLUSTERED 
(
[id] ASC
)
) ON [PRIMARY]
------
-- Insert Data into Transactions Table
INSERT INTO [dbo].[Transactions] ([transaction_date], [primary_account_id], [group_id], [transaction_amount])
VALUES ('2023-04-18 10:30:00', 1, 1001, 50.00),
   ('2023-04-18 11:45:00', 2, 1002, 25.00),
   ('2023-04-18 14:15:00', 3, 1003, 10.00)

The TSQL I used:

USE [Kazang]
GO

-- Create Dimensional Table to track Primary Account Group Movements
CREATE TABLE [dbo].[PrimaryAccountGroupMovements](
[id] [int] IDENTITY(1,1) NOT NULL,
[primary_account_id] [int] NOT NULL,
[group_id] [int] NOT NULL,
[transaction_amount] null,
[start_date] [date] NOT NULL,
[end_date] [date] NULL,
PRIMARY KEY CLUSTERED 
(
[id] ASC
),
UNIQUE NONCLUSTERED 
(
[primary_account_id] ASC,
[group_id] ASC,
[start_date] ASC
)
) ON [PRIMARY]
GO

-- Create Procedure to Append Previous Day's Data
CREATE PROCEDURE [dbo].[AppendPreviousDayData]
AS
BEGIN
-- Insert into PrimaryAccountGroupMovements
INSERT INTO [dbo].[PrimaryAccountGroupMovements] ([primary_account_id], [group_id], [start_date], [end_date])
SELECT DISTINCT [primary_account_id], [group_id], DATEADD(MINUTE, 0, CONVERT(DATETIME, CONVERT(DATE, [Transaction_Date]))) AS [start_date], DATEADD(MINUTE, -1, DATEADD(DAY, 1, CONVERT(DATETIME, CONVERT(DATE, [Transaction_Date])))) AS [end_date]
FROM [dbo].[Transactions]
WHERE [Transaction_Date] <= DATEADD(DAY, -1, GETDATE()) -- Retrieve all previous data
AND [group_id] IS NOT NULL -- Exclude records where group_id is null
AND NOT EXISTS (
SELECT 1
FROM [dbo].[PrimaryAccountGroupMovements]
WHERE [primary_account_id] = [dbo].[Transactions].[primary_account_id]
AND [group_id] = [dbo].[Transactions].[group_id]
AND [start_date] = DATEADD(MINUTE, 0, CONVERT(DATETIME, CONVERT(DATE, [Transaction_Date])))
)
END
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[AppendPreviousDayData]

SELECT 'Return Value' = @return_value

GO

Please mention any recommendations.

When I query PrimaryAccountGroupMovements: sql

Thom A SSC Guru Points: 99133 More actions · Answer 8

Well there we go... There are no rows in your table (at least the sample data you've given) where your clause Transaction_Date = '2023-01-01' is true.

Thom~

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