SQL Query - grouping?

[senojeel]

I just need a push in the right direction.

I querying a MSSQL 2000 Database. I have couple of tables, one is Organizations and the other is Installments. What they do is, the Organizations is obviously org info. The installments is a table that has payments paid out to the organizations. What I want is to query the Organizations and have in the same result row all payments for that org.

For Example.
Organizations table - OrgId, OrgName
Installments - Installment Id, OrgId, NumberPayments, PaymentAmount

I want the results to be
OrgID, OrgName, NumberPayments, PaymentAmount1, PaymentAmount2

Does that make sense?

BTW, if it makes it any easier, there are always going to be only 2 payments.

Thanks!
Shawn

[azimmer]

I just need a push in the right direction.

I querying a MSSQL 2000 Database. I have couple of tables, one is Organizations and the other is Installments. What they do is, the Organizations is obviously org info. The installments is a table that has payments paid out to the organizations. What I want is to query the Organizations and have in the same result row all payments for that org.

For Example.
Organizations table - OrgId, OrgName
Installments - Installment Id, OrgId, NumberPayments, PaymentAmount

I want the results to be
OrgID, OrgName, NumberPayments, PaymentAmount1, PaymentAmount2

Does that make sense?

BTW, if it makes it any easier, there are always going to be only 2 payments.

Thanks!
Shawn

If there are no more than two payments:

select o.OrgID, o.OrgName, (select count(*) from Installments i where i.OrgID=o.orgID) as NumberPayments, p1.PaymentAmount, p2.PaymentAmount
from Organizations o, Installments p1, Installments p2
where o.OrgID=p1.OrgID and o.OrgID=p2.OrgID and p1.[Installment ID]<>p2.[Installment ID]
 

BTW: What is NumberPayment in the Installments table?

[senojeel]

If there are no more than two payments:

select o.OrgID, o.OrgName, (select count(*) from Installments i where i.OrgID=o.orgID) as NumberPayments, p1.PaymentAmount, p2.PaymentAmount
from Organizations o, Installments p1, Installments p2
where o.OrgID=p1.OrgID and o.OrgID=p2.OrgID and p1.[Installment ID]<>p2.[Installment ID]
 

BTW: What is NumberPayment in the Installments table?

NumberPayment is just which payment it is, first or second, which I guess I wouldn't need if we are counting the number of payments already with this query.

Thanks, I will give this a try.

Shawn

[senojeel]

NumberPayment is just which payment it is, first or second, which I guess I wouldn't need if we are counting the number of payments already with this query.

Thanks, I will give this a try.

Shawn

Okay. The query is returning either 4 or 8 rows for each organization, where it should be one. The NumberPayments is correct (2) for all rows of the org. The p1.PaymountAmount is correct on all 4 or 8 rows and the p2.PaymentAmount is never right (The number there doesn't make sense at all)

FYI, Each Installment has its own row in the InstallmentsOrg table.

Here is my query. I changed a few things.

select o.OrgId, o.OrgName, 
    (select count(*)
        from InstallmentsOrg i 
        where i.OrgID=o.orgID AND YEAR(i.DateCreated) = 2007 AND i.InstallmentType = 'RAP') as NumberPayments,
    p1.InstallmentAmt as Payment1,
    p2.InstallmentAmt as Payment2,
    p1.DateCreated,
    p1.InstallmentType
from Organizations o, InstallmentsOrg p1, InstallmentsOrg p2
where o.OrgID=p1.OrgId and o.OrgID=p2.OrgId and p1.InstallmentId<>p2.InstallmentId AND YEAR(p1.DateCreated) = 2007 AND p1.InstallmentType = 'RAP'

[senojeel]

So I think I might be closer?

This probably isn't the best way to do it, but I am getting closer on my results.

Here is my query:

select 
    i.OrgId, 
    o.OrgName, 
    i1.InstallmentAmt as Installment1, 
    i2.InstallmentAmt as Installment2,
    min(i.InstallmentId) as InstallmentId_1, 
    max(i.InstallmentId) as InstallmentId_2
FROM InstallmentsOrg i
LEFT JOIN Organizations o ON i.OrgId = o.OrgId
Left Join InstallmentsOrg i1 ON i.InstallmentId = i1.InstallmentId
Left Join InstallmentsOrg i2 ON i.InstallmentId = i2.InstallmentId
 
WHERE i.InstallmentType = 'RAP' and YEAR(i.DateCreated) = 2007
 
GROUP BY i.OrgId,o.OrgName,i1.InstallmentAmt, i2.InstallmentAmt
 
order by o.OrgName 
 

My problem now is I can't get Installment2 to equal the 2nd Installment that was the ID of InstallmentId_2

Make sense?