SQLTeam.com | Weblogs | Forums

Rank Records In The Order They Are Read From The Table

sql2008r2

#1

I need to rank records in a table (Tablea) so they look like attached example. I manually put those rankings in.
I need the records to be ranked in the order that they are read from the table. The table is in the correct sequence for ranking.
Within a group, I need rankings by SalesID and Status1. If SalesID or Status1 change within a group, increase the ranking by 1.
The rankings will start from 1 again every time the Group_ID changes.


#2

Please provide

  • table definitions in the form of create statements
  • sample data in the form of insert statements
  • expected output from your sample data
  • your query
  • if you don't have an "order by" in your query, then please describe how exactly your rows should be sorted

#3

I will write records to this table and they will be in sequence by Group_ID ascending and LogID descending.

Now, I put the ranking numbers into this table manually.

I have no query to do the rankings. I need a query that will read this table and put the rankings in. I cannot figure it out.

The rankings should change anytime the SalesID or Status1 changes inside of a Group_ID.

When the Group_ID changes, the ranking number must start at 1 again.

Let me know if you need more. Have to step out for a while.

CREATE
TABLE [SC_StatusDaysDate](

   [LogID] [int] NULL,

   [Created_On] [varchar](10)

NULL,

   [Group_ID] [int] NULL,

   [SalesID] [varchar](20)

NULL,

   [Status1] [varchar](10)

NULL,

   [GroupRank] [int] NULL

)
ON [PRIMARY]

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6539123,
N'2017-11-02', 2062335,
N'MARKAM',
N'D', 1)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6539122,
N'2017-11-02', 2062335,
N'MARKAM',
N'D', 1)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6539121,
N'2017-11-02', 2062335,
N'MARKAM',
N'D', 1)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6539120,
N'2017-11-02', 2062335,
N'MARKAM',
N'D', 1)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6539119,
N'2017-11-02', 2062335,
N'MARKAM',
N'D', 1)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6375137,
N'2017-10-12', 2062335,
N'ARREP',
N'C', 2)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6375136,
N'2017-10-12', 2062335,
N'ARREP',
N'C', 2)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6375135,
N'2017-10-12', 2062335,
N'ARREP',
N'C', 2)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6375134,
N'2017-10-12', 2062335,
N'ARREP',
N'C', 2)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6311588,
N'2017-09-18', 2062335,
N'MARKAM',
N'D', 3)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6311587,
N'2017-09-18', 2062335,
N'MARKAM',
N'D', 3)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6311586,
N'2017-09-18', 2062335,
N'MARKAM',
N'D', 3)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6135939,
N'2017-07-06', 2062335,
N'CSDIS',
N'I', 4)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6135938,
N'2017-07-06', 2062335,
N'CSDIS',
N'I', 4)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6468804,
N'2017-09-01', 2065114,
N'MARKAM',
N'C', 1)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6468803,
N'2017-09-01', 2065114,
N'MARKAM',
N'C', 1)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6466186,
N'2017-09-01', 2065114,
N'MARKAM',
N'C', 1)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6466185,
N'2017-09-01', 2065114,
N'MARKAM',
N'C', 1)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6279108,
N'2017-05-26', 2065114,
N'MARKAM',
N'I', 2)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6279107,
N'2017-05-26', 2065114,
N'MARKAM',
N'I', 2)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6279106,
N'2017-05-26', 2065114,
N'MARKAM',
N'I', 2)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6279105,
N'2017-05-26', 2065114,
N'MARKAM',
N'I', 2)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6275635,
N'2017-05-26', 2065114,
N'MARKAM',
N'I', 2)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6275631,
N'2017-05-26', 2065114,
N'MARKAM',
N'I', 2)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6275629,
N'2017-05-26', 2065114,
N'MARKAM',
N'I', 2)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6275628,
N'2017-05-26', 2065114,
N'MARK',
N'I', 3)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6071559,
N'2017-05-26', 2065114,
N'MARKAM',
N'I', 4)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6071556,
N'2017-05-26', 2065114,
N'MARKAM',
N'I', 4)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6071554,
N'2017-05-26', 2065114,
N'MARKAM',
N'I', 4)

INSERT [SC_StatusDaysDate]
([LogID], [Created_On], [Group_ID], [SalesID], [Status1],
[GroupRank]) VALUES
(6071553,
N'2017-05-26', 2065114,
N'MARKAM',
N'I', 4)


#4

But you do have a query to read the data, which you showed i screendump from excel? Please show the query - especially your "order by" will be interesting.


#5

Hmm this doesn't look exactly like the excel screendump.

Try this

Query
with cte1
  as (select logid
            ,created_on
            ,group_id
            ,salesid
            ,status1
            ,row_number() over(partition by group_id order by created_on desc,logid desc) as rn
            ,dense_rank() over(partition by group_id order by created_on desc) as rk
        from sc_statusdaysdate
     )
    ,cte2
  as (select b.group_id
            ,b.rn
            ,1 as adjustment
        from cte1 as a
             inner join cte1 as b
                     on b.group_id=a.group_id
                    and (b.salesid!=a.salesid
                     or  b.status1!=a.status1
                        )
                    and b.rn=a.rn+1
                    and b.rk=a.rk
     )
select a.logid
      ,a.created_on
      ,a.group_id
      ,a.salesid
      ,a.status1
      ,a.rk+sum(isnull(b.adjustment,0)) as grouprank
  from cte1 as a
       left outer join cte2 as b
                    on b.group_id=a.group_id
                   and b.rn<=a.rn
 group by a.logid
         ,a.created_on
         ,a.group_id
         ,a.salesid
         ,a.status1
         ,a.rk
 order by a.group_id
         ,a.created_on desc
         ,a.logid desc
;

#6

I am pulling these records in from a Oracle DB and putting them into a SQL table called SC_StatusDaysDate . See query below. Once they are in that SQL table I need the Rank to be put in. Right now the Rank is 0 for all the records.

The records are in the correct sequence to be Ranked.

These records are different then the ones in my example but the idea is the same.

I need a query to update these records with a RANK.

SET @sql = 'SELECT GPCOMP1.GPPROBLOG.PROBLOG_ID, GPCOMP1.GPPROBLOG.CREATED_ON, GPCOMP1.GPPROBLOG.GROUP_ID, GPCOMP1.GPPROBLOG.SALESID,GPCOMP1.GPPROBLOG.STATUS as Status1, 0 as GroupRank

             FROM GPCOMP1.GPPROB, GPCOMP1.GPPROBLOG

              WHERE GPCOMP1.GPPROB.PROBLEM_ID = GPCOMP1.GPPROBLOG.PROBLEM_ID AND GPCOMP1.GPPROB.GROUP_ID = GPCOMP1.GPPROBLOG.GROUP_ID AND

                           (GPCOMP1.GPPROB.OPEN_INVOICE_TRAN_ID IS NOT NULL) AND (GPCOMP1.GPPROBLOG.PAYDATE IS NULL) AND (GPCOMP1.GPPROBLOG.CLOSEDATE IS NULL) AND (GPCOMP1.GPPROB.GROUP_ID = 2062335 or GPCOMP1.GPPROB.GROUP_ID = 2062342 or GPCOMP1.GPPROB.GROUP_ID = 2065114)  ORDER BY GPCOMP1.GPPROBLOG.GROUP_ID, GPCOMP1.GPPROBLOG.PROBLOG_ID DESC '

 SET @sql = N'INSERT INTO [SC_StatusDaysDate] SELECT  * from openquery ([GTPFR],    '''
  • Replace(@sql,
    '''',
    '''''') +
    ''')'

#7

Did you try the query I provided?


#8

As you can tell, I am new to this website.

That being said, where do I find the query that you provided ?


#9

Click on the arrow where it reads "Query"


#10

Not working the way I need.

What I need is :

logid created_on group_id salesid status1 grouprank

6539123 2017-11-02 2062335 MARKAM D 1

6539122 2017-11-02 2062335 MARKAM D 1

6539121 2017-11-02 2062335 MARKAM D 1

6539120 2017-11-02 2062335 MARKAM D 1

6539119 2017-11-02 2062335 MARKAM D 1

6375137 2017-10-12 2062335 ARREP C 2

6375136 2017-10-12 2062335 ARREP C 2

6375135 2017-10-12 2062335 ARREP C 2

6375134 2017-10-22 2062335 ARREP C 2

6311588 2017-09-18 2062335 MARKAM D 3

