Warehouse wise Inventory Query

[amol_bairagi]

Dear Experts,

I am trying to get a query that contains the following fields from each table and want to add Warehouse code & name in these query but its give me error.

select a.ItemCode,a.[Name], a.[Warehouse],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,t1.Warehouse, 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]' group by t1.ItemCode,t1.[Warehouse] union all select t1.ItemCode, t1.Warehouse ,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]' group by t1.ItemCode,t1.Warehouse union all select t1.ItemCode, t1.Warehouse ,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]' group by t1.ItemCode)a group by a.ItemCode, a.[Name],a.[Warehouse]

Regards

Amol

[kothandaraman_nagarajan]

Hi,

Try this query,

Declare @fromdate as datetime

Declare @Todate as datetime

set @fromdate = ( select min(Ta.[DocDate]) from OINM ta where Ta.[DocDate] >= [%0])

set @Todate = ( select max(Tb.[DocDate]) from OINM tb where Tb.[DocDate] <= [%1])

select a.ItemCode,a.[Name], a.[Warehouse],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,t1.Warehouse, 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 < @Fromdate

group by t1.ItemCode,t1.[Warehouse]

union all

select t1.ItemCode, t1.Warehouse ,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 >= @Fromdate and t1.DocDate <= @Todate

group by t1.ItemCode,t1.Warehouse

union all

select t1.ItemCode, t1.Warehouse ,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 <= @Todate

group by t1.ItemCode,t1.Warehouse)a

group by a.ItemCode, a.[Name],a.[Warehouse]

Regards,

Nagarajan

[amol_bairagi]

Dear Johan,

Thanks for reply, when i run these query i face these below error

"). [Microsoft][SQL Server Native Client 10.0][SQL Server]Must specify table to select from. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement '' (SWEI) (s) could not be prepared.

and when i run these query at Sql server level then " Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string."

Could you please help out from these issue.

Regards

Amol

[Johan_H]

Hi Amol,

You forgot to add , t1.Warehouse to the GROUP BY clause of the last query in the outer FROM clause. Please try this:

select a.ItemCode
      ,a.[Name]
  ,a.[Warehouse]
  ,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
            ,t1.Warehouse
,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]' 
  group by t1.ItemCode,t1.[Warehouse] 
  union all 
  select t1.ItemCode, t1.Warehouse ,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]' 
  group by t1.ItemCode,t1.Warehouse 
  union all 
  select t1.ItemCode, t1.Warehouse ,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]' 
  group by t1.ItemCode, t1.Warehouse)a 
group by a.ItemCode, a.[Name],a.[Warehouse]

Regards,

Johan