Walter wrote:
Hello everybody

In a table I have data about money received from the clients. Sometimes a
client pay more than a month, like this:

Client Date(mm/dd/yyyy) Months received
---------------------------------------------
Alice 01/02/2011 2
Alice 03/05/2011 3
Sylvia 01/08/2011 1
Sylvia 02/06/2011 1
Sylvia 03/02/2011 1
Vivian 02/04/2011 1
Zuny 01/10/2011 1
Zuny 03/15/2011 3

And I need to get something like this:

Client Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dic
----------------------------------------------------------------------
Alice Yes Yes Yes Yes Yes
Sylvia Yes Yes Yes
Vivian Yes
Zuny Yes Yes Yes Yes


How I can get it?. I need to write a Stored Procedure or is it possible with
just a select?

*******

Sets answer:

That's a nice and cosy table, Walter. Your particular question may be answered like this:

with MonthsPaid(Client, Months) as
SELECT Client, SUM(MonthsReceived)
FROM PaymentTable
GROUP BY 1)

SELECT Client,
CASE WHEN Months >= 1 THEN 'Yes' END as Jan,
CASE WHEN Months >= 2 THEN 'Yes' END as Feb,
CASE WHEN Months >= 3 THEN 'Yes' END as Mar,
CASE WHEN Months >= 4 THEN 'Yes' END as Apr,
CASE WHEN Months >= 5 THEN 'Yes' END as May,
CASE WHEN Months >= 6 THEN 'Yes' END as Jun,
CASE WHEN Months >= 7 THEN 'Yes' END as Jul,
CASE WHEN Months >= 8 THEN 'Yes' END as Aug,
CASE WHEN Months >= 9 THEN 'Yes' END as Sep,
CASE WHEN Months >= 10 THEN 'Yes' END as Oct,
CASE WHEN Months >= 11 THEN 'Yes' END as Nov,
CASE WHEN Months >= 12 THEN 'Yes' END as Dic /*I thought it was Dec*/
FROM MonthsPaid

However, that's a very specific answer to the table you describe. For a more general (and probably more useful) answer, there are a few things to consider:

Zuny paid three months in March and you wanted that to mean the period February-April (and not March-May), whilst when Alice did the same (even 10 days before Zyny), her payment period was March-May. I figured I could just use the SUM function, since it didn't seem to matter what time the payment was made. The alternative interpretation is that it does matter, but that some human reasoning should be involved and that one should know that Zuny was just late with his payment for February. Unfortunately, SQL is lousy at human reasoning and writing SQL that can handle this is complex - it might be OK as long as it is limited to the current year, but if a three month payment in January could also be for the period December-February or even September-November last year, then it is difficult. Add to that the possibility of someone paying for 14 months one December, that there's a miscommunication between Sylvia and her husband so that her account gets two simultaneous payments, or that Vivian happens to be away for three months so that she should be exempt for payment from February-April, but pay from May again (or even worse, that Vivian in January paid for January and May in one single payment), and you at best end up with seriously complex SQL. If such things can happen, I would recommend you to either have two further fields - Period_From and Period_To OR one field Period and one record for each period.

HTH,
Set