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.
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
1 Like
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)
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.
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
;
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,
'''',
'''''') +
''')'
Did you try the query I provided?
As you can tell, I am new to this website.
That being said, where do I find the query that you provided ?
Click on the arrow where it reads "Query"
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
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
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.
I mean first 5 records.
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.
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)
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)
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
;
1 Like
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]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
)
;
1 Like
The new code to insert into the table is working fine. Will do some testing and get back to you.
Thank you again.