Problem with Rank() function

Transact-SQL
1

Hello,

I have troubles with this query:

_SELECT _

_id, _

_SUM(repayment) as suma, _

DATEADD(S, -1, DATEADD(mm,DATEDIFF (m,0,date+1,0)) AS obd

RANK() OVER(PARTITION BY id,suma, ORDER BY DATEADD(S, -1, DATEADD(mm,DATEDIFF (m,0,date+1,0)) asc) AS rank

FROM DDV.DBO.DATA

ORDER BY id, obd

which generates this result:

id suma obd rank
1 0 1.1.2014 1
2 0 1.2.2014 2
3 0 1.3.2014 3
4 8 1.4.2014 1
5 0 1.5.2014 4
6 0 1.6.2014 5

I need to order it by id and suma and everytime when there is a different value in a column suma , the RANK should count from number 1 again. Using above mentioned result table - in a row with id=5 the rank should be 1 and not 4.

Could anybody help me with this please??

Thank you!

2

Hi,

Will Dense_rank() provide a solution to rank().

Regards
Anna

3

Consumable test data, with dates in ISO format, is always useful!

CREATE TABLE #t
(
    id int NOT NULL
    ,suma int NOT NULL
    ,obd datetime NOT NULL
);
INSERT INTO #t
VALUES (1, 0, '20140101')
    ,(2, 0, '20140102')
    ,(3, 0, '20140103')
    ,(4, 8, '20140104')
    ,(5, 0, '20140105')
    ,(6, 0, '20140106');

One approach is to use the order difference technique to group consecutive dates where suma remains the same:

WITH Grps
AS
(
    SELECT *
        ,DATEDIFF(day, 0, obd)
            - ROW_NUMBER() OVER (PARTITION BY suma ORDER BY obd) AS Grp
    FROM #t
)
-- select * from Grps
SELECT id, suma, obd
    ,ROW_NUMBER() OVER (PARTITION BY suma, Grp ORDER BY obd) AS Ranking
FROM Grps
ORDER BY obd;
4

Looking at this again, it seems your dates might be in European format.
If this is the case then alter the code to:

WITH Grps
AS
(
    SELECT *
        ,ROW_NUMBER() OVER (ORDER BY obd)
            - ROW_NUMBER() OVER (PARTITION BY suma ORDER BY obd) AS Grp
    FROM #t
)
-- select * from Grps
SELECT id, suma, obd
    ,ROW_NUMBER() OVER (PARTITION BY suma, Grp ORDER BY obd) AS Ranking
FROM Grps
ORDER BY obd;