November 26, 2012 at 8:10 am
CREATE TABLE #holding(
[TIDM] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLIENTCODE] int NULL ,
) ON [PRIMARY]
INSERT #holding
(TIDM,CLIENTCODE)
values ('ABC',1234),
('BCD',1234),
('BCD',1234),
('LMF',1234),
('DEC',1234),
('ADE',1234),
('LGJ',1234),
('PCD',1234),
('ABC',1234)
so i want a list of clientcode who dont hold a particular TIDM.
for eg if I my search parameter is ABC then the result should be empty row. However if my parameter is YYY then the result should be only one row which should have account number 1234.
November 26, 2012 at 8:19 am
freecoder (11/26/2012)
CREATE TABLE #holding([TIDM] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLIENTCODE] int NULL ,
) ON [PRIMARY]
INSERT #holding
(TIDM,CLIENTCODE)
values ('ABC',1234),
('BCD',1234),
('BCD',1234),
('LMF',1234),
('DEC',1234),
('ADE',1234),
('LGJ',1234),
('PCD',1234),
('ABC',1234)
so i want a list of clientcode who dont hold a particular TIDM.
for eg if I my search parameter is ABC then the result should be empty row. However if my parameter is YYY then the result should be only one row which should have account number 1234.
Nice job posting ddl and sample data. Your description however is very unclear. So if there are rows you want none returned and if there are no rows you want one returned? The first part of that is simple. The second part is not so simple unless the account number 1234 is hardcoded.
This works according to your sample data and description.
declare @Parm char(3) = 'ABC'
--set @Parm = 'YYY' --uncomment this for the YYY value
select top 1 1234
from #holding
where not exists(select * from #holding where TIDM = @Parm)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 26, 2012 at 8:19 am
Another option, based on your description.
DECLARE @searchParam VARCHAR(9) = 'YYY';
SELECT CLIENTCODE
FROM (SELECT CLIENTCODE, CASE WHEN MAX(b.TIDM) IS NULL THEN 1 ELSE 0 END
FROM #holding a
LEFT OUTER JOIN (VALUES(@searchParam)) b(TIDM) ON a.TIDM = b.TIDM
GROUP BY CLIENTCODE
) a(CLIENTCODE, display)
WHERE a.display = 1;
November 26, 2012 at 8:33 am
sorry if i was unclear.
basically I am writing a stored procedure for a user to check if a certain clientcode does not hold a certain tidm.
CREATE TABLE #holding(
[TIDM] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CLIENTCODE] int NULL ,
) ON [PRIMARY]
INSERT #holding
(TIDM,CLIENTCODE)
values ('ABC',1234),
('BCD',1234),
('BCD',1234),
('LMF',1234),
('DEC',1234),
('ADE',1234),
('LGJ',1234),
('PCD',1234),
('ABC',1222),
('ACC',1254)
('ADC',1274)
('LLJ',1234)
('KMC',1234)
A user can search both with tidm and clientcode
If the search parameter @tidm is ' ' and @clientcode = ' ' then I want to display all the results
If the search parameter @tidm is 'ABC' and @clientcode = ' ' then I want to display the clientcode who dont have ABC tidm. So in this case the result should exclude 1234 and 1222 and display rest of the client codes to tell the user that those clientcodes does not have ABC tidm.
if @tidm = 'ABC' and @clientcode = '1234' then the result should be everthing excluding the the rows with clientcode 1234.
November 26, 2012 at 8:40 am
freecoder (11/26/2012)
sorry if i was unclear.basically I am writing a stored procedure for a user to check if a certain clientcode does not hold a certain tidm.
A user can search both with tidm and clientcode
If the search parameter @tidm is ' ' and @clientcode = ' ' then I want to display all the results
If the search parameter @tidm is 'ABC' and @clientcode = ' ' then I want to display the clientcode who dont have ABC tidm. So in this case the result should exclude 1234 and 1222 and display rest of the client codes to tell the user that those clientcodes does not have ABC tidm.
if @tidm = 'ABC' and @clientcode = '1234' then the result should be everthing excluding the the rows with clientcode 1234.
Other than lacking a "@clientcode" variable, the code I supplied you with appears to do what you've requested. Are you struggling to edit it to suit your purposes?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply