x

SQL Query needed

Hi All,

I have a table that stores staff transfer details. Data as follows.

TransferID RefID SalesCode SalesRep Dept  JoinDt    TransferDt
53         54    481        John    ABC  31-Jul-05    03-Oct-09
54         53    481        John    XYZ  04-Oct-09	
71         76    566        Tom     XYZ  03-Feb-08    10-Oct-09
76         71    566        Tom     ABC  11-Oct-09


For a report, I wanted to show the data in below format.

SalesCode SalesRep From To       JoinDt  TransferDt
481       John     ABC  XYZ	31-Jul-05 04-Oct-09
566       Tom      XYZ  ABC	03-Feb-08 11-Oct-09


Can anyone please help with this?

Thanks, Vinod

more ▼

asked Oct 27, 2009 at 04:32 AM in Default

Vinod gravatar image

Vinod
5 3 3 3

Vinod - you should mark those sections as Code, so they format properly...
Oct 27, 2009 at 04:59 AM Rob Farley
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Original data... let's call this table Tenure.

TransferID RefID SalesCode SalesRep Dept JoinDt    TransferDt 
53         54    481       John     ABC  31-Jul-05 03-Oct-09 
54         53    481       John     XYZ  04-Oct-09 
71         76    566       Tom      XYZ  03-Feb-08 10-Oct-09 
76         71    566       Tom      ABC  11-Oct-09

Your code is:

SELECT  t_from.SalesCode,  t_from.SalesRep,  t_from.Dept as [From],  t_to.Dept as [To],  t_from.JoinDt as JoinDt,  t_to.JoinDt as TransferDt FROM Tenure t_from  JOIN  Tenure t_to  ON t_to.SalesRep = t_from.SalesRep  AND t_to.JoinDt = DATEADD(day,1,t_from.TransferDt) ; 
more ▼

answered Oct 27, 2009 at 05:02 AM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

That works!! Thank you very much :)
Oct 27, 2009 at 05:47 AM Vinod
If it works, mark it as the answer - you get extra reputation for doing so, which means you'll be able to do more on the site!
Oct 27, 2009 at 06:58 AM Melvyn Harbour 1 ♦♦
(comments are locked)
10|1200 characters needed characters left
SELECT T1.Salescode  ,T1.SalesRep  ,T1.Dept as [From]  ,T2.Dept as [To]  ,T1.JoinDt as [JoinDt]  ,T2.JoinDt as [TransferDt] FROM tbl_Staff_transfer_dtls T1 INNER JOIN tbl_Staff_transfer_dtls T2 ON T1.SalesRep = T2.SalesRep AND T2.JoinDt= DATEADD(day,1,T1.TransferDt) 
more ▼

answered Oct 27, 2009 at 07:50 AM

Venkat 1 gravatar image

Venkat 1
31 1 1 1

(comments are locked)
10|1200 characters needed characters left
Select SalesCode, SalesRep, [From], [To], JoinDt, TransferDt FROM transferDetails

Or am I missing something here? You have to put brackets around FROM since From is a reserved word. I also suggest you add a where clause if you don't want every record from the table.

Select SalesCode, SalesRep, [From], [To], JoinDt, TransferDt FROM transferDetails WHERE SalesDep='something' 
more ▼

answered Oct 27, 2009 at 04:55 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 35 37 48

Håkan, He wants the [From] and [To] fields from separate rows.
Oct 27, 2009 at 05:05 AM Rob Farley
Ok, I missed that part because of the formatting issues.
Oct 27, 2009 at 05:18 AM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1948
x986

asked: Oct 27, 2009 at 04:32 AM

Seen: 918 times

Last Updated: Oct 27, 2009 at 10:42 AM