April 26, 2023 at 8:44 pm
In my sql result, I want to create a column called RCount.
Condition: For each RecNo, AccNo
* If the field Reason is "Accepted", it doesn't matter what other records contain for a specific RecNo, AccNo. It should return 1 for the very first instance of Accepted and ignore the rest.
* If more than one reason is filled and one got Accepted, return 1 only for the first record that contains "Accepted". Ignore the rest
* If there are no "Accepted" records, return 1 for the first row that has "Rejected". Ignore the rest
*** "Accepted" always takes precedence over "Rejected".
I wrote the following code to get the required result and it is working well. Since the code is a bit long, I was wondering whether there is an alternate way to get the required result.
Your comments, suggestions are greatly appreciated.
CREATE TABLE #t1([RecNo] [varchar](10) NULL, [AccNo] [varchar](10) NULL, [OrdDate] [datetime] NOT NULL,[Reason] [varchar](10) NULL
)
ON [PRIMARY]
insert into #t1 values('B111','AC100','2022-01-10 13:37:06.000','Rejected')
insert into #t1 values('B111','AC100','2022-01-10 13:37:06.000','Rejected')
insert into #t1 values('B111','AC100','2022-01-10 13:37:06.000','Accepted')
insert into #t1 values('B111','AC200','2022-01-10 13:37:06.000','Accepted')
insert into #t1 values('B222','AC123','2022-01-10 13:37:06.000','Rejected')
insert into #t1 values('B222','AC123','2022-01-10 13:37:06.000','Rejected')
insert into #t1 values('B222','AC123','2022-01-10 13:37:06.000','Rejected')
insert into #t1 values('B333','AC300','2022-01-10 13:37:06.000','Rejected')
insert into #t1 values('B333','AC300','2022-01-10 13:37:06.000','Accepted')
insert into #t1 values('B444','AC400','2022-01-10 13:37:06.000','Accepted')
insert into #t1 values('B555','AC500','2022-01-10 13:37:06.000','Accepted')
insert into #t1 values('B555','AC500','2022-01-10 13:37:06.000','NA')
insert into #t1 values('B666','AC600','2022-01-10 13:37:06.000','NA')
insert into #t1 values('B777','AC700','2022-01-10 13:37:06.000','NA')
insert into #t1 values('B777','AC700','2022-01-10 13:37:06.000','Rejected')
insert into #t1 values('B777','AC700','2022-01-10 13:37:06.000','Accepted')
insert into #t1 values('B888','AC800','2022-01-10 13:37:06.000','Accepted')
insert into #t1 values('B12345','AC800','2022-01-10 13:37:06.000','Rejected')
insert into #t1 values('B567','AC900','2022-01-10 13:37:06.000','NA')
insert into #t1 values('B567','AC900','2022-01-10 13:37:06.000','Rejected')
insert into #t1 values('B567','AC900','2022-01-10 13:37:06.000','Rejected')
insert into #t1 values('B787','AC900','2022-01-10 13:37:06.000','NA')
insert into #t1 values('B787','AC900','2022-01-10 13:37:06.000','Accepted')
insert into #t1 values('B787','AC900','2022-01-10 13:37:06.000','Accepted')
select *,
CASE When ROW_NUMBER() OVER(PARTITION BY R1.[RecNo],R1.[AccNo],R1.[Reason]
ORDER BY R1.[RecNo], R1.[AccNo],R1.[Reason])=1
and
(Select count(t2.[RecNo]) from #t1 t2 where t2.[RecNo]= R1.[RecNo]
and t2.[AccNo] = R1.[AccNo])>1
Then
Case When R1.[Reason] = 'Accepted'
then
1
else
Case When R1.[Reason] = 'Rejected'
and
(Select count(t2.[RecNo]) from #t1 t2 where t2.[RecNo]= R1.[RecNo]
and t2.[AccNo] = R1.[AccNo] and t2.[Reason] = 'Accepted'
)=0
Then 1
Else
0
End
end
ELSE
CASE When ROW_NUMBER() OVER(PARTITION BY R1.[RecNo],R1.[AccNo],R1.[Reason]
ORDER BY R1.[RecNo], R1.[AccNo],R1.[Reason])=1
and
(Select count(t2.[RecNo]) from #t1 t2 where t2.[RecNo]= R1.[RecNo]
and t2.[AccNo] = R1.[AccNo])>1 and [Reason] = 'Rejected'
Then 1
Else
Case When
ROW_NUMBER() OVER(PARTITION BY R1.[RecNo],R1.[AccNo],R1.[Reason]
ORDER BY R1.[RecNo], R1.[AccNo],R1.[Reason])=1
and R1.[Reason] in ('Rejected','Accepted')
Then 1 Else 0 End
End
END as RCount
from #t1 R1 order by RecNo, AccNo
drop table #t1
April 26, 2023 at 10:11 pm
This produces the exact same results as your query. One of the tricks to efficiently using CASE expressions is to remember that you do not have to test for conditions that MUST BE TRUE. So in my query if I know that Reason_priority <> 3, I know that the reason MUST be either 'Accepted' or 'Rejected' and I don't need to test those conditions.
SELECT RecNo
, AccNo
, OrdDate
, Reason
, CASE WHEN Reason_priority = 3 THEN 0
WHEN ROW_NUMBER() OVER(PARTITION BY RecNo, AccNo ORDER BY p.reason_priority, OrdDate) = 1 THEN 1
ELSE 0
END AS RCount
FROM #t1
CROSS APPLY ( VALUES (CASE WHEN Reason = 'Accepted' THEN 1 WHEN Reason = 'Rejected' THEN 2 ELSE 3 END) ) p(reason_priority)
ORDER BY RecNo, AccNo, Reason, RCount DESC
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 27, 2023 at 12:19 am
Here is a K.I.S.S. option. Not sure which is faster but if you are storing the results into a table you can replace the temp-table with the real table. Also going back to the business criterion here is a more simplified definition and putting it in the CASE WHEN structure to show how that can be used to reflect the IF ELSE-IF ELSE aspect
CASE WHEN Reason = 'Accepted'
THEN RecordCount = 1 FOR ALL THOSE RecordIds
WHEN Reason = 'Rejected'
THEN RecordCount = 1 FOR ALL THOSE RecordIds
ELSE RecordCount = 0 FOR ALL THOSE RecordIds
END
Or course logically what the above boils down to is simply:
IF Reason = 'Accepted" OR 'Rejected'
THEN RecordStatus = 1
ELSE RecordStatus = 0
From the business logic you presented it does not matter if it was Accepted or Rejected as it gets the same Status regardless. So you could alter the logic to just go through and find the first Accepted or Rejected and be done with it. However what I present does cover the flow of Accepted first, Rejected but only if not Accepted, and whatever otherwise.
DROP TABLE IF EXISTS #tbRecordStatus
CREATE TABLE #tbRecordStatus
( [RecordId] VARCHAR(10) NOT NULL
,[AccountId] VARCHAR(10) NULL
,[OrderDate] DATETIME2(3) NULL
,[Reason] VARCHAR(10) NULL
,[AcptdRjctd] BIT
)
ON [PRIMARY]
CREATE CLUSTERED INDEX IX_RS_RecordId
ON #tbRecordStatus ( [RecordId] );
INSERT INTO #tbRecordStatus
SELECT [ar].[RecordId]
,[ar].[AccountId]
,MAX( [OrderDate] )
,'Accepted' AS [Reason]
, 1 AS [AcceptedRejected]
FROM #tbAccptRjct AS [ar]
WHERE [ar].[Reason] = 'Accepted'
GROUP BY [ar].[RecordId], [ar].[AccountId]
INSERT INTO #tbRecordStatus
SELECT [ar].[RecordId]
,[ar].[AccountId]
,MAX( [OrderDate] )
,'Rejected' AS [Reason]
, 1 AS [AcceptedRejected]
FROM #tbAccptRjct AS [ar]
WHERE [ar].[Reason] = 'Rejected'
AND [ar].[RecordId] NOT IN ( SELECT [RecordId]
FROM #tbRecordStatus
)
GROUP BY [ar].[RecordId], [ar].[AccountId]
INSERT INTO #tbRecordStatus
SELECT [ar].[RecordId]
,[ar].[AccountId]
,MAX( [OrderDate] )
,'NA' AS [Reason]
, 1 AS [AcceptedRejected]
FROM #tbAccptRjct AS [ar]
WHERE [ar].[RecordId] NOT IN ( SELECT [RecordId]
FROM #tbRecordStatus
)
GROUP BY [ar].[RecordId], [ar].[AccountId]
SELECT *
FROM #tbRecordStatus
April 27, 2023 at 8:40 am
In my sql result, I want to create a column called RCount. Condition: For each RecNo, AccNo * If the field Reason is "Accepted", it doesn't matter what other records contain for a specific RecNo, AccNo. It should return 1 for the very first instance of Accepted and ignore the rest.
* If more than one reason is filled and one got Accepted, return 1 only for the first record that contains "Accepted". Ignore the rest
* If there are no "Accepted" records, return 1 for the first row that has "Rejected". Ignore the rest
*** "Accepted" always takes precedence over "Rejected".
You refer to 'first' and 'rest', yet there is no way I can see of ordering your test data. Should there be another column which determines row order?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 29, 2023 at 12:30 pm
Phil Parkin from what I gathered they did not care about the order just the first record that has Accepted or Rejected means that the RecordId gets a 1 otherwise if there are no Accepted/Rejected records for the RecordId it gets a 0.
April 29, 2023 at 2:18 pm
Phil Parkin from what I gathered they did not care about the order just the first record that has Accepted or Rejected means that the RecordId gets a 1 otherwise if there are no Accepted/Rejected records for the RecordId it gets a 0.
Please reread this
* If more than one reason is filled and one got Accepted, return 1 only for the first record that contains "Accepted". Ignore the rest
It doesn't matter how many times I read it, the word 'first' here suggests ordering. If seven rows are Accepted, which one is 'first'? If ordering is not important (as you are suggesting), the question should have been worded more clearly.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 3, 2023 at 5:20 pm
Unfortunately, there is no sort order. This is a 3rd party database. I don't have control of how they save data. Script should go through the data and take the first instance it finds and do the formatting accordingly. Order doesn't matter. So far provided answers work. Thanks for all replies.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply