COMPARING TWO TABLE - 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 2005 Forums

Transact-SQL (2005)

COMPARING TWO TABLE

Author

Topic

imranabdulaziz
Yak Posting Veteran

83 Posts

Posted - 2010-05-17 : 06:40:24

I have two table Master and Transaction
In master table I define set of essential good without it set is not complete.
Master table like

productgroup product Minimum qty
Bookset Book A 1
Bookset Book B 1

And transactional table where request received is like

sysid Product group Product Qty Request complete flag
1 Bookset Book A 5 Complete
1 Bookset Book B 2 Complete
2 Bookset Book A 6 Incomplete

I want to know In Transactional whether a request of is having minimum Qty. That is if request of product group BookSet comes then it should have each product of Book A and Book B That is why sysid 1 request is complete and 2 is Incomplete.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-17 : 11:56:57

create a udf like below


CREATE FUNCTION IsComplete
(
@sysid int
)
RETURNS Bit
AS
BEGIN
DECLARE @Result bit

SELECT @Result = CASE WHEN EXISTS(SELECT 1
               FROM  Master m
               LEFT JOIN (SELECT Product	
                          FROM Transaction 
                          WHERE sysid=@sysid)t
               ON t.Product = m.Product
               AND t.productgroup = m.productgroup
               WHERE t.Product IS NULL)	THEN 0 ELSE 1 END

RETURN @Result	
END

then create RequestCompleteFlag as a derived qty as

RequestCompleteFlag AS CASE WHEN dbo.IsComplete(sysid)=1 THEN 'Complete' ELSE 'Incomplete' END

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources