Hello,
I am having a problem with subqueries.The code works but i do not get the correct calculated output.
I have the table structure and the expected output below.
I need to basically find the max and min for each row within a table. the code needs to compare load_1 ,load_2 and load_3 on a particular day,particular month and on a particular year and give the max load among loads columns.
Code:
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
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
Any suggestions what am i doing wrong?
I am having a problem with subqueries.The code works but i do not get the correct calculated output.
I have the table structure and the expected output below.
I need to basically find the max and min for each row within a table. the code needs to compare load_1 ,load_2 and load_3 on a particular day,particular month and on a particular year and give the max load among loads columns.
Code:
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
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
Any suggestions what am i doing wrong?
Last edited: