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