Query please - 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

General SQL Server Forums

New to SQL Server Programming

Query please

Author

Topic

nirene
Yak Posting Veteran

98 Posts

Posted - 2010-04-23 : 06:04:33

Sample Data
Type # Amt # Trno # DC # Code # Subcode
ADV # 1000 # 1 #D # 103625 #
ADV # 1000 # 1 #C # 103400 # S1

ADV # 2000 # 2 #D # 103625 #
ADV # 2000 # 2 #C # 103400 # S2

VES # 5000 # 1 #D # 103400 # S1
VES # 5000 # 1 #C # 300070 # S1

Now I wanna find out the details of Code & Subcode of Type(VES) which does not match with Type ADV

Expected result should come with subcode of the other record

ADV # 2000 # 2 #D # 103625 # S2

Thanks in advance

Nirene

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 06:09:17

what about other ones with code 103625 ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nirene
Yak Posting Veteran

98 Posts

Posted - 2010-04-23 : 06:12:08

Hai,
Thanks for the reply and that record(103400) has to be omitted.

Nirene

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 06:17:01

nope what about other record of ADV with code 103625 ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nirene
Yak Posting Veteran

98 Posts

Posted - 2010-04-23 : 06:25:17

That also has to be omitted, only ADV that does not match with VES has to be fetched

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 06:45:15

still not clear
how did S2 come with 103625 for ADV

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nirene
Yak Posting Veteran

98 Posts

Posted - 2010-04-23 : 06:50:16

quote:
Originally posted by visakh16

still not clear
how did S2 come with 103625 for ADV

------------------------------------------------------------------------------------------------------

If possible S2 should be updated using Trno(2) & Type(ADV) out of the selected Data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 08:12:48

see below suggestion and post your question as per below format. only you knows what you're system as well as rules are!

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nirene
Yak Posting Veteran

98 Posts

Posted - 2010-04-24 : 01:12:57

Hai Visakh,
I'm using SQL Server 2000, my details as per your advice I hope.

Drop Table #Temp

Create Table #Temp (Type Char(3),Amt Real,Trno Int,DC Char(1),Code Char(6),Subcode Varchar(12))

Insert Into #Temp
Select 'ADV',1000 , 1 ,'D' ,'103625' ,'' Union All
Select 'ADV',1000 , 1 ,'C' ,'103400' ,'S1' Union All
Select 'ADV',2000 , 2 ,'D','103625' ,'' Union All
Select 'ADV',2000 , 2 ,'C','103400','S2' Union All
Select 'VES',5000 , 1 ,'D' ,'103400' ,'S1' Union All
Select 'VES',5000 , 1 ,'C' ,'300070','S1'

VES stands for Vehicle Sales Invoice and ADV for Credit Advice

I want to fetch all ADV(Credit Advices) which does not have a VES(Vehicle Sales Invoice) which is based on Code & Subcode
as you can see 'VES','103400' ,'S1' has a advice as
'ADV' '103400' ,'S1' so this detail has to to be omitted.

First Result which is expected
'ADV',2000 , 2 ,'D','103625' ,''
'ADV',2000 , 2 ,'C','103400','S2'

Final result
The Subcode 'S2' has to updated into the the result which is fetched

'ADV',2000 , 2 ,'D','103625' ,'S2'

I hope the info provided is in detail.

Regards

Nirene

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 01:23:06

[code]SELECT m.Type,m.Amt,m.Trno,
MAX(m.DC) AS DC,
MAX(m.Code) AS Code,
MAX(m.SubCode) AS SubCode
FROM #Temp m
LEFT JOIN(SELECT t1.Type,t1.Amt
FROM #Temp t1
INNER JOIN #Temp t2
ON t1.Code =t2.Code
AND t1.SubCode = t2.SubCode
AND t1.Type='ADV'
AND t2.Type = 'VES'
)m
ON m.Type=n.Type
AND m.Amt=n.Amt
WHERE n.Type IS NULL
GROUP BY m.Type,m.Amt,m.Trno
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nirene
Yak Posting Veteran

98 Posts

Posted - 2010-04-24 : 01:44:56

Visakh thanks a lot it worked fine.

alias m changed to n

m.type='ADV' just added

Nirene

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-24 : 07:17:49

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources