October 23, 2020 at 2:50 pm
I have two tables called SubAccounts and AccountCategories. Table definitions are as follows:
CREATE TABLE [dbo].[SubAccounts](
[Sub] [char](3) NOT NULL,
[Descr] [varchar](50) NOT NULL,
[Sort] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[AccountCategories](
[Category] [varchar](50) NOT NULL,
[AccountList] [varchar](255) NOT NULL,
[Sort] [int] NOT NULL
) ON [PRIMARY]
The Subaccounts table has just that - a list of Subaccounts and their descriptions (1 per row).
The AccountCategories table has a little more involved data. It contains a description (category description) and a SQL-type clause that would be used to get data for that category.
Sample data is as follows:
Sample data is as follows:
INSERT INTO SubAccounts
(Sub, Descr, Sort)
SELECT '000','SUB DESC 1','0' UNION ALL
SELECT '001','SUB DESC 2','1' UNION ALL
SELECT '002','SUB DESC 3','2' UNION ALL
SELECT '003','SUB DESC 4','3' UNION ALL
SELECT '004','SUB DESC 5','4' UNION ALL
SELECT '005','SUB DESC 6','5' UNION ALL
SELECT '006','SUB DESC 7','6' UNION ALL
SELECT '007','SUB DESC 8','7' UNION ALL
SELECT '008','SUB DESC 9','8' UNION ALL
SELECT '009','SUB DESC 10','9'
INSERT INTO AccountCategories
(Category,AccountList, Sort)
SELECT 'Description 1',' =''ACCT1''','0' UNION ALL
SELECT 'Description 2','BETWEEN ''ACCT2'' AND ''ACCT4''','1' UNION ALL
SELECT 'Description 3','IN (''ACCT5'',''ACCT6'')','2' UNION ALL
SELECT 'Description 4',' =''ACCT7''','3' UNION ALL
SELECT 'Description 5',' =''ACCT8''','4'
The result I'm trying to achive would look like the output of this SQL query:
CREATE TABLE [dbo].[SampleResults](
[Account] [varchar](50) NOT NULL,
[varchar](255) NOT NULL,
[varchar](255) NOT NULL,
[varchar](255) NOT NULL,
[varchar](255) NOT NULL,
[varchar](255) NOT NULL,
[varchar](255) NOT NULL,
[varchar](255) NOT NULL,
[varchar](255) NOT NULL,
[varchar](255) NOT NULL,
[varchar](255) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO SampleResults VALUES ('Description 1','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''000''>','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''001''>','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''002''>','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''003''>','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''004''>','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''005''>','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''006''>','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''007''>','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''008''>','<DATA HERE WILL BE FOR Account=''ACCT1'' AND Sub=''009''>')
INSERT INTO SampleResults VALUES ('Description 2','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''000''>','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''001''>','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''002''>','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''003''>','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''004''>','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''005''>','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''006''>','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''007''>','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''008''>','<DATA HERE WILL BE FOR Account BETWEEN ''ACCT2'' AND ''ACCT4'' AND Sub=''009''>')
INSERT INTO SampleResults VALUES ('Description 3','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''000''>','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''001''>','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''002''>','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''003''>','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''004''>','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''005''>','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''006''>','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''007''>','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''008''>','<DATA HERE WILL BE FOR Account IN (''ACCT5'',''ACCT6'') AND Sub=''009''>')
INSERT INTO SampleResults VALUES ('Description 4','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''000''>','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''001''>','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''002''>','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''003''>','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''004''>','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''005''>','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''006''>','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''007''>','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''008''>','<DATA HERE WILL BE FOR Account=''ACCT7'' AND Sub=''009''>')
INSERT INTO SampleResults VALUES ('Description 5','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''000''>','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''001''>','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''002''>','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''003''>','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''004''>','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''005''>','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''006''>','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''007''>','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''008''>','<DATA HERE WILL BE FOR Account=''ACCT8'' AND Sub=''009''>')
SELECT * FROM SampleResults
The values in the SUB DESC 1 through SUB DESC 10 columns explain how I want to populate that data.
The DATA would come from a third table by SQL statement (yet to be developed). That's where the Account column is (and there's obviously a Sub column as well).
I don't even know where to start with something like this. How would I be best to go about creating this, given what I have?
October 23, 2020 at 3:51 pm
This table structure is, and I don't use the word lightly, horrendous.
Is it set in stone, or is there the potential to remodel this into something which follows basic relational principles?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 23, 2020 at 3:54 pm
Potentially. What suggestions do you have?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply