use running sum to get consumables sold by equipment installation 1

[mparshley]

I don't know where to start with this.
My goal is to report the consumables sales dollars per equipment installed at a customer. Both are sold by us.

Table: customer name, sale date, part number, sale quantity, sales dollar.

The part number is used identify equipment vs. consumables.

The calculation is the "tot sales dollars for consumables / running sum of qty of equipment" by customer.

I need to be able to report and graph by various time periods.

Thank you. I usually use Crystal both this does not seem to be possible.

 

[Remou]

Please supply some sample data indicating which part numbers are equipment and which consumables. Some indication of expected result would be useful.

 

[mparshley]

I'll try again.
Our company sells lab equipment. A customer buys 1 or more pieces of the equipment. They also buy consumables. We want to know at any point in time what is their purchase of consumables per piece of equipment.

Each record is per part purchased: CUSTOMERNAME,SALEDATE,PARTNO,NETQTY,NETSALE

Equipment partno= mvs-100 or mvs-200. consumables = mvs-201 to mvs-231.

?? IF PARTNO IN (MVS-100,MVS-200) DSUM NETQTY
IF PARTNO IN (MVS-201 TO MVS-231) SUM NETSALE
PEREQUIP = SUM NETSALE/DSUM NET QTY

The result needs to be used in graphs and reportable by various timeperiods, yearly, qtrly, etc. I also need to be able to use it in other calculations.
I mention this becuase I have done this in Crystal reports but because the running total is created while printing I can't use it for charting.
Thanks again.

 

[Remou]

Sample data makes life much easier. However, do you mean something like:

SELECT b.CustomerName, b.SaleDate, b.SNetSale, 
	(SELECT SUM(NetQTY) 
	FROM tblTable 
	WHERE CustomerName=b.CustomerName 
	AND SaleDate<=b.SaleDate 
	AND PartNo 
	IN ("MVS-100","MVS-200")) AS TotQty, 
	[SNetSale]/[TotQty] AS Ans
FROM 
(SELECT CustomerName, SaleDate, Sum(NetSale) AS SNetSale
FROM tblTable
WHERE PARTNO BETWEEN "MVS-201" AND "MVS-231" 
GROUP BY CustomerName, SaleDate) B;

 

[mparshley]

Thank you!!!I'll try it tomorrow. I'm sorry I didn't supply same code at first. I really didn't know where to start.

 

[lespaul]

what he was asking for was something like this:
[tt]
TableName
FieldName1 FieldName2 FieldName3 FieldName4
data data data data
data data data data
data data data data
data data data data

TableName2
FieldName4 FieldName5 FieldName6 FieldName7
data data data data
data data data data
data data data data
data data data data

Desired Result Set
Field2 Field4 Field7 CalculatedField
data data data data
data data data data
data data data data
data data data data[/tt]

and any formula explanations that may be needed to understand any calculations.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[mparshley]

I gave your solution a try and it works perfectly. Thank you so much.