6311587 2017-09-18 2062335 MARKAM D 3

6311586 2017-09-18 2062335 MARKAM D 3

6135939 2017-07-06 2062335 CSDIS I 4

6135938 2017-07-06 2062335 CSDIS I 4

6468804 2017-09-01 2065114 MARKAM C 1

6468803 2017-09-01 2065114 MARKAM C 1

6466186 2017-09-01 2065114 MARKAM C 1

6466185 2017-09-10 2065114 MARKAM C 1

6279108 2017-05-26 2065114 MARKAM I 2

6279107 2017-05-26 2065114 MARKAM I 2

6279106 2017-05-26 2065114 MARKAM I 2

6279105 2017-05-26 2065114 MARKAM I 2

6275635 2017-05-26 2065114 MARKAM I 2

6275631 2017-05-26 2065114 MARKAM I 2

6275629 2017-05-26 2065114 MARKAM I 2

6275628 2017-05-26 2065114 MARK I 3

6071559 2017-05-26 2065114 MARKAM I 4

6071556 2017-05-26 2065114 MARKAM I 4

6071554 2017-05-26 2065114 MARKAM I 4

6071553 2017-05-26 2065114 MARKAM I 4

NULL NULL NULL NULL NULL NULL

Your results are :

Logid created_on group_id salesid status1 grouprank

6539123 2017-11-02 2062335 MARKAM D 1

6539122 2017-11-02 2062335 MARKAM D 1

6539121 2017-11-02 2062335 MARKAM D 1

6539120 2017-11-02 2062335 MARKAM D 1

6539119 2017-11-02 2062335 MARKAM D 1

6375134 2017-10-22 2062335 ARREP C 2

6375137 2017-10-12 2062335 ARREP C 3

6375136 2017-10-12 2062335 ARREP C 3

6375135 2017-10-12 2062335 MARKAM D 4

6311587 2017-09-18 2062335 MARKAM D 4

6311586 2017-09-18 2062335 MARKAM D 4

6135939 2017-07-06 2062335 CSDIS I 5

6135938 2017-07-06 2062335 CSDIS I 5

6466185 2017-09-10 2065114 MARKAM C 1

6468804 2017-09-01 2065114 MARKAM C 2

6468803 2017-09-01 2065114 MARKAM C 2

6466186 2017-09-01 2065114 MARKAM C 2

6279108 2017-05-26 2065114 MARKAM I 3

6279107 2017-05-26 2065114 MARKAM I 3

6279106 2017-05-26 2065114 MARKAM I 3

6279105 2017-05-26 2065114 MARKAM I 3

6275635 2017-05-26 2065114 MARKAM I 3

6275631 2017-05-26 2065114 MARKAM I 3

6275629 2017-05-26 2065114 MARKAM I 3

6275628 2017-05-26 2065114 MARK I 4

6071559 2017-05-26 2065114 MARKAM I 5

6071556 2017-05-26 2065114 MARKAM I 5

6071554 2017-05-26 2065114 MARKAM I 5

6071553 2017-05-26 2065114 MARKAM I 5


#11

Funny! When I run the query on the sample data you provided, I get exactly what you wrote you'd expect:

Result
logid    created_on  group_id  salesid  status1  grouprank
6539123  2017-11-02  2062335   MARKAM   D        1
6539122  2017-11-02  2062335   MARKAM   D        1
6539121  2017-11-02  2062335   MARKAM   D        1
6539120  2017-11-02  2062335   MARKAM   D        1
6539119  2017-11-02  2062335   MARKAM   D        1
6375137  2017-10-12  2062335   ARREP    C        2
6375136  2017-10-12  2062335   ARREP    C        2
6375135  2017-10-12  2062335   ARREP    C        2
6375134  2017-10-12  2062335   ARREP    C        2
6311588  2017-09-18  2062335   MARKAM   D        3
6311587  2017-09-18  2062335   MARKAM   D        3
6311586  2017-09-18  2062335   MARKAM   D        3
6135939  2017-07-06  2062335   CSDIS    I        4
6135938  2017-07-06  2062335   CSDIS    I        4
6468804  2017-09-01  2065114   MARKAM   C        1
6468803  2017-09-01  2065114   MARKAM   C        1
6466186  2017-09-01  2065114   MARKAM   C        1
6466185  2017-09-01  2065114   MARKAM   C        1
6279108  2017-05-26  2065114   MARKAM   I        2
6279107  2017-05-26  2065114   MARKAM   I        2
6279106  2017-05-26  2065114   MARKAM   I        2
6279105  2017-05-26  2065114   MARKAM   I        2
6275635  2017-05-26  2065114   MARKAM   I        2
6275631  2017-05-26  2065114   MARKAM   I        2
6275629  2017-05-26  2065114   MARKAM   I        2
6275628  2017-05-26  2065114   MARK     I        3
6071559  2017-05-26  2065114   MARKAM   I        4
6071556  2017-05-26  2065114   MARKAM   I        4
6071554  2017-05-26  2065114   MARKAM   I        4
6071553  2017-05-26  2065114   MARKAM   I        4

