February 1, 2011 at 11:20 pm
Hi
I have a table t_telephone. A telephone no may be transferred to another no. any number of times.
If a phone is not transferred, then transfer_date will be null and is_transferred_flag will be 0. Here is the structure and insert statements:
CREATE table t_telephone(id int identity,transferred_from_phone_no int,is_transferred bit,transferred_to_phone_no int,transfer_date datetime)
INSERT INTO t_telephone (transferred_from_phone_no,is_transferred,transferred_to_phone_no,transfer_date) values(null,1,1,'2011-01-19')
INSERT INTO t_telephone (transferred_from_phone_no,is_transferred,transferred_to_phone_no,transfer_date) values(2,1,3,'2011-01-21')
INSERT INTO t_telephone (transferred_from_phone_no,is_transferred,transferred_to_phone_no,transfer_date) values(1,1,2,'2011-01-20')
INSERT INTO t_telephone (transferred_from_phone_no,is_transferred,transferred_to_phone_no,transfer_date) values(3,0,null,null)
select * from t_telephone
SELECT CASE t1.is_transferred WHEN 0 THEN t1.transferred_from_phone_no
ELSE (SELECT TOP 1 (CASE t2.is_transferred WHEN 0 THEN t2.transferred_from_phone_no ELSE t2.transferred_to_phone_no END)
from t_telephone t2 where t2.id=t1.id order by ISDATE(t2.transfer_date),t2.transfer_date desc) END 'just previous no'
,* from t_telephone t1
In last query, as you can see I'm using t_telephone twice. This is to get the "just_previous_number".
This example is just a replica of what I am really facing. I don't want functional logic to get "just_previous_number" in different ways but I want to rewrite this query in a manner that I dont have to use the table at two places.
Second pbm is that "'just previous no'" values are not returned correct. I expect all values to be '3' but they are not.
Any help is appreciated.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
February 2, 2011 at 3:52 pm
Assuming that the id column is a unique or primary key column, it seems to me that your complex query:
SELECT
CASE t1.is_transferred WHEN 0 THEN t1.transferred_from_phone_no
ELSE (
SELECT TOP 1 (
CASE t2.is_transferred WHEN 0 THEN t2.transferred_from_phone_no
ELSE t2.transferred_to_phone_no END
)
from t_telephone t2
where t2.id=t1.id
order by ISDATE(t2.transfer_date), t2.transfer_date desc
) END 'just previous no', *
from t_telephone t1
is exactly equivalent to this simple query:
SELECT CASE is_transferred
WHEN 0 THEN transferred_from_phone_no
ELSE transferred_to_phone_no END 'just previous no', *
from t_telephone
Can you explain in a bit more detail what you are trying to achieve?
February 2, 2011 at 9:44 pm
ok, this is what I am trying to achieve:
if is_transferred <>0 then I want the most recent transferred_to_phone_no. That is why order by date is done. So essentially "just previous no" should have same value for all rows for a given telephone connection. I think I should have added one more column "original phone no", which should remain same for a given connection, even if no is transferred to some other no.
okayyyy, I got it.
Now my table and query should look like this and returning result as I expected:
CREATE table t_telephone(id int identity,original_phone_no int,transferred_from_phone_no int,is_transferred bit,transferred_to_phone_no int,transfer_date datetime)
INSERT INTO t_telephone (original_phone_no,transferred_from_phone_no,is_transferred,transferred_to_phone_no,transfer_date) values(777,null,1,1,'2011-01-19')
INSERT INTO t_telephone (original_phone_no,transferred_from_phone_no,is_transferred,transferred_to_phone_no,transfer_date) values(777,2,1,3,'2011-01-21')
INSERT INTO t_telephone (original_phone_no,transferred_from_phone_no,is_transferred,transferred_to_phone_no,transfer_date) values(777,1,1,2,'2011-01-20')
INSERT INTO t_telephone (original_phone_no,transferred_from_phone_no,is_transferred,transferred_to_phone_no,transfer_date) values(777,3,0,null,null)
select * from t_telephone
SELECT CASE t1.is_transferred WHEN 0 THEN t1.transferred_from_phone_no
ELSE (SELECT TOP 1 (CASE t2.is_transferred WHEN 0 THEN t2.transferred_from_phone_no ELSE t2.transferred_to_phone_no END)
from t_telephone t2 where t2.original_phone_no=t1.original_phone_no order by ISDATE(t2.transfer_date),t2.transfer_date desc) END 'just previous no'
,* from t_telephone t1
Only thing remaining now is , how Can I rewrite it without putting a self join in subquey.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
February 3, 2011 at 2:29 pm
Kumar,
You may want to try something like this. It's the best way I've found to join back to the same record set to the one just prior or just next on the same partitioning key. I hope I understood your specs correctly.
; WITH CTEPhone AS
( SELECT ID, original_phone_no, transferred_from_phone_no, is_transferred
, transferred_to_phone_no, transfer_date
, ROW_NUMBER() OVER (PARTITION BY original_phone_no ORDER BY transfer_date DESC) AS RowNum
FROM t_telephone
)
SELECT
CASE WHEN T1.is_transferred = 0 THEN T1.transferred_from_phone_no
ELSE CASE WHEN T2.is_transferred = 0 THEN T2.transferred_from_phone_no
ELSE T2.transferred_to_phone_no
END
END AS JustPreviousNo
, T1.*
FROM CTEPhone T1
LEFT JOIN CTEPhone T2 ON
T1.original_phone_no = T2.original_phone_no
AND T1.RowNum = T2.RowNum + 1
ORDER BY T1.id
Todd Fifield
February 7, 2011 at 6:44 am
Thanks for trying it Todd. Actually the quary you gave returns different previous no for a given Phone number. My requirement is that the values of column "Just previous number" will be same for all rows for a given phone number.
If you try the structure and query I gave in my previous post, you'll see that in results returned.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply