Hi
How to find who have paid amount greaterthan 50 any one alter this query.
i mention below my query
exec sp_getamt '2015-02-02','2017-09-27',50
create proc sp_getamt(@fromdate date , @todate date,@amt numeric(18,2))
as
begin
select a.empid,a.empno,empname ,
(select sum(onamt) from [onlineamt] a1 inner join employees b1 on a1.empno=b1.empno where ondate between '2015-02-02' and '2017-09-27') onlineamt,
(select sum(ofamt) from oflineamt a1 inner join employees b1 on a1.empno=b1.empno where ofdate between '2015-02-02' and '2017-09-27') offamt,
(select sum(ddamt) from ddlineamt a1 inner join employees b1 on a1.empno=b1.empno where dddate between '2015-02-02' and '2017-09-27') ddamtt,
(select sum(cheamt) from chelineamt a1 inner join employees b1 on a1.empno=b1.empno where chedate between '2015-02-02' and '2017-09-27') cheamtt
from employees a inner join [onlineamt] b on b.empno=a.empno
inner join oflineamt c on c.empno=a.empno
inner join ddlineamt d on d.empno=a.empno
left join chelineamt f on f.empno=a.empno
group by a.empid,a.empno,empname
having (onlineamt+offamt+ddamtt+cheamtt)>50
end
--select sum(onamt) from [onlineamt] a1 inner join employees b1 on a1.empno=b1.empno where ondate between '2015-02-02' and '2017-09-27'
select * from [dbo].[chelineamt]
select * from [dbo].[onlineamt]
select * from [dbo].[oflineamt]
select * from [dbo].[ddlineamt]
select * from [dbo].[employees]
create table employees
(
Empid int primary key identity(1,1),
empno int,
empname nvarchar(100)
)
insert into employees values(100,'John')
insert into employees values(102,'Mathew')
insert into employees values(103,'Hyden')
insert into employees values(104,'Jones')
insert into employees values(105,'Kapil')
insert into employees values(106,'Dhoni')
insert into employees values(107,'Revex')
insert into employees values(108,'saloni')
insert into employees values(109,'pratap')
insert into employees values(110,'John wesly')
GO
create table onlineamt
(
onid int primary key identity(1,1),
empno int,
ondate datetime,
onamt numeric(18,2)
)
--truncate table onlineamt
insert into onlineamt values(100,'2015-02-08',100)
insert into onlineamt values(101,'2015-02-09',200)
insert into onlineamt values(102,'2015-02-11',300)
insert into onlineamt values(103,'2015-02-12',400)
insert into onlineamt values(104,'2015-02-07',500)
insert into onlineamt values(100,'2015-02-08',600)
insert into onlineamt values(100,'2015-02-08',700)
insert into onlineamt values(105,'2017-02-08',800)
insert into onlineamt values(100,'2017-02-08',2000)
create table oflineamt
(
ofid int primary key identity(1,1),
empno int,
ofdate datetime,
ofamt numeric(18,2)
)
--truncate table oflineamt
insert into oflineamt values(100,'2015-02-08',101)
insert into oflineamt values(101,'2015-02-09',102)
insert into oflineamt values(102,'2016-02-11',103)
insert into oflineamt values(103,'2016-02-12',104)
insert into oflineamt values(104,'2016-02-07',105)
insert into oflineamt values(100,'2015-02-08',106)
insert into oflineamt values(100,'2015-02-08',107)
insert into oflineamt values(105,'2017-02-08',108)
insert into oflineamt values(100,'2017-02-08',2010)
create table ddlineamt
(
ddid int primary key identity(1,1),
empno int,
dddate datetime,
ddamt numeric(18,2)
)
--truncate table ddlineamt
insert into ddlineamt values(100,'2015-02-08',1100)
insert into ddlineamt values(101,'2015-02-09',2100)
insert into ddlineamt values(102,'2016-02-11',3100)
insert into ddlineamt values(103,'2016-02-12',110)
insert into ddlineamt values(104,'2016
Mark as Answer if its helpful to you
Kumaraspcode2009@gmail.com