The final stock of every month is the initial stock of the following one. My initial stock for this product this with fechacdp=000000 that is to say, 5892.00, for example 4124 = 5892-1768
select p.materialid, p.centroid, p.fechacdp,sum(p.cantidad) as total,
(select sum(cantidad) from PlanOperacion.prdztpp_md05_det2 pp where pp.materialid = p.materialid and pp.centroid = p.centroid and pp.fechacdp < p.fechacdp) as running_total
from PlanOperacion.prdztpp_md05_det2 P
where p.materialid = '200049839' and p.centroid = 'PE02'
group by p.materialid, p.centroid, p.fechacdp
order by p.fechacdp
select p.materialid, p.centroid, p.fechacdp,sum(p.cantidad) as total,
(select sum(cantidad) from PlanOperacion.prdztpp_md05_det2 pp where pp.materialid = p.materialid and pp.centroid = p.centroid and pp.fechacdp <= p.fechacdp) as running_total
from PlanOperacion.prdztpp_md05_det2 P
where p.materialid = '200049839' and p.centroid = 'PE02'
group by p.materialid, p.centroid, p.fechacdp
order by p.fechacdp
SELECT materialid, centroid,[OpeningBalance],[January2008],[February2008],[March2008],[April2008],[May2008],[June2008],[July2008],[August2008],[September2008],[October2008],[November2008],[December2008]
FROM
(SELECT materialid, centroid, cantidad,case when fechacdp > 200600 then datename(mm,convert(datetime,convert(varchar,fechacdp)+'01',112))+convert(varchar,left(fechacdp,4)) else 'OpeningBalance' end as Periods From PlanOperacion.prdztpp_md05_det2 where materialid = '200049839' and centroid = 'PE02') P
PIVOT
(
SUM(cantidad)
FOR Periods IN ([OpeningBalance],[January2008],[February2008],[March2008],[April2008],[May2008],[June2008],[July2008],[August2008],[September2008],[October2008],[November2008],[December2008])
) AS pvt
ORDER BY materialid, centroid;
SELECT materialid, centroid,[OpeningBalance],[January2008],[February2008],[March2008],[April2008],[May2008],[June2008],[July2008],[August2008],[September2008],[October2008],[November2008],[December2008]
FROM
(SELECT materialid, centroid,case when fechacdp > 200600 then datename(mm,convert(datetime,convert(varchar,fechacdp)+'01',112))+convert(varchar,left(fechacdp,4))
else 'OpeningBalance' end as Periods,
(select sum(cantidad) from PlanOperacion.prdztpp_md05_det2 pp where pp.materialid = p.materialid and pp.centroid = p.centroid and pp.fechacdp <= p.fechacdp having count(cantidad) > 1 ) as running_total
From PlanOperacion.prdztpp_md05_det2 p where materialid = '200049839' and centroid = 'PE02' group by materialid,centroid,fechacdp) Data
PIVOT
(
SUM(running_total)
FOR Periods IN ([OpeningBalance],[January2008],[February2008],[March2008],[April2008],[May2008],[June2008],[July2008],[August2008],[September2008],[October2008],[November2008],[December2008])
) AS pvt
ORDER BY materialid, centroid;
Title | # Comments | Views | Activity |
---|---|---|---|
Temporarily disable SQL Replication | 7 | 23 | |
how to fix this error | 14 | 48 | |
Read a Table and add those records into a existing parameter that was send from a SSRS report | 11 | 29 | |
Help with stripping out character in SQL LEFT/RIGHT/REPLACE | 2 | 17 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
11 Experts available now in Live!