March 10, 2010 at 6:31 pm
I have the following sql statements to create an expected output.
The output is what I would like to see but I am looking for ideas and ways to do it maybe more efficiently? My real life production data has the GROUP BY with 6 columns already. It will be 7 columns in the group by if I use my approach as seen below. Thanks
DECLARE @Cust table
(CustomerID int,
CustFlag char(1))
Insert into @Cust values (100,'Y')
Insert into @Cust values (200,' ')
Insert into @Cust values (300,'Y')
Insert into @Cust values (400,' ')
Insert into @Cust values (500,'Y')
--select * from @cust
DECLARE @CustLoc table
(CustomerID int,
Loc int)
insert into @CustLoc values (100,0)
insert into @CustLoc values (100,1)
insert into @CustLoc values (100,2)
insert into @CustLoc values (200,0)
insert into @CustLoc values (200,20)
insert into @CustLoc values (200,21)
insert into @CustLoc values (200,22)
insert into @CustLoc values (200,23)
insert into @CustLoc values (300,30)
insert into @CustLoc values (300,31)
insert into @CustLoc values (400,40)
insert into @CustLoc values (400,41)
insert into @CustLoc values (500,0)
--select * from @CustLoc
DECLARE @OutTable table
(CustomerID int,
InvoiceLoc varchar(50))
-- subset of customers with CustFlag = 'Y' does not require the location value on their invoices
Insert into @OutTable
select distinct c.CustomerID, 'Don''t care to state location'
from @Cust c where c.CustFlag='Y'
-- subset of customers with CustFlag = ' ' require a specific location per invoice
Insert into @OutTable
select c.CustomerID, cl.Loc
from @CustLoc cl inner join @Cust c
on cl.CustomerID = c.CustomerID
where c.CustFlag=' '
group by c.CustomerID, cl.Loc
-- this select shows the expected output table
select * from @OutTable order by CustomerID, InvoiceLoc
March 10, 2010 at 8:39 pm
SELECT C.CustomerID,
Loc = ISNULL(CONVERT(VARCHAR(50), CL.Loc), 'Do not care')
FROM @Cust C
LEFT
JOIN @CustLoc CL
ON CL.CustomerID = C.CustomerID
AND C.CustFlag = ' ';
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 11, 2010 at 8:42 am
Thank you, Paul. Your solution is neat and easier to read.
March 11, 2010 at 9:52 pm
You are welcome.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply