"Kriste L" wrote:

> Hi Everyone,
>
> I've existing data for a ordering system. Currently there are "Order" & "Distribute" tables.
> "Order" is keeping transaction of what product are brought and how much quantity.
> "Distribute" is a table that records how the product are issue to or return by each personnel.
>
> The operation is a FIFO method of distribution from the Order.
> And now I need to generate a report to show the product movement in FIFO method.
>
> Attached are sample DDL and the required report format.
> Does anyone have any idea how can this be done?
>
>
> Table: Order
> ************
> id   date_order product quantity
> --   ---------- ------- --------
> 11   2005-01-01 AAA     10
> 12   2005-01-03 AAA     20
> 13   2005-01-05 AAA     30
>
> Table: Distribute
> *****************
> id  date_distribute product quantity personnel_id
> --  --------------- ------- -------- ------------
> 51  2005-01-01      AAA     10       P_1
> 52  2005-01-04      AAA     13       P_2
> 53  2005-01-05      AAA      3       P_3
> 54  2005-01-06      AAA     -2       P_4
> 55  2005-01-07      AAA      8       P_5
>
> Required movement report
> ************************
> Order.id date_order Order.quantity Distribute.id date_disributed Distribute.quantity Remaining Order qty
> -------- ---------- -------------- ------------- --------------- ------------------- -------------------
> 11       2005-01-01 10             51            2005-01-01      10                  10 [20 -10]
> 12       2005-01-03 20             52            2005-01-04      13                  7  [10 - 13]
>                                    53            2005-01-05      3                   4  [7 - 3]
>                                    54            2005-01-06      -2                  6  [4 + 2]
>                                    55            2005-01-07      6 [partial]         0  [6 - 6]
> 13       2005-01-05 30             55            2005-01-07      2 [partial]         28 [30 - 2]
>                        
>
>
>
>
>
>

> > Table: Order
> > ************
> > id   date_order product quantity
> > --   ---------- ------- --------
> > 11   2005-01-01 AAA     10
> > 12   2005-01-03 AAA     20
> > 13   2005-01-05 AAA     30
> >
> > Table: Distribute
> > *****************
> > id  date_distribute product quantity personnel_id
> > --  --------------- ------- -------- ------------
> > 51  2005-01-01      AAA     10       P_1
> > 52  2005-01-04      AAA     13       P_2
> > 53  2005-01-05      AAA      3       P_3
> > 54  2005-01-06      AAA     -2       P_4
> > 55  2005-01-07      AAA      8       P_5
> >
> > Required movement report
> > ************************
> > Order.id date_order Order.quantity Distribute.id date_disributed Distribute.quantity Remaining Order qty
> > -------- ---------- -------------- ------------- --------------- ------------------- -------------------
> > 11       2005-01-01 10             51            2005-01-01      10                  0  [10 -10]
> > 12       2005-01-03 20             52            2005-01-04      13                  7  [20 - 13]
> >                                    53            2005-01-05      3                   4  [7 - 3]
> >                                    54            2005-01-06      -2                  6  [4 + 2]
> >                                    55            2005-01-07      6 [partial]         0  [6 - 6]
> > 13       2005-01-05 30             55            2005-01-07      2 [partial]         28 [30 - 2]
> >

"Kriste L" wrote:

> No, it's not just using order_by.
> It's something like deducting from Order quantity, and measure how effective is the order.
> Perhaps, following description can explain the scenario better
>
> The scenario is like this, eg,
> On 2005-01-01
>     - order with quantity 10 and distribute out 10 quantity, so remaining quantity in order = 0
> On 2005-01-03
>     - order with quantity 20, so remaining quantity in order = 20
>     - distribute out 13 quantity on 2005-01-04, so remaining quantity in order = 7   
>     - distribute out 3 quantity on 2005-01-05, so remaining quantity in order = 4
>     - withdraw distribute -2 quantity on 2005-01-06, so remaining quantity in order = 6
>     - distribute out 8 quantity out on 2005-01-07, as this order has remaining 6, so only 6 can be distributed out from this order, so this order remaining quantity = 0
>       the remaining 2 quantity will distribute out from next order
> On 2005-01-05
>     - distribute out outstanding quantity 2, so this order is now left with 28.
>
>
> > > Table: Order
> > > ************
> > > id   date_order product quantity
> > > --   ---------- ------- --------
> > > 11   2005-01-01 AAA     10
> > > 12   2005-01-03 AAA     20
> > > 13   2005-01-05 AAA     30
> > >
> > > Table: Distribute
> > > *****************
> > > id  date_distribute product quantity personnel_id
> > > --  --------------- ------- -------- ------------
> > > 51  2005-01-01      AAA     10       P_1
> > > 52  2005-01-04      AAA     13       P_2
> > > 53  2005-01-05      AAA      3       P_3
> > > 54  2005-01-06      AAA     -2       P_4
> > > 55  2005-01-07      AAA      8       P_5
> > >
> > > Required movement report
> > > ************************
> > > Order.id date_order Order.quantity Distribute.id date_disributed Distribute.quantity Remaining Order qty
> > > -------- ---------- -------------- ------------- --------------- ------------------- -------------------
> > > 11       2005-01-01 10             51            2005-01-01      10                  0  [10 -10]
> > > 12       2005-01-03 20             52            2005-01-04      13                  7  [20 - 13]
> > >                                    53            2005-01-05      3                   4  [7 - 3]
> > >                                    54            2005-01-06      -2                  6  [4 + 2]
> > >                                    55            2005-01-07      6 [partial]         0  [6 - 6]
> > > 13       2005-01-05 30             55            2005-01-07      2 [partial]         28 [30 - 2]
> > >                        
>
>

"POKEMON" wrote:

> hi
> it looks like this
>
> select o1.id,o1.date_order, o1.product, o1.quantity,d1.id as Distribute.id
> ,d1.date_distribute,isnull(d1.quantity,0) as distributedquantity,
> case when (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))>o1.quantity
> then o1.quantity else
> case when (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))<0 then 0
> else isnull(sum(o2.quantity),0)-isnull(sum(d1.quatity),0) end as
> remainingorder
> from order o1
> left join order o2 on o1.id<=o2.id and o1.product=o2.product
> left join Distribute d1 on o1.date_order<=d1.date_distribute and
> o1.product=d1.product
> left join Distribute d2 on d2.date_distribute<=d1.date_distribute and
> d2.product=d1.product
> group by o1.id, o1.date_order, o1.product, o1.quantity,d1.id
> ,d1.date_distribute
> having (isnull(sum(b.quantity),0)-isnull(sum(d2.quatity),0))+d1.quantity>0
>
> but i'm lack of time so you must do something about distributequantity:))
> also i didnt test it you might correct few syntx
> hope it works for you
>
> "Kriste L" wrote:
>
> > No, it's not just using order_by.
> > It's something like deducting from Order quantity, and measure how effective is the order.
> > Perhaps, following description can explain the scenario better
> >
> > The scenario is like this, eg,
> > On 2005-01-01
> >     - order with quantity 10 and distribute out 10 quantity, so remaining quantity in order = 0
> > On 2005-01-03
> >     - order with quantity 20, so remaining quantity in order = 20
> >     - distribute out 13 quantity on 2005-01-04, so remaining quantity in order = 7   
> >     - distribute out 3 quantity on 2005-01-05, so remaining quantity in order = 4
> >     - withdraw distribute -2 quantity on 2005-01-06, so remaining quantity in order = 6
> >     - distribute out 8 quantity out on 2005-01-07, as this order has remaining 6, so only 6 can be distributed out from this order, so this order remaining quantity = 0
> >       the remaining 2 quantity will distribute out from next order
> > On 2005-01-05
> >     - distribute out outstanding quantity 2, so this order is now left with 28.
> >
> >
> > > > Table: Order
> > > > ************
> > > > id   date_order product quantity
> > > > --   ---------- ------- --------
> > > > 11   2005-01-01 AAA     10
> > > > 12   2005-01-03 AAA     20
> > > > 13   2005-01-05 AAA     30
> > > >
> > > > Table: Distribute
> > > > *****************
> > > > id  date_distribute product quantity personnel_id
> > > > --  --------------- ------- -------- ------------
> > > > 51  2005-01-01      AAA     10       P_1
> > > > 52  2005-01-04      AAA     13       P_2
> > > > 53  2005-01-05      AAA      3       P_3
> > > > 54  2005-01-06      AAA     -2       P_4
> > > > 55  2005-01-07      AAA      8       P_5
> > > >
> > > > Required movement report
> > > > ************************
> > > > Order.id date_order Order.quantity Distribute.id date_disributed Distribute.quantity Remaining Order qty
> > > > -------- ---------- -------------- ------------- --------------- ------------------- -------------------
> > > > 11       2005-01-01 10             51            2005-01-01      10                  0  [10 -10]
> > > > 12       2005-01-03 20             52            2005-01-04      13                  7  [20 - 13]
> > > >                                    53            2005-01-05      3                   4  [7 - 3]
> > > >                                    54            2005-01-06      -2                  6  [4 + 2]
> > > >                                    55            2005-01-07      6 [partial]         0  [6 - 6]
> > > > 13       2005-01-05 30             55            2005-01-07      2 [partial]         28 [30 - 2]
> > > >                        
> >
> >

