Calculation using joins and select statements SQL

Question

I did ask for a part of this question here just to take a push myself from the point where I was stuck. But, now I experience great problems to find a solution further. Well, I should say I have ended myself to a state where I could write one single complex query which was purely logic. But, now I in this case I am aware of the logic but couldnt implement through writing. Here is the description of my problem:

Problem :

Table1:

REFID      ARTIKEL      SUPPLIERID      ORGID      PIECES      COSTPRICE      DISCOUNT
126663      TV            SONY          922         6            110           2.5
126663      TV            Toshiba       922         4            75            2
126663      TV            SONY          922         10           80            1
126663      TV            LG            922         10           80            1
126663      TV            SONY          922         4           65            1.5
126663      TV            SONY          922         14           95            1.5

Table2:

REFID      ARTIKEL      SUPPLIERID      ORGID     STOCK_SUM_PIECES     
126663      TV            SONY          922         16            
126663      TV            Toshiba       922         20

TABLE3:

REFID      ARTIKEL      SUPPLIERID      ORGID      STORE    SALE_SUM_PIECES     DATE   
126663      TV            SONY          922        100          4            01.01.2014
126663      TV            Toshiba       922        100          3            01.01.2014

Operation to perform:

Firstly I need to sum up the STOCK_SUM_PIECES and SALES_SUM_PIECES from table2 and table3 respectively.For example: SUM_RESULT is 16+4=20 for SONY from TABLE2 and TABLE3 Next, distribute the SUM_RESULT onto the rows in the table1 as shown here. In the process of distributing, once the value reaches zero, it does mean that only those rows are valid for that "SUM_PIECES" in TABLE2. For example: SUM_RESULT 20 for SONY is valid only for three rows in TABLE1 since number of pieces sum up to 20 i.e 6+10+4=20. Now select only these three rows and calculate the average of Cost price and Discount in TABLE1.

This is the most complex part I am not able to solve. I am not asking for one to write a query instead to give me an idea or method to follow to end up with the solution.

I am currently successful in making appropriate joins but cannot perform calculation during join. This is complex to me only because, I cannot use curosrs or for loop and so on since I have to make it performance efficient and also data is huge maybe billion rows. So it has to be done using joins and select statements. Any suggestions Please help!

Thanks

Atiris · Accepted Answer

OK, first part is easy, use UNION ALL and create SUM only over fields you need for fastest run. Second part is a problem. You need a window function.

First part might look like:

SELECT SUPPLIERID, SUM(TO_SUM) SUM_PIECES FROM (
  SELECT STOCK_SUM_PIECES AS TO_SUM, SUPPLIERID
    FROM TABLE2
  UNION ALL
  SELECT SALE_SUM_PIECES AS TO_SUM, SUPPLIERID
    FROM TABLE3
) GROUP CONDITION ...

This will be your table X.

If you want to distribute this sum into table 1, you are depend on sort of table 1. I suppose that your table is sorted as you want and you need split this number into rows in your order.

So, now you need a windows functions. Which get sum for each row from beginning to actual row. Look at this:

SELECT ALL_WHAT_YOU_NEED_TO_RETURN
     , SUPPLIERID
     , SUM(PIECES) 
       OVER (ORDER BY YOUR_ORDER 
              ROWS BETWEEN preceding AND current row) AS SUM_FROM_BEGIN
  FROM TABLE1
 ORDER BY YOUR_ORDER_CONDITION

This will be your table Y.
OK, now we can put it together and return only this rows from table Y which sum of pieces from beginning to actual row is lower or the same as count of pieces from table X:

SELECT ALL_WHAT_YOU_NEED_TO_RETURN
  FROM (SELECT Y.ALL_WHAT_YOU_NEED_TO_RETURN
             , Y.SUPPLIERID
             , SUM(Y.PIECES) 
               OVER (ORDER BY Y.YOUR_ORDER 
                     PARTITION BY Y.SUPPLIERID
                     ROWS BETWEEN PRECEDING AND CURRENT ROW) AS SUM_FROM_BEGIN
             , X.SUM_PIECES
          FROM TABLE1 Y
             , (SELECT SUPPLIERID, SUM(TO_SUM) SUM_PIECES 
                  FROM (SELECT STOCK_SUM_PIECES AS TO_SUM, SUPPLIERID
                          FROM TABLE2
                         UNION ALL
                        SELECT SALE_SUM_PIECES AS TO_SUM, SUPPLIERID
                          FROM TABLE3
                       )
                 GROUP BY SUPPLIERID
               ) X
         WHERE Y.SUPPLIERID = X.SUPPLIERID
    ) WHERE SUM_FROM_BEGIN <= SUM_PIECES

The most important is the last condition and window function.
I don´t know how usable is it in sql server, but in Oracle it works fast enough.

Calculation using joins and select statements SQL

Answers (2)

Related Questions