0

I have a query that I am "rolling up values". Lets say I have two rows that are the "same", save for one field:

    [CNumber]   [PNumber]    [date]   [FullName]           [Type]  [P]          [S]   [End] 
    T00000000   A0100000    2014/04/11  CUSTOMER            TYPE1   828600.86   0.00    0.00
    T00000000   A0100000    2014/04/11  CUSTOMER            TYPE2   250000.00   0.00    0.00
    T00000000   A0100000    2014/04/11  CUSTOMER            TYPE3   250000.00   0.00    0.00
    T00000000   A0100000    2014/04/11  CUSTOMER            TYPE4   250000.00   0.00    0.00

I am sum'ing those rows, with the help of over(partition by TNumber) That is working just fine. No issue there, the number get added, so both rows have the same amounts.

T00000000   A0100000    2014/04/11  CUSTOMER            TYPE1   1578600.86  0.00    0.00
T00000000   A0100000    2014/04/11  CUSTOMER            TYPE2   1578600.86  0.00    0.00
T00000000   A0100000    2014/04/11  CUSTOMER            TYPE3   1578600.86  0.00    0.00
T00000000   A0100000    2014/04/11  CUSTOMER            TYPE4   1578600.86  0.00    0.00

Totally fine, and is what I want.

Now I just want to pick ONE of them to display. some of the partitions have unique row numbers just fine and dandy. I am attempting to do that with the code below:

    ;WITH D AS
    (
        SELECT A.[CNumber]
              ,A.[PNumber]
              ,A.[Date]
              ,ROW_NUMBER() OVER(PARTITION BY A.[CNumber] ORDER BY A.[date] DESC) AS CRowNumber
              ,A.CType
              ,A.P
              ,A.S
              ,A.[End]
        FROM #tmp as A

    )

SELECT  B.CNumber
    ,B.PNumber
    ,B.LDate
    ,C.FullName
    ,B.[CType]
    ,D.CRowNumber
    ,SUM(B.P) OVER(PARTITION BY B.[CNumber]) AS P
    ,SUM(B.E) OVER(PARTITION BY B.[CNumber])  AS E
    ,SUM(B.[End]) OVER(PARTITION BY B.[CNumber]) AS [End]

FROM #tmp AS B
    INNER JOIN [DATABASE].dbo.[TABLE] AS C
        ON B.PNumber = c.Code
    INNER JOIN D AS D
        ON t.PNumber = CT.PNumber
WHERE B.I >= @Var1
GROUP BY B.CNumber
    ,B.PNumber
    ,CONVERT(varchar(11),B.[Date],111)
    ,SUBSTRING(c.FullName,10,40)
    ,B.CType
    ,B.Paid
    ,B.Expense
    ,B.[End]
    ,D.CRowNumber
ORDER BY B.[CNumber] DESC

When I run the code, the row_number() isn't unique to the [CNumber] such like:

    [CNumber]   [PNumber]    [date]   [FullName]           [Type]  [P]          [S]   [End] [CRowNumber] 
    T00000000   A0100000    2014/04/11  CUSTOMER            TYPE1   1578600.86  0.00    0.00  1
    T00000000   A0100000    2014/04/11  CUSTOMER            TYPE2   1578600.86  0.00    0.00  2
    T00000000   A0100000    2014/04/11  CUSTOMER            TYPE3   1578600.86  0.00    0.00  3
    T00000000   A0100000    2014/04/11  CUSTOMER            TYPE4   1578600.86  0.00    0.00  1

Am I just misunderstanding and or totally off base,(on how to go about this) how row_number functions with over(partition by)?

I would like to see that all the rows within the partition have their own unique row number.

EDIT 1

switched the logic from having the "type" column tag along through the CTE, to applying it once I had the final select ready. It must've been the join at the bottom for B.PNumber = C.code that was screwing it up.

Thanks for all the suggestions.

  • 2
    Sorry, I don't have enough points to make comments so I have to reply down here. What do you get when you run just the query inside the CTE? Is Row_Number correct there? My suspicion is it is something to do with the joins in the lower query. – Robert L Davis Apr 11 '14 at 20:12
  • Doing a spot check of a few trouble records, I get the proper row_number ordering. I'll try removing each of the joins and see which one is causing it. Is it because I am doing the row_numbering before I make those joins? – RateControl Apr 11 '14 at 20:18
  • Weird. I can add a comment to this, but I can't create a whole new comment. I suspect that B.PNumber = c.Code is a many-to-many relationship which duplicates records from the CTE. – Robert L Davis Apr 11 '14 at 20:29
  • 1
    Welcome @sqlsoldier you'll soon have enough points! – Max Vernon Apr 12 '14 at 3:17
  • 1
    @Robert you can always comment on your own posts, and now you can comment anywhere :) good to have you on board... – Jack Douglas Apr 12 '14 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.