August 20, 2009 at 6:57 pm
Hi,
I have a two table one(Table1) consisting of AID ResID and having data as follow.
AID ResID
1 1
1 2
1 3
and another(Table2) consisting of AID Seq and having data as
AID Seq
1 111
The ResID from Table1 is represented in a seq 111 in Table2. The sequence is order based on ResID.i,e 1 at the second position of the sequence represent ResID 2.
Now, If ResID=2 is deleted from the Table1; then i need to remove its bit in the seq in table2
Thus, final data should look similar to this:
Table1
AID ResID
1 1
1 3
Table2
AID Seq
1 11
Now, the second bit in seq represents ResID=3 now.
Kindly, suggest me how we can achieve this using SP or TSQL.
Regards
August 20, 2009 at 7:52 pm
What is the datatype of the SEQ column. Also, you're kinda new... take a look at the article in the first link in my signature line below. I guarantee you'll get better answers quicker than what you have with this question. Yes, it takes a little time on your part but you'll spend less time waiting and more time thanking folks for their answers. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 20, 2009 at 8:51 pm
Hi,
Ya am kind of new to this:-) . Please find the details below
USE [Test]
GO
CREATE TABLE [dbo].[Table1](
[AID] [int] NOT NULL,
[ResID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table2](
[AID] [int] NOT NULL,
[Seq] [varchar](50)
) ON [PRIMARY]
GO
INSERT INTO [Table1] VALUES(1,1)
INSERT INTO [Table1] VALUES(1,2)
INSERT INTO [Table1] VALUES(1,3)
GO
INSERT INTO [Table2] VALUES(1,111)
Thanks, Nav
August 20, 2009 at 10:18 pm
Very cool... that pretty much explains it and it allows me to test my code for you. This will do it.
DELETE FROM Table1
WHERE AID = 1
AND ResID = 2
;
WITH
cteCount AS
(
SELECT AID, REPLICATE('1',COUNT(ResID)) AS Seq
FROM Table1
GROUP BY AID
)
UPDATE t2
SET Seq = c.Seq
FROM Table2 t2
INNER JOIN cteCount c
ON t2.AID = c.AID
;
SELECT * FROM Table2
--Jeff Moden
Change is inevitable... Change for the better is not.
August 21, 2009 at 12:37 am
Hi Jeff,
Thanks!!! but this works only for the value of seq 111
Will it work for seq like 110, 101, 001,011? when the resid 2 is removed, the final output should be correspondingly 10,11,01,01. Apologies, i didn't mentioned this earlier.
Below are different values that could be in table2 and expected result if ResID=2 is Removed
INSERT INTO [Table2] VALUES(1,011) /* Final Result should be 01
INSERT INTO [Table2] VALUES(1,101) /* Final Result should be 11
INSERT INTO [Table2] VALUES(1,110) /* Final Result should be 10
INSERT INTO [Table2] VALUES(1,001) /* Final Result should be 01
INSERT INTO [Table2] VALUES(1,100) /* Final Result should be 10
INSERT INTO [Table2] VALUES(1,111) /* Final Result should be 11
INSERT INTO [Table2] VALUES(1,000) /* Final Result should be 00
If ResID=3 is removed, then the value should respectively be
01
10
11
00
10
11
00
Thanks again for your help
August 21, 2009 at 1:43 am
This has now become a string replacement sort of problem. From what I understand - you need to replace the existing value at a particular position in the string with a blank (i.e. you need to remove a value at a particular position in a string)
DECLARE @Table1 TABLE(
[AID] [int] NOT NULL,
[ResID] [int] NOT NULL
)
DECLARE @Table2 TABLE(
[AID] [int] NOT NULL,
[Seq] [varchar](50)
)
INSERT INTO @Table1 VALUES(1,1)
INSERT INTO @Table1 VALUES(1,2)
INSERT INTO @Table1 VALUES(1,3)
INSERT INTO @Table2 VALUES(1,'111') /* Final Result should be 11*/
INSERT INTO @Table2 VALUES(1,'011') /* Final Result should be 01*/
INSERT INTO @Table2 VALUES(1,'101') /* Final Result should be 11*/
INSERT INTO @Table2 VALUES(1,'110') /* Final Result should be 10*/
INSERT INTO @Table2 VALUES(1,'001') /* Final Result should be 01*/
INSERT INTO @Table2 VALUES(1,'100') /* Final Result should be 10*/
INSERT INTO @Table2 VALUES(1,'111') /* Final Result should be 11*/
INSERT INTO @Table2 VALUES(1,'000') /* Final Result should be 00*/
SELECT AID,ResId FROM @Table1
SELECT AID,Seq FROM @Table2
DECLARE @AID int, @ResID int
SELECT @AID = 1, @ResID = 2
DELETE FROM @Table1
WHERE AID = @AID
AND ResID = @ResID
--SELECT * FROM @Table1;
SELECT AID,Seq,STUFF(Seq,@ResID,1,'')
FROM @Table2
WHERE AID = @AID
GO
August 21, 2009 at 1:45 am
Can you provide some further example of the data in both tables ?
For instance how is the BitString '1010' represented in Table1 ?
---- EDIT
Just for clarification , i may be missing something ,but how are '0's stored in table 1?
Additionally , why are you building the bit string as a string and not using an integer.
An int can hold upto 2^30. Decimal can hold more admittedly that could cause extra issues when using it later.
August 21, 2009 at 4:37 am
naveenreddy.84 (8/21/2009)
Hi Jeff,Thanks!!! but this works only for the value of seq 111
Will it work for seq like 110, 101, 001,011? when the resid 2 is removed, the final output should be correspondingly 10,11,01,01. Apologies, i didn't mentioned this earlier.
Below are different values that could be in table2 and expected result if ResID=2 is Removed
INSERT INTO [Table2] VALUES(1,011) /* Final Result should be 01
INSERT INTO [Table2] VALUES(1,101) /* Final Result should be 11
INSERT INTO [Table2] VALUES(1,110) /* Final Result should be 10
INSERT INTO [Table2] VALUES(1,001) /* Final Result should be 01
INSERT INTO [Table2] VALUES(1,100) /* Final Result should be 10
INSERT INTO [Table2] VALUES(1,111) /* Final Result should be 11
INSERT INTO [Table2] VALUES(1,000) /* Final Result should be 00
If ResID=3 is removed, then the value should respectively be
01
10
11
00
10
11
00
Thanks again for your help
I guess I don't understand... if table two has a value of ...
INSERT INTO [Table2] VALUES(1,101) /* Final Result should be 11
According to your previous example, you cannot have "0" in the table because you remove "1"'s from table 2 when you remove a row from table 1.
So my question would be, what would table 1 have for a value at that point in time for the row in table 2 above??
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2009 at 1:45 pm
No answer to my previous question?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 23, 2009 at 9:37 pm
Hi,
The Value in table2 (for a given AID value) is a sequence of 1's and 0's, Where each position represents the corresponding Value of ResID in table1 in the asc order of ResID(always). For Example
if the Table1 contains ResID 1,2,3 for a given AID value of 1. Then, for the same AID value of 1 in table2, the value could be 011,101,110,001,100,010,000,111. Suppose ResID represents switch 1,2,3, then '1'(In sequence) represents ON status of and '0'(In sequence) Represent the OFF status of the switch. The values in table2 represents different combination of Up and Down Switch.
I was trying to find a way to determine the position of a given ResID in the sequence, and remove the value at that position in sequence and concatenate the remaining string.
Suppose in the above example, if i remove ResID=2, then in all the sequence i need to remove value at position 2.
August 23, 2009 at 9:44 pm
Hi,
The representation of value in Table1 in sequence is as follow
ResID S1 S2 S3 S4 S5 S6
1 0 1 1 1 0 1
2 1 0 1 1 0 0
3 1 1 0 1 1 0
4 1 1 1 0 1 1
August 23, 2009 at 10:41 pm
Does this do what you want?
I was trying to find a way to determine the position of a given ResID in the sequence, and remove the value at that position in sequence and concatenate the remaining string
DECLARE @Table1 TABLE(
[AID] [int] NOT NULL,
[ResID] [int] NOT NULL
)
DECLARE @Table2 TABLE(
[AID] [int] NOT NULL,
[Seq] [varchar](50)
)
INSERT INTO @Table1 VALUES(1,1)
INSERT INTO @Table1 VALUES(1,2)
INSERT INTO @Table1 VALUES(1,3)
INSERT INTO @Table2 VALUES(1,'111') /* Final Result should be 11*/
INSERT INTO @Table2 VALUES(1,'011') /* Final Result should be 01*/
INSERT INTO @Table2 VALUES(1,'101') /* Final Result should be 11*/
INSERT INTO @Table2 VALUES(1,'110') /* Final Result should be 10*/
INSERT INTO @Table2 VALUES(1,'001') /* Final Result should be 01*/
INSERT INTO @Table2 VALUES(1,'100') /* Final Result should be 10*/
INSERT INTO @Table2 VALUES(1,'111') /* Final Result should be 11*/
INSERT INTO @Table2 VALUES(1,'000') /* Final Result should be 00*/
SELECT AID,ResId FROM @Table1
SELECT AID,Seq FROM @Table2
DECLARE @AID int, @ResID int
SELECT @AID = 1, @ResID = 2
DELETE FROM @Table1
WHERE AID = @AID
AND ResID = @ResID
--SELECT * FROM @Table1;
SELECT AID,Seq,STUFF(Seq,@ResID,1,'')
FROM @Table2
WHERE AID = @AID
GO
August 24, 2009 at 2:07 am
I still dont see how you are getting
ResID S1 S2 S3 S4 S5 S6
1 0 1 1 1 0 1
2 1 0 1 1 0 0
3 1 1 0 1 1 0
4 1 1 1 0 1 1
Represented in a table like this....
DECLARE @Table1 TABLE(
[AID] [int] NOT NULL,
[ResID] [int] NOT NULL
)
Please provide an insert script.......
Also why do you think that manipulating the present string is a better option than simply recalculating the entire string? Did you consider the other points i made as well ?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply