Board index » delphi » Need help with SQL statement

Need help with SQL statement

 >BillingPeriodID  ContractID     ClientID     CareType     Amount
 >1                    1            1             1         100
 >1                    1            1             2         200
 >1                    2            5             1         75
 >1                    2            5             1         20
 >

Quote
>I would get the following results:

>ContractID     ClientID     Board     Allowance
>1                    1                100        200
>2                    5                95           0

>and so on.

I'm not sure exactly what you're after, you appear to want two result
for the allowance column? I#m guessing that's a typo, maybe this will is
what you're after

select contractid,
clientid,
sum(decode( caretype,1,amount,0) ) as board,
sum(decode( caretype,2,amount,0) ) as allowance
from   ....
where  billingid = somevalue
group by contractid, clientid

Your SQL flavour might prefer CASE WHEN instead of the decode.

HTH
Bill

 

Re:Need help with SQL statement


Quote
"Eric Harmon" <Eric.Har...@tpx.turbopower.com> wrote in message

news:3de41120$1@newsgroups.borland.com...

Quote

> To simplify dramatically, I have a table with the following fields:

> BillingPeriodID INTEGER
> ContractID  INTEGER
> ClientID      INTEGER
> CareType   INTEGER
> Amount      FLOAT

> CareType 1 means Board, and CareType 2 means Allowance.

> I need a query that totals up the board and allowance for each
> ContractID/ClientID combination, for a given BillingPeriodID.  (In other
> words, the BillingPeriodID is in the WHERE clause).  I want a result set
> with the following columns:

> ContractID
> ClientID
> Board
> Allowance

> Given the following data:

> BillingPeriodID    ContractID     ClientID     CareType     Amount
> 1                        1                    1                1
> 100
> 1                        1                    1                2
> 200
> 1                        2                    5                1
> 75
> 1                        2                    5                1
> 20

> I would get the following results:

> ContractID     ClientID     Board     Allowance
> 1                    1                100        200
> 2                    5                95           0

> I can't figure out how to SUM the appropriate amounts to return the
correct
> values in Board and Allowance.  Any help would be greatly appreciated.

Eric, it's very difficult to take a single column and return two horizontal
columns in an SQL statement, though its very simple in a stored procedure.
What about including the CareType in the result set, then it becomes a
simple group by:

Select ContractID, ClientID, CareType, Sum(Amount) as BoardAllowance
from table
where BillingPeriodID = 1
group by ContractID, ClientID, CareType

ContractID    ClientID    CareType    BoardAllowance
1                    1                1                100
1                    1                2                200
2                    5                1                95
2                    5                2                0

Otherwise go to a stored procedure.

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
It used to be that other people's achievements were considered an
inspiration, not a grievance.

Re:Need help with SQL statement


Wayne,

Quote
> Select ContractID, ClientID, CareType, Sum(Amount) as BoardAllowance
> from table
> where BillingPeriodID = 1
> group by ContractID, ClientID, CareType

That will work perfectly.  Right now this is a Paradox table, so I don't
have the ability to do stored procedure.  I'm going to be upsizing this app
to MS SQL Server shortly, though.  All this is going to do is create a
temporary table, so your suggestion works perfectly - I can traverse the
query results and create the temp table easily enough.

Thanks!

-Eric

Other Threads