January 27, 2023 at 5:14 pm
Please see my DDL.
I would like to create:
a) Dimensional table that tracks Primary Accounts’ group movements. An account moves to
different groups that are associated with levels of commission paid. Analysts need 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 dates. A unique Id needs to be created to link to the records associated with that time and
group.
b)Daily aggregated table or data view 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.
Can these tables be Stored Procedures to automate the process or are Views relevant in this scenario?
/****** Object: Table [dbo].[Transaction] Script Date: 2023/01/27 19:12:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Transaction](
[id] [varchar](50) NULL,
[created] [varchar](50) NULL,
[primary_account_id] [varchar](50) NULL,
[parent_account_id] [varchar](50) NULL,
[provider_account_id] [varchar](50) NULL,
[icp_account_id] [varchar](50) NULL,
[group_id] [varchar](50) NULL,
[primary_type] [varchar](50) NULL,
[transaction_amount] [varchar](50) NULL,
[stock_cost] [varchar](50) NULL,
[vat] [varchar](50) NULL,
[superdealer_vat_reg] [varchar](50) NULL,
[vendor_vat_reg] [varchar](50) NULL,
[vendor_comm] [varchar](50) NULL,
[superdealer_comm] [varchar](50) NULL,
[icp_comm] [varchar](50) NULL,
[psitek_comm] [varchar](50) NULL,
[vendor_rbc_cost] [varchar](50) NULL,
[bank_charge] [varchar](50) NULL,
[bank_charge_owner] [varchar](50) NULL,
[detail_type_name] [varchar](50) NULL,
[detail_object_id] [varchar](50) NULL,
[primary_account_balance] [varchar](50) NULL,
[content_type] [varchar](50) NULL,
[reversal_id] [varchar](50) NULL,
[comment] [varchar](50) NULL
) ON [PRIMARY]
GO
--------------------------------
/****** Object: Table [dbo].[GeneralG] Script Date: 2023/01/27 19:12:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GeneralG](
[ContentType_Provider_id] [smallint] NOT NULL,
[created] [datetime] NULL,
[modified] [datetime] NULL,
[name] [varchar](30) NULL,
[description] [varchar](94) NULL,
PRIMARY KEY CLUSTERED
(
[ContentType_Provider_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
-------------------
/****** Object: Table [dbo].[Currency] Script Date: 2023/01/27 19:13:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Currency](
[Currency_id] [smallint] NOT NULL,[varchar](3) NULL,
[prefix] [varchar](3) NULL,
[name] [varchar](18) NULL,
[smallest_unit] [varchar](5) NULL,
[format_string] [varchar](4) NULL,
PRIMARY KEY CLUSTERED
(
[Currency_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
--------------------
/****** Object: Table [dbo].[ContentType] Script Date: 2023/01/27 19:13:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ContentType](
[ContentTypeManager_id] [smallint] NOT NULL,
[created] [datetime] NULL,
[modified] [datetime] NULL,
[description] [varchar](26) NULL,
[currency_id] [smallint] NULL,
[interface_id] [smallint] NULL,
[barcode] [real] NULL,
[barcode_type_id] [smallint] NULL,
[value] [varchar](7) NULL,
[cost] [varchar](8) NULL,
[status] [smallint] NULL,
[stock_item] [smallint] NULL,
[stock_code] [varchar](9) NULL,
[content_type_provider_id] [smallint] NULL,
[dynamic_amount] [smallint] NULL,
[profit_available] [varchar](9) NULL,
[category_id] [smallint] NULL,
[priority] [smallint] NULL,
PRIMARY KEY CLUSTERED
(
[ContentTypeManager_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
---------------------
/****** Object: Table [dbo].[ContentManager] Script Date: 2023/01/27 19:14:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ContentManager](
[ContentType_Provider_id] [smallint] NOT NULL,
[created] [datetime] NULL,
[modified] [datetime] NULL,
[name] [varchar](30) NULL,
[description] [varchar](94) NULL,
PRIMARY KEY CLUSTERED
(
[ContentType_Provider_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
January 28, 2023 at 6:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
January 31, 2023 at 3:24 pm
Please see my DDL.
I would like to create:
a) Dimensional table that tracks Primary Accounts’ group movements. An account moves to different groups that are associated with levels of commission paid. Analysts need 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 dates. A unique Id needs to be created to link to the records associated with that time and group.
b)Daily aggregated table or data view 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.
Can these tables be Stored Procedures to automate the process or are Views relevant in this scenario?
?? Tables can't be Stored Procedures, no.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply