May 11, 2008 at 11:05 pm
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..
May 12, 2008 at 1:11 am
Try this:
SELECT DISTINCT
bg.bg_id [BugId],
bgAuditOpen.bg_added_dt [Open],
bgAuditClose.bg_added_dt [Close],
DATEDIFF(dd, bgAuditClose.bg_added_dt, bgAuditOpen.bg_added_dt) [NumberOfDays]
FROM
bugs bg
INNER JOIN Bug_Status_Audit bgAuditOpen ON bg.bg_id = bgAuditOpen.BgStAd_bg_id AND bgAuditOpen.BgStAd_New_st_id = 1
INNER JOIN Bug_Status_Audit bgAuditClose ON bg.bg_id = bgAuditClose.BgStAd_id AND bgAuditClose.BgStAd_New_st_id = 5
WHERE
bgAuditOpen.bg_added_dt IS NOT NULL
AND bgAuditClose.bg_added_dt IS NOT NULL
May 12, 2008 at 8:45 am
SELECT bg.bg_id AS BugID,
MIN(CASE WHEN BgStAd_New_st_id = 1 THEN bgAudit.bgstad_added_dt ELSE NULL END) AS [Open]
MAX(CASE WHEN BgStAd_New_st_id = 5 THEN bgAudit.bgstad_added_dt ELSE NULL END) AS [Close]
FROM Bugs AS bg
INNER JOIN Bug_Status_Audit AS bgAudit ON bgAudit.BgStAd_bg_id = bg.bg_id
GROUP BY bg.bg_id
N 56°04'39.16"
E 12°55'05.25"
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply