Hi!
How I can get only define money by pay parameter?
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 (16, 1500, N'2019/20', N'A0002', 3, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (17, 1500, N'2019/20', N'A0003', 3, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (18, 2000, N'2019/20', N'A0003', 3, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (19, 2000, N'2019/20', N'A0002', 3, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (20, 2000, N'2019/20', N'A0001', 3, 0)
INSERT INTO @cash (Id, smoney, syear, std, course, pay) VALUES (21, 800, N'2019/20', N'A0003', 3, 1)
select case pay when 0 then sum(smoney) end'money', syear, std, course
from @cash
WHERE syear = (SELECT MAX(syear) FROM @cash)
group by syear, std, course, pay
I want below result:
|
money
|
syear
|
course
|
std
|
|
3500,00
|
2019/20
|
3
|
A0001
|
|
3500,00
|
2019/20
|
3
|
A0002
|
|
3500,00
|
2019/20
|
3
|
A0003
|
