March 12, 2017 at 5:39 pm
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!
March 13, 2017 at 3:14 am
sanda.jan00 - Sunday, March 12, 2017 5:39 PMHello,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 5I 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!
Change the RANK() clause part to this:
Rn = ROW_NUMBER() OVER(PARTITION BY suma ORDER BY ID)
Use the output from this query either as a CTE or as aderived table. Subtract the new column Rn from ID, and use the result as a newpartition value for RANK().
If you post up a CREATE TABLE with INSERTS which can be runto create and populate a sample table, someone will show you the code.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply