Reference of SubQuery in a Query

  • 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..

  • Please don't cross post. It just waste people's time and fragments replies. Many people here read all the forums

    No replies to this thread plese. Direct replies to the following thread

    http://www.sqlservercentral.com/Forums/Topic498549-8-1.aspxthanks.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply