Can this be done with SQl?? - 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 2000 Forums

Transact-SQL (2000)

Can this be done with SQl??

Author

Topic

rtr1900
Starting Member

48 Posts

Posted - 2008-05-05 : 10:22:11

Hi,

I have just programmed some code in ASP, using QL syntaxes. But I wa wondering if this can be done directly in SQL. Let me expain.

I have in a table all the product of the company, I will only need the Code of the Article.
The second table has mulyiple lines of that cpdo of article with different data.
I need to take the latest 3 of the article and do a calculation which gives me the average of it.

Let megive you an example

Table 1:

CODE ART
2500

table 2:
CODE ART DESCRIPTION CODELAB DATE UNITS DISCOUNT
2500 PRODUCT 1 255 01/05/2008 50 5
2500 PRODUCT 1 255 28/04/2008 20 4
2500 PRODUCT 1 255 27/04/2008 50 6
2500 PRODUCT 1 255 24/04/2008 60 5

The result will be
CODE ART DESCRIPTION CODELAB TOTUNITS AVG DISCOUNT
2500 PRODUCT 1 255 120 5

etc....

I have done it in ASP, a bucle inside a bucle. The first bucles is the list of all the artciles, and then the second, searches the second table fr the article of the first table and doin gthe calculations of the last 3 sales of it.

Can this be done in 1 SQL syntax?

Thx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-05 : 10:24:32

Yes.

Use an aggregated function named SUM and use GROUP BY to show by which columns you need to aggregate.

E 12°55'05.25"
N 56°04'39.16"

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-05 : 10:33:55

How's this?

select [CODE ART], DESCRIPTION, CODELAB, sum(UNITS) as TOTUNITS, Avg(DISCOUNT) as [AVG DISCOUNT]
from
(select top 3 [CODE ART], DESCRIPTION, CODELAB, UNITS, DISCOUNT
from Table2 A
join Table1 B on A.[CODE ART] = B.[CODE ART]
Order by Date desc)
Group by
[CODE ART], DESCRIPTION, CODELAB

An infinite universe is the ultimate cartesian product.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-05 : 10:37:55

[code]DECLARE @Table1 TABLE (CodeArt INT)

INSERT @Table1
SELECT 2500

DECLARE @Table2 TABLE (CodeArt INT, Description VARCHAR(20), CodeLab TINYINT, Date SMALLDATETIME, Units TINYINT, Discount SMALLMONEY)

SET DATEFORMAT DMY

INSERT @Table2
SELECT 2500, 'PRODUCT 1', 255, '01/05/2008', 50, 5 UNION ALL
SELECT 2500, 'PRODUCT 1', 255, '28/04/2008', 20, 4 UNION ALL
SELECT 2500, 'PRODUCT 1', 255, '27/04/2008', 50, 6 UNION ALL
SELECT 2500, 'PRODUCT 1', 255, '24/04/2008', 60, 5

SELECT t2.CodeArt,
t2.Description,
t2.CodeLab,
t2.Units,
t2.Discount,
IDENTITY(INT, 1, 1) AS RowID
INTO #Temp
FROM @Table1 AS t1
INNER JOIN @Table2 AS t2 ON t2.CodeArt = t1.CodeArt
ORDER BY t2.CodeArt,
t2.Date DESC

SELECT t.CodeArt,
t.Description,
t.CodeLab,
SUM(t.Units) AS TotUnits,
AVG(Discount) AS AvgDiscount
FROM #Temp AS t
INNER JOIN (
SELECT CodeArt,
MIN(RowID) + 2 AS maxID
FROM #Temp
GROUP BY CodeArt
) AS y ON y.CodeArt = t.CodeArt
WHERE t.RowID < = y.maxID
GROUP BY t.CodeArt,
t.Description,
t.CodeLab[/code]

E 12°55'05.25"
N 56°04'39.16"

rtr1900
Starting Member

48 Posts

Posted - 2008-05-05 : 12:05:28

Oh Boy!
This sounds great! I will take the code and try it out!
hx for even writing the code. Thx Cat_jesus and Peso (you really made an effort of even writing the wholething I see!!!!)

Ok, I will come bacxk with the results!

Thx already

rtr1900
Starting Member

48 Posts

Posted - 2008-05-05 : 12:34:20

Hi Cat_jesus,

Itried your code, but it doesn´t go well.
My code using QSP+ SQL was something like:

SQl="Select * from articles"
execute rs
Do While not rs.EOF
sql2=" select * from table2 where cod_Art="&rs("cod_art")&""
execute rs2
Do While not rs.EOF

calculate here the data for only the three latest sales

rs.Movenext
LOOP
rs.Movenext
LOOP

