Hello Svein

Thank you very much for your answer, it was very clear, I know now what to
do.

Each pay is in a yearly basis, a client can not pay November to February by
example but they can (in very rare cases) pay from July or August in
advance.

Really I save the money received, not the months received, but knowing the
money I can know the months because it is the same for each month.

Thank you very much again.

Walter.



2011/9/19 Svein Erling Tysv�r <svein.erling.tysvaer@...>

> **
>
>
> 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
>
>
>

[Non-text portions of this message have been removed]