select a.ItemCode,a.[Name],SUM(a.[OB-Qty]) [OB-Qty],SUM(a.[OB-Value]) [OB-Value],
sum(a.[Issue]) [Issue] ,sum(a.[Receipt]) [Receipt],
SUM(a.[Cls-Qty]) [Cls-Qty], SUM([ClsValue]) [ClsValue] from(
select t1.ItemCode,max(t1.dscription) [Name],
(sum(isnull(t1.inqty,0)) - sum(isnull(t1.outqty,0)) ) [OB-Qty],
sum(isnull(t1.transvalue,0)) [OB-Value],0 [Issue], 0 [Receipt],
0 [Cls-Qty],0 [ClsValue]
from OINM t1 where
t1.docdate < '[%0]' and t1.[Warehouse] = '[%3]' group by t1.ItemCode
union all
select t1.ItemCode,max(t1.dscription) [Name],0 [OB-Qty],0 [OB-Value],
(sum(isnull(t1.outqty,0))) [Issue],
(sum(isnull(t1.inqty,0))) [Receipt],
0 [Cls-Qty],0 [ClsValue]
from OINM t1
where t1.docdate >= '[%0]' and t1.DocDate <= '[%1]' and t1.[Warehouse] = '[%3]'
group by t1.ItemCode
union all
select t1.ItemCode,max(t1.dscription) [Name],0 [OB-Qty],0 [OB-Value],
0 [Issue],0 [Receipt],
(sum(isnull(t1.inqty,0)) - sum(isnull(t1.outqty,0)) ) [Cls-Qty],
sum(isnull(t1.transvalue,0)) [ClsValue]
from OINM t1
where t1.DocDate <= '[%1]' and t1.[Warehouse] = '[%3]'
group by t1.ItemCode)a
group by a.ItemCode,a.[Name]
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Declare @ToDate Datetime
set @FromDate =
(Select min(S0.Docdate) from OINM S0 where S0.Docdate >='[%0]')
set @ToDate =
(Select max(S1.Docdate) from OINM S1 where S1.Docdate <='[%1]')
select * from
(
SELECT T0.itemcode,
min(T0.Dscription) as 'Item Description',
min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location,
(isnull((
Select sum(isnull(inqty,0))
from OINM O1
where O1.itemcode=T1.itemcode
and O1.Warehouse=W1.Whscode
and O1.docdate<@FromDate ),0)-
isnull((
Select sum(isnull(outqty,0))
from OINM O1
where O1.itemcode=T1.itemcode
and O1.Warehouse=W1.Whscode
and O1.docdate<@FromDate),0)
) as [Opening Stock],
isnull((
Select sum(isnull(inqty,0))
from OINM O1
where O1.itemcode=T1.itemcode
and O1.Warehouse=W1.Whscode
and O1.docdate>=@FromDate
and O1.docdate<=@ToDate and O1.inqty>0
and O1.transtype in (20,18)),0
) as [Purchase Quantity],
isnull((
Select sum(isnull(outqty,0))
from OINM O1
where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode
and O1.docdate>=@FromDate and O1.docdate<=@ToDate
and O1.outqty>0 and O1.transtype in (21,19)),0
) as [Purchase Return Quantity],
isnull((
Select sum(isnull(outqty,0))
from OINM O1
where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode
and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0
and O1.transtype in (13,15)),0
) as [sale Quatity],
(isnull
((
Select sum(isnull(inqty,0))
from OINM O1
where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode
and O1.docdate<=@ToDate),0
)-
isnull((
Select sum(isnull(outqty,0))
from OINM O1
where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode
and O1.docdate<=@ToDate),0)
) as [Closing Stock]
FROM OINM T0
INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
INNER JOIN OITW T2 ON T1.ItemCode = T2.ItemCode
INNER JOIN OITB B1 ON T1.ItmsGrpCod=B1.ItmsGrpCod
INNER JOIN OWHS W1 ON T2.WhsCode = W1.WhsCode
INNER JOIN OLCT C1 ON W1.Location=C1.Code
Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location
) a
where (a.[Opening Stock]
+a.[Purchase Quantity]
+ a.[Purchase Return Quantity]
+a.[sale Quatity]+a.[Closing Stock]
) !=0
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
declare @a char(60)
set @a = (select distinct t20.docdate from oinm t20 where t20.docdate = '[%2]')
select distinct c.code, c.name
,SUM(c.bal) [Bal],sum(c.Val) [Val],
(case when avg(c.[FullStock]) > 0 then avg(c.[Pric]) / avg(c.[FullStock]) end ) [AvgPrice],
(case when SUM(c.[0-30 Days]) >= SUM(c.bal) then SUM(c.bal) else SUM(c.[0-30 Days]) end) [0-30 Days],
(case when SUM(c.[0-30 Days]) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >= SUM(c.bal) then (SUM(c.bal) - sum(c.[0-30 Days]))
else sum(c.[30-60 Days]) end)end) [30-60 Days],
(case when SUM(c.[0-30 Days]) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then
(SUM(c.bal) - (sum(c.[0-30 Days]) + sum(c.[30-60 Days]))) else
sum(c.[60-90 Days])end)end) end) [60-90 Days],
(case when SUM(c.[0-30 Days]) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days]) + sum(c.[90-180 Days])) >= SUM(c.bal) then
(SUM(c.bal) - (sum(c.[0-30 Days]) + sum(c.[30-60 Days]) + sum(c.[60-90 Days]))) else sum(c.[90-180 Days])
end)end) end) end) [90-180 Days],
(case when SUM(c.[0-30 Days]) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days]) + sum(c.[90-180 Days])) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days]) +
sum(c.[90-180 Days]) + sum(c.[180-360 Days]) ) >= SUM(c.bal) then
(SUM(c.bal) - (sum(c.[0-30 Days]) + sum(c.[30-60 Days]) + sum(c.[60-90 Days]) + sum(c.[90-180 Days])))
else sum(c.[180-360 Days])end)end)end)end)end) [180-360 Days],
(case when SUM(c.[0-30 Days]) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >= SUM(c.bal) then 0 else
(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then 0 else sum(c.[Above 360 Days])
end)end)end) [Above 360 Days]
from (
select b.code, b.name,
b.Bal, b.Val,b.[Pric],b.[FullStock],
isnull(case when b.days <=30 then b.Bal end,0)[0-30 Days] ,
isnull(case when b.days <= 30 then b.val end,0)[0-30 Days-Value] ,
isnull(case when b.days between 31 and 60 then b.Bal end,0) [30-60 Days],
isnull(case when b.days between 61 and 90 then b.Bal end,0) [60-90 Days],
isnull(case when b.days between 91 and 180 then b.Bal end,0) [90-180 Days],
isnull(case when b.days between 181 and 360 then b.Bal end,0) [180-360 Days],
isnull(case when b.days >= 361 then b.Bal end,0) [Above 360 Days]
from
(
select a.code,a.name,
a.bal,a.val,datediff(dd,dt,@a)'days',a.[Receipt],a.[Pric],a.[FullStock]
from
(
select max(t0.itemcode)'Code',max(t0.Dscription)'Name',
(select sum(isnull(t11.transvalue,0)) from oinm t11 where (t11.itemcode = (t0.itemcode))
and (t11.docdate <= '[%2]' )) [Pric],
(select (sum(isnull(t11.inqty,0)) - sum(isnull(t11.outqty,0)) ) from oinm t11 where (t11.itemcode = (t0.itemcode))
and (t11.docdate <= '[%2]' )) [FullStock],
--(t0.Warehouse)'Wh',
(sum(isnull(t0.inqty,0))) [Receipt],
(sum(isnull(t0.inqty,0)) - sum(isnull(t0.outqty,0)) )'Bal',sum(isnull(t0.transvalue,0))'Val',(t0.docdate)'dt'
from oinm t0 inner join oitm t1 on t0.itemcode=t1.itemcode inner join oitb ob on ob.itmsgrpcod = t1.itmsgrpcod
where (ob.itmsgrpnam = '[%1]')
and (t0.docdate <= '[%2]')
group by t0.itemcode,t0.docdate
)a
)b
)c where c.bal>0
group by c.code, c.name
Hope Helpful
Regards
Kennedy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
3 | |
2 | |
2 | |
2 | |
2 | |
2 | |
1 | |
1 | |
1 | |
1 |