help 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 2008 Forums

Transact-SQL (2008)

help query

Author

Topic

kabon
Starting Member

48 Posts

Posted - 2013-04-10 : 03:26:42

I have 3 tables which is table A, B and C.

Table A

NO|Name|
1223.008201|Nick|
1224.008201|Jo|

Table B

ID|AMOUNT|CODE|
1223|2000|008201|

Table C

ID|AMOUNT|CODE|
1224|4000|008201|

and I must have result like this

Table A

NO|Name|MAX_AMOUNT
1223.008201|Nick|2000|
1224.008201|Jo|4000|

please help make for this query

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-10 : 03:49:23

SELECT ta.Num, ta.Name, MAX(COALESCE(tb.AMOUNT, tc.AMOUNT)) MAX_AMOUNT
FROM TableA ta
LEFT JOIN TableB tb ON ta.Num = tb.ID+'.'+tb.CODE
LEFT JOIN TableC tc ON ta.Num = tc.ID+'.'+tc.CODE
GROUP BY ta.Num, ta.Name

kabon
Starting Member

48 Posts

Posted - 2013-04-10 : 04:00:34

i want to create new field and the name is MAX_AMOUNT

if table A like this:
NO|Name|AMOUNT1|
1223.008201|Nick|1000|
1224.008201|Jo|3000|

and the result must:

table A:

NO|Name|AMOUNT1|MAX_AMOUNT|
1223.008201|Nick|1000|3000|
1224.008201|Jo|3000|7000|

please help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-10 : 04:41:50

tell us rules for calculating MAX_AMOUNT after showing enough sample data. just showing two lines of data and output like above doesnt help anyone in understanding what your rules are!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-10 : 04:45:52

May be this?
-- ALTER TableA to add new column MAX_AMOUNT


DECLARE @TableA Table (Num VARCHAR(20), Name VARCHAR(10), MAX_AMOUNT INT)
INSERT INTO @TableA (Num,Name)
SELECT '1223.008201', 'Nick' union all
SELECT '1224.008201', 'Jo'

DECLARE @TableB Table (ID VARCHAR(10), AMOUNT INT, CODE VARCHAR(10))
INSERT INTO @TableB
SELECT '1223', 2000, '008201' union all
SELECT '1223', 9000, '008201'   -- New record

DECLARE @TableC Table (ID VARCHAR(10), AMOUNT INT, CODE VARCHAR(10))
INSERT INTO @TableC
SELECT '1224', 4000, '008201' 
/*Table A
Num|Name|MAX_AMOUNT
1223.008201|Nick|2000|
1224.008201|Jo|4000|*/
;WITH CTE AS 
(
	SELECT ta.Num, ta.Name, MAX(COALESCE(tb.AMOUNT, tc.AMOUNT)) MAX_AMOUNT
		FROM @TableA ta 
		LEFT JOIN @TableB tb ON ta.Num = tb.ID+'.'+tb.CODE
		LEFT JOIN @TableC tc ON ta.Num = tc.ID+'.'+tc.CODE
		GROUP BY ta.Num, ta.Name
)
UPDATE ta 
SET ta.MAX_AMOUNT = c.MAX_AMOUNT
FROM @TableA ta 
JOIN CTE C ON ta.Num = c.Num

SELECT * FROM @TableA

kabon
Starting Member

48 Posts

Posted - 2013-04-11 : 00:36:48

chandu,
i have query like this?

SELECT a.[column 0] as recid,SUM(convert(float,a.[Column 9])+convert(float,b.[COLUMN 3])) as total
into ##D from A AS a
left join B as b on a.[column 0]=b.[column 1]+'.'+b.[column 6]
left join C as c on a.[column 0]=c.[column 1]+'.'+c.[column 6]
group by a.[column 0]

why it still error in there?

kabon
Starting Member

48 Posts

Posted - 2013-04-11 : 00:44:23

it says "Warning: Null value is eliminated by an aggregate or other SET operation."

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-11 : 01:27:10

That is warning message only, but not the error... right?
http://msdn.microsoft.com/en-us/library/ms190368.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-11 : 02:13:02

quote:
Originally posted by kabon

it says "Warning: Null value is eliminated by an aggregate or other SET operation."

If you want to supress it turn ANSI WARNINGS setting to OFF

http://msdn.microsoft.com/en-us/library/ms190368(v=sql.90).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

kabon
Starting Member

48 Posts

Posted - 2013-04-15 : 03:47:16

do you know how to change this query which is use "NOT IN" into "IF NOT EXISTS"

SELECT LEFT(HIST.RECID,19) as ID_HIS into ##HISFILTER
FROM '+@SourceDB+'.dbo.FBNK_LIMIT#HIS AS HIST WITH(NOLOCK)
WHERE LEFT(HIST.RECID,19) NOT IN
(SELECT RECID FROM '+@SourceDB+'.dbo.FBNK_LIMIT WITH (NOLOCK))

thank you

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-15 : 05:10:53

what is the problem with above query?

SELECT LEFT(HIST.RECID,19) as ID_HIS into ##HISFILTER
FROM '+@SourceDB+'.dbo.FBNK_LIMIT#HIS AS HIST WITH(NOLOCK)
WHERE NOT EXISTS (SELECT fl.RECID FROM '+@SourceDB+'.dbo.FBNK_LIMIT fl WHERE fl.RECID = LEFT(HIST.RECID,19) WITH (NOLOCK))

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-15 : 06:24:13

where's rest of the part? why is your db changing at runtime?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-15 : 06:24:14

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-04-15 : 06:26:47

[code]DECLARE @TableA Table (Num VARCHAR(20), Name VARCHAR(10), MAX_AMOUNT INT)
INSERT @TableA (Num,Name)
SELECT '1223.008201', 'Nick' union all
SELECT '1224.008201', 'Jo'

DECLARE @TableB Table (ID VARCHAR(10), AMOUNT INT, CODE VARCHAR(10))
INSERT @TableB
SELECT '1223', 2000, '008201'

DECLARE @TableC Table (ID VARCHAR(10), AMOUNT INT, CODE VARCHAR(10))
INSERT @TableC
SELECT '1224', 4000, '008201'

-- Solution
SELECT a.Num,
a.Name,
b.AMOUNT
FROM @TableA AS a
INNER JOIN (
SELECT ID + '.' + CODE AS Num,
AMOUNT
FROM @TableB

UNION ALL

SELECT ID + '.' + CODE AS Num,
AMOUNT
FROM @TableC
) AS b ON b.Num = a.Num[/code]

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

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-15 : 09:04:10

quote:
Originally posted by kabon

I have 3 tables which is table A, B and C.

Table A

NO|Name|
1223.008201|Nick|
1224.008201|Jo|

Table B

ID|AMOUNT|CODE|
1223|2000|008201|

Table C

ID|AMOUNT|CODE|
1224|4000|008201|

and I must have result like this

Table A

NO|Name|MAX_AMOUNT
1223.008201|Nick|2000|
1224.008201|Jo|4000|

please help make for this query

The sample shows that "amount" information is stored for each employee in a separate table... So if there are N number (thousands/millions) of employees, would there be N number of tables too?

I see you're getting correct solutions from the team (as per given example) but I am afraid that those won't be consistent for your needs (due to the DB design)

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources