Hi!
How to get max years and min one number
I used below script its working when all std has max syear. When one or two std has max syear then not shows others.
DECLARE @cash TABLE (Id int, smoney money, syear char(7), std char(5), course int, pay int)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (1, 2000, N'2017/18', N'A0001', 1, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (2, 1000, N'2017/18', N'A0001', 1, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (3, 1000, N'2017/18', N'A0002', 1, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (4, 1000, N'2017/18', N'A0002', 1, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (5, 1000, N'2017/18', N'A0003', 1, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (6, 2000, N'2017/18', N'A0003', 1, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (7, 500, N'2017/18', N'A0001', 1, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (8, 500, N'2017/18', N'A0002', 1, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (9, 500, N'2018/19', N'A0002', 2, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (10, 500, N'2018/19', N'A0001', 2, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (11, 500, N'2018/19', N'A0003', 2, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (12, 2500, N'2018/19', N'A0003', 2, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (13, 2500, N'2018/19', N'A0002', 2, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (14, 2500, N'2018/19', N'A0001', 2, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (15, 1500, N'2019/20', N'A0001', 3, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (20, 2000, N'2019/20', N'A0001', 3, 0)
select case pay when 0 then sum(smoney) end'money', syear, course, std
from @cash
WHERE syear = (SELECT MAX(syear) FROM @cash) AND pay = 0
group by syear, std, course, pay
I want this result:
|
money
|
syear
|
course
|
std
|
|
3000,00
|
2018/19
|
2
|
A0002
|
|
3000,00
|
2018/19
|
2
|
A0003
|
|
3500,00
|
2019/20
|
3
|
A0001
|
