October 3, 2007 at 6:36 am
Hi,
I have the following tables :
Create Table #Request ( [requestid] int , [customername] Varchar(30) , [stateno] nvarchar(5) , [cityno] int , Callid int, UniqueNo int);
Create Table #Call(Callid int,Calltype int,callDetailid int )
// CallType 1=New 2=Change 3=Delete
Create Table #CallDetail(callDetailId int,empid int)
The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on
I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType and insert into #Request table along with the other data. How do I do this?
SAMPLE DATA
[Code]Insert into #CallDetail(12123,1)
Insert into #CallDetail(53423,1)
Insert into #CallDetail(6532,1)
Insert into #CallDetail(62323,1)
Insert into #CallDetail(124235,1)
Insert into #CallDetail(65423,2)
Insert into #CallDetail(56234,2)
Insert into #CallDetail(2364,2)
Insert into #CallDetail(34364,2)
Insert into #CallDetail(85434,2)
Insert Into #Call(111,1,12123)
Insert Into #Call(112,1,53423)
Insert Into #Call(114,1,6532)
Insert Into #Call(123,2,62323)
Insert Into #Call(134,1,124235)
Insert Into #Call(143,2,65423)
Insert Into #Call(145,1,56234)
Insert Into #Call(154,2,2364)
Insert Into #Call(185,1,34364)
Insert Into #Call(195,1,85434)
Insert Into #request Values('324234','Jack','SA023',12,111,0);
Insert Into #request Values('223452','Tom','SA023',12,112,0);
Insert Into #request Values('456456','Bobby','SA024',12,114,0);
Insert Into #request Values('22322362','Guck','SA024',44,123,0);
Insert Into #request Values('22654392','Luck','SA023',12,134,0);
Insert Into #request Values('225652','Jim','SA055',67,143,0);
Insert Into #request Values('126756','Jasm','SA055',67,145,0);
Insert Into #request Values('786234','Chuck','SA055',67,154,0);
Insert Into #request Values('66234','Mutuk','SA059',72,185,0);
Insert Into #request Values('2232362','Buck','SA055',67,195,0);
[/code]
EXPECTED OUTPUT will be (See the last column for unique nos). :
[Code]Insert Into #request Values('324234','Jack','SA023',12,111,1);
Insert Into #request Values('223452','Tom','SA023',12,112,2);
Insert Into #request Values('456456','Bobby','SA024',12,143,1); // Calltype = 1 empid= 1, but state is different, hence unique id is 1
Insert Into #request Values('22322362','Guck','SA024',44,114,1);
Insert Into #request Values('22654392','Luck','SA023',12,123,3);
Insert Into #request Values('225652','Jim','SA055',67,143,1);
Insert Into #request Values('126756','Jasm','SA023',69,134,1);
Insert Into #request Values('786234','Chuck','SA023',72,145,2);
Insert Into #request Values('66234','Mutuk','SA059',72,185,1);
Insert Into #request Values('2232362','Buck','SA055',67,195,2);
[/code]
Please note that this will not be run as a batch query, but the no. has to be generated and inserted into #record table in realtime. I have given bulk of records for understanding of the problem
Plz help. I am stuck from 2 days on this :
lalit
October 3, 2007 at 7:08 am
Two solutions come to my mind:
in one you would use a helper table to keep track of the existing empid, StateNo, CityNo, CallType combinations, with an identity column for these, so you can look this up and ensure uniqueness.
The other solution is if you know how the domain for the empid, StateNo, .... and construct a number from these. For example since the number of States is likely to remain under 100, 7 bits are sufficient to represent this, the next n bits in the number .... (this you could compute even on the fly and have it as a computed column).
Which leads to the question, why do you need this? Are you planning query based on this number? Do you need to persist it, or aren't the above four values sufficient to identify the rows?
Regards,
Andras
October 3, 2007 at 7:20 am
Hi,
First of all thanks for reading this. 🙂
I need the logic as there are quiet a lot of things that will be done based on this unique no. I know that the above four fields are enough to identiy the info, but we need that no. in the same pattern.
Why?
Simply because the manual system used to do that and to change that now would mean take the ire of the client as the managers have already committed.
One of my friends told me that using a CTE and paritioning could be a solution to this, but someone new to sql cannot imagine much what to do with it!!
thanks.
October 3, 2007 at 3:19 pm
I guess you coud code some function to calculate the unique number per combination of the four parameters. Something like this:
CREATE FUNCTION fn_UniqueNumber
(
@empid int, @StateNo int, @CityNo int, @CallType int
)
RETURNS int
AS
BEGIN
declare @MaxNumber int
set @maxNumber= isnull
(
(select Max(UniqueNumber) from Requests R
inner join Calls C
on R.Callid=C.Callid
and R.StateNo= @StateNo
and R.CityNo=@CityNo
and R.CallTipe=@CallType
inner join CallDetails D
on C.callDetailid= D.callDetailid
and C.empid=@empId
)
,0)
Return @MaxNumber + 1
END
and then perform the insert:
Insert Into #request Values('324234','Jack','SA023',12,111, dbo.fn_UniquNumber(@empid,12,111,@calltype));
Though you already have set the values for @empid and @calltype from the previos inserts into Calls and CallDetails tables.
October 3, 2007 at 9:36 pm
thanks..i will try this out 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply