SQL query to get dif between dates in select statment

Question

I have a two tables call RFS and RFS_History.

 RFS_id  | name 
 --------+--------
    12   |  xx
    14   |  yy
    15   |  zz

figure 1 :RFS table

     RFS_id  |  gate | End    | start 
     --------+-------+--------+-------
        12   |  aa   | 19/02  | 20/03
        12   |  bb   | 30/01  | 12/08
        12   |  cc   | 30/01  | 12/08
        13   |  aa   | 30/01  | 12/08
        12   |  dd   | 30/01  | 12/08

figure 2 :RFS history

My initial query is a select * query to get information where FRSname ='xx'

SELECT * FROM RFS, RFSHistory 
WHERE RFSname="xx"  And RFShistory.RFS_ID=RFS.RFS_ID

result is:

     RFS_id  |  gate | End    | start 
     --------+-------+--------+-------
        12   |  aa   | 19/02  | 19/01
        12   |  bb   | 12/04  | 12/02
        12   |  cc   | 20/03  | 12/03
        12   |  dd   | 30/09  | 12/08

figure 3

however I want to get a result like bellow format :

 RFS_id  | gate_aa | gate_bb | gate_cc | gate_dd
 ----------------------------------------------
    12   | 30 days  |  60dyas | 8days   | 18days

gate_aa is duraion and it gets from start - end date. Please help me to write single query to get this result.

Answer 1

Use datediff() to get date difference and Pivot() to convert row into cloumn like here in your case gate wise column

Sample Syntax

SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS DiffDate

Answer 2

You can use the below query for get the difference b/w dates

SELECT RFS.ID,(RFS_HISTORY.end_t-RFS_HISTORY.start_t) AS DiffDate,gate FROM RFS, RFS_HISTORY WHERE name='aa' And RFS_HISTORY.ID=RFS.ID group by RFS.ID,gate,RFS_HISTORY.end_t,RFS_HISTORY.start_t

Answer 3

I think you want to convert rows into columns on the values. This can be done with the help of pivoting.

SELECT * FROM RFS, RFSHistory
pivot  for columname on [values]

I actually forgot the syntax but you can google it