P.S.: Don´t check the code, I just wrote it down so anybody can read it.
So what I did was first get the whole list of articles and then use it one by one checking the table of sales and doing a calculation (taking 3 latest sales, the the average discount + tot units sold with the 3 sales).
I know it is a long shot. But using just one SQl would it be possible.
I tried your cat_jesus, but I am getting stuck. The one of Peso, I havn´t tried as I just execute the sql command from the GUI of SQl server 2003, s I believe you can only excecute one command, right?

thx already

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-05 : 15:56:31

SQL server 2003? There's no such beast as far as I know.

From query analyzer in SQL server 2000 you can run multiple statements. My code was only really for a guide(I didn't check it for syntax) but you should not have to loop at all.

Peso's code is good and can be run in a query window as is.

I do not know what QSP is.

An infinite universe is the ultimate cartesian product.

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-05 : 16:02:42

ok I just used Peso's code and then added my fixed version after it. We both come up with the same values.

Paste this in a query window after you've pasted Peso's code.



drop table #temp

select DT1.[CODEART], DT1.DESCRIPTION, DT1.CODELAB, sum(DT1.UNITS) as TOTUNITS, Avg(DT1.DISCOUNT) as [AVG DISCOUNT]
from
(select top 3 A.[CODEART], DESCRIPTION, CODELAB, UNITS, DISCOUNT
	from @Table2 A
	join @Table1 B on A.[CODEART] = B.[CODEART]
	Order by Date desc) as DT1
Group by DT1.[CODEART], DESCRIPTION, CODELAB

Again, you don't need to loop, unless there's something you aren't telling us.

An infinite universe is the ultimate cartesian product.

rtr1900
Starting Member

48 Posts

Posted - 2008-05-07 : 09:29:33

quote:
Originally posted by cat_jesus

SQL server 2003? There's no such beast as far as I know.

From query analyzer in SQL server 2000 you can run multiple statements. My code was only really for a guide(I didn't check it for syntax) but you should not have to loop at all.

Peso's code is good and can be run in a query window as is.

I do not know what QSP is.

An infinite universe is the ultimate cartesian product.

Sorry I meant Windows Server 2003 using SQl server
And I also meant ASP not QSP

I am not sure if I explain myself good, cause I used your code Cat_jesus, but it returns only 3 lines. I will try to explain again:

table 1:
COntains all the articules. We only need th code of the article, n othing more

table 2:
Contains all the sales of each product. Which means that a product can have several lines.
I only need to get the latest 3 sales of "each" product, and take the average discount of it (calulated overr several data), and the total amount of units of those 3 sales.

So what I do in ASP, I call all the articles, and then one by one I pass the second table, and take the latest 3 sales, and calculate them, that result I show them.

So I am using two SQL syntaxes, and a calulation between them. Evereything is executed from an ASP file.

do you believe this can be done in SQl directly?
So I can use DTSrun to execute them every 10 minutes.

Thx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-07 : 10:05:23

So you listen to the one that screams loudest?

E 12°55'05.25"
N 56°04'39.16"

rtr1900
Starting Member

48 Posts

Posted - 2008-05-08 : 07:41:46

quote:
Originally posted by Peso

So you listen to the one that screams loudest?

E 12°55'05.25"
N 56°04'39.16"

???? Please explain

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-08 : 12:11:10

quote:

cause I used your code Cat_jesus, but it returns only 3 lines

I just ran the code again and it returns the same row that Peso's does.

quote:

table 2:
Contains all the sales of each product. Which means that a product can have several lines.

Define "lines" in this context. Is it another column you haven't told us about?

quote:

do you believe this can be done in SQl directly?

No, I don't believe it. Peso and I demonstrated it. It is a fact. Did you run Peso's code? You can copy the code and run it in a query window to see for yourself.

An infinite universe is the ultimate cartesian product.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-08 : 16:39:54

I'm not sure I 100% understand the requirements, but here is a something that might work for you:

SELECT 
	CodeArt, 
	CodeLab,
	SUM(Units) AS SumUnits,
	AVG(Discount) AS AvgDiscount
FROM
	(	
		SELECT 
			a.CodeArt, 
			a.Date,
			a.CodeLab,
			a.Units,
			a.Discount
		FROM 
			@Table2 a
		INNER JOIN
			@Table2 b
			ON a.CodeArt = b.CodeArt
		WHERE 
			a.Date <= b.Date
		GROUP BY 
			a.CodeArt, 
			a.Date,
			a.CodeLab,
			a.Units,
			a.Discount
		HAVING 
			COUNT(*) BETWEEN 1 AND 3
	) AS T
GROUP BY
	CodeArt, 
	CodeLab

EDIT: I just noticed the Description column. Oh well, you get the idea.

rtr1900
Starting Member

48 Posts

Posted - 2008-05-09 : 11:45:43

Thx Lamprey

i just ran your code (Adapted a bit) and it is working. I just noticed that the AVG will not work for me, cause it takes the 3 values and devide it by 3, whil eit need to calculated it also with the amount of units sold.... anyway I am getting there bit by bit. Thx again! all of you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources