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)
 stored procedure help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 04/02/2013 :  15:21:39  Show Profile  Reply with Quote
Here are the two tables, CUSTOMER and PRODUCTS

CUSTOMER

cus_id email_adr email_flag
1 x y
2 y n
3 z n

PRODUCTS


cus_id prod_cd prod_state
1 p Y
1 q N
1 r Y
2 p Y
2 q Y
3 p N
3 q N
3 r Y
3 s N

What is the stored procedure to set email_flag to Y when for each cus_id the prod_state is Y for atleast one prod_cd and to set email_flag to N when for each cus_id the prod_state is N for all prod_cd?

list of pro_cds is (p,q,r,s,t,u) and also I am passing the parameter @email for email_adr from my c# application

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1596 Posts

Posted - 04/02/2013 :  16:14:49  Show Profile  Reply with Quote
I'm not clear what purpose the email address has as a parameter since you seem to be updating the entire table. See if this leads you in the right direction:
create table #CUSTOMER (   --Stuff you should be providing
	cus_id	int,
	email_adr varchar(256),
	email_flag char(1)
	)

insert into #CUSTOMER
values
	(1, 'x', 'y'),
	(2, 'y', 'n'),
	(3, 'z', 'n')

create table #PRODUCTS (
	cus_id	int,
	prod_cd	varchar(50),
	prod_state char(1)
	)

insert into #PRODUCTS
values
	(1, 'p', 'Y'),
	(1, 'q', 'N'),
	(1, 'r', 'Y'),
	(2, 'p', 'Y'),
	(2, 'q', 'Y'),
	(3, 'p', 'N'),
	(3, 'q', 'N'),
	(3, 'r', 'Y'),
	(3, 's', 'N')
go
create proc dbo.MyProc(@email varchar(256))    -- Start of solution
as
update c
set email_flag = a.prod_state
--select * 
from #CUSTOMER c
	inner join (
		select c1.cus_id, max(p.prod_state) prod_state
		from #CUSTOMER c1
		inner join #PRODUCTS p
			on c1.cus_id = p.cus_id
		group by
			c1.cus_id
		) a
	on a.cus_id = c.cus_id
go
exec dbo.MyProc 'What is the email address for?'

select *
from #CUSTOMER


=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber

Edited by - Bustaz Kool on 04/02/2013 16:19:42
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
269 Posts

Posted - 04/02/2013 :  18:08:33  Show Profile  Reply with Quote

UPDATE c
SET
    email_adr = @email, --??
    email_flag = CASE WHEN EXISTS(
        SELECT 1    
        FROM dbo.PRODUCTS p
        WHERE
            p.cus_id = c.cus_id AND
            p.prod_cd IN ('p','q','r','s','t','u') AND
            p.prod_state = 'Y'
        )
        THEN 'Y' ELSE 'N' END
FROM dbo.CUSTOMER c

Edited by - ScottPletcher on 04/02/2013 18:11:03
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/03/2013 :  01:49:20  Show Profile  Reply with Quote

UPDATE c
SET c.email_flag = CASE WHEN Ycnt > 0 THEN 'Y' ELSE 'N' END
FROM CUSTOMER c
INNER JOIN (
            SELECT cus_id,
            SUM(CASE WHEN prod_state='Y' THEN 1 ELSE 0 END) AS Ycnt
            FROM PRODUCTS
            GROUP BY cus_id
           )p
ON p.cus_id = c.cus_id


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

maddyslayer
Yak Posting Veteran

57 Posts

Posted - 04/03/2013 :  12:05:29  Show Profile  Reply with Quote
Thanks man, that worked.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/03/2013 :  13:50:36  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.11 seconds. Powered By: Snitz Forums 2000