Hi Svein

Thanks for your help in pushing me forward. I have tried your
suggestion and I have modified it (see below). Before reaching this
stage I had your query return to me a huge set so I did the WHERE clause
and I was able to tone down the results (all non-'FIN' items were there
too). It had 3 kinds of records. iRepresentativeID was NULL: This was
for the items that were never sold. iRepresentativeID was 0: This was
for item that were sold but the bills did not have any representative
attached. iRepresentativeID had a value: This was for bills with
representatives selected, this group of data records had only as many
records per representative as that they had sold. So my original
requirement to get repeat sets of mItems records for each representative
with bQty filled in for the items actually sold was yet not achieved
like below:

Rep1, ItemA, NULL
Rep1, ItemB, 10
Rep1, ItemC, 150
Rep1, ItemD, NULL
Rep2, ItemA, 55
Rep3, ItemB, NULL
Rep4, ItemC, 25
Rep5, ItemD, NULL

But instead something like
NULL, ItemD, NULL
0, ItemA, 255
Rep1, ItemB, 10
Rep1, ItemC, 150
Rep2, ItemA, 55
Rep4, ItemC, 25

Plus my story does not end there, the end result is I have to provide
Qty. and Value for a given period plus Qty. and Value before the start
of the given period, so if a rep has sold 10 this month, I need to 10 as
well as 50 which was sold in the previous months starting April 09
(50+10 = 60 which is total sale to date since April 09). I tried to do
double CTE but I guess that does not work, so I planned to do 4 of your
suggested CTE but the missing (unsold) items was still an issue. So I
continue plodding with my mammoth query (also below your updated query)
for your reference and give you an idea of my required output. This
query if run in FlameRobin returns dataset from only one representative
but luckily when using the query in ReportManager for reporting I can
add a line
AND tSaleInvoice.iRepresentativeID = :iID
for each of the sub-select and I achieve my final result. Of course
ideally I wanted only one big select that would return the whole set so
it is browse-able too in FlameRobin but for the moment it is good enough.

If you have any further suggestions I would love to hear them, else rest
assured I have my result, maybe not as clean as could have been achieved.

Thanks for your help previously and in this instance.

Kind regards
Bhavbhuti

**************

WITH SumbQty(iRepresentativeID, iItemID, bQtySum) as
(SELECT si.iRepresentativeID, sii.iItemID, SUM(sii.bQty)
FROM tSaleInvoice si
JOIN sSaleInvoiceItem sii
ON si.iID = sii.iPID
WHERE CAST(si.tDt AS DATE) BETWEEN '1.1.2010' AND '31.1.2010'
GROUP BY 1, 2)

SELECT si.iRepresentativeID, UPPER(i.cDesc) as cDesc, UPPER(i.cPack) as
cPack, sq.bQtySum as bQty
FROM mItems i
LEFT JOIN sSaleInvoiceItem sii
ON i.iID = sii.iItemID
LEFT JOIN tSaleInvoice si
ON sii.iPID = si.iID
LEFT JOIN SumbQty sq
ON si.iRepresentativeID = sq.iRepresentativeID
AND sii.iItemID = sq.iItemID
WHERE i.cTypeID = 'FIN'
AND (i.lHierarchialStock = 1
OR NOT EXISTS (SELECT 1
FROM mItems d
WHERE d.iPID = i.iID))
-- AND COALESCE(si.iRepresentativeID, 0) > 0
ORDER BY 1, 2, 3

***********************

SELECT mI.*
, (SELECT SUM(sSII.bQty)
FROM sSaleInvoiceItem sSII
JOIN tSaleInvoice tSI
ON tSI.iID = sSII.iPID
JOIN mAccounts mA
ON mA.iID = tSI.iRepresentativeID
WHERE CAST(tSI.tDt AS DATE) BETWEEN '1.1.2010' AND '31.1.2010'
AND sSII.iItemID = mI.iID) AS bQty
, (SELECT SUM(sSII.bQty)
FROM sSaleInvoiceItem sSII
JOIN tSaleInvoice tSI
ON tSI.iID = sSII.iPID
JOIN mAccounts mA
ON mA.iID = tSI.iRepresentativeID
WHERE CAST(tSI.tDt AS DATE) BETWEEN '1.4.2009' AND '31.12.2009'
AND sSII.iItemID = mI.iID) AS bProgQty
, (SELECT SUM(sSII.bAmt)
FROM sSaleInvoiceItem sSII
JOIN tSaleInvoice tSI
ON tSI.iID = sSII.iPID
JOIN mAccounts mA
ON mA.iID = tSI.iRepresentativeID
WHERE CAST(tSI.tDt AS DATE) BETWEEN '1.1.2010' AND '31.1.2010'
AND sSII.iItemID = mI.iID) AS bValue
, (SELECT SUM(sSII.bAmt)
FROM sSaleInvoiceItem sSII
JOIN tSaleInvoice tSI
ON tSI.iID = sSII.iPID
JOIN mAccounts mA
ON mA.iID = tSI.iRepresentativeID
WHERE CAST(tSI.tDt AS DATE) BETWEEN '1.4.2009' AND '31.12.2009'
AND sSII.iItemID = mI.iID) AS bProgValue
FROM mItems mI
WHERE mI.cTypeID IN ('FIN')
AND (mI.lHierarchialStock = 1
OR NOT EXISTS (SELECT 1
FROM mItems d
WHERE d.iPID = mI.iID))
AND mI.cTypeID IN ('FIN')
ORDER BY UPPER(mI.cDesc), UPPER(mI.cPack)