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 doing a SUM for Fiscal Year data

I have a problem that at first sounded easy (perhaps it still is), but as I
get into it I'm getting more and more . Here is the problem. I
have a table holding transctions (we'll call the table Trx for ease of use),
and this table has a Date column that records the date of the transaction.
It also has a quantity column showing how many widgets were sold. Now, if I
want to do a SUM on this table to count the total number of widgets sold
YTD, BUT my year is a fiscal year running 5/1 - 4/30, how would one do this
programmatically?
I don't want to hard code the dates in (WHERE Trx.Date between '5/1/2005'
and '4/30/2006') b/c this query will embed into a report that needs to flow
from year to year with no updates.
I've tried using various combo's of DatePart and comparing the Trx.Date to
GetDate() and so on, but can't quite get this. It needs to be smart enough
to capture the right data if I am printing this report on 2/1/2006 (which
would then go back to 5/1/2005) or on 7/1/2006 (which would then go back to
5/1/2006).
Any thoughts/ideas/help would be GREAT! Thanks so much...
JasonThe following will find the current fiscal year beginning, assuming that
your FY starts on May 1:
select
dateadd (mm, -(datediff (mm, convert (char (4), getdate (), 112) + '0501',
getdate ()) + 12) % 12, convert (char (6), getdate (), 112) + '01')
It will determine it to be 2005-05-01. You can adapt this as you like. For
example, change the '0501' to '0901' for a FY start on Sep 1. Running it
today (Aug 4, 2005) will show a FY start of 2004-09-01.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Jason" <jason@.nospam.com> wrote in message
news:Ops4H1TmFHA.3448@.TK2MSFTNGP12.phx.gbl...
I have a problem that at first sounded easy (perhaps it still is), but as I
get into it I'm getting more and more . Here is the problem. I
have a table holding transctions (we'll call the table Trx for ease of use),
and this table has a Date column that records the date of the transaction.
It also has a quantity column showing how many widgets were sold. Now, if I
want to do a SUM on this table to count the total number of widgets sold
YTD, BUT my year is a fiscal year running 5/1 - 4/30, how would one do this
programmatically?
I don't want to hard code the dates in (WHERE Trx.Date between '5/1/2005'
and '4/30/2006') b/c this query will embed into a report that needs to flow
from year to year with no updates.
I've tried using various combo's of DatePart and comparing the Trx.Date to
GetDate() and so on, but can't quite get this. It needs to be smart enough
to capture the right data if I am printing this report on 2/1/2006 (which
would then go back to 5/1/2005) or on 7/1/2006 (which would then go back to
5/1/2006).
Any thoughts/ideas/help would be GREAT! Thanks so much...
Jason|||create table fiscal_year(num int, date_from datetime, date_to datetime)
insert into fiscal_year values(2005, '5/1/2005', '4/30/2006')
-- assuming date_sold truncated to midnight
select ...
from sales, fiscal_year
where date_sold between date_from and date_to
and fiscal_year.num = @.year|||This works GREAT! Thanks so much...
"AK" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1123193194.307575.11490@.g49g2000cwa.googlegroups.com...
> create table fiscal_year(num int, date_from datetime, date_to datetime)
> insert into fiscal_year values(2005, '5/1/2005', '4/30/2006')
> -- assuming date_sold truncated to midnight
> select ...
> from sales, fiscal_year
> where date_sold between date_from and date_to
> and fiscal_year.num = @.year
>|||The key here is to create a value to adjust the dates based on when your
fiscal year starts. Assuming 5/1/2004 is the beginning of fiscal year 2005.
DECLARE @.FYStart INT,
@.FYOffset INT
SET @.FYStart = 5 -- The calendar month that begins the fiscal year
SET @.FYOffset = CASE @.FYStart WHEN 1 THEN 0 ELSE 13 - @.FYStart END
Then we can use this value (@.FYOffset) to adjust the dates.
DECLARE @.FiscalYear INT
SET @.FiscalYear = 2005 -- The fiscal year you want to search for
SELECT ...
FROM Trx
WHERE DATEPART(YEAR,DATEADD(MONTH,@.OFFSET,Trx.Date) = @.FiscalYear
"Jason" <jason@.nospam.com> wrote in message
news:Ops4H1TmFHA.3448@.TK2MSFTNGP12.phx.gbl...
>I have a problem that at first sounded easy (perhaps it still is), but as I
>get into it I'm getting more and more . Here is the problem. I
>have a table holding transctions (we'll call the table Trx for ease of
>use), and this table has a Date column that records the date of the
>transaction. It also has a quantity column showing how many widgets were
>sold. Now, if I want to do a SUM on this table to count the total number
>of widgets sold YTD, BUT my year is a fiscal year running 5/1 - 4/30, how
>would one do this programmatically?
> I don't want to hard code the dates in (WHERE Trx.Date between '5/1/2005'
> and '4/30/2006') b/c this query will embed into a report that needs to
> flow from year to year with no updates.
> I've tried using various combo's of DatePart and comparing the Trx.Date to
> GetDate() and so on, but can't quite get this. It needs to be smart
> enough to capture the right data if I am printing this report on 2/1/2006
> (which would then go back to 5/1/2005) or on 7/1/2006 (which would then go
> back to 5/1/2006).
> Any thoughts/ideas/help would be GREAT! Thanks so much...
> Jason
>