Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2012
    Posts
    2

    Unanswered: Finding the balance of a client for next 5days

    I have tables as below.
    When the user execute the query, user want to see the balance of each client for the next coming five days!


    **TABLE1**


    Code:
    ClientId    Balance BalanceDate  
        1           1000    10/01/2012
        2           2000    10/02/2012
        3           3000    10/03/2012
        4           4000    10/04/2012
        5           4000    10/04/2012
    I have implemeted the code as below


    Code:
     SELECT  ClientId, Balance, [0] '10/1/2012',[1] '10/2/2012', [2] '10/3/2012', [3] '10/4/2012', [4] '10/5/2012'
        FROM    ( 
        SELECT  EquipmentCode,Balance,  DayNumber
        FROM    TABLE1 NextDays) AS SourceTable 
                PIVOT 
                ( 
                 MIN(BalanceDate) 
                 FOR DayNumber IN ([0],[1], [2], [3], [4]
                 ) AS PivotTable
    Client1 has the balance 1000 on 10/01/2012, that means he will have th balance on 10/02/2012,10/03/2012,10/04/2012,10/05/2012

    **The reuslt of the above logic is**


    Code:
    ClientId Balance '10/1/2012' '10/2/2012' '10/3/2012' '10/4/2012' '10/5/2012'  
    1        1000        1000        null        null        null        null          
    2        2000        null        2000        null        null        null          
    3        3000        null        null        3000        null        null          
    4        4000        null        null        null        4000        null          
    5        4000        null        null        null        4000        null
    But Im looking for the result as


    Code:
    ClientId Balance '10/1/2012' '10/2/2012' '10/3/2012' '10/4/2012' '10/5/2012     
    1        1000        1000        1000        1000        1000        1000           
    2        2000        null        2000        2000        2000        2000        
    3        3000        null        null        3000        3000        3000        
    4        4000        null        null        null        4000        4000        
    5        4000        null        null        null        4000        4000
    Help will be appriciated, Thank You!

  2. #2
    Join Date
    Jan 2004
    Location
    Issy les Moulineaux, France
    Posts
    24
    Hi.
    Not sure of what you exactly want.
    Can you have the following (Your sample plus the last line) :
    Code:
    ClientId    Balance BalanceDate  
        1           1000    10/01/2012
        2           2000    10/02/2012
        3           3000    10/03/2012
        4           4000    10/04/2012
        5           4000    10/04/2012
        5            500    10/05/2012
    In this case, what result do you expect ?

    Regards,
    Eric

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •