SQLTeam.com | Weblogs | Forums

Extract schedule with crossed reference


#1

Good Morning,
I’m not much expert to query sql and so I’ve a question:
I have to extract for my clients the active commissions payed, passive commissions payed, the number of closed operations and the margin. In add I have to evidence for every client the contained letter in another schedule (wctdd) in tiprem8 area. In this schedule, another, there is the area codcli also (I’m not expand this part yet).
At moment this is my work: how problems I can evidence that is possible to extract the passive commissions with a research of the same reference number of active commission and connect it to its client. I attach a excel paper with the schedule.
In add, I have to another problem in referement to this: for the calculation of operations if tipoope is different to CVC I must to add the amount that I research in “quanti” and not calculate the only operation how I’ ve done for the CVC.
I hope have been clear to explanation the problem.

select M2.CODCLI, sum( M2.CTVCNO) AS Comm_att, sum(M.CTVCAL) AS comm_pass, sum( M2.CTVCNO) - sum(M.CTVCAL) AS margine, count(M2.ctvcno) as eseguiti

from mov M JOIN mov M2 ON M.N_ORD=M2.N_ORD AND M2.CTVCNO>0 and M2.tipocp='P'
where M.datope= to_date('01-06-2017','DD-MM-YYYY') AND M.CTVCAL>0 and m.tipocp='C' and M.N_ord is not null and M2.datope= to_date('01-06-2017','DD-MM-YYYY')
GROUP BY M2.CODCLI
ORDER BY M2.CODCLI


#2

i have to solve my problem early, because i have fast delivery time and i would understand if i can use this metod.

Thanks.


#3

If you would like responses[ please post DDL in the form of create table](http://www.sqlservercentral.com/articles/Best+Practices/61537/) or declare a table variable. Include insert statements of sample data. Post what you have tried and what result you would like.


#4

i have an excel file but. How can i upload it? The system don't allow upload excel file.

Thanks.


#5

Excel file no use to us. Please post an example in the format @jotorre_riversidedpss.org asked for - a worked example with Create Table, some Insert data, and ideally the expected results and then anyone here can just use your Sandpit to work up a TESTED solution for you.

Otherwise we would each have to duplicate that work to create test data and ... in my experience ... people don't have the time, or feel that if you can't be bothered why should they.


#6

An Excel file isn't what I'm talking about. Did you follow the link? This website will convert CSV or Excel to insert statements.


#7


#8

Can't be ar$ed presumably


#9

something like this Michael, fill in the rest. as you can see it will take a lot of work for someone else to do all of this for you.

create table #mov(N_ORD int, DATOPE date, TIPOPE varchar(10), 
TIPOCP varchar(10), CODCLI int, QUANTI int, CTVAL int, CTVCNO int);

insert into #mov
select 8835201, '02/06/2016', 'CVC', 'P', 235, 12, 0 , 2