Hi,all
I have a following query ,
select distinct bg.bg_id [BugId],
(select top 1 bgAudit.bgstad_added_dt from Bug_Status_Audit bgAudit where bgAudit.BgStAd_bg_id=bg.bg_id and BgStAd_New_st_id=1) [Open] ,
(select top 1 bgAudit.bgstad_added_dt from Bug_Status_Audit bgAudit where bgAudit.BgStAd_bg_id=bg.bg_id and BgStAd_New_st_id=5) [Close]
from bugs bg inner join Bug_Status_Audit on bg.bg_id=BgStAd_bg_id
---------------------------------------------------------------------
above query return three column name BugId,Open,Close.
Column Open,Close come from Subquery. and of type DateTime.
I want to put in where clause that both Open,Close should not be null.
and i also want fourth column that use function datediff().
for above two thing, is their any way to use subquery in same query with their reference. or i need to enter same subquery in where Clause and DateDiff () function.
Thanks in advance..