 |
BorlandTalk.com
Borland discussion newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Larry Killen
Guest
|
 Posted: Mon Apr 18, 2005 8:09 pm Post subject: perplexing select |
 |
|
This might not be the right place to ask this but...
I want to write a query to find the yield of a process by dividing the
output into the input. One glitch is that their may be more output paths
then input paths. I.e. internal 1:many
The intended query is:
select sum(HT1.WT)/ sum(HT2.WT)As Yield, HT1.EmployeeID
From HopTransactions HT1
left outer join HopTransactions HT2 on (HT2.EmployeeID = HT1.EmployeeID)
and (HT2.ProdDate = HT1.ProdDate)
where HT1.ProdDate = '3/1/2005'
and HT1.GroupID = 1
and HT2.GroupID = 2
and HT1.EmployeeID = 17227 -- added for test purposes
Group by HT1.EmployeeID
Order by HT1.EmployeeID
The return is:
Yield EmployID
2.2167 17227
=============================================================================
The long-hand version by using two queries and deviding by hand is:1.5201
select sum(HT1.WT), HT1.EmployeeID
From HopTransactions HT1
where HT1.ProdDate = '3/1/2005'
and HT1.GroupID = 1
and HT1.EmployeeID = 17227
Group by HT1.EmployeeID
anser : 234.5598
select sum(HT2.WT), HT2.EmployeeID
From HopTransactions HT2
where HT2.ProdDate = '3/1/2005'
and HT2.GroupID = 2
and HT2.EmployeeID = 17227
Group by HT2.EmployeeID
answer : 154.3073
Answer = 234.5598/154.3073 = > 1.5201
============================================================================
This issues of nulls does not come into play since none are permitted. I
expected the same result and therefore could run it for all employess by
removing the employeeID filter. Can someone show me how to get the same
results using the single query with a join?
TIA
Larry
CREATE TABLE [HopTransactions] (
[EmployeeID] [int] NOT NULL ,
[GroupID] [smallint] NOT NULL ,
[WT] [money] NOT NULL ,
[DateTime] [datetime] NOT NULL ,
[ProdDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
|
|
| Back to top |
|
 |
Patrick
Guest
|
| Posted: Mon Apr 18, 2005 8:29 pm Post subject: Re: perplexing select |
|
|
| Quote: | Can someone show me how to get the same results using the single query with
a join?
TIA
Larry
|
I can't, and please use any solutions that don't use temp tables, but in the
mean time, this should work (off the cuffMS-SQL 2K syntax)
DECLARE @TEMP1 Table (Total money, EmployeeID int)
DECLARE @TEMP2 Table (Total money, EmployeeID int)
INSERT INTO @TEMP1
select sum(HT1.WT), HT1.EmployeeID
From HopTransactions HT1
where HT1.ProdDate = '3/1/2005'
and HT1.GroupID = 1
and HT1.EmployeeID = 17227
Group by HT1.EmployeeID
INSERT INTO @TEMP2
select sum(HT2.WT), HT2.EmployeeID
From HopTransactions HT2
where HT2.ProdDate = '3/1/2005'
and HT2.GroupID = 2
and HT2.EmployeeID = 17227
Group by HT2.EmployeeID
@TEMP1 and @TEMP2 should now be 1-1
SELECT @TEMP1.Total/@TEMP2.Total AS Yield, @TEMP1.EmployeeID
FROM @TEMP1
JOIN @TEMP2
ON TEMP2.EmployeeID = @TEMP1.EmployeeID
|
|
| Back to top |
|
|
Brian Evans
Guest
|
| Posted: Mon Apr 18, 2005 9:21 pm Post subject: Re: perplexing select |
|
|
Larry Killen wrote:
| Quote: | This might not be the right place to ask this but...
I want to write a query to find the yield of a process by dividing the
output into the input. One glitch is that their may be more output paths
then input paths. I.e. internal 1:many
The intended query is:
select sum(HT1.WT)/ sum(HT2.WT)As Yield, HT1.EmployeeID
From HopTransactions HT1
left outer join HopTransactions HT2 on (HT2.EmployeeID = HT1.EmployeeID)
and (HT2.ProdDate = HT1.ProdDate)
where HT1.ProdDate = '3/1/2005'
and HT1.GroupID = 1
and HT2.GroupID = 2
and HT1.EmployeeID = 17227 -- added for test purposes
Group by HT1.EmployeeID
Order by HT1.EmployeeID
The return is:
Yield EmployID
2.2167 17227
|
One way is to Select from the employee table and use two joins to
the HopTransactions table to get the values you need. Also put
the GroupID and date conditions in the joins and not the WHERE.
SELECT A.EmployeeID,SUM(B.WT) / SUM (C.WT) as Yield
FROM EMPLOYEES A
LEFT JOIN HopTransactions B on A.EmployeeID = B.EMployeeID
and B.GroupID = 1 and B.ProdDate = '3/1/2005'
LEFT JOIN HopTransactions C on A.EmployeeID = C.EmployeeID
and C.GroupID = 1 and C.ProdDate = '3/1/2005'
WHERE A.EmployeeID = 17227
GROUP BY A.EmployeeID
Brian Evans
|
|
| Back to top |
|
|
Larry Killen
Guest
|
| Posted: Tue Apr 19, 2005 3:33 pm Post subject: Re: perplexing select |
|
|
Brian,
I tried and get the wrong value. I get 2.2167 rather then 1.5201. This is
where I was at before. Patrick's works and returns the correct answer
though it is not as elegant. I will use the two temp tables but I am still
perplexed as to why I can't do two joins. Maybe this is a flaw in MS/SQL.
Thanks, all of you.
Larry
SELECT E.EmployeeID, SUM(G1.WT) / SUM (G2.WT) as Yield
FROM EMPLOYEEINFO E
JOIN HopTransactions G1 on (G1.EmployeeID = E.EmployeeID)
and G1.GroupID = 1 and G1.ProdDate = '3/1/2005'
JOIN HopTransactions G2 on (G2.EmployeeID = E.EmployeeID)
and G2.GroupID = 2 and G2.ProdDate = '3/1/2005'
WHERE E.EmployeeID = 17227
GROUP BY E.EmployeeID
"Brian Evans" <brian (AT) promaxis (DOT) com> wrote
| Quote: | Larry Killen wrote:
This might not be the right place to ask this but...
I want to write a query to find the yield of a process by dividing the
output into the input. One glitch is that their may be more output paths
then input paths. I.e. internal 1:many
The intended query is:
select sum(HT1.WT)/ sum(HT2.WT)As Yield, HT1.EmployeeID
From HopTransactions HT1
left outer join HopTransactions HT2 on (HT2.EmployeeID =
HT1.EmployeeID)
and (HT2.ProdDate = HT1.ProdDate)
where HT1.ProdDate = '3/1/2005'
and HT1.GroupID = 1
and HT2.GroupID = 2
and HT1.EmployeeID = 17227 -- added for test purposes
Group by HT1.EmployeeID
Order by HT1.EmployeeID
The return is:
Yield EmployID
2.2167 17227
One way is to Select from the employee table and use two joins to
the HopTransactions table to get the values you need. Also put
the GroupID and date conditions in the joins and not the WHERE.
SELECT A.EmployeeID,SUM(B.WT) / SUM (C.WT) as Yield
FROM EMPLOYEES A
LEFT JOIN HopTransactions B on A.EmployeeID = B.EMployeeID
and B.GroupID = 1 and B.ProdDate = '3/1/2005'
LEFT JOIN HopTransactions C on A.EmployeeID = C.EmployeeID
and C.GroupID = 1 and C.ProdDate = '3/1/2005'
WHERE A.EmployeeID = 17227
GROUP BY A.EmployeeID
Brian Evans
|
|
|
| Back to top |
|
|
Patrick
Guest
|
| Posted: Tue Apr 19, 2005 8:32 pm Post subject: Re: perplexing select |
|
|
| Quote: | I will use the two temp tables but I am still perplexed as to why I can't
do two joins. Maybe this is a flaw in MS/SQL.
For the record, I am pretty sure it is possible--I just don't know how to |
write the statement. Depending on the environment, the temp table might be
fast enough. If I had a problem with performance I would punt the procedure
to our DBA, who can spend some quality time with it. In the meantime, my
users get the info they need. :)
|
|
| Back to top |
|
|
Wayne Niddery [TeamB]
Guest
|
| Posted: Tue Apr 19, 2005 9:28 pm Post subject: Re: perplexing select |
|
|
Larry Killen wrote:
| Quote: | I tried and get the wrong value. I get 2.2167 rather then 1.5201. This is
where I was at before. Patrick's works and returns the
correct answer though it is not as elegant. I will use the two temp
tables but I am still perplexed as to why I can't do two joins. Maybe this
is a flaw in MS/SQL.
|
No, there's no bug here, only the nature of SQL joins.
| Quote: | SELECT E.EmployeeID, SUM(G1.WT) / SUM (G2.WT) as Yield
FROM EMPLOYEEINFO E
JOIN HopTransactions G1 on (G1.EmployeeID = E.EmployeeID)
and G1.GroupID = 1 and G1.ProdDate = '3/1/2005'
JOIN HopTransactions G2 on (G2.EmployeeID = E.EmployeeID)
and G2.GroupID = 2 and G2.ProdDate = '3/1/2005'
WHERE E.EmployeeID = 17227
GROUP BY E.EmployeeID
|
What is happening here is it is first selecting only those EMPLOYEEINFO
records that have a match in G1. It then only selects rows from that set
that have matches in G2. If there are fewer G2 matches than G1 matches, then
some G1 information will be eliminated. If the other way then some G2
information will be eliminated. You will only ever get the correct sums
where there happens to be the same number of matches in both G1 and G2.
When you want sums from different tables in the same query, you must use
subqueries.
Also, though I'm not sure it matters in this instance (inner joins), you
should only include the conditions in the join that actually represent the
link (employeeID in this case), everything else is normally in the Where
clause. The difference is this: Joins are processed first, and the where
clause only acts on the resulting set of rows from the joins. Putting such
conditions in the join can produce null fields for that joined table which
could then also affect the Where clause.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: http://www.logicfundamentals.com/RADBooks.html
"Nature abhors the vacuum tube." - J.R. Pierce, Bell Labs engineer who
coined the term 'transistor'
|
|
| Back to top |
|
|
Larry Killen
Guest
|
| Posted: Thu Apr 21, 2005 12:28 pm Post subject: Re: perplexing select |
|
|
Okay,
Thanks to both of you. I got my solution and learned a little more SQL.
Larry
"Wayne Niddery [TeamB]" <wniddery (AT) chaffaci (DOT) on.ca> wrote
| Quote: | Larry Killen wrote:
I tried and get the wrong value. I get 2.2167 rather then 1.5201. This
is where I was at before. Patrick's works and returns the
correct answer though it is not as elegant. I will use the two temp
tables but I am still perplexed as to why I can't do two joins. Maybe
this is a flaw in MS/SQL.
No, there's no bug here, only the nature of SQL joins.
SELECT E.EmployeeID, SUM(G1.WT) / SUM (G2.WT) as Yield
FROM EMPLOYEEINFO E
JOIN HopTransactions G1 on (G1.EmployeeID = E.EmployeeID)
and G1.GroupID = 1 and G1.ProdDate = '3/1/2005'
JOIN HopTransactions G2 on (G2.EmployeeID = E.EmployeeID)
and G2.GroupID = 2 and G2.ProdDate = '3/1/2005'
WHERE E.EmployeeID = 17227
GROUP BY E.EmployeeID
What is happening here is it is first selecting only those EMPLOYEEINFO
records that have a match in G1. It then only selects rows from that set
that have matches in G2. If there are fewer G2 matches than G1 matches,
then some G1 information will be eliminated. If the other way then some G2
information will be eliminated. You will only ever get the correct sums
where there happens to be the same number of matches in both G1 and G2.
When you want sums from different tables in the same query, you must use
subqueries.
Also, though I'm not sure it matters in this instance (inner joins), you
should only include the conditions in the join that actually represent the
link (employeeID in this case), everything else is normally in the Where
clause. The difference is this: Joins are processed first, and the where
clause only acts on the resulting set of rows from the joins. Putting such
conditions in the join can produce null fields for that joined table which
could then also affect the Where clause.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: http://www.logicfundamentals.com/RADBooks.html
"Nature abhors the vacuum tube." - J.R. Pierce, Bell Labs engineer who
coined the term 'transistor'
|
|
|
| Back to top |
|
|
Betsy
Guest
|
| Posted: Thu Apr 21, 2005 10:47 pm Post subject: Re: perplexing select |
|
|
damn! I was studying it and had just come to the conclusion that you had
some records in you HT2 table that you were missing with the out join... I
sure wouldn't have had the sql statement... but I was picturing the
poblem... little pat for me...
anywho... I found this really cool forum the other day... it's very
active... lots of responses and questions and seems to have no shortage of
sql (flipping) wizards... doesn't look like they are always real nice to
each other but there's some really good stuff there...
http://www.sqlteam.com/forums/default.asp
b
"Larry Killen" <LarryKillen (AT) Gainco (DOT) Com> wrote
| Quote: | Okay,
Thanks to both of you. I got my solution and learned a little more SQL.
Larry
"Wayne Niddery [TeamB]" <wniddery (AT) chaffaci (DOT) on.ca> wrote in message
news:4265780e$1 (AT) newsgroups (DOT) borland.com...
Larry Killen wrote:
I tried and get the wrong value. I get 2.2167 rather then 1.5201. This
is where I was at before. Patrick's works and returns the
correct answer though it is not as elegant. I will use the two temp
tables but I am still perplexed as to why I can't do two joins. Maybe
this is a flaw in MS/SQL.
No, there's no bug here, only the nature of SQL joins.
SELECT E.EmployeeID, SUM(G1.WT) / SUM (G2.WT) as Yield
FROM EMPLOYEEINFO E
JOIN HopTransactions G1 on (G1.EmployeeID = E.EmployeeID)
and G1.GroupID = 1 and G1.ProdDate = '3/1/2005'
JOIN HopTransactions G2 on (G2.EmployeeID = E.EmployeeID)
and G2.GroupID = 2 and G2.ProdDate = '3/1/2005'
WHERE E.EmployeeID = 17227
GROUP BY E.EmployeeID
What is happening here is it is first selecting only those EMPLOYEEINFO
records that have a match in G1. It then only selects rows from that set
that have matches in G2. If there are fewer G2 matches than G1 matches,
then some G1 information will be eliminated. If the other way then some
G2
information will be eliminated. You will only ever get the correct sums
where there happens to be the same number of matches in both G1 and G2.
When you want sums from different tables in the same query, you must use
subqueries.
Also, though I'm not sure it matters in this instance (inner joins), you
should only include the conditions in the join that actually represent
the
link (employeeID in this case), everything else is normally in the Where
clause. The difference is this: Joins are processed first, and the
where
clause only acts on the resulting set of rows from the joins. Putting
such
conditions in the join can produce null fields for that joined table
which
could then also affect the Where clause.
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: http://www.logicfundamentals.com/RADBooks.html
"Nature abhors the vacuum tube." - J.R. Pierce, Bell Labs engineer who
coined the term 'transistor'
|
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