"POKEMON" wrote:

> corrections:))
>
>
> select o1.id,o1.date_order, o1.product, o1.quantity,d1.id as Distribute_id
> ,d1.date_distribute,
> case when
> isnull(d1.quantity,0)>(isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))
> then (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0)) else
> isnull(d1.quantity,0) as distributed_quantity,
> case when (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))>o1.quantity
> then o1.quantity
> when (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))<0 then 0
> else isnull(sum(o2.quantity),0)-isnull(sum(d1.quatity),0) end as
> remainingorder
> from order o1
> left join order o2 on o2.id<=o1.id and o1.product=o2.product
> left join Distribute d1 on o1.date_order<=d1.date_distribute and
> o1.product=d1.product
> left join Distribute d2 on d2.date_distribute<=d1.date_distribute and
> d2.id<=d1.id
> and d2.product=d1.product
> group by o1.id, o1.date_order, o1.product, o1.quantity,d1.id
> ,d1.date_distribute
> having (isnull(sum(b.quantity),0)-isnull(sum(d2.quatity),0))+d1.quantity>0
>
>
> "POKEMON" wrote:
>
> > hi
> > it looks like this
> >
> > select o1.id,o1.date_order, o1.product, o1.quantity,d1.id as Distribute.id
> > ,d1.date_distribute,isnull(d1.quantity,0) as distributedquantity,
> > case when (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))>o1.quantity
> > then o1.quantity else
> > case when (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))<0 then 0
> > else isnull(sum(o2.quantity),0)-isnull(sum(d1.quatity),0) end as
> > remainingorder
> > from order o1
> > left join order o2 on o1.id<=o2.id and o1.product=o2.product
> > left join Distribute d1 on o1.date_order<=d1.date_distribute and
> > o1.product=d1.product
> > left join Distribute d2 on d2.date_distribute<=d1.date_distribute and
> > d2.product=d1.product
> > group by o1.id, o1.date_order, o1.product, o1.quantity,d1.id
> > ,d1.date_distribute
> > having (isnull(sum(b.quantity),0)-isnull(sum(d2.quatity),0))+d1.quantity>0
> >
> > but i'm lack of time so you must do something about distributequantity:))
> > also i didnt test it you might correct few syntx
> > hope it works for you
> >
> > "Kriste L" wrote:
> >
> > > No, it's not just using order_by.
> > > It's something like deducting from Order quantity, and measure how effective is the order.
> > > Perhaps, following description can explain the scenario better
> > >
> > > The scenario is like this, eg,
> > > On 2005-01-01
> > >     - order with quantity 10 and distribute out 10 quantity, so remaining quantity in order = 0
> > > On 2005-01-03
> > >     - order with quantity 20, so remaining quantity in order = 20
> > >     - distribute out 13 quantity on 2005-01-04, so remaining quantity in order = 7   
> > >     - distribute out 3 quantity on 2005-01-05, so remaining quantity in order = 4
> > >     - withdraw distribute -2 quantity on 2005-01-06, so remaining quantity in order = 6
> > >     - distribute out 8 quantity out on 2005-01-07, as this order has remaining 6, so only 6 can be distributed out from this order, so this order remaining quantity = 0
> > >       the remaining 2 quantity will distribute out from next order
> > > On 2005-01-05
> > >     - distribute out outstanding quantity 2, so this order is now left with 28.
> > >
> > >
> > > > > Table: Order
> > > > > ************
> > > > > id   date_order product quantity
> > > > > --   ---------- ------- --------
> > > > > 11   2005-01-01 AAA     10
> > > > > 12   2005-01-03 AAA     20
> > > > > 13   2005-01-05 AAA     30
> > > > >
> > > > > Table: Distribute
> > > > > *****************
> > > > > id  date_distribute product quantity personnel_id
> > > > > --  --------------- ------- -------- ------------
> > > > > 51  2005-01-01      AAA     10       P_1
> > > > > 52  2005-01-04      AAA     13       P_2
> > > > > 53  2005-01-05      AAA      3       P_3
> > > > > 54  2005-01-06      AAA     -2       P_4
> > > > > 55  2005-01-07      AAA      8       P_5
> > > > >
> > > > > Required movement report
> > > > > ************************
> > > > > Order.id date_order Order.quantity Distribute.id date_disributed Distribute.quantity Remaining Order qty
> > > > > -------- ---------- -------------- ------------- --------------- ------------------- -------------------
> > > > > 11       2005-01-01 10             51            2005-01-01      10                  0  [10 -10]
> > > > > 12       2005-01-03 20             52            2005-01-04      13                  7  [20 - 13]
> > > > >                                    53            2005-01-05      3                   4  [7 - 3]
> > > > >                                    54            2005-01-06      -2                  6  [4 + 2]
> > > > >                                    55            2005-01-07      6 [partial]         0  [6 - 6]
> > > > > 13       2005-01-05 30             55            2005-01-07      2 [partial]         28 [30 - 2]
> > > > >                        
> > >
> > >

> then o1.quantity
> when (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))<0 then 0
> else isnull(sum(o2.quantity),0)-isnull(sum(d1.quatity),0) end as
> remainingorder
> from order o1
> left join order o2 on o2.id<=o1.id and o1.product=o2.product
> left join Distribute d1 on o1.date_order<=d1.date_distribute and
> o1.product=d1.product
> left join Distribute d2 on d2.date_distribute<=d1.date_distribute and
> d2.id<=d1.id
> and d2.product=d1.product
> group by o1.id, o1.date_order, o1.product, o1.quantity,d1.id
> ,d1.date_distribute
> having (isnull(sum(b.quantity),0)-isnull(sum(d2.quatity),0))+d1.quantity>0
>
>
> "POKEMON" wrote:
>
> > corrections:))
> >
> >
> > select o1.id,o1.date_order, o1.product, o1.quantity,d1.id as
Distribute_id
> > ,d1.date_distribute,
> > case when
> >

> > > >
> > > >
> > > > > > Table: Order
> > > > > > ************
> > > > > > id   date_order product quantity
> > > > > > --   ---------- ------- --------
> > > > > > 11   2005-01-01 AAA     10
> > > > > > 12   2005-01-03 AAA     20
> > > > > > 13   2005-01-05 AAA     30
> > > > > >
> > > > > > Table: Distribute
> > > > > > *****************
> > > > > > id  date_distribute product quantity personnel_id
> > > > > > --  --------------- ------- -------- ------------
> > > > > > 51  2005-01-01      AAA     10       P_1
> > > > > > 52  2005-01-04      AAA     13       P_2
> > > > > > 53  2005-01-05      AAA      3       P_3
> > > > > > 54  2005-01-06      AAA     -2       P_4
> > > > > > 55  2005-01-07      AAA      8       P_5
> > > > > >
> > > > > > Required movement report
> > > > > > ************************
> > > > > > Order.id date_order Order.quantity Distribute.id date_disributed

> > > > > >
> > > >
> > > >

"Kriste L" wrote:

