June 29, 2010 at 12:46 pm
Hi, I am a novice in sql using excel to query a SQL database.
I want to combine the results from the following two queries where the ORG and Period corresponds, but since the relationships between the two tables or not one to one I end up with missing lines
The results should look like this:
Org PeriodAccount Amount budgetAmount
From the follwowing two tables
Table One: Budgets
SELECT IntGLBudgets.Org, IntGLBudgets.period, IntGLBudgets.account, IntGLBudgets.budgetAmount
FROM SSI.dbo.IntGLBudgets IntGLBudgets
WHERE (IntGLBudgets.Org=?) AND (IntGLBudgets.period>=?)
Table Two: Actuals
SELECT GLSummary.Org, GLSummary.Period, GLSummary.Account, GLSummary.Amount
FROM SSI.dbo.GLSummary GLSummary
WHERE (GLSummary.Org =?) AND (GLSummary.Period>=?)
The actuals have records in the table that does not exist in the budget and vice versa for the budget data.
I need the result to include all the lines from both tables, subject to the parameters though.
Any help or direction woudl be appreciated. Thansk in advance, Pieter:unsure:
June 29, 2010 at 1:00 pm
I'm confused, are you combining results from both tables or trying to join them together? There are two things here.
If I have two tables, A and B, as this
TableA
------
1
2
3
4
TableB
-------
1
A
B
C
And I combine them, I get
1
2
3
4
1
A
B
C
(I could remove the dup_
If I join them, I might have something like
1 1
2
3
4
A
B
C
Or I could be missing the rows that don't match (the 1 matches in this case)
June 29, 2010 at 1:00 pm
If I understand correctly, in either table you can have no records for a match in the other table, but you want to show the data from both tables. correct?
If so, you'll need to perform a LEFT JOIN between the tables to get the values (present or missing) from the tables. But you will then need to reverse this and union the results togeter.
Like this:
declare @Org int, @period int -- will be parameters to the procedure
SELECT IntGL.Org, IntGL.period, IntGL.account, GL.Amount, IntGL.budgetAmount
FROM SSI.dbo.IntGLBudgets IntGL
LEFT JOIN SSI.dbo.GLSummary GL
ON IntGL.Org = GL.Org
AND IntGL.period = GL.period
AND IntGL.account = GL.account
WHERE (IntGL.Org=@Org) AND (IntGL.period>=@Period)
UNION
SELECT GL.Org, GL.period, GL.account, GL.Amount, IntGL.budgetAmount
FROM SSI.dbo.GLSummary GL
LEFT JOIN SSI.dbo.IntGLBudgets IntGL
ON IntGL.Org = GL.Org
AND IntGL.period = GL.period
AND IntGL.account = GL.account
WHERE (GL.Org =@Org) AND (GL.Period>=@Period)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 29, 2010 at 1:09 pm
Wayne
Thanks, your understanding of the question is correct.
I just need to figure out how to link the parameters in the worksheet to the declare statement and then test the query.
Thanks in the meantime.
June 29, 2010 at 1:28 pm
If I read this correctly (I probably didn't), it sounds like a union of the two.
June 29, 2010 at 1:35 pm
I have changes the suggest query to:
declare @Org text, @period int -- will be parameters to the procedure
SET @Org = 'RSA/JNB/T00'
SET @period =201001
SELECT IntGL.Org, IntGL.period, IntGL.account, GL.Amount, IntGL.budgetAmount
FROM SSI.dbo.IntGLBudgets IntGL
LEFT JOIN SSI.dbo.GLSummary GL
ON IntGL.Org = GL.Org
AND IntGL.period = GL.period
AND IntGL.account = GL.account
WHERE (IntGL.Org=@Org) AND (IntGL.period>=@Period)
UNION
SELECT GL.Org, GL.period, GL.account, GL.Amount, IntGL.budgetAmount
FROM SSI.dbo.GLSummary GL
LEFT JOIN SSI.dbo.IntGLBudgets IntGL
ON IntGL.Org = GL.Org
AND IntGL.period = GL.period
AND IntGL.account = GL.account
WHERE (GL.Org =@Org) AND (GL.Period>=@Period)
when running the query Excel Responds with the following error message:
The text, ntext and image data types are invalid for local variables
June 29, 2010 at 1:46 pm
you can do this using
UNION (if you want no duplicates)
or UNION ALL (if you want duplicate rows too)
Thanks [/font]
June 29, 2010 at 1:51 pm
I did notice the word UNION from three posts. It was also suggested at the office.
As a novice though I have no idea what to do with it, (and I did try examples following a google search)
June 29, 2010 at 2:05 pm
UNION combines two queries. Need the same number of fields, but
-- query 1
select fielda, fieldB
from OneTable
UNION
-- query 2
Select FieldC, FieldD
from AnotherTable
June 29, 2010 at 2:13 pm
Steve Jones - Editor (6/29/2010)
UNION combines two queries. Need the same number of fields, but
-- query 1select fielda, fieldB
from OneTable
UNION
-- query 2
Select FieldC, FieldD
from AnotherTable
Thanks Steve, but how to I get the Actuals and the Budget values in seperate columns / fields?
June 29, 2010 at 2:20 pm
Is this Actuals? Or Budget?
SELECT IntGL.Org, IntGL.period, IntGL.account, GL.Amount, IntGL.budgetAmount
FROM SSI.dbo.IntGLBudgets IntGL
LEFT JOIN SSI.dbo.GLSummary GL
ON IntGL.Org = GL.Org
AND IntGL.period = GL.period
AND IntGL.account = GL.account
WHERE (IntGL.Org=@Org) AND (IntGL.period>=@Period)
I'm a little unclear about what you want for results? It looks like you are joining budget to actuals already, or is that not correct?
If you have two queries, one with actuals, one with budgets, you can do something like:
select
a.Period,
a.BudgetAmt 'Budget',
0 'Actual'
from BudgetTable a
UNION
select
b.Period,
0 'Budget',
b.ActualAmt 'Actual'
from ActualTable b
June 29, 2010 at 2:28 pm
Steve Jones - Editor (6/29/2010)
Is this Actuals? Or Budget?I'm a little unclear about what you want for results? It looks like you are joining budget to actuals already, or is that not correct?
Steve
The budgeted numbers are in one table whereas the actuals are in another.
I want to combine the two tables (subject to the parameters period and organisation into a combined table that should look like this:
Org Period Account Amount budgetAmount
The existing queries that I do have is
From the follwowing two tables
Table One: IntGLBudgets
SELECT IntGLBudgets.Org, IntGLBudgets.period, IntGLBudgets.account, IntGLBudgets.budgetAmount
FROM SSI.dbo.IntGLBudgets IntGLBudgets
WHERE (IntGLBudgets.Org=?) AND (IntGLBudgets.period>=?)
Table Two: GLSummary
SELECT GLSummary.Org, GLSummary.Period, GLSummary.Account, GLSummary.Amount
FROM SSI.dbo.GLSummary GLSummary
WHERE (GLSummary.Org =?) AND (GLSummary.Period>=?)
The actuals have records in the GLSummary table that does not exist in the IntGLBudgets table and vice versa.
I need the result to include all the lines from both tables, subject to the parameters though.
June 29, 2010 at 2:30 pm
I'm confused. The two queries are doing this. If you don't have matching lines, you can't combine things onto the same row.
If you have matching lines, they are combined.
June 29, 2010 at 2:32 pm
The confusing part to me is you say records from one will not be in the other table. If that is so, you have no way to join the two tables and you would need a union.
If however, you have something in common, such as Org, Period and Account, you would perform a join, probably a full outer join.
June 29, 2010 at 2:44 pm
Steve Cullen (6/29/2010)
The confusing part to me is you say records from one will not be in the other table. If that is so, you have no way to join the two tables and you would need a union.If however, you have something in common, such as Org, Period and Account, you would perform a join, probably a full outer join.
If I was unclear, apologies. What I meant to say is that the Budget table does not have entries for all the actual accounts, since it looks into the future. The actuals does not have expenditure for all the budgeted accounts becuase there is deviation from the budget. This does not mean that the Org, Period and accounts does not have anything in common, they are the same data types.
Having said that, the following query do combine the two tables. However, I still do not know how to seperate the actual amount from the budget amount since the query return the budgetAmount below the Amount column. I need the Actual Amount in a seperate column from the Budget Amount.
SELECT GLSummary.Org, GLSummary.Period, GLSummary.Account, GLSummary.Amount
FROM SSI.dbo.GLSummary GLSummary
WHERE (GLSummary.Org='RSA/JNB/T00') AND (GLSummary.Period>=201001)
UNION
SELECT IntGLBudgets.Org, IntGLBudgets.period, IntGLBudgets.account, IntGLBudgets.budgetAmount
FROM SSI.dbo.IntGLBudgets IntGLBudgets
WHERE (IntGLBudgets.Org='RSA/JNB/T00') AND (IntGLBudgets.period>=201001)
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply