[QUERY] Help...

[Lupin_3d]

I have 2 table:
1->Deals with ID,IDCompany,Concluded
2->DealDetail with ID,IDDeal,DateStart,DateEnd

I want to extract the list of Deals with the DateStart less than
Today, not concluded.
the list must have this fiels: IDCompany, DateStart,DateEnd

I've did this query:
------------
SELECT DISTINCT
D.IDcompany,
DSS.Start,
DSE.[End]
FROM tblDealDetail DD
RIGHT JOIN
(
SELECT
IDCompany,
MIN(DD.DataStart) Start
FROM tblDealDetail DD
LEFT JOIN tblDeals D
ON D.ID = DD.IDDeal
WHERE D.Concluded <> 1
GROUP BY IDCompany
) DSS
ON DSS.Start = DD.DataStart

RIGHT JOIN (
SELECT
IDCompany,
MIN(DD.DataEnd) [End]
FROM tblDealDetail DD
LEFT JOIN tblDeals D
ON D.ID = DD.IDDeal
WHERE D.Concluded <> 1
GROUP BY IDCompany
) DSE
ON DSE.[End] = DD.DataEnd

LEFT JOIN tblDeals D
ON DD.IDDeal = D.ID

WHERE D.IDCompany = DSS.IDCompany
------------
But when i have 2 deal with the 2 equal DateStart the resultset don't
show me the deal.

P.S. The datestart and dataend must belong to the same dealdetail...

Thanks
Lorenzo

[David Portas]

I'm not certain but it seems like you need something like this:

SELECT D.id, D.idcompany, MIN(T.datestart), MAX(T.dateend)
FROM tblDeals AS D
JOIN tblDealDetail AS T
ON D.id = T.iddeal
AND concluded = 0
GROUP BY D.id, D.idcompany
HAVING MIN(T.datestart)<'20040423'

If this doesn't answer your question it would help if you could post DDL
(CREATE TABLE statements for the tables), sample data (as INSERT statements)
and show the end result you require.

--
David Portas
SQL Server MVP
--