#12

Here are results I just got.

You are using Created_On date in your Row_Number and Dense_Rank calculations which are causing this issue. The SalesID and Status1 has not changed for the first 4 records, so they should all have a Rank of 1.


#13

I mean first 5 records.


#14

You have to ignore created_On date. It does not mean anything in these calculations.
Sorry that the records I first sent and that I am using now are a little different, which might be causing this little confusion. The rank should only change if the Group_id, SalesID or Status1 change. If the Group_ID changes, the rank has to reset to 1.


#15

Lets start with these records in the table and work from there.
Run your code against these records.
I am sorry for messing this up. I will only work with these records from now on.

INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539123, N'2017-11-02', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539122, N'2017-11-02', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539121, N'2017-11-03', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539120, N'2017-11-04', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539119, N'2017-11-05', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6375137, N'2017-10-12', 2062335, N'ARREP', N'C', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6375136, N'2017-10-13', 2062335, N'ARREP', N'C', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6375135, N'2017-10-13', 2062335, N'ARREP', N'C', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6375134, N'2017-10-22', 2062335, N'ARREP', N'C', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6311588, N'2017-09-18', 2062335, N'MARKAM', N'D', 3)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6311587, N'2017-09-19', 2062335, N'MARKAM', N'D', 3)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6311586, N'2017-09-20', 2062335, N'MARKAM', N'D', 3)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6135939, N'2017-07-06', 2062335, N'CSDIS', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6135938, N'2017-07-07', 2062335, N'CSDIS', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6468804, N'2017-09-01', 2065114, N'MARKAM', N'C', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6468803, N'2017-09-02', 2065114, N'MARKAM', N'C', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6466186, N'2017-09-03', 2065114, N'MARKAM', N'C', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6466185, N'2017-09-10', 2065114, N'MARKAM', N'C', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6279108, N'2017-05-26', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6279107, N'2017-05-26', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6279106, N'2017-05-26', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6279105, N'2017-05-27', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6275635, N'2017-05-28', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6275631, N'2017-05-29', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6275629, N'2017-05-29', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6275628, N'2017-05-26', 2065114, N'MARK', N'I', 3)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6071559, N'2017-05-26', 2065114, N'MARKAM', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6071556, N'2017-05-26', 2065114, N'MARKAM', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6071554, N'2017-05-27', 2065114, N'MARKAM', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6071553, N'2017-05-26', 2065114, N'MARKAM', N'I', 4)


#16

Sorry again, use these records instead.

These records are in the correct order. Group_ID , LogID DESC,
This will cause the Created_ON to be in DESC order within a GroupID.
But, we are still only ranking if Group_ID, SalesID or Status1 changes.

INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539123, N'2017-11-10', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539122, N'2017-11-09', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539121, N'2017-11-09', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539120, N'2017-11-08', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6539119, N'2017-11-08', 2062335, N'MARKAM', N'D', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6375137, N'2017-10-12', 2062335, N'ARREP', N'C', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6375136, N'2017-10-12', 2062335, N'ARREP', N'C', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6375135, N'2017-10-11', 2062335, N'ARREP', N'C', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6375134, N'2017-10-10', 2062335, N'ARREP', N'C', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6311588, N'2017-09-18', 2062335, N'MARKAM', N'D', 3)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6311587, N'2017-09-17', 2062335, N'MARKAM', N'D', 3)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6311586, N'2017-09-16', 2062335, N'MARKAM', N'D', 3)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6135939, N'2017-07-06', 2062335, N'CSDIS', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6135938, N'2017-07-05', 2062335, N'CSDIS', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6468804, N'2017-09-10', 2065114, N'MARKAM', N'C', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6468803, N'2017-09-09', 2065114, N'MARKAM', N'C', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6466186, N'2017-09-09', 2065114, N'MARKAM', N'C', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6466185, N'2017-09-08', 2065114, N'MARKAM', N'C', 1)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6279108, N'2017-05-26', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6279107, N'2017-05-26', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6279106, N'2017-05-26', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6279105, N'2017-05-25', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6275635, N'2017-05-25', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6275631, N'2017-05-24', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6275629, N'2017-05-23', 2065114, N'MARKAM', N'I', 2)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6275628, N'2017-05-23', 2065114, N'MARK', N'I', 3)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6071559, N'2017-05-22', 2065114, N'MARKAM', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6071556, N'2017-05-22', 2065114, N'MARKAM', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6071554, N'2017-05-21', 2065114, N'MARKAM', N'I', 4)
INSERT [SC_StatusDaysDate] ([LogID], [Created_On], [Group_ID], [SalesID], [Status1], [GroupRank]) VALUES (6071553, N'2017-05-21', 2065114, N'MARKAM', N'I', 4)


#17

How about this:

Query
with cte1
  as (select logid
            ,created_on
            ,group_id
            ,salesid
            ,status1
            ,row_number() over(partition by group_id order by created_on desc,logid desc) as rn
            ,dense_rank() over(partition by group_id order by salesid desc) as rk
        from sc_statusdaysdate
     )
    ,cte2
  as (select a.group_id
            ,a.rn
            ,1 as adjustment
        from cte1 as a
             left outer join cte1 as b
                          on b.group_id=a.group_id
                         and b.salesid=a.salesid
                         and b.status1=a.status1
                         and b.rn=a.rn-1
                         and b.rk=a.rk
       where b.logid is null
     )
select a.logid
      ,a.created_on
      ,a.group_id
      ,a.salesid
      ,a.status1
      ,sum(b.adjustment) as grouprank
  from cte1 as a
       left outer join cte2 as b
                    on b.group_id=a.group_id
                   and b.rn<=a.rn
 group by a.logid
         ,a.created_on
         ,a.group_id
         ,a.salesid
         ,a.status1
         ,a.rk
 order by a.group_id
         ,a.created_on desc
         ,a.logid desc
;

#18

Wow, does exactly what I need for the report. Thank you very much.

One other favor.

Can you create another version of your code that will find the minimum Created_On date of any record ranked number 1 in a Group_ID and insert that Created_On date along with the Group_Id into a table ?

CREATE TABLE [SC_EarliestDate](

        [Group_ID] [int] NULL,

        [Created_On] [varchar](10) NULL,

         ) ON [PRIMARY]

#19

Something like

Query
with cte1
  as (select logid
            ,created_on
            ,group_id
            ,salesid
            ,status1
            ,row_number() over(partition by group_id order by created_on desc,logid desc) as rn
            ,dense_rank() over(partition by group_id order by salesid desc) as rk
        from sc_statusdaysdate
     )
    ,cte2
  as (select a.group_id
            ,a.rn
            ,1 as adjustment
        from cte1 as a
             left outer join cte1 as b
                          on b.group_id=a.group_id
                         and b.salesid=a.salesid
                         and b.status1=a.status1
                         and b.rn=a.rn-1
                         and b.rk=a.rk
       where b.logid is null
     )
insert into sc_earliestdate(group_id,created_on)
select top(1) with ties
       group_id
      ,created_on
  from (select a.logid
              ,a.created_on
              ,a.group_id
              ,a.salesid
              ,a.status1
              ,sum(b.adjustment) as grouprank
          from cte1 as a
               left outer join cte2 as b
                            on b.group_id=a.group_id
                           and b.rn<=a.rn
         group by a.logid
                 ,a.created_on
                 ,a.group_id
                 ,a.salesid
                 ,a.status1
                 ,a.rk
       ) as a
 order by row_number() over(partition by group_id
                                order by grouprank
                                        ,created_on
                           )
;

#20

The new code to insert into the table is working fine. Will do some testing and get back to you.

Thank you again.