In myTable I have
RequiredAmount
RequiredDate
GrantedAmount
GrantedDate
I wish to obtain one view showing the sum per year.
For the required part it would be:
SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
FROM myTable
GROUP BY YEAR(RequiredDate)
And for the Granted part:
SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
FROM myTable
GROUP BY YEAR(GrantedDate)
Now, what I need is a presentation with three columns:
myYear, reqAmount, grantAmount
I'm fooling around with the table joined to itself, but can't seem to
get it right...maybe wrong approach?
Regards /SnedkerRead about Cross-tab reports in sql server help file
Madhivanan
Morten Snedker wrote:
> In myTable I have
> RequiredAmount
> RequiredDate
> GrantedAmount
> GrantedDate
> I wish to obtain one view showing the sum per year.
> For the required part it would be:
> SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
> FROM myTable
> GROUP BY YEAR(RequiredDate)
>
> And for the Granted part:
> SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
> FROM myTable
> GROUP BY YEAR(GrantedDate)
>
> Now, what I need is a presentation with three columns:
> myYear, reqAmount, grantAmount
> I'm fooling around with the table joined to itself, but can't seem to
> get it right...maybe wrong approach?
>
> Regards /Snedker|||use a full outer join (self)..
something like this..
untested..
SELECT COALESCE(A.myYear,B.myYear), COALESCE(A.reqAmount,0),
COALESCE(B.grantAmount,0)
FROM
(SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
FROM myTable
GROUP BY YEAR(RequiredDate)
) A FULL OUTER JOIN
(SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
FROM myTable
GROUP BY YEAR(GrantedDate)) B
ON A.myYear= B.myYear
Hope this helps.
-Omni|||Can you see if this works for you
SELECT
YEAR(tDate) as TransYear,
SUM(CASE WHEN tType = 'R' then tAmt else 0 end) as SumReq,
SUM(CASE WHEN tType = 'G' then tAmt else 0 end) as GraReq
FROM
(
select 'R' as tType,reqAmount as tAmt,ReqDate as tDate FROM Mytable
UNION ALL
select 'G' as tType,GrantAmount as tAmt,GrantDate as tDate FROM Mytable
) x
GROUP BY YEAR(tDate)
- Sha Anand
"Morten Snedker" wrote:
> In myTable I have
> RequiredAmount
> RequiredDate
> GrantedAmount
> GrantedDate
> I wish to obtain one view showing the sum per year.
> For the required part it would be:
> SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
> FROM myTable
> GROUP BY YEAR(RequiredDate)
>
> And for the Granted part:
> SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
> FROM myTable
> GROUP BY YEAR(GrantedDate)
>
> Now, what I need is a presentation with three columns:
> myYear, reqAmount, grantAmount
> I'm fooling around with the table joined to itself, but can't seem to
> get it right...maybe wrong approach?
>
> Regards /Snedker
>|||Hi,
Nice solution.. But Why do you need a case? Can't it be something simple
like this.
select TransYear, sum(GrantAmount) SumGrant, sum(reqAmount) SumReq
from
(select 0 as GrantAmount,reqAmount ,year(ReqDate) as TransYear FROM Mytable
UNION ALL
select GrantAmount,0 as reqAmount, year(GrantDate) as TransYear FROM
Mytable) x
group by TransYear
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||If your a consultant you know your clients couldn't give
a whit about how you provide a solution just as long as you
give them one.Perhaps we can help.Check out RAC for easy
solutions to all kinds of data manipulation problems including
crosstabs on sql server.
www.rac4sql.net
Showing posts with label sum. Show all posts
Showing posts with label sum. Show all posts
Monday, March 19, 2012
JOINing on derived tables?
I have an UPDATE query that sets a "quantity" field in a table based
on the sum of events in another table. Those events are "basketed"
into different accounts through a four-way tupple, (acctId, portcode,
mgrgpcode, invpgm).
UPDATE requires the use of a derived table when using aggregates, fair
enough. The problem is that the interior derived table query is very
expensive, yet only a few rows of the returned recordset match in the
outer table. Without artificial limits, the query takes on the order
of 30 seconds, when the entire query batch otherwise takes about 5 to
10.
Here is the query in question (tpPNL means "temporary profit 'n
loss") . tpHPL already contains a number of rows for various accounts,
ONE of these rows is in an account that needs the complex calculation
of the inner query. Yet when the query runs, it does so for every
record in tblTrades, which has 2 million+ rows. I have artificially
introduced a WHERE constraint to limit this down for testing purposes,
but this is far from ideal. What should happen is that the inner query
will return one row for every (acctId, portcode, mgrgpcode, invpgm)
tupple in the outer table (tpHPL).
I realize I can do another sub-select on tpHPL and return a list of
which of those tupples is being used, but this strikes me as yet
another performance hit. Is there some easy way to have the inner
JOINed on the outer so this "just happens"?
UPDATE tpPNL SET
openingMVLocalCcy = s.openingMV,
closingMVLocalCcy = s.closingMV,
openingMVAcctCcy = s.openingMV * h.openingFX,
closingMVAcctCcy = s.closingMV * h.closingFX
FROM tpPNL h JOIN
(SELECT acctId, portcode, mgrgpcode, invpgm,
SUM(
CASE
WHEN TranDate>@.startDate THEN 0
ELSE amount
END) as openingMV,
SUM(
CASE
WHEN TranDate>@.endDate THEN 0
ELSE amount
END) as closingMV
FROM tblTrades
WHERE deleted=0
AND portcode=400
GROUP BY acctId, portcode, mgrgpcode, invpgm) as s
ON s.acctId=h.accountId AND s.portcode=h.portfolioId AND
s.mgrgpcode=h.groupIdI would write an EXISTS test in the subquery that checks for matches
in tpHPL. If there are really as few matches as you say it should pay
off. Alternately, it might be possible to simply JOIN tblTrades and
tpHPL in the subquery, though that would only work if the set of join
columns constitutes the full key to tpHPL.
Not that it sounds like you need me to tell you how to do that, just
saying that is what I would do.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Apr 2008 08:02:13 -0700 (PDT), Maury Markowitz
<maury.markowitz@.gmail.com> wrote:
>I have an UPDATE query that sets a "quantity" field in a table based
>on the sum of events in another table. Those events are "basketed"
>into different accounts through a four-way tupple, (acctId, portcode,
>mgrgpcode, invpgm).
>UPDATE requires the use of a derived table when using aggregates, fair
>enough. The problem is that the interior derived table query is very
>expensive, yet only a few rows of the returned recordset match in the
>outer table. Without artificial limits, the query takes on the order
>of 30 seconds, when the entire query batch otherwise takes about 5 to
>10.
>Here is the query in question (tpPNL means "temporary profit 'n
>loss") . tpHPL already contains a number of rows for various accounts,
>ONE of these rows is in an account that needs the complex calculation
>of the inner query. Yet when the query runs, it does so for every
>record in tblTrades, which has 2 million+ rows. I have artificially
>introduced a WHERE constraint to limit this down for testing purposes,
>but this is far from ideal. What should happen is that the inner query
>will return one row for every (acctId, portcode, mgrgpcode, invpgm)
>tupple in the outer table (tpHPL).
>I realize I can do another sub-select on tpHPL and return a list of
>which of those tupples is being used, but this strikes me as yet
>another performance hit. Is there some easy way to have the inner
>JOINed on the outer so this "just happens"?
>UPDATE tpPNL SET
> openingMVLocalCcy = s.openingMV,
> closingMVLocalCcy = s.closingMV,
> openingMVAcctCcy = s.openingMV * h.openingFX,
> closingMVAcctCcy = s.closingMV * h.closingFX
>FROM tpPNL h JOIN
>(SELECT acctId, portcode, mgrgpcode, invpgm,
> SUM(
> CASE
> WHEN TranDate>@.startDate THEN 0
> ELSE amount
> END) as openingMV,
>SUM(
> CASE
> WHEN TranDate>@.endDate THEN 0
> ELSE amount
> END) as closingMV
> FROM tblTrades
> WHERE deleted=0
> AND portcode=400
>GROUP BY acctId, portcode, mgrgpcode, invpgm) as s
> ON s.acctId=h.accountId AND s.portcode=h.portfolioId AND
>s.mgrgpcode=h.groupId|||On Apr 30, 11:41=A0am, "Roy Harvey (SQL Server MVP)"
<roy_har...@.snet.net> wrote:
> off. =A0Alternately, it might be possible to simply JOIN tblTrades and
> tpHPL in the subquery, though that would only work if the set of join
> columns constitutes the full key to tpHPL.
Can you give me a simple example of this? I was thinking of something
like...
WHERE acctId IN (select distinct acctId from tpHPL)
AND portcode IN (select distinct portfolio from tpHPL)
but that seems expensive!
Maury|||Your approach of using two independent IN clauses is incorrect.
Imagine that we had two rows of data in tpHPL:
acctId portcode
ABC XYZ
BCD MNO
Using two independent IN clauses that would match any of four
combinations:
ABC XYZ
ABC MNO
BCD MNO
BCD XYZ
What I suggest instead is to use an EXISTS test in the subquery.
UPDATE tpPNL
SET openingMVLocalCcy = s.openingMV,
closingMVLocalCcy = s.closingMV,
openingMVAcctCcy = s.openingMV * h.openingFX,
closingMVAcctCcy = s.closingMV * h.closingFX
FROM tpPNL h
JOIN (SELECT acctId, portcode, mgrgpcode, invpgm,
SUM(CASE WHEN TranDate > @.startDate
THEN 0
ELSE amount
END) as openingMV,
SUM(CASE WHEN TranDate > @.endDate
THEN 0
ELSE amount
END) as closingMV
FROM tblTrades
WHERE deleted = 0
AND portcode = 400
AND EXISTS
(SELECT * FROM tpPNL as X
WHERE tblTrades.acctId = X.accountId
AND tblTrades.portcode = X.portfolioId
AND tblTrades.mgrgpcode = X.groupId)
GROUP BY acctId, portcode, mgrgpcode, invpgm) as s
ON s.acctId = h.accountId
AND s.portcode = h.portfolioId
AND s.mgrgpcode = h.groupId
Roy Harvey
Beacon Falls, CT
On Wed, 30 Apr 2008 11:28:02 -0700 (PDT), Maury Markowitz
<maury.markowitz@.gmail.com> wrote:
>On Apr 30, 11:41 am, "Roy Harvey (SQL Server MVP)"
><roy_har...@.snet.net> wrote:
>> off. Alternately, it might be possible to simply JOIN tblTrades and
>> tpHPL in the subquery, though that would only work if the set of join
>> columns constitutes the full key to tpHPL.
>Can you give me a simple example of this? I was thinking of something
>like...
>WHERE acctId IN (select distinct acctId from tpHPL)
> AND portcode IN (select distinct portfolio from tpHPL)
>but that seems expensive!
>Maury
on the sum of events in another table. Those events are "basketed"
into different accounts through a four-way tupple, (acctId, portcode,
mgrgpcode, invpgm).
UPDATE requires the use of a derived table when using aggregates, fair
enough. The problem is that the interior derived table query is very
expensive, yet only a few rows of the returned recordset match in the
outer table. Without artificial limits, the query takes on the order
of 30 seconds, when the entire query batch otherwise takes about 5 to
10.
Here is the query in question (tpPNL means "temporary profit 'n
loss") . tpHPL already contains a number of rows for various accounts,
ONE of these rows is in an account that needs the complex calculation
of the inner query. Yet when the query runs, it does so for every
record in tblTrades, which has 2 million+ rows. I have artificially
introduced a WHERE constraint to limit this down for testing purposes,
but this is far from ideal. What should happen is that the inner query
will return one row for every (acctId, portcode, mgrgpcode, invpgm)
tupple in the outer table (tpHPL).
I realize I can do another sub-select on tpHPL and return a list of
which of those tupples is being used, but this strikes me as yet
another performance hit. Is there some easy way to have the inner
JOINed on the outer so this "just happens"?
UPDATE tpPNL SET
openingMVLocalCcy = s.openingMV,
closingMVLocalCcy = s.closingMV,
openingMVAcctCcy = s.openingMV * h.openingFX,
closingMVAcctCcy = s.closingMV * h.closingFX
FROM tpPNL h JOIN
(SELECT acctId, portcode, mgrgpcode, invpgm,
SUM(
CASE
WHEN TranDate>@.startDate THEN 0
ELSE amount
END) as openingMV,
SUM(
CASE
WHEN TranDate>@.endDate THEN 0
ELSE amount
END) as closingMV
FROM tblTrades
WHERE deleted=0
AND portcode=400
GROUP BY acctId, portcode, mgrgpcode, invpgm) as s
ON s.acctId=h.accountId AND s.portcode=h.portfolioId AND
s.mgrgpcode=h.groupIdI would write an EXISTS test in the subquery that checks for matches
in tpHPL. If there are really as few matches as you say it should pay
off. Alternately, it might be possible to simply JOIN tblTrades and
tpHPL in the subquery, though that would only work if the set of join
columns constitutes the full key to tpHPL.
Not that it sounds like you need me to tell you how to do that, just
saying that is what I would do.
Roy Harvey
Beacon Falls, CT
On Wed, 30 Apr 2008 08:02:13 -0700 (PDT), Maury Markowitz
<maury.markowitz@.gmail.com> wrote:
>I have an UPDATE query that sets a "quantity" field in a table based
>on the sum of events in another table. Those events are "basketed"
>into different accounts through a four-way tupple, (acctId, portcode,
>mgrgpcode, invpgm).
>UPDATE requires the use of a derived table when using aggregates, fair
>enough. The problem is that the interior derived table query is very
>expensive, yet only a few rows of the returned recordset match in the
>outer table. Without artificial limits, the query takes on the order
>of 30 seconds, when the entire query batch otherwise takes about 5 to
>10.
>Here is the query in question (tpPNL means "temporary profit 'n
>loss") . tpHPL already contains a number of rows for various accounts,
>ONE of these rows is in an account that needs the complex calculation
>of the inner query. Yet when the query runs, it does so for every
>record in tblTrades, which has 2 million+ rows. I have artificially
>introduced a WHERE constraint to limit this down for testing purposes,
>but this is far from ideal. What should happen is that the inner query
>will return one row for every (acctId, portcode, mgrgpcode, invpgm)
>tupple in the outer table (tpHPL).
>I realize I can do another sub-select on tpHPL and return a list of
>which of those tupples is being used, but this strikes me as yet
>another performance hit. Is there some easy way to have the inner
>JOINed on the outer so this "just happens"?
>UPDATE tpPNL SET
> openingMVLocalCcy = s.openingMV,
> closingMVLocalCcy = s.closingMV,
> openingMVAcctCcy = s.openingMV * h.openingFX,
> closingMVAcctCcy = s.closingMV * h.closingFX
>FROM tpPNL h JOIN
>(SELECT acctId, portcode, mgrgpcode, invpgm,
> SUM(
> CASE
> WHEN TranDate>@.startDate THEN 0
> ELSE amount
> END) as openingMV,
>SUM(
> CASE
> WHEN TranDate>@.endDate THEN 0
> ELSE amount
> END) as closingMV
> FROM tblTrades
> WHERE deleted=0
> AND portcode=400
>GROUP BY acctId, portcode, mgrgpcode, invpgm) as s
> ON s.acctId=h.accountId AND s.portcode=h.portfolioId AND
>s.mgrgpcode=h.groupId|||On Apr 30, 11:41=A0am, "Roy Harvey (SQL Server MVP)"
<roy_har...@.snet.net> wrote:
> off. =A0Alternately, it might be possible to simply JOIN tblTrades and
> tpHPL in the subquery, though that would only work if the set of join
> columns constitutes the full key to tpHPL.
Can you give me a simple example of this? I was thinking of something
like...
WHERE acctId IN (select distinct acctId from tpHPL)
AND portcode IN (select distinct portfolio from tpHPL)
but that seems expensive!
Maury|||Your approach of using two independent IN clauses is incorrect.
Imagine that we had two rows of data in tpHPL:
acctId portcode
ABC XYZ
BCD MNO
Using two independent IN clauses that would match any of four
combinations:
ABC XYZ
ABC MNO
BCD MNO
BCD XYZ
What I suggest instead is to use an EXISTS test in the subquery.
UPDATE tpPNL
SET openingMVLocalCcy = s.openingMV,
closingMVLocalCcy = s.closingMV,
openingMVAcctCcy = s.openingMV * h.openingFX,
closingMVAcctCcy = s.closingMV * h.closingFX
FROM tpPNL h
JOIN (SELECT acctId, portcode, mgrgpcode, invpgm,
SUM(CASE WHEN TranDate > @.startDate
THEN 0
ELSE amount
END) as openingMV,
SUM(CASE WHEN TranDate > @.endDate
THEN 0
ELSE amount
END) as closingMV
FROM tblTrades
WHERE deleted = 0
AND portcode = 400
AND EXISTS
(SELECT * FROM tpPNL as X
WHERE tblTrades.acctId = X.accountId
AND tblTrades.portcode = X.portfolioId
AND tblTrades.mgrgpcode = X.groupId)
GROUP BY acctId, portcode, mgrgpcode, invpgm) as s
ON s.acctId = h.accountId
AND s.portcode = h.portfolioId
AND s.mgrgpcode = h.groupId
Roy Harvey
Beacon Falls, CT
On Wed, 30 Apr 2008 11:28:02 -0700 (PDT), Maury Markowitz
<maury.markowitz@.gmail.com> wrote:
>On Apr 30, 11:41 am, "Roy Harvey (SQL Server MVP)"
><roy_har...@.snet.net> wrote:
>> off. Alternately, it might be possible to simply JOIN tblTrades and
>> tpHPL in the subquery, though that would only work if the set of join
>> columns constitutes the full key to tpHPL.
>Can you give me a simple example of this? I was thinking of something
>like...
>WHERE acctId IN (select distinct acctId from tpHPL)
> AND portcode IN (select distinct portfolio from tpHPL)
>but that seems expensive!
>Maury
Joining multiple tables
Hi,
I need write a script get data from 3 different. All 3 tables will have
emailAddr, modify_date and sum details. But information may be duplicated in
the 3 tables, having same emailAddr but different modify_date eg. My end
result(new table) will contain the entry with latest modify_date if there is
a duplication of emailAddr, else the entry that is only available in single
table will also be insert into the new table.
Thanx..Can you give the primary key foriegn key trlationships with the table
definition and few sample data and expected result?
--
"yingying" wrote:
> Hi,
> I need write a script get data from 3 different. All 3 tables will have
> emailAddr, modify_date and sum details. But information may be duplicated
in
> the 3 tables, having same emailAddr but different modify_date eg. My end
> result(new table) will contain the entry with latest modify_date if there
is
> a duplication of emailAddr, else the entry that is only available in singl
e
> table will also be insert into the new table.
> Thanx..|||The tables are not related to each other.. only field that is similiar is th
e
emailAddr.. this is the field i expected to check..
Eg.. (table structure)
tblA (a_Id, emailAddr, dateModify, fname, lname)
tblB (b_Id, emailAddr, dateModify, addr, contact)
tblC (c_Id, emailAddr, dateModify, work1, work2)
tblResult (r_Id, emailAddr, dateModify)
(data in db)
tblA
1, aa@.abc.com, 29/04/2006, aa, aa
2, bb@.abc.com, 28/04/2006, bb, bb
tblB
1, aa@.abc.com, 30/04/2006, bb, bb
2, cc@.abc.com, 01/05/2006, cc, cc
3, bb@.abc.com, 03/05/2006, bb, bb
tblC
1, aa@.abc.com, 03/05/2006, aa, aa
2, dd@.abc.com, 01/05/2006, dd, dd
(Expected result stored into tblResult)
tblResult
1, aa@.abc.com, 03/05/2006
2, bb@.abc.com, 03/05/2006
3, cc@.abc.com, 01/05/2006
4, dd@.abc.com, 01/05/2006
"Omnibuzz" wrote:
> Can you give the primary key foriegn key trlationships with the table
> definition and few sample data and expected result?
> --
>
>
> "yingying" wrote:
>|||try this and let me know if this was what you required
select emailAddr,max(datemodify) as datemodify
from
(
select emailAddr,datemodify from tblA
union all
select emailAddr,datemodify from tblB
union all
select emailAddr,datemodify from tblC
) as A
group by emailaddr|||of course r_id in the tbl_result can be an identity
--
"yingying" wrote:
> Hi,
> I need write a script get data from 3 different. All 3 tables will have
> emailAddr, modify_date and sum details. But information may be duplicated
in
> the 3 tables, having same emailAddr but different modify_date eg. My end
> result(new table) will contain the entry with latest modify_date if there
is
> a duplication of emailAddr, else the entry that is only available in singl
e
> table will also be insert into the new table.
> Thanx..|||Thanx.. I got the idea how it works..
Another question.. If i was to have same email addr in the same table how am
i goin to get the data with the latest date having the same email addr.
(table structure)
tblA (a_Id, emailAddr, dateModify, lname, fname)
(data in table)
tblA
1, aa@.abc.com, 03/05/2006, aa, aa
2, aa@.abc.com, 01/05/2006, bb, bb
3, aa@.abc.com, 29/04/2006, cc, cc
4, dd@.abc.com, 01/05/2006, dd, dd
5, dd@.abc.com, 02/05/2006, ee, ee
6, ff@.abc.com, 01/05/2006, ff, ff
(Expected result)
1, aa@.abc.com, 03/05/2006, aa, aa
5, dd@.abc.com, 02/05/2006, ee, ee
6, ff@.abc.com, 01/05/2006, ff, ff
"Omnibuzz" wrote:
> try this and let me know if this was what you required
> select emailAddr,max(datemodify) as datemodify
> from
> (
> select emailAddr,datemodify from tblA
> union all
> select emailAddr,datemodify from tblB
> union all
> select emailAddr,datemodify from tblC
> ) as A
> group by emailaddr
>|||The same query will work for your requirement.
--
"yingying" wrote:
> Thanx.. I got the idea how it works..
> Another question.. If i was to have same email addr in the same table how
am
> i goin to get the data with the latest date having the same email addr.
> (table structure)
> tblA (a_Id, emailAddr, dateModify, lname, fname)
> (data in table)
> tblA
> 1, aa@.abc.com, 03/05/2006, aa, aa
> 2, aa@.abc.com, 01/05/2006, bb, bb
> 3, aa@.abc.com, 29/04/2006, cc, cc
> 4, dd@.abc.com, 01/05/2006, dd, dd
> 5, dd@.abc.com, 02/05/2006, ee, ee
> 6, ff@.abc.com, 01/05/2006, ff, ff
> (Expected result)
> 1, aa@.abc.com, 03/05/2006, aa, aa
> 5, dd@.abc.com, 02/05/2006, ee, ee
> 6, ff@.abc.com, 01/05/2006, ff, ff
>
> "Omnibuzz" wrote:
>|||I used same query but it did not return the right values i need..
select emailAddr, max(datestamp) as datemodify, fname, lname into #temp
from
(
select emailAddr, datestamp, fname, lname from tblA
) As A
group by emailAddr, fname, lname
(data in tblA)
1, aa@.abc.com, 29/04/2006, aa, aa
2, bb@.abc.com, 03/05/2006, bb, bb
3, cc @.abc.com, 03/05/2006, cc, cc
4, aa@.abc.com, 03/05/2006, aa2, aa2
(Expected result)
2, bb@.abc.com, 03/05/2006, bb, bb
3, cc @.abc.com, 03/05/2006, cc, cc
4, aa@.abc.com, 03/05/2006, aa2, aa2
but wat i got was
2, bb@.abc.com, 03/05/2006, bb, bb
3, cc @.abc.com, 03/05/2006, cc, cc
1, aa@.abc.com, 29/04/2006, aa, aa
4, aa@.abc.com, 03/05/2006, aa2, aa2
If cases with entry of same email addr, i will need to get the entry with
the latest date. When i didn't include the fname and lname, the result was
ok.. but after i add those 2 fields, the result was not wat i need.
Isit that i need to do this in 2 different steps in order to get the
required data'
Another question, when i have fname, lname in the 'select' query and not
having them in the 'group by', it gave me an error..
-->>
'A.fname' is invalid in the select list because it is not contained in
either an aggregate function or the GROUP BY clause.
what does this mean?
Thanx..
"Omnibuzz" wrote:
> The same query will work for your requirement.
> --
>
>
> "yingying" wrote:
>|||If you want the additional details with the latest date, try
select
emailAddr, datestamp as datemodify, fname, lname
into #temp
from tblA
where datestamp = (
select max(datestamp) from tblA as A2
where A2.emailAddr = tblA.emailAddr
)
nearly equivalent variations (differing in the case
where there are ties for the datestamp value, or
nullable columns) include
...
from tblA
where datestamp = (
select top 1 datestamp from tblA as A2
where A2.emailAddr = tblA.emailAddr
order by datestamp desc
)
and
...
from tblA
where not exists (
select * from tblA as A2
where A2.emailAddr = tblA.emailAddr
and A2.datestamp > tblA.datestamp
)
or in SQL Server 2005,
with Ranked(emailAddr, datestamp, fname, lname, rk) as (
select
emailAddr, datestamp, fname, lname,
rank() over (partition by emailAddr order by datestamp desc)
from tblA
)
select emailAddr, datestamp as datemodify, fname, lname
into #temp
from Ranked
where rk = 1
Steve Kass
Drew University
yingying wrote:
>I used same query but it did not return the right values i need..
>select emailAddr, max(datestamp) as datemodify, fname, lname into #temp
>from
>(
>select emailAddr, datestamp, fname, lname from tblA
> ) As A
>group by emailAddr, fname, lname
>(data in tblA)
>1, aa@.abc.com, 29/04/2006, aa, aa
>2, bb@.abc.com, 03/05/2006, bb, bb
>3, cc @.abc.com, 03/05/2006, cc, cc
>4, aa@.abc.com, 03/05/2006, aa2, aa2
>(Expected result)
>2, bb@.abc.com, 03/05/2006, bb, bb
>3, cc @.abc.com, 03/05/2006, cc, cc
>4, aa@.abc.com, 03/05/2006, aa2, aa2
>but wat i got was
>2, bb@.abc.com, 03/05/2006, bb, bb
>3, cc @.abc.com, 03/05/2006, cc, cc
>1, aa@.abc.com, 29/04/2006, aa, aa
>4, aa@.abc.com, 03/05/2006, aa2, aa2
>If cases with entry of same email addr, i will need to get the entry with
>the latest date. When i didn't include the fname and lname, the result was
>ok.. but after i add those 2 fields, the result was not wat i need.
>Isit that i need to do this in 2 different steps in order to get the
>required data'
>Another question, when i have fname, lname in the 'select' query and not
>having them in the 'group by', it gave me an error..
>-->>
>'A.fname' is invalid in the select list because it is not contained in
>either an aggregate function or the GROUP BY clause.
>what does this mean?
>Thanx..
>"Omnibuzz" wrote:
>
>|||If you are selecting more columns than what you had specified, then you will
have to use a correlated sub-query.
--
"yingying" wrote:
> I used same query but it did not return the right values i need..
> select emailAddr, max(datestamp) as datemodify, fname, lname into #temp
> from
> (
> select emailAddr, datestamp, fname, lname from tblA
> ) As A
> group by emailAddr, fname, lname
> (data in tblA)
> 1, aa@.abc.com, 29/04/2006, aa, aa
> 2, bb@.abc.com, 03/05/2006, bb, bb
> 3, cc @.abc.com, 03/05/2006, cc, cc
> 4, aa@.abc.com, 03/05/2006, aa2, aa2
> (Expected result)
> 2, bb@.abc.com, 03/05/2006, bb, bb
> 3, cc @.abc.com, 03/05/2006, cc, cc
> 4, aa@.abc.com, 03/05/2006, aa2, aa2
> but wat i got was
> 2, bb@.abc.com, 03/05/2006, bb, bb
> 3, cc @.abc.com, 03/05/2006, cc, cc
> 1, aa@.abc.com, 29/04/2006, aa, aa
> 4, aa@.abc.com, 03/05/2006, aa2, aa2
> If cases with entry of same email addr, i will need to get the entry with
> the latest date. When i didn't include the fname and lname, the result was
> ok.. but after i add those 2 fields, the result was not wat i need.
> Isit that i need to do this in 2 different steps in order to get the
> required data'
> Another question, when i have fname, lname in the 'select' query and not
> having them in the 'group by', it gave me an error..
> -->>
> 'A.fname' is invalid in the select list because it is not contained in
> either an aggregate function or the GROUP BY clause.
> what does this mean?
> Thanx..
> "Omnibuzz" wrote:
>
I need write a script get data from 3 different. All 3 tables will have
emailAddr, modify_date and sum details. But information may be duplicated in
the 3 tables, having same emailAddr but different modify_date eg. My end
result(new table) will contain the entry with latest modify_date if there is
a duplication of emailAddr, else the entry that is only available in single
table will also be insert into the new table.
Thanx..Can you give the primary key foriegn key trlationships with the table
definition and few sample data and expected result?
--
"yingying" wrote:
> Hi,
> I need write a script get data from 3 different. All 3 tables will have
> emailAddr, modify_date and sum details. But information may be duplicated
in
> the 3 tables, having same emailAddr but different modify_date eg. My end
> result(new table) will contain the entry with latest modify_date if there
is
> a duplication of emailAddr, else the entry that is only available in singl
e
> table will also be insert into the new table.
> Thanx..|||The tables are not related to each other.. only field that is similiar is th
e
emailAddr.. this is the field i expected to check..
Eg.. (table structure)
tblA (a_Id, emailAddr, dateModify, fname, lname)
tblB (b_Id, emailAddr, dateModify, addr, contact)
tblC (c_Id, emailAddr, dateModify, work1, work2)
tblResult (r_Id, emailAddr, dateModify)
(data in db)
tblA
1, aa@.abc.com, 29/04/2006, aa, aa
2, bb@.abc.com, 28/04/2006, bb, bb
tblB
1, aa@.abc.com, 30/04/2006, bb, bb
2, cc@.abc.com, 01/05/2006, cc, cc
3, bb@.abc.com, 03/05/2006, bb, bb
tblC
1, aa@.abc.com, 03/05/2006, aa, aa
2, dd@.abc.com, 01/05/2006, dd, dd
(Expected result stored into tblResult)
tblResult
1, aa@.abc.com, 03/05/2006
2, bb@.abc.com, 03/05/2006
3, cc@.abc.com, 01/05/2006
4, dd@.abc.com, 01/05/2006
"Omnibuzz" wrote:
> Can you give the primary key foriegn key trlationships with the table
> definition and few sample data and expected result?
> --
>
>
> "yingying" wrote:
>|||try this and let me know if this was what you required
select emailAddr,max(datemodify) as datemodify
from
(
select emailAddr,datemodify from tblA
union all
select emailAddr,datemodify from tblB
union all
select emailAddr,datemodify from tblC
) as A
group by emailaddr|||of course r_id in the tbl_result can be an identity
--
"yingying" wrote:
> Hi,
> I need write a script get data from 3 different. All 3 tables will have
> emailAddr, modify_date and sum details. But information may be duplicated
in
> the 3 tables, having same emailAddr but different modify_date eg. My end
> result(new table) will contain the entry with latest modify_date if there
is
> a duplication of emailAddr, else the entry that is only available in singl
e
> table will also be insert into the new table.
> Thanx..|||Thanx.. I got the idea how it works..
Another question.. If i was to have same email addr in the same table how am
i goin to get the data with the latest date having the same email addr.
(table structure)
tblA (a_Id, emailAddr, dateModify, lname, fname)
(data in table)
tblA
1, aa@.abc.com, 03/05/2006, aa, aa
2, aa@.abc.com, 01/05/2006, bb, bb
3, aa@.abc.com, 29/04/2006, cc, cc
4, dd@.abc.com, 01/05/2006, dd, dd
5, dd@.abc.com, 02/05/2006, ee, ee
6, ff@.abc.com, 01/05/2006, ff, ff
(Expected result)
1, aa@.abc.com, 03/05/2006, aa, aa
5, dd@.abc.com, 02/05/2006, ee, ee
6, ff@.abc.com, 01/05/2006, ff, ff
"Omnibuzz" wrote:
> try this and let me know if this was what you required
> select emailAddr,max(datemodify) as datemodify
> from
> (
> select emailAddr,datemodify from tblA
> union all
> select emailAddr,datemodify from tblB
> union all
> select emailAddr,datemodify from tblC
> ) as A
> group by emailaddr
>|||The same query will work for your requirement.
--
"yingying" wrote:
> Thanx.. I got the idea how it works..
> Another question.. If i was to have same email addr in the same table how
am
> i goin to get the data with the latest date having the same email addr.
> (table structure)
> tblA (a_Id, emailAddr, dateModify, lname, fname)
> (data in table)
> tblA
> 1, aa@.abc.com, 03/05/2006, aa, aa
> 2, aa@.abc.com, 01/05/2006, bb, bb
> 3, aa@.abc.com, 29/04/2006, cc, cc
> 4, dd@.abc.com, 01/05/2006, dd, dd
> 5, dd@.abc.com, 02/05/2006, ee, ee
> 6, ff@.abc.com, 01/05/2006, ff, ff
> (Expected result)
> 1, aa@.abc.com, 03/05/2006, aa, aa
> 5, dd@.abc.com, 02/05/2006, ee, ee
> 6, ff@.abc.com, 01/05/2006, ff, ff
>
> "Omnibuzz" wrote:
>|||I used same query but it did not return the right values i need..
select emailAddr, max(datestamp) as datemodify, fname, lname into #temp
from
(
select emailAddr, datestamp, fname, lname from tblA
) As A
group by emailAddr, fname, lname
(data in tblA)
1, aa@.abc.com, 29/04/2006, aa, aa
2, bb@.abc.com, 03/05/2006, bb, bb
3, cc @.abc.com, 03/05/2006, cc, cc
4, aa@.abc.com, 03/05/2006, aa2, aa2
(Expected result)
2, bb@.abc.com, 03/05/2006, bb, bb
3, cc @.abc.com, 03/05/2006, cc, cc
4, aa@.abc.com, 03/05/2006, aa2, aa2
but wat i got was
2, bb@.abc.com, 03/05/2006, bb, bb
3, cc @.abc.com, 03/05/2006, cc, cc
1, aa@.abc.com, 29/04/2006, aa, aa
4, aa@.abc.com, 03/05/2006, aa2, aa2
If cases with entry of same email addr, i will need to get the entry with
the latest date. When i didn't include the fname and lname, the result was
ok.. but after i add those 2 fields, the result was not wat i need.
Isit that i need to do this in 2 different steps in order to get the
required data'
Another question, when i have fname, lname in the 'select' query and not
having them in the 'group by', it gave me an error..
-->>
'A.fname' is invalid in the select list because it is not contained in
either an aggregate function or the GROUP BY clause.
what does this mean?
Thanx..
"Omnibuzz" wrote:
> The same query will work for your requirement.
> --
>
>
> "yingying" wrote:
>|||If you want the additional details with the latest date, try
select
emailAddr, datestamp as datemodify, fname, lname
into #temp
from tblA
where datestamp = (
select max(datestamp) from tblA as A2
where A2.emailAddr = tblA.emailAddr
)
nearly equivalent variations (differing in the case
where there are ties for the datestamp value, or
nullable columns) include
...
from tblA
where datestamp = (
select top 1 datestamp from tblA as A2
where A2.emailAddr = tblA.emailAddr
order by datestamp desc
)
and
...
from tblA
where not exists (
select * from tblA as A2
where A2.emailAddr = tblA.emailAddr
and A2.datestamp > tblA.datestamp
)
or in SQL Server 2005,
with Ranked(emailAddr, datestamp, fname, lname, rk) as (
select
emailAddr, datestamp, fname, lname,
rank() over (partition by emailAddr order by datestamp desc)
from tblA
)
select emailAddr, datestamp as datemodify, fname, lname
into #temp
from Ranked
where rk = 1
Steve Kass
Drew University
yingying wrote:
>I used same query but it did not return the right values i need..
>select emailAddr, max(datestamp) as datemodify, fname, lname into #temp
>from
>(
>select emailAddr, datestamp, fname, lname from tblA
> ) As A
>group by emailAddr, fname, lname
>(data in tblA)
>1, aa@.abc.com, 29/04/2006, aa, aa
>2, bb@.abc.com, 03/05/2006, bb, bb
>3, cc @.abc.com, 03/05/2006, cc, cc
>4, aa@.abc.com, 03/05/2006, aa2, aa2
>(Expected result)
>2, bb@.abc.com, 03/05/2006, bb, bb
>3, cc @.abc.com, 03/05/2006, cc, cc
>4, aa@.abc.com, 03/05/2006, aa2, aa2
>but wat i got was
>2, bb@.abc.com, 03/05/2006, bb, bb
>3, cc @.abc.com, 03/05/2006, cc, cc
>1, aa@.abc.com, 29/04/2006, aa, aa
>4, aa@.abc.com, 03/05/2006, aa2, aa2
>If cases with entry of same email addr, i will need to get the entry with
>the latest date. When i didn't include the fname and lname, the result was
>ok.. but after i add those 2 fields, the result was not wat i need.
>Isit that i need to do this in 2 different steps in order to get the
>required data'
>Another question, when i have fname, lname in the 'select' query and not
>having them in the 'group by', it gave me an error..
>-->>
>'A.fname' is invalid in the select list because it is not contained in
>either an aggregate function or the GROUP BY clause.
>what does this mean?
>Thanx..
>"Omnibuzz" wrote:
>
>|||If you are selecting more columns than what you had specified, then you will
have to use a correlated sub-query.
--
"yingying" wrote:
> I used same query but it did not return the right values i need..
> select emailAddr, max(datestamp) as datemodify, fname, lname into #temp
> from
> (
> select emailAddr, datestamp, fname, lname from tblA
> ) As A
> group by emailAddr, fname, lname
> (data in tblA)
> 1, aa@.abc.com, 29/04/2006, aa, aa
> 2, bb@.abc.com, 03/05/2006, bb, bb
> 3, cc @.abc.com, 03/05/2006, cc, cc
> 4, aa@.abc.com, 03/05/2006, aa2, aa2
> (Expected result)
> 2, bb@.abc.com, 03/05/2006, bb, bb
> 3, cc @.abc.com, 03/05/2006, cc, cc
> 4, aa@.abc.com, 03/05/2006, aa2, aa2
> but wat i got was
> 2, bb@.abc.com, 03/05/2006, bb, bb
> 3, cc @.abc.com, 03/05/2006, cc, cc
> 1, aa@.abc.com, 29/04/2006, aa, aa
> 4, aa@.abc.com, 03/05/2006, aa2, aa2
> If cases with entry of same email addr, i will need to get the entry with
> the latest date. When i didn't include the fname and lname, the result was
> ok.. but after i add those 2 fields, the result was not wat i need.
> Isit that i need to do this in 2 different steps in order to get the
> required data'
> Another question, when i have fname, lname in the 'select' query and not
> having them in the 'group by', it gave me an error..
> -->>
> 'A.fname' is invalid in the select list because it is not contained in
> either an aggregate function or the GROUP BY clause.
> what does this mean?
> Thanx..
> "Omnibuzz" wrote:
>
Friday, March 9, 2012
Join with Subselect and Sum
I have an appointment table (appts) and a charge table (charge).
For the day the query is run, I want to return anyone with an appointment
that has a balance on the charge table.
I am looking at doing a subselect but I'm having a syntax problem.
select APPTS.ACCOUNT
from APPTS
where APPTS.DATE = getdate()
and company = 'main'
INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
FROM CHARGE
WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
What am I doing incorrectly on the subselect?
THANKS,
MEGyou are missing criteria to justify your first where clause
where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
> SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
> FROM CHARGE
> WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0) = [whatever][/colo
r]
"MEG" wrote:
> I have an appointment table (appts) and a charge table (charge).
> For the day the query is run, I want to return anyone with an appointment
> that has a balance on the charge table.
> I am looking at doing a subselect but I'm having a syntax problem.
> select APPTS.ACCOUNT
> from APPTS
> where APPTS.DATE = getdate()
> and company = 'main'
> INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
> where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
> SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
> FROM CHARGE
> WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
> What am I doing incorrectly on the subselect?
> THANKS,
> MEG|||Try,
select
a.ACCOUNT
from
APPTS as a
INNER JOIN
CHARGE as c
on a.COMPANY = c.COMPANY
and a.ACCOUNT = c.ACCOUNT
where
a.[DATE] = convert(char(8), getdate(), 112)
and a.company = 'main'
group by
a.ACCOUNT
having
SUM(c.CHGAMOUNT - c.PAYINS1 - c.PAYINS2 - c.PAYGUAR - c.WRITEOFF - c.ADJUST
+ c.DEDUCTIBLE - c.ONACCT) > 0
AMB
"MEG" wrote:
> I have an appointment table (appts) and a charge table (charge).
> For the day the query is run, I want to return anyone with an appointment
> that has a balance on the charge table.
> I am looking at doing a subselect but I'm having a syntax problem.
> select APPTS.ACCOUNT
> from APPTS
> where APPTS.DATE = getdate()
> and company = 'main'
> INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
> where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
> SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
> FROM CHARGE
> WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
> What am I doing incorrectly on the subselect?
> THANKS,
> MEG|||On Wed, 7 Sep 2005 12:16:03 -0700, MEG wrote:
>I have an appointment table (appts) and a charge table (charge).
>For the day the query is run, I want to return anyone with an appointment
>that has a balance on the charge table.
>I am looking at doing a subselect but I'm having a syntax problem.
>select APPTS.ACCOUNT
>from APPTS
>where APPTS.DATE = getdate()
>and company = 'main'
>INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
>where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
>SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
>FROM CHARGE
>WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
>What am I doing incorrectly on the subselect?
Hi MEG,
The subselect in itself looks okay. But the order of the phrases in the
complete query is not quite right. Try:
select APPTS.ACCOUNT
from APPTS
INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
where APPTS.DATE = getdate()
and company = 'main'
and ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) -
SUM(ONACCT)
FROM CHARGE
WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) >
0)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
For the day the query is run, I want to return anyone with an appointment
that has a balance on the charge table.
I am looking at doing a subselect but I'm having a syntax problem.
select APPTS.ACCOUNT
from APPTS
where APPTS.DATE = getdate()
and company = 'main'
INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
FROM CHARGE
WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
What am I doing incorrectly on the subselect?
THANKS,
MEGyou are missing criteria to justify your first where clause
where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
> SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
> FROM CHARGE
> WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0) = [whatever][/colo
r]
"MEG" wrote:
> I have an appointment table (appts) and a charge table (charge).
> For the day the query is run, I want to return anyone with an appointment
> that has a balance on the charge table.
> I am looking at doing a subselect but I'm having a syntax problem.
> select APPTS.ACCOUNT
> from APPTS
> where APPTS.DATE = getdate()
> and company = 'main'
> INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
> where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
> SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
> FROM CHARGE
> WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
> What am I doing incorrectly on the subselect?
> THANKS,
> MEG|||Try,
select
a.ACCOUNT
from
APPTS as a
INNER JOIN
CHARGE as c
on a.COMPANY = c.COMPANY
and a.ACCOUNT = c.ACCOUNT
where
a.[DATE] = convert(char(8), getdate(), 112)
and a.company = 'main'
group by
a.ACCOUNT
having
SUM(c.CHGAMOUNT - c.PAYINS1 - c.PAYINS2 - c.PAYGUAR - c.WRITEOFF - c.ADJUST
+ c.DEDUCTIBLE - c.ONACCT) > 0
AMB
"MEG" wrote:
> I have an appointment table (appts) and a charge table (charge).
> For the day the query is run, I want to return anyone with an appointment
> that has a balance on the charge table.
> I am looking at doing a subselect but I'm having a syntax problem.
> select APPTS.ACCOUNT
> from APPTS
> where APPTS.DATE = getdate()
> and company = 'main'
> INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
> where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
> SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
> FROM CHARGE
> WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
> What am I doing incorrectly on the subselect?
> THANKS,
> MEG|||On Wed, 7 Sep 2005 12:16:03 -0700, MEG wrote:
>I have an appointment table (appts) and a charge table (charge).
>For the day the query is run, I want to return anyone with an appointment
>that has a balance on the charge table.
>I am looking at doing a subselect but I'm having a syntax problem.
>select APPTS.ACCOUNT
>from APPTS
>where APPTS.DATE = getdate()
>and company = 'main'
>INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
>where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
>SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
>FROM CHARGE
>WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
>What am I doing incorrectly on the subselect?
Hi MEG,
The subselect in itself looks okay. But the order of the phrases in the
complete query is not quite right. Try:
select APPTS.ACCOUNT
from APPTS
INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
where APPTS.DATE = getdate()
and company = 'main'
and ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) -
SUM(ONACCT)
FROM CHARGE
WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) >
0)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Join with a Having clause -- having problems too
Could someone please help me with a query that I am trying to create or suggest a better way?
What I am trying to do is is sum the production information (tbl_ProductionInfo) that is greater than the last date a particular task was done (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) and the production has met the quanity ran (Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)).
When I put this critera (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) in the Where clause I get an error "An aggregate may not appear in a Where unless it is in a subquery contained in a Having...etc."
SELECT DISTINCT tbl_ProductionInfo.LineNum, tbl_ProductionInfo.Dept, tbl_ProductionInfo.EquipType, Sum(tbl_ProductionInfo.Production) AS SumOfProduction, tbl_Mertering.PMType
FROM tbl_ProductionInfo LEFT JOIN tbl_Mertering ON tbl_ProductionInfo.EquipType = tbl_Mertering.EquipType
WHERE tbl_Mertering.DateOfChange>=tbl_ProductionInfo.EntryDate AND tbl_Mertering.UD2=0 AND tbl_ProductionInfo.LineNum= tbl_Mertering.LineNum AND tbl_ProductionInfo.EquipType= tbl_Mertering.EquipType
GROUP BY tbl_ProductionInfo.LineNum, tbl_ProductionInfo.Dept, tbl_ProductionInfo.EquipType, tbl_Mertering.PMType
HAVING Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)Can you post the DDL for the tables?|||Sorry for the late reply as I have been in meetings all afternoon and please excuse my ingorance, but I am not sure what the DDL is.|||DDL: Data Definition Language.
The SQL Statements that can be used to create the tables and objects involved in your problem, or at least the relevant parts.
Brett is asking for more information on your table design.
blindman|||Here is a little more history.
The production table already existed. I am attempting to create a "meterting" scheduler for our home grown CMMS. We want to create workorders (table that already exists) based on the amount of production that has occurred. I created the metering table to holds the date that work is done. I want to keep each record for historical data.
The flow of the program is
User completes the existing work order; this action creates a record in the metering table; DateOfChange is populated UD2 defaults to 0.
Each day the job runs that looks for Metering records with UD2 = 0 and sums the production for that line, equiptype, dept, ItemDesc. If the sum is greater >= LifeCycle then I write a new work order and change the UD2 =1.
I run this job as an active X (because I am weak in SQL). The writing of the work order and the changing of UD2 works fine. I just can't get the sum right because it is not picking up the >= Max(DateOfChange).
Here is the table structure.
tbl_ProductionInfo Columns
Name Type Size
ProdID int (autonumber) 4
EntryDate Date/Time 8
LineNum Text 10
Shift Text 10
SubEmp Text 35
Dept Text 10
EquipType Text 35
ProductType Text 10
ContNum int 4
Production int 4
ScheduledTime int 4
OnHold int 4
Speed int 4
Potential int 4
PM int 35
EditedBy Text 35
DateCode Text 15
Employee Text 35
UtilTime int 4
Util float 8
tbl_metering Name Type Size
Id int (autonumber) 4
Dept Text 35
EquipType Text 35
LineNum Text 2
Station Text 35
ItemDesc Text 35
LifeCycle int 4
DateOfChange smalldate 8
Comments Text 250
PMType Text 40
UD2 int 4
CreateWo int 4|||To start with, rewrite your query like this:
SELECT tbl_ProductionInfo.LineNum,
tbl_ProductionInfo.Dept,
tbl_ProductionInfo.EquipType,
Sum(tbl_ProductionInfo.Production) AS SumOfProduction,
tbl_Mertering.PMType
FROM tbl_ProductionInfo
inner join tbl_Mertering
ON tbl_ProductionInfo.EquipType = tbl_Mertering.EquipType
and tbl_ProductionInfo.EntryDate <= tbl_Mertering.DateOfChange
AND tbl_ProductionInfo.LineNum = tbl_Mertering.LineNum
AND tbl_ProductionInfo.EquipType= tbl_Mertering.EquipType
WHERE tbl_Mertering.UD2=0
GROUP BY tbl_ProductionInfo.LineNum,
tbl_ProductionInfo.Dept,
tbl_ProductionInfo.EquipType,
tbl_Mertering.PMType
HAVING Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)
DISTINCT is not need in GROUP BY queries, and your LEFT JOIN is superfluous when you are matching records in the WHERE clause.
Now to your problem...
Does tbl_Mertering hold a history of values, differentiated by DateOfChange, or is DateOfChange just updated every time a record is modified?
blindman|||A new record is written and DateOfChange is added for each record so that I maintain a record of the date the PM was done. The reason for this is that there may be delay in when work is actually done. With the historic data, we can tell what the average production and/or actual production between PM's is.|||What I am trying to do is is sum the production information (tbl_ProductionInfo) that is greater than the last date a particular task was done (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) and the production has met the quanity ran (Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)).
[I can't speak to whether the above Max() code, etc, is appropriate or correct. I simply include it as part of a quote. In fact I don't think it is...]
I suggest that you first construct a query which returns "the production information that is greater than the last date..." This query will return all rows.
Then build a second query which is based on the first (i.e. it takes input from the first), and does the sum.
That combination is clear, easy to understand, and also easy to prove/audit by desk checking. Furthermore, when you go to run the combined query, the DBMS will automatically consider both queries in combination when building the overall execution plan.|||Yes! I do need to approach it differently as I continue to get an incorrect sum of production.
I had thought that I could return a recordset with all the records in the metering table with UD2 = 0 and then with active x loop through the recordset with a second query that would sum the production based on the critera (line, date, equiptype, etc) and sum >= LifeCycle.
I had just hoped that I could learn a cleaner way.
Thanks,
Lee|||My BAD!
I missed the signing
tbl_ProductionInfo.EntryDate <= tbl_Mertering.DateOfChange to
tbl_ProductionInfo.EntryDate >= tbl_Mertering.DateOfChange
In the INNER JOIN critera.
Also I found that that collects the production data was not putting the right equipment type in the column. I corrected that as well and now IT SEEMS to be working fine.
Thanks to all for your help!
You are the best!
What I am trying to do is is sum the production information (tbl_ProductionInfo) that is greater than the last date a particular task was done (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) and the production has met the quanity ran (Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)).
When I put this critera (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) in the Where clause I get an error "An aggregate may not appear in a Where unless it is in a subquery contained in a Having...etc."
SELECT DISTINCT tbl_ProductionInfo.LineNum, tbl_ProductionInfo.Dept, tbl_ProductionInfo.EquipType, Sum(tbl_ProductionInfo.Production) AS SumOfProduction, tbl_Mertering.PMType
FROM tbl_ProductionInfo LEFT JOIN tbl_Mertering ON tbl_ProductionInfo.EquipType = tbl_Mertering.EquipType
WHERE tbl_Mertering.DateOfChange>=tbl_ProductionInfo.EntryDate AND tbl_Mertering.UD2=0 AND tbl_ProductionInfo.LineNum= tbl_Mertering.LineNum AND tbl_ProductionInfo.EquipType= tbl_Mertering.EquipType
GROUP BY tbl_ProductionInfo.LineNum, tbl_ProductionInfo.Dept, tbl_ProductionInfo.EquipType, tbl_Mertering.PMType
HAVING Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)Can you post the DDL for the tables?|||Sorry for the late reply as I have been in meetings all afternoon and please excuse my ingorance, but I am not sure what the DDL is.|||DDL: Data Definition Language.
The SQL Statements that can be used to create the tables and objects involved in your problem, or at least the relevant parts.
Brett is asking for more information on your table design.
blindman|||Here is a little more history.
The production table already existed. I am attempting to create a "meterting" scheduler for our home grown CMMS. We want to create workorders (table that already exists) based on the amount of production that has occurred. I created the metering table to holds the date that work is done. I want to keep each record for historical data.
The flow of the program is
User completes the existing work order; this action creates a record in the metering table; DateOfChange is populated UD2 defaults to 0.
Each day the job runs that looks for Metering records with UD2 = 0 and sums the production for that line, equiptype, dept, ItemDesc. If the sum is greater >= LifeCycle then I write a new work order and change the UD2 =1.
I run this job as an active X (because I am weak in SQL). The writing of the work order and the changing of UD2 works fine. I just can't get the sum right because it is not picking up the >= Max(DateOfChange).
Here is the table structure.
tbl_ProductionInfo Columns
Name Type Size
ProdID int (autonumber) 4
EntryDate Date/Time 8
LineNum Text 10
Shift Text 10
SubEmp Text 35
Dept Text 10
EquipType Text 35
ProductType Text 10
ContNum int 4
Production int 4
ScheduledTime int 4
OnHold int 4
Speed int 4
Potential int 4
PM int 35
EditedBy Text 35
DateCode Text 15
Employee Text 35
UtilTime int 4
Util float 8
tbl_metering Name Type Size
Id int (autonumber) 4
Dept Text 35
EquipType Text 35
LineNum Text 2
Station Text 35
ItemDesc Text 35
LifeCycle int 4
DateOfChange smalldate 8
Comments Text 250
PMType Text 40
UD2 int 4
CreateWo int 4|||To start with, rewrite your query like this:
SELECT tbl_ProductionInfo.LineNum,
tbl_ProductionInfo.Dept,
tbl_ProductionInfo.EquipType,
Sum(tbl_ProductionInfo.Production) AS SumOfProduction,
tbl_Mertering.PMType
FROM tbl_ProductionInfo
inner join tbl_Mertering
ON tbl_ProductionInfo.EquipType = tbl_Mertering.EquipType
and tbl_ProductionInfo.EntryDate <= tbl_Mertering.DateOfChange
AND tbl_ProductionInfo.LineNum = tbl_Mertering.LineNum
AND tbl_ProductionInfo.EquipType= tbl_Mertering.EquipType
WHERE tbl_Mertering.UD2=0
GROUP BY tbl_ProductionInfo.LineNum,
tbl_ProductionInfo.Dept,
tbl_ProductionInfo.EquipType,
tbl_Mertering.PMType
HAVING Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)
DISTINCT is not need in GROUP BY queries, and your LEFT JOIN is superfluous when you are matching records in the WHERE clause.
Now to your problem...
Does tbl_Mertering hold a history of values, differentiated by DateOfChange, or is DateOfChange just updated every time a record is modified?
blindman|||A new record is written and DateOfChange is added for each record so that I maintain a record of the date the PM was done. The reason for this is that there may be delay in when work is actually done. With the historic data, we can tell what the average production and/or actual production between PM's is.|||What I am trying to do is is sum the production information (tbl_ProductionInfo) that is greater than the last date a particular task was done (Max(tbl_Mertering.DateOfChange >= tbl_ProductionInfo.EntryDate )) and the production has met the quanity ran (Sum(tbl_ProductionInfo.Production)>=Max(tbl_Mertering.lifecycle)).
[I can't speak to whether the above Max() code, etc, is appropriate or correct. I simply include it as part of a quote. In fact I don't think it is...]
I suggest that you first construct a query which returns "the production information that is greater than the last date..." This query will return all rows.
Then build a second query which is based on the first (i.e. it takes input from the first), and does the sum.
That combination is clear, easy to understand, and also easy to prove/audit by desk checking. Furthermore, when you go to run the combined query, the DBMS will automatically consider both queries in combination when building the overall execution plan.|||Yes! I do need to approach it differently as I continue to get an incorrect sum of production.
I had thought that I could return a recordset with all the records in the metering table with UD2 = 0 and then with active x loop through the recordset with a second query that would sum the production based on the critera (line, date, equiptype, etc) and sum >= LifeCycle.
I had just hoped that I could learn a cleaner way.
Thanks,
Lee|||My BAD!
I missed the signing
tbl_ProductionInfo.EntryDate <= tbl_Mertering.DateOfChange to
tbl_ProductionInfo.EntryDate >= tbl_Mertering.DateOfChange
In the INNER JOIN critera.
Also I found that that collects the production data was not putting the right equipment type in the column. I corrected that as well and now IT SEEMS to be working fine.
Thanks to all for your help!
You are the best!
Join two tables using sum and max
I've got two tables, one called clientsharedeals and the clientorderdeals. In the first table, I have four fields (Rundate, Accno, Dealid, Nominal) that I need to sum(Nominal), grouping by dealid.
Once I've done this, I need to join to clientorderdeals, also having the same fields plus one extra (Rundate, Accno, Dealid, Nominal and Dealseq). Because of Dealseq, I can have more than one row in the table, matching (Rundate, Accno, Dealid, Nominal) of the first table. However, Dealseq increments, so I need to select max(Dealseq).
My query is doubling up on nominal because in my select statement, I am only using one account number, so I know what the value is for nominal and there are two rows in clientorderdeals - and it is not selecting max(dealseq) but both.
Can someone please cast some pearls my way ?
ThanksThis may not be what you want, but if you post your question in the following manner with the expected results, I'm sure you'd get an answer rather quickly
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myTable99(Rundate datetime, Accno int, Dealid int, Nominal int)
CREATE TABLE myTable00(Rundate datetime, Accno int, Dealid int, Nominal int, Dealseq int)
GO
INSERT INTO myTable99(Rundate, Accno, Dealid, Nominal)
SELECT '1/1/2005',1,1,1 UNION ALL
SELECT '1/1/2005',1,2,1 UNION ALL
SELECT '1/1/2005',1,3,1 UNION ALL
SELECT '1/1/2005',1,4,1
INSERT INTO myTable00(Rundate, Accno, Dealid, Nominal, Dealseq)
SELECT '1/1/2005',1,1,1,1 UNION ALL
SELECT '1/1/2005',1,2,1,1 UNION ALL
SELECT '1/1/2005',1,3,1,1 UNION ALL
SELECT '1/1/2005',1,1,1,2 UNION ALL
SELECT '1/1/2005',1,2,1,2 UNION ALL
SELECT '1/1/2005',1,3,1,2 UNION ALL
SELECT '1/1/2005',1,4,1,1
GO
SELECT *
FROM (
SELECT Dealid, SUM(Nominal) AS SUM_Nominal
FROM myTable99
GROUP BY Dealid) AS xxx
JOIN ( SELECT *
FROM myTable00 a
WHERE DealSeq = (SELECT MAX(Dealseq)
FROM myTable00 b
WHERE a.Dealid = b.Dealid)) AS yyy
ON xxx.Dealid = yyy.Dealid
SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
GO
Once I've done this, I need to join to clientorderdeals, also having the same fields plus one extra (Rundate, Accno, Dealid, Nominal and Dealseq). Because of Dealseq, I can have more than one row in the table, matching (Rundate, Accno, Dealid, Nominal) of the first table. However, Dealseq increments, so I need to select max(Dealseq).
My query is doubling up on nominal because in my select statement, I am only using one account number, so I know what the value is for nominal and there are two rows in clientorderdeals - and it is not selecting max(dealseq) but both.
Can someone please cast some pearls my way ?
ThanksThis may not be what you want, but if you post your question in the following manner with the expected results, I'm sure you'd get an answer rather quickly
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myTable99(Rundate datetime, Accno int, Dealid int, Nominal int)
CREATE TABLE myTable00(Rundate datetime, Accno int, Dealid int, Nominal int, Dealseq int)
GO
INSERT INTO myTable99(Rundate, Accno, Dealid, Nominal)
SELECT '1/1/2005',1,1,1 UNION ALL
SELECT '1/1/2005',1,2,1 UNION ALL
SELECT '1/1/2005',1,3,1 UNION ALL
SELECT '1/1/2005',1,4,1
INSERT INTO myTable00(Rundate, Accno, Dealid, Nominal, Dealseq)
SELECT '1/1/2005',1,1,1,1 UNION ALL
SELECT '1/1/2005',1,2,1,1 UNION ALL
SELECT '1/1/2005',1,3,1,1 UNION ALL
SELECT '1/1/2005',1,1,1,2 UNION ALL
SELECT '1/1/2005',1,2,1,2 UNION ALL
SELECT '1/1/2005',1,3,1,2 UNION ALL
SELECT '1/1/2005',1,4,1,1
GO
SELECT *
FROM (
SELECT Dealid, SUM(Nominal) AS SUM_Nominal
FROM myTable99
GROUP BY Dealid) AS xxx
JOIN ( SELECT *
FROM myTable00 a
WHERE DealSeq = (SELECT MAX(Dealseq)
FROM myTable00 b
WHERE a.Dealid = b.Dealid)) AS yyy
ON xxx.Dealid = yyy.Dealid
SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
GO
Subscribe to:
Posts (Atom)