Select with multiple derivative tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select with multiple derivative tables

Author

Topic

drtduarte
Starting Member

12 Posts

Posted - 2010-12-16 : 08:53:52

Hello friends,

I'm having problems to achieve the needed result with one sql querie.

Here you have what I have:

SELECT pn.vendnm as vendedor, YEAR(fdata) as year, month(fdata) as month,
pn.ecomissao, vendas, eini1, efim1,
SUM(CASE WHEN (vendas BETWEEN eini1 and efim1) THEN etiliquido*com1/100 WHEN (vendas BETWEEN eini2 and efim2)
THEN etiliquido*com2/100 WHEN (vendas BETWEEN eini3 and efim3)
THEN etiliquido*com3/100 WHEN (vendas BETWEEN eini4 and efim4)
THEN etiliquido*com4/100 WHEN (vendas BETWEEN eini5 and efim5)
THEN etiliquido*com5/100 WHEN (vendas BETWEEN eini6 and efim6)
THEN etiliquido*com6/100 WHEN (vendas BETWEEN eini7 and efim7)
THEN etiliquido*com7/100 WHEN (vendas BETWEEN eini8 and efim8)
THEN etiliquido*com8/100 else 0 end) as comissao
FROM(SELECT pn.vendnm as vendedor, YEAR(fdata) as year, month(fdata) as month,
ecomissao,
SUM(CASE WHEN (month(pn.fdata)=9 and year(pn.fdata)>=2010) THEN ETILIQUIDO else 0 end ) AS 'vendas',
eini1,efim1,com1,eini2,efim2,com2,eini3,efim3,com3,eini4,efim4,com4,eini5,efim5,com5,eini6,efim6,
com6,eini7,efim7,com7,eini8,efim8,com8
FROM pn (nolock)
INNER JOIN
ECOM
ON (year(fdata)=2010 and month(fdata)=09 and pn.vendnm='Manuel Coelho' and ecom=ecomissao)
GROUP BY pn.vendnm, YEAR(fdata), month(fdata), ecomissao, eini1,
efim1,com1,eini2,efim2,com2,eini3,efim3,com3,eini4,efim4,com4,eini5,efim5,com5,eini6,efim6,
com6,eini7,efim7,com7,eini8,efim8,com8) dt_pn
INNER JOIN
pn
on (year(fdata)=2010 and month(fdata)=09 and pn.vendnm='Manuel Coelho' and dt_pn.ecomissao=pn.ecomissao)

After this I will have to group by month and ecomissao the total of comissao

I need to finish this very fast, and I'm really lost :(

If somebody can help me It will be great...

Thanks
David

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources