Problem with Subqueries - 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

SQL Server Development (2000)

Problem with Subqueries

Author

Topic

gs
Starting Member

14 Posts

Posted - 2008-02-08 : 12:17:04

Hello

I am having a problem with subqueries.
The code does not give errors, but it does not do the calculation correctly.
I had 2 subqueries .The first inner subquery needs to find the max and min numbers ( between loads 1 to 3) but it has to do it on a daily basis.
ie. I need to know max value(load) between load_1,load_2,load_3 every day on a particular year, on a particular month and on a particular day . so basically,I need max within a row.
With that basic subquery, i am adding a couple of check constraints in order to find max and min for each day( group by) .Also a couple of check constraint to confirm before updating table 'temp'.

For this code, the answer i am getting is the same number for offmin and offmax for all 365 days . So the max and min does not change corresponding to each day's load.

What is the problem? I am thinking it is finding max within a column which is not what I want..
Do help.
Thanks

UPDATE temp
SET offmin=m.tmpmin,
offmax=m.tmpmax
FROM(Select t.year_ID,t.mnth_ID,t.day_ID,tmpmin=min(t.l),
tmpmax=max(t.l)
FROM( select n.year_ID,n.mnth_ID,n.day_ID,l=n.load_1
FROM temp n
UNION ALL
SELECT n.year_ID,n.mnth_ID,n.day_ID,l=n.load_2
FROM temp n
UNION ALL
SELECT n.year_ID,n.mnth_ID,n.day_ID,l=n.load_3
FROM temp n
) AS t,class p
WHERE t.year_ID=p.year_ID
AND t.mnth_ID=p.mnth_ID
AND t.day_ID=p.day_ID
GROUP BY t.year_ID,mnth_ID,day_ID) AS m,class v
WHERE m.year_ID=v.year_ID
AND m.mnth_ID=v.mnth_ID
AND m.day_ID=v.day_ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 13:05:59

Can you post your table structures and your correct requirement with sample o/p?

gs
Starting Member

14 Posts

Posted - 2008-02-08 : 13:46:55

Table Structure for table'temp'

Temp Table

Year_ID int
Mnth_ID tinyint
Day_ID int
offmin float
offmax float
load_1 float
load_2 float
load_3 float
Where columns offmin and offmax is the output columns and load_1,load_2 and load_3 are used to get the output numbers

gs
Starting Member

14 Posts

Posted - 2008-02-08 : 13:54:06

Sample Table and output

Year_ID mnth_ID day_ID load_1 load_2 load_3
2008 1 1 200 300 400
2008 1 2 100 150 200

This is the expected output off_max and off_min

off_max off_min
400 200
200 100

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-02-11 : 02:25:40

create a function to find the min / max

CREATE FUNCTION f_min 
(
	@v1 int, 
	@v2 int, 
	@v3 int
)
RETURNS int
AS
BEGIN
    	RETURN 
	(
		SELECT 	MIN(val) 
		FROM 	
		(
			SELECT val = @v1 UNION ALL 
			SELECT val = @v2 UNION ALL 
			SELECT val = @v3 
		) v
	)
END


select Year_ID, mnth_ID, day_ID, 
       off_max = dbo.f_max(load_1, load_2, load_3),
       off_min = dbo.f_min(load_1, load_2, load_3)
from   sampletable

KH
[spoiler]Time is always against us[/spoiler]

gs
Starting Member

14 Posts

Posted - 2008-02-11 : 10:18:11

kh,
Thanks for your reply.

But why isnt my code working ?

When I paste the same code till the outer select subquery statement in the query analyzer , the answers in the tmpmax and tmpmin are correct. But when I use the complete code with update statement the answer is wrong. How can this be possivle

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 10:32:43

Here is an example how to do this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86906

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources