Help finding the Max Total

Hi,

I have the following code

SELECT
PR.WBS2,
SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR'
THEN Ledgerar.amount * - 1
ELSE '0' END) AS BillExt
FROM PR
LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND
PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND
pr.wbs1 = '001-298'
GROUP BY PR.WBS2

It prints out:
Wbs2 BillExt
0141 0
0143 0
1217 20580

I want the code to return the wbs2 code '1217' because it has the highest amount in BillExt '20580'.

Can someone help me with this?

Thanks.
lauraThe quick and dirty version...

select top 1 a.WBS2, a.BillExt
from
(SELECT
PR.WBS2,
SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR'
THEN Ledgerar.amount * - 1
ELSE '0' END) AS BillExt
FROM PR
LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND
PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND
pr.wbs1 = '001-298'
GROUP BY PR.WBS2) a
order by BillExt desc|||Nothing dirty about it...|||That would be nice if there was only 1 record that needed to be returned.
select a.WBS2, BillExt=max(a.BillExt)
from
(SELECT
PR.WBS2,
SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR'
THEN Ledgerar.amount * - 1
ELSE '0' END) AS BillExt
FROM PR
LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND
PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND
pr.wbs1 = '001-298'
GROUP BY PR.WBS2) a
group by a.WBS2
order by BillExt desc|||Now going a little further once I find the maximum total what if I then have to insert a value in that record. Is there a way to do this without using subqueries and instead using case statements?

For instance :

Wbs2 BillExt MaxValue
0141 0 0
0143 0 0
1217 20580 1

Thanks,
Laura|||That would be nice if there was only 1 record that needed to be returned.

Which is wat she asked for...I left my mind reading hat at home...

Laura....INSERT What to Where?

Insert into that record?

I don't understand.|||I'm sorry it's hard to describe what I am asking.

If I have the following information in a table:

WBS1 Amount MAX
0141 0
1217 2
1222 200

I first want to find the maximum of amount which is 200. Next, I want to put a 1 in the MAX column where the largest sum appears in amount.

Ending result:

WBS1 Amount MAX
0141 0
1217 2
1222 200 1

Is this possible?|||Well, once you know the ID of the record with the highest value you can issue and UPDATE statement to set its MAX field value to 1. But you will also need to issue an UPDATE statement to reset all other MAX field values that might have been the largest value before.

I can't recommend marking a record as the "Maximum value" when that status can change at any time. It is better to have a function or view or stored procedure that finds the maximum record dynamically.

Why do you want to mark this record and what are you going to do with it?|||Well, I simplified this example quite a bit to generate ideas for myself. But What I am supposed to do is create a report for accounting.

Accounting bills there clients based on services provided. Each service is designated codes. What is supposed to happen in one of the reports is to print the total reimbursable amount into the service that was billed the most.

There is no insertion are update that can be done it is just for display purposes only.

One of the restrictions that I have been under is that I cannot use subqueries only case statements which makes it even more difficult.

So I may have to go an entirely different path.

Thanks for your help,
Laura|||You can't use subqueries? What kind of lunacy is that?

You could still use a stored proc that first loads the MAX value's primary key into a variable and then uses the variable in subsequent queries.

Can't use subqueries? Kindly direct the person who gave you that directive to this informative website:

http://www.hov-hov.dk/you.htm|||Well, I simplified this example quite a bit to generate ideas for myself. But What I am supposed to do is create a report for accounting.

Accounting bills there clients based on services provided. Each service is designated codes. What is supposed to happen in one of the reports is to print the total reimbursable amount into the service that was billed the most.

There is no insertion are update that can be done it is just for display purposes only.

One of the restrictions that I have been under is that I cannot use subqueries only case statements which makes it even more difficult.

So I may have to go an entirely different path.

Thanks for your help,
LauraCan you "cheat" and JOIN a virtual table? Technically that isn't a sub-query.

-PatP

Side note to Blindman, I'm going to have to bookmark that site!

HELP ! Carried Foward / Brought Forward

In my detail section I would like to have a report that has a "Carried
Forward" amount as a total for the page and then on the next page header the
value as "Brought Foward" this will total up at the page footer and be
"Carried Forward" to next page and so on until the last page where "Carried
Foward" wouldn't be shown but "Brought Foward" would.
Does anyone have any expamples as this a pretty common requirement for
finacial reports / billing. I have been racking my brains over this one but
can't get it to work.
Regards
Toby.You can get the Carried Forward by doing an aggregate of report items in the
page footer (=Sum(ReportItems!SalesTextbox.Value))
However, there's currently no way to get the Brought Forward since the
Previous function is not yet supported in the page header/footer.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Toby" <toby.maillist@.exmlsystems.com> wrote in message
news:ufJIaisjEHA.808@.TK2MSFTNGP12.phx.gbl...
> In my detail section I would like to have a report that has a "Carried
> Forward" amount as a total for the page and then on the next page header
the
> value as "Brought Foward" this will total up at the page footer and be
> "Carried Forward" to next page and so on until the last page where
"Carried
> Foward" wouldn't be shown but "Brought Foward" would.
> Does anyone have any expamples as this a pretty common requirement for
> finacial reports / billing. I have been racking my brains over this one
but
> can't get it to work.
> Regards
> Toby.
>|||Could I write a function / code behind to implement this ? This is kind of a
show stopper.
Regards
Toby.
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:%23VucRJtjEHA.632@.TK2MSFTNGP12.phx.gbl...
> You can get the Carried Forward by doing an aggregate of report items in
the
> page footer (=Sum(ReportItems!SalesTextbox.Value))
> However, there's currently no way to get the Brought Forward since the
> Previous function is not yet supported in the page header/footer.
> --
> This post is provided 'AS IS' with no warranties, and confers no rights.
All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No
user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach
of
> children under 3.
> "Toby" <toby.maillist@.exmlsystems.com> wrote in message
> news:ufJIaisjEHA.808@.TK2MSFTNGP12.phx.gbl...
> > In my detail section I would like to have a report that has a "Carried
> > Forward" amount as a total for the page and then on the next page header
> the
> > value as "Brought Foward" this will total up at the page footer and be
> > "Carried Forward" to next page and so on until the last page where
> "Carried
> > Foward" wouldn't be shown but "Brought Foward" would.
> >
> > Does anyone have any expamples as this a pretty common requirement for
> > finacial reports / billing. I have been racking my brains over this one
> but
> > can't get it to work.
> >
> > Regards
> >
> > Toby.
> >
> >
>|||You could store the current value in a shared member variable and use the
previous value by doing something like this:
public shared Previous as integer
public function Carry(value as integer)
Previous = value
Carry = value
end function
Textbox in page footer: =Code.Carry(Sum(ReportItems!textbox7.Value))
Textbox in page header: =Code.Previous
Since the custom code/classes get instantiated separately for each page
request, you'd have to do this as a shared member variable.
And due to the shared member variable, if two people run the report at the
same time, it would smash the Previous counter.
But if you're really motivated, you could make the Previous shared member
variable a hash table based on user id, at which point you'd only have
problems if the same user ran the report more than once at the same time.
Textbox in page footer: =Code.SetCarry(Sum(ReportItems!textbox7.Value),
User.UserID)
Textbox in page header: =Code.GetCarry(User.UserID)
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Toby" <toby.maillist@.exmlsystems.com> wrote in message
news:ubruZitjEHA.4068@.TK2MSFTNGP10.phx.gbl...
> Could I write a function / code behind to implement this ? This is kind of
a
> show stopper.
> Regards
> Toby.
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:%23VucRJtjEHA.632@.TK2MSFTNGP12.phx.gbl...
> > You can get the Carried Forward by doing an aggregate of report items in
> the
> > page footer (=Sum(ReportItems!SalesTextbox.Value))
> > However, there's currently no way to get the Brought Forward since the
> > Previous function is not yet supported in the page header/footer.
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
> All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
> user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach
> of
> > children under 3.
> > "Toby" <toby.maillist@.exmlsystems.com> wrote in message
> > news:ufJIaisjEHA.808@.TK2MSFTNGP12.phx.gbl...
> > > In my detail section I would like to have a report that has a "Carried
> > > Forward" amount as a total for the page and then on the next page
header
> > the
> > > value as "Brought Foward" this will total up at the page footer and be
> > > "Carried Forward" to next page and so on until the last page where
> > "Carried
> > > Foward" wouldn't be shown but "Brought Foward" would.
> > >
> > > Does anyone have any expamples as this a pretty common requirement for
> > > finacial reports / billing. I have been racking my brains over this
one
> > but
> > > can't get it to work.
> > >
> > > Regards
> > >
> > > Toby.
> > >
> > >
> >
> >
>|||Feeling motivated, Thanks
Toby.
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:ueqKY3tjEHA.556@.tk2msftngp13.phx.gbl...
> You could store the current value in a shared member variable and use the
> previous value by doing something like this:
> public shared Previous as integer
> public function Carry(value as integer)
> Previous = value
> Carry = value
> end function
> Textbox in page footer: =Code.Carry(Sum(ReportItems!textbox7.Value))
> Textbox in page header: =Code.Previous
> Since the custom code/classes get instantiated separately for each page
> request, you'd have to do this as a shared member variable.
> And due to the shared member variable, if two people run the report at the
> same time, it would smash the Previous counter.
> But if you're really motivated, you could make the Previous shared member
> variable a hash table based on user id, at which point you'd only have
> problems if the same user ran the report more than once at the same time.
> Textbox in page footer: =Code.SetCarry(Sum(ReportItems!textbox7.Value),
> User.UserID)
> Textbox in page header: =Code.GetCarry(User.UserID)
>
> --
> This post is provided 'AS IS' with no warranties, and confers no rights.
All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No
user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach
of
> children under 3.
> "Toby" <toby.maillist@.exmlsystems.com> wrote in message
> news:ubruZitjEHA.4068@.TK2MSFTNGP10.phx.gbl...
> > Could I write a function / code behind to implement this ? This is kind
of
> a
> > show stopper.
> >
> > Regards
> >
> > Toby.
> >
> > "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> > news:%23VucRJtjEHA.632@.TK2MSFTNGP12.phx.gbl...
> > > You can get the Carried Forward by doing an aggregate of report items
in
> > the
> > > page footer (=Sum(ReportItems!SalesTextbox.Value))
> > > However, there's currently no way to get the Brought Forward since the
> > > Previous function is not yet supported in the page header/footer.
> > >
> > > --
> > > This post is provided 'AS IS' with no warranties, and confers no
rights.
> > All
> > > rights reserved. Some assembly required. Batteries not included. Your
> > > mileage may vary. Objects in mirror may be closer than they appear. No
> > user
> > > serviceable parts inside. Opening cover voids warranty. Keep out of
> reach
> > of
> > > children under 3.
> > > "Toby" <toby.maillist@.exmlsystems.com> wrote in message
> > > news:ufJIaisjEHA.808@.TK2MSFTNGP12.phx.gbl...
> > > > In my detail section I would like to have a report that has a
"Carried
> > > > Forward" amount as a total for the page and then on the next page
> header
> > > the
> > > > value as "Brought Foward" this will total up at the page footer and
be
> > > > "Carried Forward" to next page and so on until the last page where
> > > "Carried
> > > > Foward" wouldn't be shown but "Brought Foward" would.
> > > >
> > > > Does anyone have any expamples as this a pretty common requirement
for
> > > > finacial reports / billing. I have been racking my brains over this
> one
> > > but
> > > > can't get it to work.
> > > >
> > > > Regards
> > > >
> > > > Toby.
> > > >
> > > >
> > >
> > >
> >
> >
>