Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  General SQL Server Forums  New to SQL Server Programming  Column Reference issue

Author  Topic  rc1138 Starting Member 35 Posts Posted - 2010-08-31 : 18:55:18 Hi, Was wondering if anyone can help out regarding this column reference issue Column reference Query First table Budget_Accounts [Id] [int] IDENTITY(1,1) NOT NULL, [acctNum] [int] NOT NULL, Id acctNum 1 1903 2 1909 3 1910 4 1911 5 1912 6 1914 2nd Table BudgetGT [Id] [int] IDENTITY(1,1) NOT NULL, [StoreNbr] [int] NOT NULL, [1903] [numeric](9, 2) NULL, [1909] [numeric](9, 2) NULL, [1910] [numeric](9, 2) NULL, [1911] [numeric](9, 2) NULL, [1912] [numeric](9, 2) NULL, [1914] [numeric](9, 2) NULL, Id StoreNbr 1903 1909 1910 1911 1912 1914 1 3037 1000 null 0.00 50,000 1000 null 2 3145 Null 1000 Null Null Null Null 3rd Table BudgetSS [Id] [int] IDENTITY(1,1) NOT NULL, [StoreNbr] [int] NOT NULL, [1903] [numeric](9, 2) NULL, [1909] [numeric](9, 2) NULL, [1910] [numeric](9, 2) NULL, [1911] [numeric](9, 2) NULL, [1912] [numeric](9, 2) NULL, [1914] [numeric](9, 2) NULL, Id StoreNbr 1903 1909 1910 1911 1912 1914 1 3037 1000 null 0.00 50,000 1000 null 2 3145 Null 1000 Null Null Null Null Last Table AccountData [Id] [int] IDENTITY(1,1) NOT NULL, [store_nbr] [int] NOT NULL, [data_type] [nvarchar](50) NOT NULL, [acct_1] [nvarchar](70) NULL, [amt_1] [money] NULL, [acct_2] [nvarchar](70) NULL, [amt_2] [money] NULL, [acct_3] [nvarchar](70) NULL, [amt_3] [money] NULL, Id store_nbr data_type acct_1 amt_1 acct_2 amt_2 acct_3 amt_3 1 3037 Expense 1911 – Associate Wages 121.90 No Account# Chosen 0.00 No Account# 0.00 2 3037 Growth Team 1911 – Associate Wages 50000.00 No Account # Chosen 0.00 No Account# 0.00 The last 3 tables are populated via webforms. The first table has static information so nothing will be added to it. Conditions – Depending on what is in the data_type column the amounts will be filtered based on wether it is an SS amount or a GT amount. If the data_type = anything other than Growth Team it should be filtered in an SS amount otherwise if it is Growth Team then it goes to GG amount. The intended output of my query is as: Account# SS Spent SS Budget GT Spent GT Budget Total Spent Total Budget Remaining Budget 1903 0.00 1000 0.00 1000 0.00 2000 2000 1909 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1910 0.00 0.00 0.00 0.00 0.00 0.00 0.00 1911 121.90 50000.00 50000.00 50000.00 50121.90 100000 49878.1 1912 0.00 1000 0.00 1000 0.00 2000 2000 1914 0.00 0.00 0.00 0.00 0.00 0.00 0.00 What I initially did to achieve the above output was create 6 more tables that handles the filtering when the user enters information in the webform. That was a bit shortsighted as I forgot that users can actually delete data from the last table. When user deletes the data from the last table it does not delete the other items on the other table so the above gridview still shows the amounts on it despite the user already deleting that particular amount. This was my original query SELECT a.acctNum AS [Account Number], a.acctDesc AS [Account Name], sum(isnull(d .Amt2 + d .Amt3 + d .Amt4, 0)) AS [SS Spent], sum(isnull(d .Amt5, 0)) AS [SS Budget], sum(isnull(d .Amt6 + d .Amt7 + d .Amt8, 0)) AS [GT Spent], sum(isnull(d .Amt9, 0)) AS [GT Budget], sum(isnull((d .Amt2 + d .Amt3 + d .Amt4) + (d .Amt6 + d .Amt7 + d .Amt8), 0)) AS [Total Spent], sum(isnull(d .Amt5 + d .Amt9, 0)) AS [Total Budget], sum(isnull((d .Amt5 + d .Amt9) - (d .Amt2 + d .Amt3 + d .Amt4) + (d .Amt6 + d .Amt7 + d .Amt8), 0)) AS [Remaining Budget] FROM Budget_Accounts a LEFT JOIN (SELECT account, CASE WHEN t = 2 THEN amt ELSE 0 END, CASE WHEN t = 3 THEN amt ELSE 0 END, CASE WHEN t = 4 THEN amt ELSE 0 END, CASE WHEN t = 5 THEN amt ELSE 0 END, CASE WHEN t = 6 THEN amt ELSE 0 END, CASE WHEN t = 7 THEN amt ELSE 0 END, CASE WHEN t = 8 THEN amt ELSE 0 END, CASE WHEN t = 9 THEN amt ELSE 0 END FROM (SELECT '2' [t], StoreNbr, [1903], [1909], [1910], [1911], [1912], [1914] FROM BudgetSSAmount1 WHERE StoreNbr = @StoreNbr UNION ALL SELECT '3', StoreNbr, [1903], [1909], [1910], [1911], [1912], [1914] FROM BudgetSSAmount2 WHERE StoreNbr = @StoreNbr UNION ALL SELECT '4', StoreNbr, [1903], [1909], [1910], [1911], [1912], [1914] FROM BudgetSSAmount3 WHERE StoreNbr = @StoreNbr UNION ALL SELECT '5', StoreNbr, [1903], [1909], [1910], [1911], [1912], [1914] FROM BudgetSSTest WHERE StoreNbr = @StoreNbr UNION ALL SELECT '6', StoreNbr, [1903], [1909], [1910], [1911], [1912], [1914] FROM BudgetGTAmount1 WHERE StoreNbr = @StoreNbr UNION ALL SELECT '7', StoreNbr, [1903], [1909], [1910], [1911], [1912], [1914] FROM BudgetGTAmount2 WHERE StoreNbr = @StoreNbr UNION ALL SELECT '8', StoreNbr, [1903], [1909], [1910], [1911], [1912], [1914] FROM BudgetGTAmount3 WHERE StoreNbr = @StoreNbr UNION ALL SELECT '9' [t], StoreNbr, [1903], [1909], [1910], [1911], [1912], [1914] FROM BudgetGTTest WHERE StoreNbr = @StoreNbr) AS [p] UNPIVOT (Amt FOR Account IN ([1903], [1909], [1910], [1911], [1912], [1914])) AS [u]) d (Account, Amt2, Amt3, Amt4, Amt5, Amt6, Amt7, Amt8, Amt9) ON a.acctNum = d .Account GROUP BY acctNum, acctDesc The @StoreNbr value is taken from a query string. So instead of using the 10 tables I was wondering if the same result is achievable with the 4 tables so when users delete entries it actually reflects it on the gridview