What is wrong in my query? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

What is wrong in my query?

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2007-11-19 : 06:27:38

Hi,

I have 3 tables. 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 (same for the same CallDetailID) and ordered by the date created

DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30), 
StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)

SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL

SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL

SELECT '456456', 'Bobby', 'SA023', 12, 114, Null UNION ALL

SELECT '22322362', 'Guck', 'SA023', 12, 123, Null UNION ALL

SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL

SELECT '225652', 'Jim', 'SA023', 67, 143, Null UNION ALL

SELECT '126756', 'Jasm', 'SA023', 67, 145, Null UNION ALL

SELECT '786234', 'Chuck', 'SA023', 67, 154, Null UNION ALL

SELECT '66234', 'Mutuk', 'SA023', 67, 185, Null UNION ALL

SELECT '2232362', 'Buck', 'SA023', 67, 195, Null 


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 

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 = dt.CallGroup
   FROM @Request r
      JOIN @Call c
         ON r.CallID = c.CallID
      JOIN (SELECT 
               CallDetailID,
               EntryDt,
               CallGroup = ROW_NUMBER() OVER (ORDER BY EntryDt )
            FROM @CallDetail
           ) dt
         ON c.CallDetailID = dt.CallDetailID

select * from @Request

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 67 185 2
2232362 Buck SA055 67 195 2

I made the city from 67 to 68 and 72 for Chuck and Mutuk
SELECT '786234', 'Chuck', 'SA055', 68, 154, Null UNION ALL

EXPECTED :
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 68 154 1
66234 Mutuk SA059 72 185 1
2232362 Buck SA055 67 195 2

So the unique no generated for it needs to be 1 and not 2. That is because we are looking out for a unique no. which remains the same for the same calldetailid, however changes for the following condition :
Per empid, Per StateNo, Per CityNo, Per CallType (same for the same CallDetailID) and ordered by the date created"

Similarly for different state or empid or calltype

Something like this :
PARTITION BY EmpID, r.StateNo, r.CityNo, c.CallType ORDER BY cd.EntryDt
Any clues?

alzdba
Starting Member

10 Posts

Posted - 2007-11-19 : 08:36:43

Can you explain why you'd expect the result EXPECTED using your ranking function to get a UniqueNo ?


USE adventureworks;

SELECT Row_Number() OVER (ORDER BY A.City) as RowNum
, Rank() OVER (ORDER BY A.City) as Rank
, Dense_Rank() OVER (ORDER BY A.City) as DenseRank
, NTile(3) OVER (ORDER BY A.City) as NTile_3
, NTile(4) OVER (ORDER BY A.City) as NTile_4
, A.City, SP.StateProvinceCode 
FROM Person.Address as A 
INNER JOIN Person.StateProvince as SP
ON A.StateProvinceID = SP.StateProvinceID 
WHERE SP.CountryRegionCode = 'US' 
AND SP.StateProvinceCode = 'AZ'

lols
Posting Yak Master

174 Posts

Posted - 2007-11-19 : 08:50:16

I am sorry i dont understand you...

how to modify my query to get the expected results?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-20 : 00:56:33

I am not following you.

Why is mutok a 1 if he has the same

EmpID, StateNo , CityNo , CallType

as chuck?

lols
Posting Yak Master

174 Posts

Posted - 2007-11-20 : 01:01:09

Hi,

I wrote
I made the city from 67 to 68 and 72 for Chuck and Mutuk

So Mutuk has the same empid, stateno and calltype but not teh same cityno as now it has become 72.

that is why it should be 1.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-20 : 01:09:33

Your expected results are not making sense to me.

Here's the query on how to do the following,

Break each EmpID, StateNo, CityNo, and Call type into groups, then number sequntially within each group ordering by the entry date from earliest to latest.

If this isn't what you are looking for please explain more.


DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30), 
StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)
Insert Into @Request(RequestID,Customername,StateNo, CityNo, CallID, UniqueNo)
SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL

SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL

SELECT '456456', 'Bobby', 'SA023', 12, 114, Null UNION ALL

SELECT '22322362', 'Guck', 'SA023', 12, 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', 68, 154, Null UNION ALL

SELECT '66234', 'Mutuk', 'SA055', 72, 185, Null UNION ALL

SELECT '2232362', 'Buck', 'SA055', 67, 195, Null 


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 

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'
--

select *,Row_Number() over (Partition by  c.EmpID,a.stateno,a.CityNo,b.CallType order by c.Entrydt) as CorrecteduniqueID
from @Request a
inner Join
	@call b
on a.CallID  = b.callID
Inner Join
@CallDetail c
on c.CallDetailID = b.CallDetailID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources