i have one table with the following values:
job name start_dt end_dt
gggggg 4/5/2013 5/5/2013
iiiii 6/5/13 7/8/13
I want to subract the start_dt of gggggg from end_dt of iiiii.
I am using this query:
select a.job_nm,(b.end_dt-a.start_dt) as difference from JOB a,b
where a.job_nm='ggggg' and b.job_nm='iiiii'.
This is giving me no output.
If this is SQL Server you can use DATEDIFF():
select a.job_nm,
datediff(day, a.start_dt, b.end_dt) as [difference]
from JOB a,b
where a.job_nm='ggggg'
and b.job_nm='iiiii'
One way to do this is conditional aggregation:
select (max(case when j.job_nm = 'iiiii' then b.end_dt end) -
max(case when j.job_nm = 'ggggg' then a.start_dt end)
) as difference
from Job j
where j.job_nm in ('ggggg', 'iiiii');
You can use the datediff() function, but for datetimes I find subtraction easier.
