T-sql: ranking groups by changed positive values

There is some data in a table:

    date   finance
    1      5 (income)
    3     -3 (spending)
    4     -1 (spending)
    5     10 (income)
    9     -2 (spending)
   10     -3 (spending)
   15     -3 (spending)

Every positive value forms a group, each group begins with a positive value and includes all the negative values before the next posivitve value.

  • Inserting a predetermined datetime value into SQL table
  • Update one column from substring of another column
  • SQL Server 2000 - Query a Table’s Foreign Key relationships
  • Is there any potential performance problems if I update by deleting and inserting
  • “Class not registered” error when opening recordset using VB6
  • Stored procedure
  • So I have to identify groups like this:

        date  finance  rank
        1      5       group 1  
        3     -3       group 1
        4     -1       group 1
    
        5     10       group 2
        9     -2       group 2
       10     -3       group 2
       15     -3       group 2
    

    Dates always grow and are never the same.

    Is that possible with window functions t-sql? What is the optimal way to get this result?

  • How to avoid plan cache bloat using queries in entity framework
  • SQL Server Join In Order
  • Input string was not in a correct format.‎
  • Can I get the results of a stored procedure into a cursor within another stored procedure in SQL
  • How to find the total between the dates for each values
  • passing parameter to the CRYSTAL REPORT through C# in asp.net
  • 2 Solutions collect form web for “T-sql: ranking groups by changed positive values”

    This is a little trashy but it appears to work

    SELECT [date], finance 
        , 'group ' 
            + rtrim((SELECT count(1) FROM #Tmp WHERE finance > 0 AND [date] < t.[date])  
                + CASE WHEN finance > 0 THEN 1 ELSE 0 END) 'Group'
    FROM #Tmp t 
    

    It seems like your grouping is quite dependent on the order of the rows.

    Assuming you have a Row# [R] in your source table the following code should work.

    --FIND ROWS WITH POSITIVE VALUE
    SELECT R2=DENSE_RANK() OVER(ORDER BY R), R
    INTO #TEMP
    FROM <SOURCE_TABLE>
    WHERE FINANCE>0
    
    --CREATE GROUPS BY DETECTING STARTING ROW AND ENDING ROW (ENDING ROW BEING 1 LESS THAN NEXT STARTING ROW)
    SELECT GROUP_N=ROW_NUMBER() OVER(ORDER BY LOW.R)    --ASSIGN GROUP #S IN THE SAME ORDER AS THEY APPEAR IN THE SOURCE TABLE
        ,R_START=LOW.R, R_END=(HIG.R-1)
    INTO #TEMP2
    FROM #TEMP LOW
    JOIN #TEMP HIG
        ON LOW.R2=(HIG.R2-1)
    UNION
    --LAST GROUP STARTS AT THE LAST DETECTED STARTING ROW AND ENDS AT THE LAST ROW OF THE SOURCE TABLE
    SELECT R_START_LAST_GROUP=(SELECT MAX(R) FROM #TEMP), R_END_LAST_GROUP=(SELECT MAX(R) FROM <SOURCE_TABLE>)
    
    --GET SOURCE DATA AND APPEND GROUP #
    SELECT DAT.*, GRP.GROUP_N
    FROM #TEMP2 GRP
    JOIN <SOURCE_TABLE> DAT
        ON DAT.R BETWEEN GRP.R_START AND GRP.R_END
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.