Skip to Content
avatar image
Former Member

Warehouse wise Inventory Query

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Mar 02, 2017 at 01:43 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 03, 2017 at 11:15 AM

    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


    Add comment
    10|10000 characters needed characters exceeded

  • Mar 03, 2017 at 12:02 PM

    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

    Add comment
    10|10000 characters needed characters exceeded