How to resolve this Query

Posted by Jayakumars under Sql Server on 9/26/2017 | Points: 10 | Views : 297 | Status : [Member] [MVP] | Replies : 1
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


Responses

Posted by: Pavanandey on: 9/28/2017 [Member] Bronze | Points: 25

Up
0
Down
Try this one

select Empid,Empname from employees where empno in
(

select unique a.empno from onlineamt a, oflineamt b, ddlineamt c where a.empno = b.empno and a.empno =c.empno
where a.onamt + b.onamt + c.onamt > 50 and a.ondate between ("dates") and b.ofdate between ("dates") and c.dddate between ("dates")
)

Thanks
Pavan Kumar
Mark Answer if this fits the need

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response