May 17, 2018 at 11:29 am
I am trying to write a query that combines 3 different table together. 2 of the tables I have no problem grabbing the info for. There is a direct relationship between them. The last table total blows my Quirey up. There isn't a relationship per say to go off of. I wrote the below query but the MAX command throughs the end results off.
Select GL_TransLines.[GLL_Account] as "Account"
, GL_TransLines.[Amount] as "Current Expenses"
, GL_TransLines.[COP_Period] as "Period"
, GL_TransLines.[GLT_TransDate] as "Trans Date"
, max(BUD_BudgetAmount) as "Budgeted"
, VEN_POName as "Vendor"
, GL_Translines.[COA_Subgroup] as "Subgroup"
from GL_TransLines
join BUD on GL_TransLines.[GLL_Account] = BUD_AcctNbr
join Synoptix_gl on Synoptix_GL.[Trans ID] = GL_TransLines.[GLT_TransNbr]
left outer join VEN on VEN_VendorID = Synoptix_GL.[Vendor ID]
where Exists (Select BUD_AcctNbr
, BUD_BudgetAmount
, BUD_Period
, BUD_Year
From bud
where Bud_Year = Year(getdate())) and GL_TransLines.[COP_Year] = Year(Getdate())
group by GL_TransLines.[GLL_Account]
, GL_TransLines.[COP_Period]
, GL_TransLines.[Amount]
, GL_TransLines.[GLT_TransDate]
, VEN_POName
, GL_Translines.[COA_Subgroup]
order by GL_TransLines.[GLL_Account]
I know there has to be a better way to write this but my SQL knowledge is very limited. This was the best I could come up with.
Any help in fixing this would be greatly appreciated.
May 17, 2018 at 11:40 am
Please post DDL for the tables or T-SQL for table variables and sample data in the form of inserts.
May 17, 2018 at 1:26 pm
Solonhipo - Thursday, May 17, 2018 11:29 AMI am trying to write a query that combines 3 different table together. 2 of the tables I have no problem grabbing the info for. There is a direct relationship between them. The last table total blows my Quirey up. There isn't a relationship per say to go off of. I wrote the below query but the MAX command throughs the end results off.Select GL_TransLines.[GLL_Account] as "Account"
, GL_TransLines.[Amount] as "Current Expenses"
, GL_TransLines.[COP_Period] as "Period"
, GL_TransLines.[GLT_TransDate] as "Trans Date"
, max(BUD_BudgetAmount) as "Budgeted"
, VEN_POName as "Vendor"
, GL_Translines.[COA_Subgroup] as "Subgroup"
from GL_TransLines
join BUD on GL_TransLines.[GLL_Account] = BUD_AcctNbr
join Synoptix_gl on Synoptix_GL.[Trans ID] = GL_TransLines.[GLT_TransNbr]
left outer join VEN on VEN_VendorID = Synoptix_GL.[Vendor ID]
where Exists (Select BUD_AcctNbr
, BUD_BudgetAmount
, BUD_Period
, BUD_Year
From bud
where Bud_Year = Year(getdate())) and GL_TransLines.[COP_Year] = Year(Getdate())
group by GL_TransLines.[GLL_Account]
, GL_TransLines.[COP_Period]
, GL_TransLines.[Amount]
, GL_TransLines.[GLT_TransDate]
, VEN_POName
, GL_Translines.[COA_Subgroup]
order by GL_TransLines.[GLL_Account]I know there has to be a better way to write this but my SQL knowledge is very limited. This was the best I could come up with.
Any help in fixing this would be greatly appreciated.
Let's start with formatting your query so it's more readable, and then fixing a couple of things. You are placing an EXISTS subquery in your WHERE clause that has no connection or correlation to the main query, and as long as there is data in the BUD table with a value of the current year in the Bud_Year column in any row of the table, all rows that meet the join conditions will qualify, so I'm not sure that's a useful element in a WHERE clause. Not sure if the optimizer short-cuts that scenario to make it that simple, and I'd rather be safe than sorry. Also, there's no need to select columns for an exists subquery. Just use SELECT 1. Also, as the entire purpose of the EXISTS clause seems to be at odds with common sense, let's change the condition so that only rows in the BUD table for the current year are considered. Finally, let's alias all the tables and the columns so that any potential confusion is eliminated:SELECT
GLT.GLL_Account as "Account"
, GLT.Amount as "Current Expenses"
, GLT.COP_Period as "Period"
, GLT.GLT_TransDate as "Trans Date"
, MAX(B1.BUD_BudgetAmount) as "Budgeted"
, V.VEN_POName as "Vendor"
, GLT.COA_Subgroup as "Subgroup"
FROM GL_TransLines AS GLT
INNER JOIN BUD AS B1
ON GLT.GLL_Account = B1.BUD_AcctNbr
INNER JOIN Synoptix_gl AS SGL
ON SGL.[Trans ID] = GLT.GLT_TransNbr
LEFT OUTER JOIN VEN AS V
ON V.VEN_VendorID = SGL.[Vendor ID]
WHERE B1.Bud_Year = YEAR(GETDATE())
AND GLT.COP_Year = YEAR(GETDATE())
GROUP BY
GLT.GLL_Account
, GLT.COP_Period
, GLT.Amount
, GLT.GLT_TransDate
, V.VEN_POName
, GLT.COA_Subgroup
ORDER BY GLT.GLL_Account;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 17, 2018 at 1:27 pm
Solonhipo - Thursday, May 17, 2018 11:29 AMI am trying to write a query that combines 3 different table together. 2 of the tables I have no problem grabbing the info for. There is a direct relationship between them. The last table total blows my Quirey up. There isn't a relationship per say to go off of. I wrote the below query but the MAX command throughs the end results off.Select GL_TransLines.[GLL_Account] as "Account"
, GL_TransLines.[Amount] as "Current Expenses"
, GL_TransLines.[COP_Period] as "Period"
, GL_TransLines.[GLT_TransDate] as "Trans Date"
, max(BUD_BudgetAmount) as "Budgeted"
, VEN_POName as "Vendor"
, GL_Translines.[COA_Subgroup] as "Subgroup"
from GL_TransLines
join BUD on GL_TransLines.[GLL_Account] = BUD_AcctNbr
join Synoptix_gl on Synoptix_GL.[Trans ID] = GL_TransLines.[GLT_TransNbr]
left outer join VEN on VEN_VendorID = Synoptix_GL.[Vendor ID]
where Exists (Select BUD_AcctNbr
, BUD_BudgetAmount
, BUD_Period
, BUD_Year
From bud
where Bud_Year = Year(getdate())) and GL_TransLines.[COP_Year] = Year(Getdate())
group by GL_TransLines.[GLL_Account]
, GL_TransLines.[COP_Period]
, GL_TransLines.[Amount]
, GL_TransLines.[GLT_TransDate]
, VEN_POName
, GL_Translines.[COA_Subgroup]
order by GL_TransLines.[GLL_Account]I know there has to be a better way to write this but my SQL knowledge is very limited. This was the best I could come up with.
Any help in fixing this would be greatly appreciated.
You do know that this:
EXISTS
(
SELECT [BUD_AcctNbr], [BUD_BudgetAmount], [BUD_Period], [BUD_Year] FROM [bud] WHERE [Bud_Year] = YEAR(GETDATE())
)
Does not return anything to your query and that the column list could be replaced a 1.
In fact, and I may be wrong, but this looks like it will act like 1 = 1 as long as you have even one row of data in the BUD table for the current year.
May 17, 2018 at 1:28 pm
Took a little time to reformat your code:
SELECT
[GL_TransLines].[GLL_Account] AS "Account"
, [GL_TransLines].[Amount] AS "Current Expenses"
, [GL_TransLines].[COP_Period] AS "Period"
, [GL_TransLines].[GLT_TransDate] AS "Trans Date"
, MAX([BUD_BudgetAmount]) AS "Budgeted"
, [VEN_POName] AS "Vendor"
, [GL_TransLines].[COA_Subgroup] AS "Subgroup"
FROM
[GL_TransLines]
JOIN [BUD]
ON [GL_TransLines].[GLL_Account] = [BUD_AcctNbr]
JOIN [Synoptix_gl]
ON [Synoptix_GL].[Trans ID] = [GL_TransLines].[GLT_TransNbr]
LEFT OUTER JOIN [VEN]
ON [VEN_VendorID] = [Synoptix_GL].[Vendor ID]
WHERE
EXISTS
(
SELECT [BUD_AcctNbr], [BUD_BudgetAmount], [BUD_Period], [BUD_Year] FROM [bud] WHERE [Bud_Year] = YEAR(GETDATE())
)
AND [GL_TransLines].[COP_Year] = YEAR(GETDATE())
GROUP BY
[GL_TransLines].[GLL_Account]
, [GL_TransLines].[COP_Period]
, [GL_TransLines].[Amount]
, [GL_TransLines].[GLT_TransDate]
, [VEN_POName]
, [GL_TransLines].[COA_Subgroup]
ORDER BY
[GL_TransLines].[GLL_Account];
May 17, 2018 at 3:08 pm
If you do an inner join to the BUD table you shouldn't need the EXISTS.SELECT GLT.GLL_Account AS Account,
GLT.Amount AS Current_Expenses,
GLT.COP_Period AS Period,
GLT.GLT_TransDate AS Trans_Date,
MAX(B1.BUD_BudgetAmount) AS Budgeted,
V.VEN_POName AS Vendor,
GLT.COA_Subgroup AS Subgroup
FROM GL_TransLines AS GLT
INNER JOIN (SELECT BUD_BudgetAmount, BUD_AcctNbr
FROM BUD
WHERE Bud_Year = YEAR(GETDATE())
) AS B1
ON GLT.GLL_Account = B1.BUD_AcctNbr
INNER JOIN Synoptix_gl AS SGL
ON SGL.[Trans ID] = GLT.GLT_TransNbr
LEFT OUTER JOIN VEN AS V
ON V.VEN_VendorID = SGL.[Vendor ID]
WHERE GLT.COP_Year = YEAR(GETDATE())
GROUP BY GLT.GLL_Account,
GLT.COP_Period,
GLT.Amount,
GLT.GLT_TransDate,
V.VEN_POName,
GLT.COA_Subgroup
ORDER BY GLT.GLL_Account;
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
May 18, 2018 at 11:04 am
SELECT GL_TransLines.[GLL_Account] as "Account"
, GL_TransLines.[Amount] as "Current Expenses"
, GL_TransLines.[COP_Period] as "Period"
, GL_TransLines.[GLT_TransDate] as "Trans Date"
, BUD.BUD_BudgetAmount as "Budgeted"
, VEN_POName as "Vendor"
, GL_Translines.[COA_Subgroup] as "Subgroup"
FROM GL_TransLines
INNER JOIN Synoptix_gl ON Synoptix_GL.[Trans ID] = GL_TransLines.[GLT_TransNbr]
INNER JOIN (
SELECT BUD_AcctNbr, MAX(BUD_BudgetAmount) AS BUD_BudgetAmount
FROM BUD
WHERE Bud_Year = YEAR(GETDATE())
GROUP BY BUD_AcctNbr
) AS BUD ON GL_TransLines.[GLL_Account] = BUD.BUD_AcctNbr
LEFT OUTER JOIN VEN ON VEN_VendorID = Synoptix_GL.[Vendor ID]
WHERE GL_TransLines.[COP_Year] = YEAR(GETDATE())
ORDER BY GL_TransLines.[GLL_Account]
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply