How to get sum of amount of all databases together through cursor

I am stucking in one place in sql query. I have 20 databases and all database's table name and columns name are same but data is different. I am creating a sql query to get payment amount of particular date from all databases together.

I have many columns in payment table. I need on sum(amount) regarding there paymentTypeID column because I can get amount on the basis of paymentTypeID. All paymentTypeIDs are different in databases but sometimes it is same. I have created a cursor for this.

My code is:

 create table #tmpDBs(DBName varchar(255), CurrentDayPaymentAmount float)

 declare cDBs cursor for

 select name from master.dbo.sysdatabases

 declare @DB varchar(255)

 open cDBs
 fetch next from cDBs into @DB
 while @@fetch_status = 0
 begin
 exec('

 declare @DateCurrent datetime

 set @DateCurrent = 开发者_高级运维'{Current Date}'

 insert into #tmpDBs

 select DBName = ' + @DB + ',

 CurrentDayPaymentAmount = (select sum(p.amount) from ' + @DB + '.dbo.Payment p     where     p.eDate between @DateCurrent and dateadd(day, 1, @DateCurrent) and     (p.paymenttypeid in (14, 15, 16, 17, 21, 22, 24, 35, 37, 38, 50)))

 ')
 fetch next from cDBs into @DB 
 end 
 close cDBs
 deallocate cDBs
 select * from #tmpDBs
 drop table #tmpDBs

I used In clause to pass all databases paymenttypeid together. This code is for retrieve the payment amount from 2 databases. paymenttypeid 14, 15, 16,17, 22, 24, 38 is for first database and 21, 35, 37, 50 is for second database. My problem is that when I put these paymentTypeID in In clause it adds the amount of matching paymenttypeid of other database. For example if first database actual amount is 4589$ but it adds additional amount say 5469$ by using second database paymenttypeid. If I pass only one database paymenttypeid it shows the correct amount.

What is the issue in my query.

---

I think you explained the issue yourself: you want only types 1, 2 and 3 from DB1 and only types 4, 5 and 6 from DB2, but your query doesn't do that, it asks for types 1, 2, 3, 4, 5 and 6 from DB1 and DB2.

That means you need to use a different WHERE clause for each databases, depending on which types you want. So how do you know which types you want from each database and how can you express that logic in your stored procedure?

As a completely different solution, create a view in each database with the correct WHERE clause and then just query the view for your report.