SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 help query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kabon
Starting Member

Indonesia
48 Posts

Posted - 04/10/2013 :  03:26:42  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/10/2013 :  03:49:23  Show Profile  Reply with Quote
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
Go to Top of Page

kabon
Starting Member

Indonesia
48 Posts

Posted - 04/10/2013 :  04:00:34  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/10/2013 :  04:41:50  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/10/2013 :  04:45:52  Show Profile  Reply with Quote
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
Go to Top of Page

kabon
Starting Member

Indonesia
48 Posts

Posted - 04/11/2013 :  00:36:48  Show Profile  Reply with Quote
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?
Go to Top of Page

kabon
Starting Member

Indonesia
48 Posts

Posted - 04/11/2013 :  00:44:23  Show Profile  Reply with Quote
it says "Warning: Null value is eliminated by an aggregate or other SET operation."
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/11/2013 :  01:27:10  Show Profile  Reply with Quote
That is warning message only, but not the error... right?
http://msdn.microsoft.com/en-us/library/ms190368.aspx
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/11/2013 :  02:13:02  Show Profile  Reply with Quote
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
Go to Top of Page

kabon
Starting Member

Indonesia
48 Posts

Posted - 04/15/2013 :  03:47:16  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/15/2013 :  05:10:53  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/15/2013 :  06:24:13  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/15/2013 :  06:24:14  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30113 Posts

Posted - 04/15/2013 :  06:26:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 04/15/2013 :  09:04:10  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000