November 19, 2007 at 10:37 am
Hi,
I have 3 tables. The tables are populated in the following order: One row for CallDetail, One for Call and one for Request
I have to generate a UniqueNo - Per Empid, Per StateNo, Per CityNo, Per CallType. I need to generate this no. for each unique value of the CallDetailID. As a last condition, we can order the rows by the date created
Let me explain with data :
-- Table 1
DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30),
StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)
INSERT @Request
SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL
SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL
SELECT '456456', 'Bobby', 'SA024', 12, 114, Null UNION ALL
SELECT '22322362', 'Guck', 'SA024', 44, 123, Null UNION ALL
SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL
SELECT '225652', 'Jim', 'SA055', 67, 143, Null UNION ALL
SELECT '126756', 'Jasm', 'SA055', 67, 145, Null UNION ALL
SELECT '786234', 'Chuck', 'SA055', 67, 154, Null UNION ALL
SELECT '66234', 'Mutuk', 'SA059', 72, 185, Null UNION ALL
SELECT '2232362', 'Buck', 'SA055', 67, 195, Null
-- Tabl 2
DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)
INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 1, 12123 UNION ALL
SELECT 114, 2, 12123 UNION ALL
SELECT 123, 2, 12123 UNION ALL
SELECT 134, 3, 12123 UNION ALL
SELECT 143, 1, 6532 UNION ALL
SELECT 145, 1, 6532 UNION ALL
SELECT 154, 2, 6532 UNION ALL
SELECT 185, 2, 6532 UNION ALL
SELECT 195, 3, 6532
-- Table 3
DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT, EntryDt DateTime)
INSERT @CallDetail
SELECT 12123, 1, '11/5/2007 10:41:34 AM' UNION ALL
SELECT 6532, 1, '11/5/2007 12:12:34 PM'
--Query written
UPDATE r
SET r.UniqueNo = p.RecID
FROM @Request AS r
INNER JOIN (
SELECT r.RequestID,
ROW_NUMBER() OVER (PARTITION BY cd.EmpID, r.StateNo, r.CityNo, c.CallDetailID, c.CallType ORDER BY cd.EntryDt) AS RecID
FROM @Request AS r
INNER JOIN @Call AS c ON c.CallID = r.CallID
INNER JOIN @CallDetail AS cd ON cd.CallDetailID = c.CallDetailID
) AS p ON p.RequestID = r.RequestID
WHERE r.UniqueNo IS NULL
select * from @Request
OUTPUT FROM QUERY :
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 2
456456 Bobby SA024 12 114 1
22322362 Guck SA024 44 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA055 67 143 1
126756 Jasm SA055 67 145 2
786234 Chuck SA055 67 154 1
66234 Mutuk SA059 72 185 1
2232362 Buck SA055 67 195 1
EXPECTED OUTPUT:
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 1
456456 Bobby SA024 12 114 1
22322362 Guck SA024 44 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA055 67 143 2
126756 Jasm SA055 67 145 2
786234 Chuck SA055 67 154 2
66234 Mutuk SA059 72 185 2
2232362 Buck SA055 67 195 2
As you can see, since the 1st 5 rows have the same CallDetailId, same stateno, same city, same Calltype hence all have the unique id as 1(as thedate created is earlier). Similarly the last 5 rows have the same logic.
How can I get the Expected output?
Any clues?
November 19, 2007 at 2:01 pm
Lalit,
The only method I can think of is to use a cursor to store the pervious calldetailid and compare it to the current. If they are the same, give the uniqueid the same id as the previous; otherwise, increment the id by 1.
-- Table 1
DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30),
StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)
INSERT @Request
SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL
SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL
SELECT '456456', 'Bobby', 'SA024', 12, 114, Null UNION ALL
SELECT '22322362', 'Guck', 'SA024', 44, 123, Null UNION ALL
SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL
SELECT '225652', 'Jim', 'SA055', 67, 143, Null UNION ALL
SELECT '126756', 'Jasm', 'SA055', 67, 145, Null UNION ALL
SELECT '786234', 'Chuck', 'SA055', 67, 154, Null UNION ALL
SELECT '66234', 'Mutuk', 'SA059', 72, 185, Null UNION ALL
SELECT '2232362', 'Buck', 'SA055', 67, 195, Null
-- Tabl 2
DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)
INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 1, 12123 UNION ALL
SELECT 114, 2, 12123 UNION ALL
SELECT 123, 2, 12123 UNION ALL
SELECT 134, 3, 12123 UNION ALL
SELECT 143, 1, 6532 UNION ALL
SELECT 145, 1, 6532 UNION ALL
SELECT 154, 2, 6532 UNION ALL
SELECT 185, 2, 6532 UNION ALL
SELECT 195, 3, 6532
-- Table 3
DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT, EntryDt DateTime)
INSERT @CallDetail
SELECT 12123, 1, '11/5/2007 10:41:34 AM' UNION ALL
SELECT 6532, 1, '11/5/2007 12:12:34 PM'
--******************************************
DECLARE RunningTotalCursor
CURSOR LOCAL FAST_FORWARD FOR
--query to use for cursor
SELECT RequestID, c.CallDetailID
FROM @Request AS r
INNER JOIN @Call AS c ON c.CallID = r.CallID
INNER JOIN @CallDetail AS cd ON cd.CallDetailID = c.CallDetailID
WHERE r.UniqueNo IS NULL
OPEN RunningTotalCursor
--declare variables for cursor
DECLARE @RequestID INT
DECLARE @CallDetailID int
--set starting value of the running total
DECLARE @UniqueID INT, @PrevCallDetailID INT
SET @PrevCallDetailID = -1
FETCH NEXT FROM RunningTotalCursor
INTO @RequestID, @CallDetailId
WHILE @@FETCH_STATUS = 0
BEGIN
--if this is the first time going through set the value to 1
IF @PrevCallDetailID = -1
BEGIN
SET @UniqueID = 1
END
--if the previous and current detail id are the same
--use the existing id; otherwise, increment by 1
ELSE IF @PrevCallDetailID = @CallDetailId
BEGIN
SET @UniqueID = @UniqueID
END
ELSE
BEGIN
SET @UniqueID = @UniqueID + 1
END
--Update unique values
UPDATE @Request
SET UniqueNo = @UniqueID
WHERE RequestID = @RequestID
SET @PrevCallDetailID = @CallDetailId
FETCH NEXT FROM RunningTotalCursor
INTO @RequestID, @CallDetailId
END
CLOSE RunningTotalCursor
DEALLOCATE RunningTotalCursor
/*======================================
VIEW RESULTS
======================================*/
select * from @request
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply