Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: Simple T-SQL Looping Question

    Hi again everyone,

    I have a tiny table that has three fields, Contract Name, Month/Year, and Cost. For every contract I wish to sum the Cost which is easy but append all Month/Year values per contract and cost into one field.

    Example: AB1234 | $456.33 | 04/01/2008, 05/01/2008, 10/01/2008.

    If I place the summed amounts per contract per month in a temporary table and sort it by contract, can I count on a loop taking each record per contract in order? In other words for the three records for contract AB1234 "do while" contract value is 'AB1234' where the "do while" is append one month after another into one field? At the end of the loop I can simply read in the next contract and continue for each contract value. Advise? Thx.

  2. #2
    Join Date
    Aug 2009
    Posts
    262
    can u give me what code you have written so far?

    version of your software
    and sample ddl . ( i.e create table, and few inserts)

  3. #3
    Join Date
    Feb 2004
    Posts
    193
    SQL Server 2000. This is what I have so far. My main concern was if I create a temp table does a cursor retrieve the records in order? It looks like it does. Some of the output I just placed there so I can see the processing. I will fine tune it as I think I am getting it. Thanks.


    declare @RowNum int
    declare @P2PContract nchar(5)
    declare @P2PContract_Next nchar(5)
    declare @CurrntMnthP2PAmnt smallmoney

    declare CustList cursor for
    select P2PContract, CurrntMnthP2PAmnt from #CMSPDEReceivableYearContractCalc

    OPEN CustList

    FETCH NEXT FROM CustList
    INTO @P2PContract, @CurrntMnthP2PAmnt

    set @RowNum = 0
    WHILE @@FETCH_STATUS = 0 -- 0 if a record is fetched, 1 if no more records
    BEGIN
    set @RowNum = @RowNum + 1
    print cast(@RowNum as char(2)) + ' ' + @P2PContract + ' ' + cast(@CurrntMnthP2PAmnt as char(12))

    FETCH NEXT FROM CustList
    INTO @P2PContract_Next, @CurrntMnthP2PAmnt

    IF @P2PContract = @P2PContract_Next -- if the "current" contract = "next" contract record
    --
    BEGIN
    print '@P2PContract: ' + @P2PContract
    print '@P2PContract_Next: ' + @P2PContract_Next
    print 'Continue appending months' -- code to keep appending months
    END

    ELSE IF @P2PContract <> @P2PContract_Next
    BEGIN
    print '@P2PContract <> @P2PContract_Next'
    print 'append last month of group' -- code to keep appending months
    print 'Bring in next contract value' -- code to keep appending months
    set @P2PContract = @P2PContract_Next
    -- ELSE BREAK -- stop appending months bec a different value for contract is reached
    END
    END

    CLOSE CustList
    DEALLOCATE CustList




    Results:
    1 H0543 0.00
    @P2PContract: H0543
    @P2PContract_Next: H0543
    Continue appending months
    2 H0543 0.00
    @P2PContract: H0543
    @P2PContract_Next: H0543
    Continue appending months
    3 H0543 2535.13
    @P2PContract: H0543
    @P2PContract_Next: H0543
    Continue appending months
    4 H0543 0.00
    @P2PContract <> @P2PContract_Next
    append last month of group
    Bring in next contract value
    5 H3815 -160.72
    @P2PContract: H3815
    @P2PContract_Next: H3815
    Continue appending months
    6 H3815 756.02
    @P2PContract <> @P2PContract_Next
    append last month of group
    Bring in next contract value
    7 H5425 804.80
    @P2PContract: H5425
    @P2PContract_Next: H5425
    Continue appending months
    8 H5425 2.05
    @P2PContract: H5425
    @P2PContract_Next: H5425
    Continue appending months
    9 H5425 0.00
    @P2PContract <> @P2PContract_Next
    append last month of group
    Bring in next contract value
    10 S5596 38.03
    @P2PContract <> @P2PContract_Next
    append last month of group
    Bring in next contract value
    11 S5644 1871.67
    @P2PContract: S5644
    @P2PContract_Next: S5644
    Continue appending months
    12 S5644 6.15
    @P2PContract: S5644
    @P2PContract_Next: S5644
    Continue appending months
    13 S5644 0.00
    @P2PContract <> @P2PContract_Next
    append last month of group
    Bring in next contract value
    14 S5678 1849.54
    @P2PContract: S5678
    @P2PContract_Next: S5678
    Continue appending months
    15 S5678 1955.07
    @P2PContract <> @P2PContract_Next
    append last month of group
    Bring in next contract value
    16 S5884 4666.65
    @P2PContract: S5884
    @P2PContract_Next: S5884
    Continue appending months
    17 S5884 2392.33
    @P2PContract: S5884
    @P2PContract_Next: S5884
    Continue appending months
    18 S5884 0.00
    @P2PContract <> @P2PContract_Next
    append last month of group
    Bring in next contract value
    19 S5921 1773.39
    @P2PContract: S5921
    @P2PContract_Next: S5921
    Continue appending months
    20 S5921 9.70
    @P2PContract: S5921
    @P2PContract_Next: S5921
    Continue appending months
    21 S5921 6.70
    @P2PContract: S5921
    @P2PContract_Next: S5921
    Continue appending months
    22 S5921 0.00
    @P2PContract: S5921
    @P2PContract_Next: S5921
    Continue appending months
    23 S5921 0.00
    @P2PContract: S5921
    @P2PContract_Next: S5921
    Continue appending months
    24 S5921 27.83
    @P2PContract <> @P2PContract_Next
    append last month of group
    Bring in next contract value
    25 S5931 0.00
    @P2PContract <> @P2PContract_Next
    append last month of group
    Bring in next contract value
    26 S5960 2151.41
    @P2PContract <> @P2PContract_Next
    append last month of group
    Bring in next contract value
    27 S5967 10839.43
    @P2PContract: S5967
    @P2PContract_Next: S5967
    Continue appending months
    28 S5967 69.73
    @P2PContract: S5967
    @P2PContract_Next: S5967
    Continue appending months
    29 S5967 3826.41
    @P2PContract: S5967
    @P2PContract_Next: S5967
    Continue appending months
    30 S5967 0.00
    @P2PContract: S5967
    @P2PContract_Next: S5967
    Continue appending months
    31 S5967 0.00
    @P2PContract <> @P2PContract_Next
    append last month of group
    Bring in next contract value
    32 S5998 0.00
    @P2PContract: S5998
    @P2PContract_Next: S5998
    Continue appending months
    33 S5998 254.83
    @P2PContract: S5998
    @P2PContract_Next: S5998
    Continue appending months

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    1. All designs that rely on order are flawed.
    2. This is a presentation issue. Let the presentation layer handle it and return to it the data it needs.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Feb 2004
    Posts
    193
    I am not sure what you mean. The looping needs to read the contract number. IF the contract number is the same as the prior I need to append a date value into a string, this must continue until the code sees a different contract number. It must also sum the money field for the months involved for a contract.


    Contract............Sum of Money...........Months
    ABC123.................4658.23.............Apr-2008, May-2008, Sep-2009...etc --append until new K
    CDE345...................233.34...............June-2008
    .
    .
    etc

Posting Permissions

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