Query problem ! - 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)

Query problem !

Author

Topic

timolein
Starting Member

1 Post

Posted - 2007-08-10 : 03:54:58

How can i get 3 months sales calculated to one column ?

My clause brings me 4 columns:

Atkno = period
Atyks = unit
Atnet = sales
Tukoodi = product code

But i need to get 3 months period on one column, so that i have one product code row and 3 month sales calulated to same row.

SELECT Atmtil_0.Atkno, Atmtil_0.Atyks, Atmtil_0.Atnet, Tuote_0.Tukoodi 
FROM PUB.Atmtil Atmtil_0, PUB.Tuote Tuote_0 
WHERE Atmtil_0.Asnumero = Tuote_0.Asnumero AND Atmtil_0.Tukoodi = Tuote_0.Tukoodi 
AND ((Atmtil_0.Atkno Between 200704 And 200706))

Result:


Atkno  Atyks  Atnet  Tukoodi
200704    2      5     10013
200705    2      5     10013
200706    1      3     10013

What it should be is:


Atkno        Atyks  Atnet  Tukoodi
200704/05/06    5      13     10013

Br,
Timo

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2007-08-10 : 04:11:37

something like this maybe:

--**************************************************************************

SELECT STR(MIN(Atmtil_0.Atkno),6) + ' - ' + STR(MAX(Atmtil_0.Atkno),6), SUM(Atmtil_0.Atyks) AS Atyks, SUM(Atmtil_0.Atnet) AS Atnet, Tuote_0.Tukoodi
FROM PUB.Atmtil Atmtil_0, PUB.Tuote Tuote_0
WHERE Atmtil_0.Asnumero = Tuote_0.Asnumero AND Atmtil_0.Tukoodi = Tuote_0.Tukoodi
AND ((Atmtil_0.Atkno Between 200704 And 200706))
GROUP BY Tuote_0.Tukoodi

Duane.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-08-10 : 12:19:39

You might be able to eliminate the correlated sub query, but maybe something like this:

SELECT 
	MIN(Atmtil_0.Atkno) + '/' + RIGHT(SELECT MIN(Atkno) FROM PUB.Atmtil WHERE Atkno > MIN(Atmtil_0.Atkno), 2) + '/' + RIGHT(MAX(Atmtil_0.Atkno), 2) AS Atkno,
	SUM(Atmtil_0.Atyks) AS Atyks, 
	SUM(Atmtil_0.Atnet) AS Atnet, 
	Tuote_0.Tukoodi 
FROM 
	PUB.Atmtil Atmtil_0
INNER JOIN
	PUB.Tuote Tuote_0 
	ON Atmtil_0.Asnumero = Tuote_0.Asnumero 
	AND Atmtil_0.Tukoodi = Tuote_0.Tukoodi 
WHERE 
	Atmtil_0.Atkno Between 200704 And 200706
GROUP BY
	Tuote_0.Tukoodi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources