Get records based on parameter value in SQL Server

Last Reply 3 months ago By dharmendr

Posted 3 months ago

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

Posted 3 months ago

Hi PRA,

Add pay parameter to where condition.

Refer below query.

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, course, std 
from @cash
WHERE syear = (SELECT MAX(syear) FROM @cash) AND pay = 0
group by syear, std, course, pay

Output

money

syear

course

std

3500.00

2019/20

3

A0001

3500.00

2019/20

3

A0002

3500.00

2019/20

3

A0003