Suppose you have a table that contains Job ID, when it was started, and when the Job ended. Now you want the difference in days between when it was started and when it ended. You can use the PIVOT command in SQL. From there you can then derive the difference in days, hours, minutes or any combination.
[code language=”sql”]
CREATE TABLE #Temp(
NotesID int,
Jobid int,
Notes varchar(150),
Dates datetime
)
Insert into #Temp Values (1,1,’Job Started’,’01/10/2013′)
Insert into #Temp Values (2,1,’Job Ended’,’05/10/2013′)
Insert into #Temp Values (3,2,’Job Started’,’02/10/2013′)
Insert into #Temp Values (4,2,’Job Ended’,’03/10/2013′)
Insert into #Temp Values (5,3,’Job Started’,’04/10/2013′)
Insert into #Temp Values (6,3,’Job Ended’,’05/10/2013′)
Insert into #Temp Values (7,4,’Job Started’,’05/10/2013′)
Insert into #Temp Values (8,4,’Job Ended’,’06/10/2013′)
Insert into #Temp Values (9,1,’Job Started’,’06/10/2013′)
Insert into #Temp Values (10,1,’Job Ended’,’06/10/2013′)
Select *, datediff(day,[Job Started],[Job Ended]) as difference from (
Select * from
(
Select jobid,Notes,Dates from Temp
) x
pivot
(
min(Dates)
for Notes in ([Job Started],[Job Ended])
) p
) ee
Drop table #temp
[/code]
You can look at here as well.
Looking for quality web hosting? Look no further than Arvixe Web Hosting!