> Hi Pokemon,
>
> I've tried out on your suggestion, there's missing dl.quantity in the group
> by.
> I added that in but the result is incorrect.
> Btw, u mentioned that I must do something on the "distributequantity", can u
> be more detail what's that about?
>
>
>
>
>
> "POKEMON" <POKE***@discussions.microsoft.com> wrote in message
> news:79DC3653-80BC-4B65-BCA7-0FB6634E811C@microsoft.com...
> > one more correction; sorry
> > because of the sums in the query it might be slow. if so i advice you to
> use
> > udf 's.
> > hope it works for you
> >
> > select o1.id,o1.date_order, o1.product, o1.quantity,d1.id as Distribute_id
> > ,d1.date_distribute,
> > case when
> >
> isnull(d1.quantity,0)>(isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))
> > then (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0)) else
> > isnull(d1.quantity,0) end as distributed_quantity,
> > case when
> (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))>o1.quantity
> > then o1.quantity
> > when (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))<0 then 0
> > else isnull(sum(o2.quantity),0)-isnull(sum(d1.quatity),0) end as
> > remainingorder
> > from order o1
> > left join order o2 on o2.id<=o1.id and o1.product=o2.product
> > left join Distribute d1 on o1.date_order<=d1.date_distribute and
> > o1.product=d1.product
> > left join Distribute d2 on d2.date_distribute<=d1.date_distribute and
> > d2.id<=d1.id
> > and d2.product=d1.product
> > group by o1.id, o1.date_order, o1.product, o1.quantity,d1.id
> > ,d1.date_distribute
> > having (isnull(sum(b.quantity),0)-isnull(sum(d2.quatity),0))+d1.quantity>0
> >
> >
> > "POKEMON" wrote:
> >
> > > corrections:))
> > >
> > >
> > > select o1.id,o1.date_order, o1.product, o1.quantity,d1.id as
> Distribute_id
> > > ,d1.date_distribute,
> > > case when
> > >
> isnull(d1.quantity,0)>(isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))
> > > then (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0)) else
> > > isnull(d1.quantity,0) as distributed_quantity,
> > > case when
> (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))>o1.quantity
> > > then o1.quantity
> > > when (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))<0 then 0
> > > else isnull(sum(o2.quantity),0)-isnull(sum(d1.quatity),0) end as
> > > remainingorder
> > > from order o1
> > > left join order o2 on o2.id<=o1.id and o1.product=o2.product
> > > left join Distribute d1 on o1.date_order<=d1.date_distribute and
> > > o1.product=d1.product
> > > left join Distribute d2 on d2.date_distribute<=d1.date_distribute and
> > > d2.id<=d1.id
> > > and d2.product=d1.product
> > > group by o1.id, o1.date_order, o1.product, o1.quantity,d1.id
> > > ,d1.date_distribute
> > > having
> (isnull(sum(b.quantity),0)-isnull(sum(d2.quatity),0))+d1.quantity>0
> > >
> > >
> > > "POKEMON" wrote:
> > >
> > > > hi
> > > > it looks like this
> > > >
> > > > select o1.id,o1.date_order, o1.product, o1.quantity,d1.id as
> Distribute.id
> > > > ,d1.date_distribute,isnull(d1.quantity,0) as distributedquantity,
> > > > case when
> (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))>o1.quantity
> > > > then o1.quantity else
> > > > case when (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))<0
> then 0
> > > > else isnull(sum(o2.quantity),0)-isnull(sum(d1.quatity),0) end as
> > > > remainingorder
> > > > from order o1
> > > > left join order o2 on o1.id<=o2.id and o1.product=o2.product
> > > > left join Distribute d1 on o1.date_order<=d1.date_distribute and
> > > > o1.product=d1.product
> > > > left join Distribute d2 on d2.date_distribute<=d1.date_distribute and
> > > > d2.product=d1.product
> > > > group by o1.id, o1.date_order, o1.product, o1.quantity,d1.id
> > > > ,d1.date_distribute
> > > > having
> (isnull(sum(b.quantity),0)-isnull(sum(d2.quatity),0))+d1.quantity>0
> > > >
> > > > but i'm lack of time so you must do something about
> distributequantity:))
> > > > also i didnt test it you might correct few syntx
> > > > hope it works for you
> > > >
> > > > "Kriste L" wrote:
> > > >
> > > > > No, it's not just using order_by.
> > > > > It's something like deducting from Order quantity, and measure how
> effective is the order.
> > > > > Perhaps, following description can explain the scenario better
> > > > >
> > > > > The scenario is like this, eg,
> > > > > On 2005-01-01
> > > > >     - order with quantity 10 and distribute out 10 quantity, so
> remaining quantity in order = 0
> > > > > On 2005-01-03
> > > > >     - order with quantity 20, so remaining quantity in order = 20
> > > > >     - distribute out 13 quantity on 2005-01-04, so remaining
> quantity in order = 7
> > > > >     - distribute out 3 quantity on 2005-01-05, so remaining quantity
> in order = 4
> > > > >     - withdraw distribute -2 quantity on 2005-01-06, so remaining
> quantity in order = 6
> > > > >     - distribute out 8 quantity out on 2005-01-07, as this order has
> remaining 6, so only 6 can be distributed out from this order, so this order
> remaining quantity = 0
> > > > >       the remaining 2 quantity will distribute out from next order
> > > > > On 2005-01-05
> > > > >     - distribute out outstanding quantity 2, so this order is now
> left with 28.
> > > > >
> > > > >
> > > > > > > Table: Order
> > > > > > > ************
> > > > > > > id   date_order product quantity
> > > > > > > --   ---------- ------- --------
> > > > > > > 11   2005-01-01 AAA     10
> > > > > > > 12   2005-01-03 AAA     20
> > > > > > > 13   2005-01-05 AAA     30
> > > > > > >
> > > > > > > Table: Distribute
> > > > > > > *****************
> > > > > > > id  date_distribute product quantity personnel_id
> > > > > > > --  --------------- ------- -------- ------------
> > > > > > > 51  2005-01-01      AAA     10       P_1
> > > > > > > 52  2005-01-04      AAA     13       P_2
> > > > > > > 53  2005-01-05      AAA      3       P_3
> > > > > > > 54  2005-01-06      AAA     -2       P_4
> > > > > > > 55  2005-01-07      AAA      8       P_5
> > > > > > >
> > > > > > > Required movement report
> > > > > > > ************************
> > > > > > > Order.id date_order Order.quantity Distribute.id date_disributed
> Distribute.quantity Remaining Order qty
> > > > > >
> > -------- ---------- -------------- ------------- --------------- ---------
> ---------- -------------------
> > > > > > > 11       2005-01-01 10             51            2005-01-01
> 10                  0  [10 -10]
> > > > > > > 12       2005-01-03 20             52            2005-01-04
> 13                  7  [20 - 13]
> > > > > > >                                    53            2005-01-05
> 3                   4  [7 - 3]
> > > > > > >                                    54
>       2005-01-06      -2                  6  [4 + 2]
> > > > > > >                                    55            2005-01-07
> 6 [partial]         0  [6 - 6]
> > > > > > > 13       2005-01-05 30             55            2005-01-07
> 2 [partial]         28 [30 - 2]
> > > > > > >
> > > > >
> > > > >
>
>
>

"POKEMON" wrote:

> hi pokemon again
>
> you are right but i didnt understand the governing logic of the
> sum(o2.quantity) in the query i wrote.
>
> i tried udf for the sums and test it its working:))
> still you have to do something about the nulls of the orders that are after
> the last distribution(you can use isnull function for d1.id and
> d1.date_distribute)
> hope that works
>
> select o1.id,o1.date_order,o1.product, o1.quantity,d1.id as Distribute_id ,
> d1.date_distribute,
> case when
> 0>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product))
>     then
> isnull(d1.quantity,0)+(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product))
> when 
> d1.quantity>o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product))
>     then
> (o1.quantity-(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product))) else
> isnull(d1.quantity,0) end as distributed_quantity,
> case when
> (dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product))>o1.quantity then o1.quantity
> when
> (dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product))<0 then 0
> else
> (dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product)) end as remainingorder,
> dbo.getdistributedogan(d1.id,d1.product)as forobservingdist,
> dbo.getorderdogan(o1.id,o1.product)as forobservingord
> from dbo.[order] o1
> left outer join Distribute d1 on o1.date_order<=d1.date_distribute and
> o1.product=d1.product
> group by o1.id, o1.date_order,o1.product, o1.quantity,d1.id
> ,d1.date_distribute,d1.quantity,d1.product
> having
> ((dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product))+isnull(d1.quantity,0)>0
> and
> o1.quantity>(dbo.getorderdogan(o1.id,o1.product)-dbo.getdistributedogan(d1.id,d1.product)))
> or isnull(d1.quantity,0)=0
>
>
> /*
>
> CREATE FUNCTION getorderdogan
>     (@id int,@product nvarchar(50))
> RETURNS int
> AS
> BEGIN
>
> DECLARE @sum AS int
> select @sum = sum(o2.quantity) from [order] o2 where o2.id<=@id and
> o2.product=@product
> RETURN @sum
> END
>
>
> CREATE FUNCTION getdistributedogan
>     (@id int,@product nvarchar(50))
> RETURNS int
> AS
> BEGIN
>
> DECLARE @sum AS int
> select @sum = sum(d2.quantity) from Distribute d2 where d2.id<=@id and
> d2.product=@product
> RETURN isnull(@sum,0)
> END
> */
>
>
>
> "Kriste L" wrote:
>
> > Hi Pokemon,
> >
> > I've tried out on your suggestion, there's missing dl.quantity in the group
> > by.
> > I added that in but the result is incorrect.
> > Btw, u mentioned that I must do something on the "distributequantity", can u
> > be more detail what's that about?
> >
> >
> >
> >
> >
> > "POKEMON" <POKE***@discussions.microsoft.com> wrote in message
> > news:79DC3653-80BC-4B65-BCA7-0FB6634E811C@microsoft.com...
> > > one more correction; sorry
> > > because of the sums in the query it might be slow. if so i advice you to
> > use
> > > udf 's.
> > > hope it works for you
> > >
> > > select o1.id,o1.date_order, o1.product, o1.quantity,d1.id as Distribute_id
> > > ,d1.date_distribute,
> > > case when
> > >
> > isnull(d1.quantity,0)>(isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))
> > > then (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0)) else
> > > isnull(d1.quantity,0) end as distributed_quantity,
> > > case when
> > (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))>o1.quantity
> > > then o1.quantity
> > > when (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))<0 then 0
> > > else isnull(sum(o2.quantity),0)-isnull(sum(d1.quatity),0) end as
> > > remainingorder
> > > from order o1
> > > left join order o2 on o2.id<=o1.id and o1.product=o2.product
> > > left join Distribute d1 on o1.date_order<=d1.date_distribute and
> > > o1.product=d1.product
> > > left join Distribute d2 on d2.date_distribute<=d1.date_distribute and
> > > d2.id<=d1.id
> > > and d2.product=d1.product
> > > group by o1.id, o1.date_order, o1.product, o1.quantity,d1.id
> > > ,d1.date_distribute
> > > having (isnull(sum(b.quantity),0)-isnull(sum(d2.quatity),0))+d1.quantity>0
> > >
> > >
> > > "POKEMON" wrote:
> > >
> > > > corrections:))
> > > >
> > > >
> > > > select o1.id,o1.date_order, o1.product, o1.quantity,d1.id as
> > Distribute_id
> > > > ,d1.date_distribute,
> > > > case when
> > > >
> > isnull(d1.quantity,0)>(isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))
> > > > then (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0)) else
> > > > isnull(d1.quantity,0) as distributed_quantity,
> > > > case when
> > (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))>o1.quantity
> > > > then o1.quantity
> > > > when (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))<0 then 0
> > > > else isnull(sum(o2.quantity),0)-isnull(sum(d1.quatity),0) end as
> > > > remainingorder
> > > > from order o1
> > > > left join order o2 on o2.id<=o1.id and o1.product=o2.product
> > > > left join Distribute d1 on o1.date_order<=d1.date_distribute and
> > > > o1.product=d1.product
> > > > left join Distribute d2 on d2.date_distribute<=d1.date_distribute and
> > > > d2.id<=d1.id
> > > > and d2.product=d1.product
> > > > group by o1.id, o1.date_order, o1.product, o1.quantity,d1.id
> > > > ,d1.date_distribute
> > > > having
> > (isnull(sum(b.quantity),0)-isnull(sum(d2.quatity),0))+d1.quantity>0
> > > >
> > > >
> > > > "POKEMON" wrote:
> > > >
> > > > > hi
> > > > > it looks like this
> > > > >
> > > > > select o1.id,o1.date_order, o1.product, o1.quantity,d1.id as
> > Distribute.id
> > > > > ,d1.date_distribute,isnull(d1.quantity,0) as distributedquantity,
> > > > > case when
> > (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))>o1.quantity
> > > > > then o1.quantity else
> > > > > case when (isnull(sum(o2.quantity),0)-isnull(sum(d2.quatity),0))<0
> > then 0
> > > > > else isnull(sum(o2.quantity),0)-isnull(sum(d1.quatity),0) end as
> > > > > remainingorder
> > > > > from order o1
> > > > > left join order o2 on o1.id<=o2.id and o1.product=o2.product
> > > > > left join Distribute d1 on o1.date_order<=d1.date_distribute and
> > > > > o1.product=d1.product
> > > > > left join Distribute d2 on d2.date_distribute<=d1.date_distribute and
> > > > > d2.product=d1.product
> > > > > group by o1.id, o1.date_order, o1.product, o1.quantity,d1.id
> > > > > ,d1.date_distribute
> > > > > having
> > (isnull(sum(b.quantity),0)-isnull(sum(d2.quatity),0))+d1.quantity>0
> > > > >
> > > > > but i'm lack of time so you must do something about
> > distributequantity:))
> > > > > also i didnt test it you might correct few syntx
> > > > > hope it works for you
> > > > >
> > > > > "Kriste L" wrote:
> > > > >
> > > > > > No, it's not just using order_by.
> > > > > > It's something like deducting from Order quantity, and measure how
> > effective is the order.
> > > > > > Perhaps, following description can explain the scenario better
> > > > > >
> > > > > > The scenario is like this, eg,
> > > > > > On 2005-01-01
> > > > > >     - order with quantity 10 and distribute out 10 quantity, so
> > remaining quantity in order = 0
> > > > > > On 2005-01-03
> > > > > >     - order with quantity 20, so remaining quantity in order = 20
> > > > > >     - distribute out 13 quantity on 2005-01-04, so remaining
> > quantity in order = 7
> > > > > >     - distribute out 3 quantity on 2005-01-05, so remaining quantity
> > in order = 4
> > > > > >     - withdraw distribute -2 quantity on 2005-01-06, so remaining
> > quantity in order = 6
> > > > > >     - distribute out 8 quantity out on 2005-01-07, as this order has
> > remaining 6, so only 6 can be distributed out from this order, so this order
> > remaining quantity = 0
> > > > > >       the remaining 2 quantity will distribute out from next order
> > > > > > On 2005-01-05
> > > > > >     - distribute out outstanding quantity 2, so this order is now
> > left with 28.
> > > > > >
> > > > > >
> > > > > > > > Table: Order
> > > > > > > > ************
> > > > > > > > id   date_order product quantity
> > > > > > > > --   ---------- ------- --------
> > > > > > > > 11   2005-01-01 AAA     10
> > > > > > > > 12   2005-01-03 AAA     20
> > > > > > > > 13   2005-01-05 AAA     30
> > > > > > > >
> > > > > > > > Table: Distribute
> > > > > > > > *****************
> > > > > > > > id  date_distribute product quantity personnel_id
> > > > > > > > --  --------------- ------- -------- ------------
> > > > > > > > 51  2005-01-01      AAA     10       P_1
> > > > > > > > 52  2005-01-04      AAA     13       P_2
> > > > > > > > 53  2005-01-05      AAA      3       P_3
> > > > > > > > 54  2005-01-06      AAA     -2       P_4
> > > > > > > > 55  2005-01-07      AAA      8       P_5
> > > > > > > >
> > > > > > > > Required movement report
> > > > > > > > ************************
> > > > > > > > Order.id date_order Order.quantity Distribute.id date_disributed
> > Distribute.quantity Remaining Order qty
> > > > > > >
> > > -------- ---------- -------------- ------------- --------------- ---------
> > ---------- -------------------
> > > > > > > > 11       2005-01-01 10             51            2005-01-01
> > 10                  0  [10 -10]
> > > > > > > > 12       2005-01-03 20             52            2005-01-04
> > 13                  7  [20 - 13]
> > > > > > > >                                    53            2005-01-05
> > 3                   4  [7 - 3]
> > > > > > > >                                    54
> >       2005-01-06      -2                  6  [4 + 2]
> > > > > > > >                                    55            2005-01-07
> > 6 [partial]         0  [6 - 6]
> > > > > > > > 13       2005-01-05 30             55            2005-01-07
> > 2 [partial]         28 [30 - 2]
> > > > > > > >
> > > > > >
> > > > > >
> >
> >
> >