Hi again Bhavbhuti!

I still don't get what you really looking for, if you want the sales for each representative, you start with the table of your representatives (which I don't know what is named) and then LEFT JOIN that to mItems. However, that would be a different kind of query than the queries we've discussed so far (typically, you would remove references to mItems from the WHERE clause altogether). One thing I do not understand in your example, is how you can determine that Rep1 should have 4 records, whereas Rep2 should only have one. Why does Rep1 have NULL for ItemD whereas Rep2 doesn't?

Anyway, even though I don't quite understand what your looking for, I'll suggest another query (which is basically a simplification of your latest query) with iRepresentativeID added and your last reference to mI.cTypeID removed (you duplicated that line in your select - maybe you wanted it within your NOT EXISTS and refer to d rather than mI?):

WITH SumQry(iItemID, iRepresentativeID, bProgQty, bQty, bProgValue, bValue) as
(SELECT sSII.iItemID, tSI.iRepresentativeID,
SUM(case
when CAST(tSI.tDt AS DATE) BETWEEN '1.4.2009' AND '31.12.2009' then
sSII.bQty else 0 end),
SUM(case
when CAST(tSI.tDt AS DATE) BETWEEN '1.1.2010' AND '31.1.2010' then
sSII.bQty else 0 end),
SUM(case
when CAST(tSI.tDt AS DATE) BETWEEN '1.4.2009' AND '31.12.2009' then
sSII.bAmt else 0 end),
SUM(case
when CAST(tSI.tDt AS DATE) BETWEEN '1.1.2010' AND '31.1.2010' then
sSII.bAmt else 0 end)
FROM sSaleInvoiceItem sSII
JOIN tSaleInvoice tSI
ON tSI.iID = sSII.iPID
JOIN mAccounts mA
ON mA.iID = tSI.iRepresentativeID)

SELECT mI.*, sq.iRepresentativeID, sq.bQty, sq.bProgQty, sq.bValue, sq.bProgValue
FROM mItems mI
LEFT JOIN SumQry sq on mI.iID = sq.iItemID
WHERE mI.cTypeID = 'FIN'
AND (mI.lHierarchialStock = 1
OR NOT EXISTS (SELECT 1
FROM mItems d
WHERE d.iPID = mI.iID))
ORDER BY UPPER(mI.cDesc), UPPER(mI.cPack)

In my opinion this is quite a simple query, my problem is understanding what output set you're looking for.

HTH,
Set