Estoy usando SQL-Server y tengo una tabla de mis órdenes de compra (stock) . Pero me quedé atrapado en una consulta mientras intentaba obtener mi stock Todo disponible con su último precio de costo y último precio de venta .

Hice una consulta para que se ejecute correctamente, pero necesito una forma mejor y optimizada de hacerlo, porque se ralentizará cuando la tabla tenga n número de registros.

Muestra de consulta:

SELECT 
    po.ProductID, sum(po.AvailableQty) as AvailableQty,
    (select top 1 po2.CostPrice from Sales_PurchaseOrders po2 where po2.PurchasedAt=max(po.PurchasedAt)) as CostPrice,
    (select top 1 po2.SellingPrice from Sales_PurchaseOrders po2 where po2.PurchasedAt=max(po.PurchasedAt)) as SellingPrice
FROM 
    Sales_PurchaseOrders po
INNER JOIN Sales_Products p on p.ProductID=po.ProductID
GROUP BY po.ProductID

Datos de tabla:

PurchaseOrderID ProductID   CostPrice                               SellingPrice                            AvailableQty                            PurchasedAt
--------------- ----------- --------------------------------------- --------------------------------------- --------------------------------------- -----------------------
1               1           90.000000                               100.000000                              2.000000                                2016-07-28 00:00:00.000
2               1           33.580000                               50.000000                               0.000000                                2016-06-28 00:00:00.000
3               2           200.000000                              240.000000                              15.000000                               2016-07-30 00:00:00.000
4               1           50.000000                               60.000000                               0.000000                                2016-08-02 00:00:00.000
5               1           50.000000                               60.000000                               1.000000                                2016-08-03 00:00:00.000
6               1           100.000000                              110.000000                              6.000000                                2016-08-04 00:00:00.000
7               1           25.000000                               30.000000                               3.000000                                2016-08-03 00:00:00.000
8               1           20.000000                               30.000000                               0.000000                                2016-07-30 00:00:00.000
1007            1           100.000000                              200.000000                              2.000000                                2016-09-24 00:00:00.000

Resultado de la consulta:

ProductID   AvailableQty                            CostPrice                               SellingPrice
----------- --------------------------------------- --------------------------------------- ---------------------------------------
1           14.000000                               100.000000                              200.000000
2           15.000000                               200.000000                              240.000000

Puede ser a través de algún tipo de función agregada, o cualquier otra forma mejor optimizada para hacer esto.

Gracias

0
Mehmood 20 mar. 2017 a las 19:44

2 respuestas

Prueba esto:

with Sales_PurchaseOrders(PurchaseOrderID,ProductID,CostPrice,SellingPrice,AvailableQty,PurchasedAt)AS(
select 1,1,90.000000,100.000000,2.000000,'2016-07-28 00:00:00.000' union all
select 2,1,33.580000,50.000000,0.000000,'2016-06-28 00:00:00.000' union all
select 3,2,200.000000,240.000000,15.000000,'2016-07-30 00:00:00.000' union all
select 4,1,50.000000,60.000000,0.000000,'2016-08-02 00:00:00.000' union all
select 5,1,50.000000,60.000000,1.000000,'2016-08-03 00:00:00.000' union all
select 6,1,100.000000,110.000000,6.000000,'2016-08-04 00:00:00.000' union all
select 7,1,25.000000,30.000000,3.000000,'2016-08-03 00:00:00.000' union all
select 8,1,20.000000,30.000000,0.000000,'2016-07-30 00:00:00.000' union all
select 1007,1,100.000000,200.000000,2.000000,'2016-09-24 00:00:00.000'
)
select * from (
    SELECT 
po.ProductID, sum(po.AvailableQty)over(partition by po.ProductID) as AvailableQty,CostPrice,SellingPrice,
row_number()over(partition by po.ProductID order by po.PurchasedAt desc) as seq
    FROM  Sales_PurchaseOrders  po

) as t where t.seq=1
    ProductID   AvailableQty    CostPrice   SellingPrice    seq
1   1   14,000000   100,000000  200,000000  1
2   2   15,000000   200,000000  240,000000  1
0
Nolan Shang 20 mar. 2017 a las 17:17

Querida mehmood Prueba esto.

;with wt_table 
as
(
select ROW_NUMBER() over(partition by po.ProductID order by PurchasedAt desc) as Num,
AvailableQty=sum(po.AvailableQty) over(partition by po.ProductID),
po.ProductID,
po.CostPrice,
po.SellingPrice,
po.PurchasedAt
From    #Sales_PurchaseOrders po)
select * from wt_table  where Num=1
1
Abdul Ghaffar 21 mar. 2017 a las 05:03