Problem with query :/ - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Problem with query :/

Author

Topic

trallador
Starting Member

13 Posts

Posted - 2009-06-18 : 03:51:17

Hi guys,
First of all sorry for my english.

I have a problem with a query in which I wanna put in 4 colums the sum fields. The separate querys:



select cliente, sum(importe) from comunes!previ_cl where banco="	" AND vencim between (emision) AND (emision+29) group by cliente
select cliente, sum(importe) from comunes!previ_cl where banco="	" AND vencim between (emision+30) AND (emision+60) group by cliente
select cliente, sum(importe) from comunes!previ_cl where banco="	" AND vencim between (emision+61) AND (emision+90) group by cliente
select cliente, sum(importe) from comunes!previ_cl where banco="	" AND vencim > (emision+90) group by cliente

My last try using aditional table (clients):



SELECT c.codigo, c.nombre, SUM(t1.importe) AS 'SUMA29', SUM(t2.importe) AS 'SUMA30', SUM(t3.importe) AS 'SUMA60', SUM(t4.importe) AS 'SUMA91' 
FROM gestion!clientes c LEFT JOIN comunes!previ_cl t1 ON c.codigo=t1.cliente AND t1.banco=" 	" AND t1.vencim between (t1.emision) AND (t1.emision+29) 
LEFT JOIN comunes!previ_cl t2 ON c.codigo=t2.cliente AND t2.banco=" 	" AND t2.vencim between (t2.emision+30) AND (t2.emision+60)
LEFT JOIN comunes!previ_cl t3 ON c.codigo=t3.cliente AND t3.banco=" 	" AND t3.vencim between (t3.emision+61) AND (t3.emision+90)
LEFT JOIN comunes!previ_cl t4 ON c.codigo=t4.cliente AND t4.banco=" 	" AND t4.vencim > (t4.emision+90)
WHERE c.codigo IN (SELECT cliente FROM comunes!previ_cl WHERE banco="	")
GROUP BY c.codigo

But the result is wrong, sum colums duplicates if any client have 1 or more importe in diferent sum colums. Anyone can help me?

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-18 : 03:55:08

[code]SELECT Cliente,
SUM(CASE WHEN Vencim BETWEEN Emision AND Emision + 29 THEN Importe ELSE 0 END),
SUM(CASE WHEN Vencim BETWEEN Emision + 30 AND Emision + 60 THEN Importe ELSE 0 END),
SUM(CASE WHEN Vencim BETWEEN Emision + 61 AND Emision + 90 THEN Importe ELSE 0 END),
SUM(CASE WHEN Vencim >= Emision + 91 THEN Importe ELSE 0 END)
FROM [comunes!previ_cl]
WHERE Banco = ' '
GROUP BY Cliente[/code]

E 12°55'05.63"
N 56°04'39.26"

trallador
Starting Member

13 Posts

Posted - 2009-06-18 : 05:08:21

Thx for fast reply Peso but i have a small problem with this.
The query show no results. If i remove < WHERE banco = ' ' > shows results.

I try that but it does not work (0 results too):



SELECT	Cliente,
		SUM(CASE WHEN Banco='	' AND Vencim BETWEEN Emision AND Emision + 29 THEN Importe ELSE 0 END) AS 'SUM29',
		SUM(CASE WHEN Banco='	' AND Vencim BETWEEN Emision + 30 AND Emision + 60 THEN Importe ELSE 0 END) AS 'SUM60',
		SUM(CASE WHEN Banco='	' AND Vencim BETWEEN Emision + 61 AND Emision + 90 THEN Importe ELSE 0 END) AS 'SUM90',
		SUM(CASE WHEN Banco='	' AND Vencim >= Emision + 91 THEN Importe ELSE 0 END) AS 'SUM91'
FROM		[comunes!previ_cl]
GROUP BY	Cliente

Thx for all

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-18 : 05:27:41

Sorry to bother...what is [comunes!previ_cl] means? i google it nothing come out...

trallador
Starting Member

13 Posts

Posted - 2009-06-18 : 05:32:13

Do not worry. That is because the query is to use another program (programated in visual fox pro with a SQLSERVER2005 bd background, not for me), not directly in the sql

DB = comunes
TABLE = previ_cl
! = separator

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-18 : 05:38:19

I can't tell what the Banco should be equal to.
Is it 8 spaces? 2 tab characters?

You have to edit the WHERE part and put the correct value for Banco.

SELECT		Cliente,
		SUM(CASE WHEN Vencim BETWEEN Emision AND Emision + 29 THEN Importe ELSE 0 END),
		SUM(CASE WHEN Vencim BETWEEN Emision + 30 AND Emision + 60 THEN Importe ELSE 0 END),
		SUM(CASE WHEN Vencim BETWEEN Emision + 61 AND Emision + 90 THEN Importe ELSE 0 END),
		SUM(CASE WHEN Vencim >= Emision + 91 THEN Importe ELSE 0 END)
FROM		comunes!previ_cl
WHERE		Banco = ''
GROUP BY	Cliente

E 12°55'05.63"
N 56°04'39.26"

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-18 : 05:46:08

Thx alot for the information! ^^

trallador
Starting Member

13 Posts

Posted - 2009-06-18 : 05:54:05

Lol that's f****g truth. Just use the tab key to better appreciate. fail...BIG THX FOR UR TIME :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-18 : 06:03:08