Aggregate Function

[yorelsemaj]

I am seeking your help in building a script in MS SQL that reads an Invoice Detail database and Sums the Quantity Sold by Item by Date. Quantity Sold column stores figures in absolute values and another column (TransType) contains 'C' or 'S' signifying Credit or Invoice. The script below produces a detail showing quantites as negative if TransType is 'C' and positive if TransType is 'S'. How do I now get Group By ItemNo? Please simplify script if necessary.

SELECT ItemNo, Qty AS TotalSold,
CASE TransType
WHEN 'C' THEN CAST((Qty * - 1) AS int)
WHEN 'S' THEN CAST(Qty AS int)
ELSE CAST(Qty AS int)
END AS Sold
FROM InvoiceDetail
ORDER BY ItemNo

Results should look like:

ItemNo Sold
1001 10
1002 15
1003 5

[skhanal]

Do you need to show TransType?. Your sample report did not list it.

SELECT ItemNo, Qty AS TotalSold,
FROM InvoiceDetail
GROUP By ItemNo
ORDER BY ItemNo

should produce the desired report.

[yorelsemaj]

Thanks Skhanal, for responding.

I don't need to show TransType. The TransType is used in the script ONLY to determine if the transaction is a credit or an invoice.

[shamshe]

Hi there,
here is your query:

SELECT ItemNo, SUM(
CASE TransType
WHEN 'C' THEN CAST((Qty * - 1) AS int)
WHEN 'S' THEN CAST(Qty AS int)
ELSE CAST(Qty AS int)
END ) AS TotalSold
FROM InvoiceDetail
GROUP BY ItemNo
ORDER BY ItemNo

[yorelsemaj]

Sorry about this fellows....

I now have a further understanding of what exist in the database and what is to be achieve is not as simple as just accumulating the sales by ItemNo showing Net Sales. The database also has two other fields....one that tells whether the sale was a Unit or Piece, called UnitFlag. Another that tells total Pieces in a Unit called the PerCode. The flag in the UnitFlag field is 'T' if the sale was Unit and 'F' if the sale was a Piece. For example, 14 Pieces (PerCode) makes up a Unit for a particular Item. The aim is to be able to tell the Total Units sold and the Total Pieces sold. My first thought was to convert everything to Pieces while doing the net qty sold....That would mean, multiplying all transaction having UnitFlag as 'T' by the PerCode...those having UnitFlag 'F' are already in Pieces. If we then Group By ItemNo and Sum Qty, that TotalQty would be in Pieces. Then, divide the Total Pieces Sold by the PerCode to get the Integer portion, which will be the Total Units and then the remainder would be the Total Pieces. So the results would look like:

ItemNo TotalQtySold
1002 40 (Units)
1002 10 (Pieces)

Still taking into consideration, the fact that the quantities are stored in absolute values and the field TransType determines if transaction is a Sale 'S' or credi 'C'.

I hope this is not too much to ask.

Thanks in advance.

[yorelsemaj]

I am having a hard time putting this script together as I am not really an expert. Has anyone been able to achieve anything much so far?

Thanks in advance.

[shamshe]

Hi,

I think there a few problems here:
- it seems that you don't know what your goal is and what data you have; for instance what if there are different types of units (10 pieces per unit, 20 pieces per unit, etc);
- the look of result you gave unreadable from human procpective and requries more complex structure such as stored procedure with cursor to achieve that;

- do you really need those units and pieces

If result would look like:

ItemNO TotalUnitSold PiecesSold
1002 40 10
1003 20 5

then it coud be done by modifying the query I posted eralier, given that there is only one type of unit for each Item.

[yorelsemaj]

Hi Shamshe;

Indeed, different amount of Pieces makes up a Unit for different Items. For example, for Item 1002, it takes 14 Pieces to make up Unit; for Item 1003, it takes 10 Pieces to make up Unit. The next thing I should mention is, the system uses the same ItemNo to represent both Units and Pieces. The script should separate that by using the code 1002 from the table to represent Units and a separate code (not in master file) to represent Pieces. So then, I give you a before and after scenario below:

ItemNo Type Qty UnitFlag PerCode TotalPieces
1002 S 4 T 14 56
1002 S 3 F 14 3
1002 S 3 F 14 3
10 62



ItemNo TotalQtySold
Units 1002U 4
Pieces 1002P 6

Explanation:
The total pieces sold is divided by the PerCode and the Integer portion is in Units, Item 1002U and the remainder portion is in Pieces, Item 1002P.

[shamshe]

As I said before your output will require more stored procedure to achieve. But try this, may be it gives you an idea what I'm talking about:
SELECT DBJ1.ItemId,
CEILING(SUM(CASE TransType
WHEN 'C' THEN
CASE UnitFlag
WHEN 'U' THEN CAST((Qty * - 1)*DBJ1.PreCode AS int)
ELSE CAST((Qty * - 1) AS int)
END
WHEN 'S' THEN
CASE UnitFlag
WHEN 'U' THEN CAST(Qty*DBJ1.PreCode AS int)
ELSE CAST(Qty AS int)
END
END)/d2.PreCode) AS TotalUnitsSold, (SUM(CASE TransType
WHEN 'C' THEN
CASE UnitFlag
WHEN 'U' THEN CAST((Qty * - 1)*DBJ1.PreCode AS int)
ELSE CAST((Qty * - 1) AS int)
END
WHEN 'S' THEN
CASE UnitFlag
WHEN 'U' THEN CAST(Qty*DBJ1.PreCode AS int)
ELSE CAST(Qty AS int)
END
END)% d2.PreCode) AS TotalPiecesSold
FROM InvoiceDetail DBJ1
, (SELECT ItemId, PreCode FROM InvoiceDetail WHERE UnitFlag='U') d2
where DBJ1.ItemId=d2.ItemId
group by DBJ1.ItemId,d2.PreCode
ORDER BY DBJ1.ItemId;

[yorelsemaj]

Shamshe;

The script you sent really help me to achieve the objective. Had to make minor adjustments, the script really created a good foundation.

I appreciate the help and your patience.

Thanks again.

[shamshe]

you're welcome.
glad it helped