Page 2 of 2

Re: File handling vs SQL query?

PostPosted: Wed Nov 02, 2016 9:00 pm
by steve-myers
Harhsaa_KK wrote:... Now to calculate the fee for each customer based on number of bulbs, ...

We have to remember the original purpose of electricity was to provide illumination after dark. In other words, to replace candles and other forms of illumination. So charging by the number of bulbs might have made sense, at least in 1890. I'm writing this about 11:15 in the morning on a bright sunny day. Now how many "bulbs" do a desktop computer, a modern LED based flat panel monitor, and the gadgets to talk to the internet represent?

Re: File handling vs SQL query?

PostPosted: Thu Apr 06, 2017 8:16 pm
by xRDK
Not sure about all the COBOL stuff but if you wanted to do this in SQL (assuming the bulb cost is the same for all bulbs);

SELECT CUST_NUMBER, SUM(NUMBER_OF_BULBS) * COST_OF_BULB
FROM MYTABLE
GROUP BY CUST_NUMBER
ORDER BY CUST_NUMBER

Where COST_OF_BULB is what you wanna charge.

Let's say there's more than meets the eye here, and you're table has;

CUST_NUMBER, BULB_TYPE, NUMBER_OF_BULBS

And you have a bulb table with costs;

BULB_TYPE, COST_OF_MY_BULBZ

Then;

SELECT CUST_NUMBER, BULB_TYPE,
SUM(NUMBER_OF_BULBS) * (SELECT COST_OF_MY_BULBZ
FROM BULBS Z
WHERE Z.BULB_TYPE = A.BULB_TYPE)

FROM YOURTABLE A
GROUP BY CUST_NUMBER, BULB_TYPE
ORDER BY CUST_NUMBER, BULB_TYPE