Hi Team,

I am trying to achieve the following without using sub query.

For a funding, I would like to select the latest Letter created date and the ‘earliest worklist created since letter created’ date for a funding.

FundingId Leter (1, 1/1/2009 )(1, 5/5/2009) (1, 8/8/2009) (2, 3/3/2009)

FundingId WorkList (1, 5/5/2009 ) (1, 9/9/2009) (1, 10/10/2009) (2, 2/2/2009)

Expected Result - FundingId Leter WorkList (1, 8/8/2009, 9/9/2009)

I wrote a query as follows. It has a bug. It will omit those FundingId for which the minimum WorkList date is less than latest Letter date (even though it has another worklist with greater than letter created date).

CREATE TABLE #Funding( [Funding_ID] [int] IDENTITY(1,1) NOT NULL, [Funding_No] [int] NOT NULL, CONSTRAINT [PK_Center_Center_ID] PRIMARY KEY NONCLUSTERED ([Funding_ID] ASC) ) ON [PRIMARY]

CREATE TABLE #Letter( [Letter_ID] [int] IDENTITY(1,1) NOT NULL, [Funding_ID] [int] NOT NULL, [CreatedDt] [SMALLDATETIME], CONSTRAINT [PK_Letter_Letter_ID] PRIMARY KEY NONCLUSTERED ([Letter_ID] ASC) ) ON [PRIMARY]

CREATE TABLE #WorkList( [WorkList_ID] [int] IDENTITY(1,1) NOT NULL, [Funding_ID] [int] NOT NULL, [CreatedDt] [SMALLDATETIME], CONSTRAINT [PK_WorkList_WorkList_ID] PRIMARY KEY NONCLUSTERED ([WorkList_ID] ASC) ) ON [PRIMARY]

SELECT F.Funding_ID,

Funding_No,

MAX (L.CreatedDt),

MIN(W.CreatedDt)

FROM #Funding F

INNER JOIN #Letter L ON L.Funding_ID = F.Funding_ID

LEFT OUTER JOIN #WorkList W ON W.Funding_ID = F.Funding_ID

GROUP BY F.Funding_ID,Funding_No

HAVING MIN(W.CreatedDt) > MAX (L.CreatedDt)

How can I write a correct query without using subquery?

Please help

Thanks

Lijo