Hi
For decades, this type of problem has been the meat and vegetables of
discussions about SQL programming and design.
One writer on this subject has stood out, thanks to his mental clarity
and ability to set out complicated concepts in a readily comprehensible
manner.
His name is Joe Celko <http://en.wikipedia.org/wiki/Joe_Celko>. He has
published several books, including SQL For Smarties
<http://en.wikipedia.org/wiki/Special:BookSources/9780120887972> and SQL
Puzzles & Answer
<http://en.wikipedia.org/wiki/Special:BookSources/9780123735966>s, you
may even find them in .pdf format online.
Read some of what Joe has written and you will find answers to this sort
of problem.
Incidentally, I can't remember a problem like this since we stored data
on open reel tape systems. We used to set multiway masterfile update
problems for first year Information System students, before they had
widespread access to ISAM and database hosted exercises. What you have
is a 'batch' system, you might find it worthwhile rolling (virtually) A
& B together and then grouping C. The trick will be in the way you
handle grouping, Joe Cleko has an entire book on this subject - Thinking
in Sets: Auxiliary, Temporal & Virtual Tables in SQL
<http://en.wikipedia.org/wiki/Special:BookSources/9780123741370>.
This blog <http://joecelkothesqlapprentice.blogspot.co.uk/>(based on his
solutions) is worth a look.
Robin St.Clair
On 18/11/2013 02:16, Hengky Liwandouw wrote:
Dear Friends,
Please help for the select command, as i had tried many times and always can
not display the result as what i want.
I am looking for the solution on google but still can not found the right
answer to solve the problem.
I have 3 tables :
Table A
ProductID
ProductName
SupplierID
Table B
ProductID
InitialStock
Table C
ProductID
Date
In
Out
1. I want to select all productID from Table A where supplierID='XXX'.
2. Based on list from Step.1 : sum the initialstock from Table B
3. Based on list from Step 1 : Sum (in-out) from Table C where date <'BEGINNING
DATE'
4. Based on list from Step 1 : Sum (in) and sum(out) from Table C where date
between 'BEGINNING DATE' and 'ENDING DATE'
So the result will look like this :
ProductID ProductName SumofIntialStock sum(in-Out)<beginningdate
SumofIN SumofOut
xxxx xxxxxxxxxxxxx 99 99
99 99
xxxx xxxxxxxxxxxxx 99 99
99 99
xxxx xxxxxxxxxxxxx 99 99
99 99
xxxx xxxxxxxxxxxxx 99 99
99 99
What command to get result like this ? i have tried crosstab function but not
success too :(
Thanks in advance
