May 27, 2010 at 2:41 pm
hi,
there is situation when I insert a row with updated status and want to select that row for that Client and the rows that have not been updated for other clients. So dataset should a union of updated (for client A) and non-updated(for clients other than A). how would I do that.
For example
Column0 Column1 Column2 Status Column4 Column5
0 1 abc 'A' efg hij
1 1 abc 'U' klm hij
2 2 ook 'A' oko ddd
Result Set I want is Column0(1) and Column0(2 )
Thanks
May 27, 2010 at 3:13 pm
SELECT * FROM YourTable
WHERE Column1 <> 'abc' OR (Column1 = 'abc' AND Column2='U')
or
SELECT * FROM YourTable
EXCEPT
SELECT * FROM YourTable
WHERE Column1 = 'abc' AND Column2<>'U'
May 27, 2010 at 3:17 pm
The only Input I have is Column1.
May 27, 2010 at 3:28 pm
raafi.shaafi (5/27/2010)
The only Input I have is Column1.
Then you'd need to use the value of Column2 as a fixed value, since there is no other way to exclude Column0(0). Well, there is a way: if you'd use the OUTPUT clause with your INSERT statement then you could use a the result in a join to your source table. But I don't see any reason why not using the known value of Column2...
We'd need a more descriptive table structure, sample data, the insert statement and the expected result based on thaose data ina ready to use format as described in the first link in my signature to play a little more with it. So far it's more towards guessing...
May 27, 2010 at 4:05 pm
lmu92 (5/27/2010)
raafi.shaafi (5/27/2010)
The only Input I have is Column1.Then you'd need to use the value of Column2 as a fixed value, since there is no other way to exclude Column0(0). Well, there is a way: if you'd use the OUTPUT clause with your INSERT statement then you could use a the result in a join to your source table. But I don't see any reason why not using the known value of Column2...
We'd need a more descriptive table structure, sample data, the insert statement and the expected result based on thaose data ina ready to use format as described in the first link in my signature to play a little more with it. So far it's more towards guessing...
Here is some actual data. primary key includes appid clientnmber and some other columns that have been excluded in below snapshot.
Select * from SHClient where Appid = '10008129145151295541'
AppId ClientNumber ActivityStatusCd ActivityMaximumRecordNbr ClientRecordInd ApplicantRecordInd
-------------------- ------------ ---------------- ------------------------ --------------- ------------------
60961291945546127546| 2110 | O | 0 | 0 | 1
60961291945546127546| 2110 | U | 1 | 0 | 1
60961291945546127546 |2000002700 | A | 1 | 0 | 0
May 27, 2010 at 5:00 pm
So what do you want from this result? You need to define the criteria for what you want. It's not enough to say I want the first row, but define why the first row matches. What about it differentiates it from the second row?
May 27, 2010 at 5:07 pm
Steve Jones - Editor (5/27/2010)
So what do you want from this result? You need to define the criteria for what you want. It's not enough to say I want the first row, but define why the first row matches. What about it differentiates it from the second row?
just because the second row is updated record. so in short the query should show updated records with statuscd of U if present for given Client Number otherwise should pull the records with 'A' statuscd.
May 28, 2010 at 7:10 am
anyone??????
May 28, 2010 at 8:25 am
Before I spend time answering you, let me see if I actually understand your question.
DECLARE @Table1 TABLE(
[AppId] VARCHAR(50),
[ClientNumber] VARCHAR(50),
[ActivityStatusCd] CHAR(1),
[ActivityMaximumRecordNbr] INT,
[ClientRecordInd] INT,
[ApplicantRecordInd] INT)
INSERT INTO @Table1
VALUES ('60961291945546127546','2110','O',0,0,1)
INSERT INTO @Table1
VALUES ('60961291945546127546','2110','U',1,0,1)
INSERT INTO @Table1
VALUES ('60961291945546127546','2000002700','A',1,0,0)
From the above data, you're expecting : -
/*
AppId ClientNumber ActivityStatusCd ActivityMaximumRecordNbr ClientRecordInd ApplicantRecordInd
-------------------------------------------------- -------------------------------------------------- ---------------- ------------------------ --------------- ------------------
60961291945546127546 2110 U 1 0 1
60961291945546127546 2000002700 A 1 0 0
*/
One Client has the [ActivityStatusCd] of 'U' and another of 'O' so we return the 'U' record.
The second Client has the [ActivityStatusCd] of 'A' but no [ActivityStatusCd] of 'U' so we return the 'A' record.
Is that correct?
May 28, 2010 at 8:37 am
that is right!!!
May 28, 2010 at 8:42 am
think I got it.
I did it using temp tables and Cursors.
Here is what I did.
Did a select and insert into Temporary table and then
DECLARE @ClientNbr Int,
@ActivityCd nvarchar(10)
DECLARE ClientTable CURSOR FOR
SELECT ClientNumber,
ActivityStatusCd
FROM #Temp1
OPEN ClientTable
Fetch ClientTable Into @ClientNbr, @ActivityCd
WHILE @@Fetch_Status = 0
Begin
If (Select Count(*) from #Temp1 where ClientNumber = @ClientNbr and ActivityStatusCd = 'U') > 0
begin
Delete from #Temp1 where ClientNumber = @ClientNbr and ActivityStatusCd <> 'U'
end
FETCH ClientTable INTO @ClientNbr,
@ActivityCd
END
CLOSE ClientTable
DEALLOCATE ClientTable
Select * from #Temp1
Drop table #Temp1
May 28, 2010 at 11:51 am
Would you mind telling me why the code I provided a few posts back won't work for you? Still unknown to me...
May 28, 2010 at 12:15 pm
This is one way you can run it without using as much overhead. I'm sure there are ways to do it better but at least you'll avoid cursors and temp tables.
declare @AppID VARCHAR(50)
select @AppID = '60961291945546127546'
select t.AppId,
t.ClientNumber,
t.ActivityStatusCd,
t.ActivityMaximumRecordNbr,
t.ClientRecordInd,
t.ApplicantRecordInd
from @Table1 t
where t.AppId = @AppID
and
(
t.ActivityStatusCd = 'U'
or not exists
(
select sq.ClientNumber
from @Table1 sq
where sq.AppId = t.AppId
and sq.ClientNumber = t.ClientNumber
and sq.ActivityStatusCd = 'U'
)
)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply