October 27, 2009 at 1:38 am
Hi All,
I have a table that stores staff transfer details. Data as follows.
TransferIDRefIDSalesCodeSalesRepDeptJoinDtTransferDt
5354481JohnABC31-Jul-0503-Oct-09
5453481JohnXYZ04-Oct-09
7176566TomXYZ03-Feb-0810-Oct-09
7671566TomABC11-Oct-09
----------
For a report, I wanted to show the data in below format.
SalesCodeSalesRepFromToJoinDtTransferDt
481JohnABCXYZ31-Jul-0504-Oct-09
566TomXYZABC03-Feb-0811-Oct-09
----------
Can anyone please help with this?
Thanks, Vinod
October 27, 2009 at 1:56 am
I suggest you take a look at this article and post data in a more readable manner:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
-- Gianluca Sartori
October 27, 2009 at 2:13 am
ok Vinod, this time around I shall help you with test data. But as suggested by Gianluca, please make sure to post queries with data like this. You would get a lot quicker replies.
With lot of assumptions as below,
1. There will be no more than 2 records for each salesrep (which is usually unlikely 🙂 )
2. Transferdate is null where he is working and the joining date will be his transfer date for that department.
3. And some other assumption which I could have made in my query (Select clause)
I am just giving a gist of how you can do it. May be people will come up with killer queries if there are more than one transfer for a salesrep.
Create table #stxr(TID int, RID int, Scode Varchar(10),
Srep varchar(10), dept varchar(10), jDate datetime, Tdate datetime)
INSERT INTO #stxr VALUES(53, 54, 481, 'John', 'ABC', '31-Jul-05', '03-Oct-09')
INSERT INTO #stxr VALUES(54, 53, 481, 'John', 'XYZ', '04-Oct-09 ',NULL)
INSERT INTO #stxr VALUES(71, 76, 566, 'Tom', 'XYZ', '03-Feb-08','10-Oct-09')
INSERT INTO #stxr VALUES(76, 71, 566, 'Tom', 'ABC', '11-Oct-09 ',NULL)
Select Scode,
sRep,
max(Case when Tdate is not null Then Dept END) as [FROM],
max(Case when Tdate is null Then Dept END) as [TO],
max(case when Tdate is not null then Jdate END) as JoinDate,
Max(Case When tdate is null then Jdate End) as TxrDate
FROM
#stxr
Group by Scode,Srep
I am also interested to see how others will approach if there are unknown number of transfers!
---------------------------------------------------------------------------------
October 27, 2009 at 2:27 am
I don't know how you intend to display more than two transfers, but this is just a guess on your data:
DECLARE @Transfers TABLE (
TransferID int,
RefID int,
SalesCode int,
SalesRep varchar(10),
Dept char(3),
JoinDt datetime,
TransferDt datetime
)
INSERT INTO @Transfers
SELECT
53, 54, 481, 'John', 'ABC', '31-Jul-05', '03-Oct-09' UNION ALL SELECT
54, 53, 481, 'John', 'XYZ', '04-Oct-09', '11-Nov-09' UNION ALL SELECT
55, 54, 481, 'John', 'EDF', '12-Nov-09', NULL UNION ALL SELECT
71, 76, 566, 'Tom', 'XYZ', '03-Feb-08', '10-Oct-09' UNION ALL SELECT
76, 71, 566, 'Tom', 'ABC', '11-Oct-09', NULL
SELECT A.SalesCode, SalesRep, B.[From], Dept AS [To], B.JoinDt, A.JoinDt AS TransferDT
FROM @Transfers AS A
INNER JOIN (
SELECT SalesCode, JoinDt, Dept AS [From]
FROM @Transfers AS T
WHERE EXISTS (
SELECT SalesCode, MAX(TransferDt)
FROM @Transfers AS TG
WHERE TransferDt IS NOT NULL
GROUP BY SalesCode
HAVING SalesCode = T.SalesCode
AND MAX(TransferDt) = T.TransferDt
)
) AS B
ON A.SalesCode = B.SalesCode
WHERE TransferDt IS NULL
-- Gianluca Sartori
October 27, 2009 at 3:42 am
Hi,
Along with Gianluca nice approaches also try this
Use Gianluca sample data
select a.SalesCode,
a.SalesRep,
max(a.Dept) [From],
max(b.Dept) [TO],
min(a.JoinDt),
max(b.JoinDt)TransferDt
from @Transfers a ,
@Transfers b
where a.TransferDt is not null
and a.SalesCode = b.SalesCode
and a.SalesRep = a.SalesRep
and b.TransferDt is null
group by a.SalesCode,
a.SalesRep
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply