Last post Sep 19, 2018 02:40 PM by limno
Sep 19, 2018 02:00 PM|ctee|LINK
I am trying to get the count for the attempt to pass the registered subject. The attempt will count according to Subject and Category type. Every exam will increase the count +1.
However, if the category is Assignment, no count will be counted for this. Hence, the attempt will follow the last attempt. Please refer to the sample expected result.
Anyone can guide me how to write the SQL Select query.
Sep 19, 2018 02:14 PM|ryanbesko|LINK
You show us the expected result. How about the data used to get the expected result?
Sep 19, 2018 02:20 PM|ctee|LINK
Below is the data,
Sep 19, 2018 02:40 PM|limno|LINK
Try to post your question with script for TSQL question.
CREATE TABLE mytable(
SubjectCode INTEGER NOT NULL
,Subject VARCHAR(7) NOT NULL
,CategoryID INTEGER NOT NULL
,Category VARCHAR(10) NOT NULL
,Updated_Date DATE NOT NULL
INSERT INTO mytable(SubjectCode,Subject,CategoryID,Category,Updated_Date) VALUES
select Subject,Category, sum(case when Category='Assignment' then 0 else 1 end) Over(Partition by Subject Order by Updated_Date ) Attempt ,
Updated_Date from mytable
order by Updated_Date, Category desc
drop table mytable