April 14, 2008 at 7:31 am
Hi,
Im trying to create a view, but I'm having some trouble with it.
The Data in the main source table is something like this:
AccountBudgetID
Month
AccountID
BudgetAmount
I then do a inner join to the account table, the get the actual account name/code
that works great.
Next step is to have a column in stead of BugetAmount Display the month.
Then Output Im looking for is:
AccountBudgetID, AccountName, Jan, Feb, Mar, Apr, May etc....
Any one know if that is possible?
April 14, 2008 at 11:28 pm
Ok no idea what i wrote yesterday. It's even confusing to me :w00t:.
with the output as i stated yesterday
AccountBudgetID, AccountName, Jan, Feb, Mar, Apr, May etc....
In each month column, it should list the budgetamount.
ie
AccountBudgetIDAccountNameJanFebMarAprMay
1 name1100110120150200
2 name2150150150150150
3 name300000
4 name4204080160320
April 14, 2008 at 11:41 pm
Hi
You will have to use "Case" statements for this.
Iam not up to writing the statement for you now... Sorry abt that. Search this site and you will find many examples.
"Keep Trying"
April 15, 2008 at 5:55 am
this is what i came up with.
SELECT DISTINCT
(SELECT Name
FROM dbo.Account
WHERE (AccountID = AB.AccountID)) AS Account,
(SELECT Code
FROM dbo.Account AS Account_1
WHERE (AccountID = AB.AccountID)) AS AccountCode,
(SELECT BudgetAmount
FROM dbo.AccountBudget
WHERE (AccMonth = 200801) AND (AccountID = AB.AccountID)) AS Jan,
(SELECT BudgetAmount
FROM dbo.AccountBudget AS AccountBudget_11
WHERE (AccMonth = 200802) AND (AccountID = AB.AccountID)) AS Feb,
(SELECT BudgetAmount
FROM dbo.AccountBudget AS AccountBudget_10
WHERE (AccMonth = 200803) AND (AccountID = AB.AccountID)) AS Mar,
(SELECT BudgetAmount
FROM dbo.AccountBudget AS AccountBudget_9
WHERE (AccMonth = 200804) AND (AccountID = AB.AccountID)) AS Apr,
(SELECT BudgetAmount
FROM dbo.AccountBudget AS AccountBudget_8
WHERE (AccMonth = 200805) AND (AccountID = AB.AccountID)) AS May,
(SELECT BudgetAmount
FROM dbo.AccountBudget AS AccountBudget_7
WHERE (AccMonth = 200806) AND (AccountID = AB.AccountID)) AS Jun,
(SELECT BudgetAmount
FROM dbo.AccountBudget AS AccountBudget_6
WHERE (AccMonth = 200807) AND (AccountID = AB.AccountID)) AS Jul,
(SELECT BudgetAmount
FROM dbo.AccountBudget AS AccountBudget_5
WHERE (AccMonth = 200808) AND (AccountID = AB.AccountID)) AS Aug,
(SELECT BudgetAmount
FROM dbo.AccountBudget AS AccountBudget_4
WHERE (AccMonth = 200809) AND (AccountID = AB.AccountID)) AS Sep,
(SELECT BudgetAmount
FROM dbo.AccountBudget AS AccountBudget_3
WHERE (AccMonth = 200810) AND (AccountID = AB.AccountID)) AS Oct,
(SELECT BudgetAmount
FROM dbo.AccountBudget AS AccountBudget_2
WHERE (AccMonth = 200811) AND (AccountID = AB.AccountID)) AS Nov,
(SELECT BudgetAmount
FROM dbo.AccountBudget AS AccountBudget_1
WHERE (AccMonth = 200812) AND (AccountID = AB.AccountID)) AS Dec
FROM dbo.AccountBudget AS AB
ORDER BY AccountCode
Needless to say, this query takes Looooong. Just to run the query in Query analyzer, takes about 3.5 seconds.
April 15, 2008 at 8:45 am
15 correlated sub-queries will do that (kill performance).
Use the CASE syntax like Chirag pointed out. Looks something like this:
SELECTNAME,
Code,
max(case when (AccMonth = 200801) then amount end) as Jan,
max(case when (AccMonth = 200802) then amount end) as Apr,
max(case when (AccMonth = 200803) then amount end) as Mar,
max(case when (AccMonth = 200804) then amount end) as Apr,
max(case when (AccMonth = 200805) then amount end) as May,
max(case when (AccMonth = 200806) then amount end) as Jun,
max(case when (AccMonth = 200807) then amount end) as Jul,
max(case when (AccMonth = 200808) then amount end) as Aug,
max(case when (AccMonth = 200809) then amount end) as Sep,
max(case when (AccMonth = 200810) then amount end) as Oct,
max(case when (AccMonth = 200811) then amount end) as Nov,
max(case when (AccMonth = 200812) then amount end) as [Dec]
from dbo.AccountBudget
group by name, code
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply