Hiya Svein

As usual you have made my day. I used your latest query and the result
is below and it is working very fine for me, thanks a million. Please
see it below (:iID is an artifact from using it from ReportManager).

Kind regards
Bhavbhuti

WITH SumQry(iItemID, iRepresentativeID, bProgQty, bQty, bProgFreeQty,
bFreeQty, bProgAmt, bAmt, bProgFreeAmt, bFreeAmt) as
(SELECT sSII.iItemID, tSI.iRepresentativeID
, SUM(CASE WHEN CAST(tSI.tDt AS DATE) BETWEEN '2009.04.01' AND
'2009.12.31'
AND sSII.cSaleType <> 'FREE' THEN
sSII.bQty ELSE 0 END)
, SUM(CASE WHEN CAST(tSI.tDt AS DATE) BETWEEN '2010.01.01' AND
'2010.01.31'
AND sSII.cSaleType <> 'FREE' THEN
sSII.bQty ELSE 0 END)
, SUM(CASE WHEN CAST(tSI.tDt AS DATE) BETWEEN '2009.04.01' AND
'2009.12.31'
AND sSII.cSaleType = 'FREE' THEN
sSII.bQty ELSE 0 END)
, SUM(CASE WHEN CAST(tSI.tDt AS DATE) BETWEEN '2010.01.01' AND
'2010.01.31'
AND sSII.cSaleType = 'FREE' THEN
sSII.bQty ELSE 0 END)
, SUM(CASE WHEN CAST(tSI.tDt AS DATE) BETWEEN '2009.04.01' AND
'2009.12.31'
AND sSII.cSaleType <> 'FREE' THEN
sSII.bAmt ELSE 0 END)
, SUM(CASE WHEN CAST(tSI.tDt AS DATE) BETWEEN '2010.01.01' AND
'2010.01.31'
AND sSII.cSaleType <> 'FREE' THEN
sSII.bAmt ELSE 0 END)
, SUM(CASE WHEN CAST(tSI.tDt AS DATE) BETWEEN '2009.04.01' AND
'2009.12.31'
AND sSII.cSaleType = 'FREE' THEN
sSII.bAmt ELSE 0 END)
, SUM(CASE WHEN CAST(tSI.tDt AS DATE) BETWEEN '2010.01.01' AND
'2010.01.31'
AND sSII.cSaleType = 'FREE' THEN
sSII.bAmt ELSE 0 END)
FROM sSaleInvoiceItem sSII
JOIN tSaleInvoice tSI
ON tSI.iID = sSII.iPID
AND tSI.iRepresentativeID = :iID
JOIN mAccounts mA
ON mA.iID = tSI.iRepresentativeID
GROUP BY 1, 2)

SELECT mI.*, sQ.iRepresentativeID, sQ.bQty, sQ.bProgQty, sQ.bFreeQty,
sQ.bProgFreeQty
, sQ.bAmt, sQ.bProgAmt, sQ.bFreeAmt, sQ.bProgFreeAmt
FROM mItems mI
LEFT JOIN SumQry sQ
ON mI.iID = sQ.iItemID
WHERE mI.cTypeID IN ('FIN', 'SAMPLE')
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)

On 31/01/2010 07:02 am, Svein Erling wrote:
>
> 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?):
>