Skip to Content

In this report i have marked one line..if this width < 30,i need to multiply by a number 0.3 and if the width >=30,it multiplies by 0.37...how to use this logic here..??? anyone can help??

In this report i have marked one line..if this width < 30,i need to multiply by a number 0.3 and if the width >=30,it multiplies by 0.37...how to use this logic here..??? anyone can help??

Declare @FromDate Datetime

Declare @ToDate Datetime

Declare @SCCode nvarchar(30)

select @FromDate = min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >='[%0]'

select @ToDate = max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <='[%1]'

--Rcpt from PRDN (Condition checked for Return component exclusion also)

SELECT T2.U_STKNO as 'PRN No', T2.PostDate as Date,

T2.DocNum AS 'WorkOrderNo',

  1. b.DocNum as 'Issue Doc No',

ISNULL(d.DocNum,'') as 'Receipt Doc No',

  1. b.U_IssPSCName as 'SubContractor Name',

T2.ItemCode as 'FG Item Code',

T3.ItemName as 'FG Item Name',

T2.PlannedQty as 'FG Planned Qty',

T2.U_OD as 'OD',

T2.U_ID as 'ID',

T2.U_OD/25.4 as 'Inches',

(T2.U_OD-T2.U_ID)/2 as 'Width',

0 as 'FG Pending Qty',

0 as 'FG Receipt Qty',

'' as 'Issue Item Code',

'' as 'Issue Item Name',

Sum(ISNULL(a.Quantity,0)) as 'Total Issue Quantity',

0 as 'Issue Item - Return Quantity',

'' as 'Return Doc No',

SUM(ISNULL(a.U_IssPTotWeight,0)) as 'Total Issue Weight',

SUM(ISNULL(c.U_Quantity,0)) as 'Total Receipt Weight'

from OWOR T2 inner join WOR1 T4 on T2.DocEntry = T4.DocEntry

INNER JOIN OITM T1 ON T1.ItemCode = T4.ItemCode inner join OITM T3 on T3.ItemCode = T2.ItemCode

LEFT join IGE1 a on T2.DocNum = a.BaseRef Inner JOIN OIGE b on a.DocEntry = b.DocEntry and T4.ItemCode not in (a.ItemCode)

LEFT JOIN IGN1 c ON c.BaseRef = T2.DocNum and T2.ItemCode = c.ItemCode INNER JOIN OIGN d on c.DocEntry = d.DocEntry

WHERE b.Series in('101','20') and T2.PostDate >= @FromDate and T2.PostDate <= @ToDate and b.U_IssPSCName = '[%2]'

GROUP BY T2.U_STKNO, T2.PostDate, T2.DocNum, b.DocNum, d.DocNum, b.U_IssPSCName,T2.ItemCode,T3.ItemName,T2.PlannedQty,T2.U_OD,T2.U_ID, T2.U_OD/25.4,(T2.U_OD-T2.U_ID)/2

UNION ALL

SELECT T2.U_STKNO as 'PRN No', T2.PostDate as Date,

T2.DocNum AS 'WorkOrderNo',

  1. b.DocNum as 'Issue Doc No',

ISNULL(d.DocNum,'') as 'Receipt Doc No',

  1. b.U_IssPSCName as 'SubContractor Name',

T2.ItemCode as 'Item Code',

T3.ItemName as 'Item Name',

T2.PlannedQty as 'Planned Qty',

T2.U_OD as 'OD',

T2.U_ID as 'ID',

T2.U_OD/25.4 as 'Inches',

(T2.U_OD-T2.U_ID)/2 as 'Width',

(Select (T2.PlannedQty - (Select ISNULL(sum(a1.Quantity),0) from IGN1 a1 inner join OWOR b1 on a1.BaseRef = b1.DocNum and a1.ItemCode in (b1.itemcode) where b1.DocNum = t2.DocNum))) as 'Pending Qty',

(Select ISNULL(sum(a1.Quantity),0) from IGN1 a1 inner join OWOR b1 on a1.BaseRef = b1.DocNum and a1.ItemCode in (b1.itemcode) where b1.DocNum = t2.DocNum) as 'Receipt Qty',

  1. a.ItemCode as 'Issued Item Code',
  2. a.Dscription as 'Issued Item Name',

Sum(ISNULL(a.Quantity,0)) as 'Total Issue Quantity',

(Select (Select ISNULL(sum(a1.Quantity),0) from IGN1 a1 inner join OWOR b1 on a1.BaseRef = b1.DocNum inner join WOR1 b2 on b1.DocEntry = b2.DocEntry where b1.DocNum = t2.DocNum and a1.ItemCode in (b2.itemcode))) as 'Issue Item - Return Quantity',

(ISNULL((Select (Select a2.DocNum from OIGN a2 where a2.DocEntry = a1.DocEntry) from IGN1 a1 inner join OWOR b1 on a1.BaseRef = b1.DocNum inner join WOR1 b2 on b1.DocEntry = b2.DocEntry where b1.DocNum = t2.DocNum and a1.ItemCode in (b2.itemcode)),'')) as 'Return Doc No',

SUM(ISNULL(a.U_IssPTotWeight,0)) as 'Total Issue Weight',

SUM(ISNULL(c.U_Quantity,0)) as 'Total Receipt Weight'

from OWOR T2 inner join WOR1 T4 on T2.DocEntry = T4.DocEntry

INNER JOIN OITM T1 ON T1.ItemCode = T4.ItemCode inner join OITM T3 on T3.ItemCode = T2.ItemCode

LEFT join IGE1 a on T2.DocNum = a.BaseRef Inner JOIN OIGE b on a.DocEntry = b.DocEntry and T4.ItemCode in (a.ItemCode)

LEFT JOIN IGN1 c ON c.BaseRef = T2.DocNum and T2.ItemCode = c.ItemCode LEFT JOIN OIGN d on c.DocEntry = d.DocEntry

WHERE b.Series in('101','20') and T2.PostDate >= @FromDate and T2.PostDate <= @ToDate and b.U_IssPSCName = '[%2]'

GROUP BY T2.U_STKNO, T2.PostDate, T2.DocNum, b.DocNum, d.DocNum, b.U_IssPSCName,T2.ItemCode,T3.ItemName,T2.PlannedQty,T2.U_OD,T2.U_ID,T2.U_OD/25.4,(T2.U_OD-T2.U_ID)/2,a.ItemCode,a.Dscription order by T2.DocNum desc

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Posted on Apr 08, 2014 at 12:45 AM

    In this report i have marked one line..if this width < 30,i need to multiply by a number 0.3 and if the width >=30,it multiplies by 0.37...how to use this logic here..??? anyone can help??

    Declare @FromDate Datetime

    Declare @ToDate Datetime

    Declare @SCCode nvarchar(30)

    select @FromDate = min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >='[%0]'

    select @ToDate = max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <='[%1]'

    --Rcpt from PRDN (Condition checked for Return component exclusion also)

    SELECT T2.U_STKNO as 'PRN No', T2.PostDate as Date,

    T2.DocNum AS 'WorkOrderNo',

    1. b.DocNum as 'Issue Doc No',

    ISNULL(d.DocNum,'') as 'Receipt Doc No',

    1. b.U_IssPSCName as 'SubContractor Name',

    T2.ItemCode as 'FG Item Code',

    T3.ItemName as 'FG Item Name',T2.PlannedQty as 'FG Planned Qty',

    T2.U_OD as 'OD',T2.U_ID as 'ID',T2.U_OD/25.4 as 'Inches',

    (T2.U_OD-T2.U_ID)/2 as 'Width',

    0 as 'FG Pending Qty',0 as 'FG Receipt Qty','' as 'Issue Item Code','' as 'Issue Item Name',Sum(ISNULL(a.Quantity,0)) as 'Total Issue Quantity',0 as 'Issue Item - Return Quantity',

    '' as 'Return Doc No',SUM(ISNULL(a.U_IssPTotWeight,0)) as 'Total Issue Weight',

    SUM(ISNULL(c.U_Quantity,0)) as 'Total Receipt Weight'

    from OWOR T2 inner join WOR1 T4 on T2.DocEntry = T4.DocEntryI

    NNER JOIN OITM T1 ON T1.ItemCode = T4.ItemCode inner join OITM T3 on T3.ItemCode = T2.ItemCode

    LEFT join IGE1 a on T2.DocNum = a.BaseRef Inner JOIN OIGE b on a.DocEntry = b.DocEntry

    and T4.ItemCode not in (a.ItemCode)

    LEFT JOIN IGN1 c ON c.BaseRef = T2.DocNum and T2.ItemCode = c.ItemCode INNER JOIN OIGN d on c.DocEntry = d.DocEntry

    WHERE b.Series in('101','20') and T2.PostDate >= @FromDate and T2.PostDate <= @ToDate and b.U_IssPSCName = '[%2]'

    GROUP BY T2.U_STKNO, T2.PostDate, T2.DocNum, b.DocNum, d.DocNum, b.U_IssPSCName,T2.ItemCode,T3.ItemName,T2.PlannedQty,T2.U_OD,T2.U_ID, T2.U_OD/25.4,(T2.U_OD-T2.U_ID)/2

    UNION ALL SELECT T2.U_STKNO as 'PRN No', T2.PostDate as Date,

    T2.DocNum AS 'WorkOrderNo',

    1. b.DocNum as 'Issue Doc No',

    ISNULL(d.DocNum,'') as 'Receipt Doc No',

    1. b.U_IssPSCName as 'SubContractor Name',

    T2.ItemCode as 'Item Code',T3.ItemName as 'Item Name',T2.PlannedQty as 'Planned Qty'

    ,T2.U_OD as 'OD',T2.U_ID as 'ID',T2.U_OD/25.4 as 'Inches',

    (T2.U_OD-T2.U_ID)/2 as 'Width',

    (Select (T2.PlannedQty - (Select ISNULL(sum(a1.Quantity),0)

    from IGN1 a1 inner join OWOR b1 on a1.BaseRef = b1.DocNum and a1.ItemCode in (b1.itemcode) where b1.DocNum = t2.DocNum))) as 'Pending Qty',

    (Select ISNULL(sum(a1.Quantity),0) from IGN1 a1 inner join OWOR b1 on a1.BaseRef = b1.DocNum and a1.ItemCode in (b1.itemcode)

    where b1.DocNum = t2.DocNum) as 'Receipt Qty',

    1. a.ItemCode as 'Issued Item Code',
    2. a.Dscription as 'Issued Item Name',

    Sum(ISNULL(a.Quantity,0)) as 'Total Issue Quantity',

    (Select (Select ISNULL(sum(a1.Quantity),0) from IGN1 a1 inner join OWOR b1 on a1.BaseRef = b1.DocNum inner join WOR1 b2 on b1.DocEntry = b2.DocEntry

    where b1.DocNum = t2.DocNum and a1.ItemCode in (b2.itemcode))) as 'Issue Item - Return Quantity',

    (ISNULL((Select (Select a2.DocNum from OIGN a2 where a2.DocEntry = a1.DocEntry) from IGN1 a1 inner join OWOR b1 on a1.BaseRef = b1.DocNum inner join WOR1 b2 on b1.DocEntry = b2.DocEntry where b1.DocNum = t2.DocNum and a1.ItemCode in (b2.itemcode)),'')) as 'Return Doc No',

    SUM(ISNULL(a.U_IssPTotWeight,0)) as 'Total Issue Weight',

    SUM(ISNULL(c.U_Quantity,0)) as 'Total Receipt Weight'

    from OWOR T2 inner join WOR1 T4 on T2.DocEntry = T4.DocEntry

    INNER JOIN OITM T1 ON T1.ItemCode = T4.ItemCode inner join OITM T3 on T3.ItemCode = T2.ItemCode

    LEFT join IGE1 a on T2.DocNum = a.BaseRef Inner JOIN OIGE b on a.DocEntry = b.DocEntry and T4.ItemCode in (a.ItemCode)

    LEFT JOIN IGN1 c ON c.BaseRef = T2.DocNum and T2.ItemCode = c.ItemCode LEFT JOIN OIGN d on c.DocEntry = d.DocEntry

    WHERE b.Series in('101','20') and T2.PostDate >= @FromDate and T2.PostDate <= @ToDate and b.U_IssPSCName = '[%2]'

    GROUP BY T2.U_STKNO, T2.PostDate, T2.DocNum, b.DocNum, d.DocNum, b.U_IssPSCName,T2.ItemCode,T3.ItemName,

    T2.PlannedQty,T2.U_OD,

    T2.U_ID,T2.U_OD/25.4,(T2.U_OD-T2.U_ID)/2,a.ItemCode,a.Dscription

    order by T2.DocNum desc

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi,

      Try this:

      Declare @FromDate Datetime

      Declare @ToDate Datetime

      Declare @SCCode nvarchar(30)

      select @FromDate = min(S0.Docdate) from dbo.OINM S0 where S0.Docdate >='[%0]'

      select @ToDate = max(S1.Docdate) from dbo.OINM s1 where S1.Docdate <='[%1]'

      --Rcpt from PRDN (Condition checked for Return component exclusion also)

      SELECT T2.U_STKNO as 'PRN No', T2.PostDate as Date,

      T2.DocNum AS 'WorkOrderNo',

      1. b.DocNum as 'Issue Doc No',

      ISNULL(d.DocNum,'') as 'Receipt Doc No',

      1. b.U_IssPSCName as 'SubContractor Name',

      T2.ItemCode as 'FG Item Code',T3.ItemName as 'FG Item Name',T2.PlannedQty as 'FG Planned Qty',T2.U_OD as 'OD',T2.U_ID as 'ID',T2.U_OD/25.4 as 'Inches',(T2.U_OD-T2.U_ID)/2 as 'Width',case when ((T2.U_OD-T2.U_ID)/2) <30 then ((T2.U_OD-T2.U_ID)/2) *0.3 end, 0 as 'FG Pending Qty',0 as 'FG Receipt Qty','' as 'Issue Item Code','' as 'Issue Item Name',Sum(ISNULL(a.Quantity,0)) as 'Total Issue Quantity',0 as 'Issue Item - Return Quantity','' as 'Return Doc No',SUM(ISNULL(a.U_IssPTotWeight,0)) as 'Total Issue Weight',SUM(ISNULL(c.U_Quantity,0)) as 'Total Receipt Weight'from OWOR T2 inner join WOR1 T4 on T2.DocEntry = T4.DocEntryINNER JOIN OITM T1 ON T1.ItemCode = T4.ItemCode inner join OITM T3 on T3.ItemCode = T2.ItemCodeLEFT join IGE1 a on T2.DocNum = a.BaseRef Inner JOIN OIGE b on a.DocEntry = b.DocEntry and T4.ItemCode not in (a.ItemCode)LEFT JOIN IGN1 c ON c.BaseRef = T2.DocNum and T2.ItemCode = c.ItemCode INNER JOIN OIGN d on c.DocEntry = d.DocEntryWHERE b.Series in('101','20') and T2.PostDate >= @FromDate and T2.PostDate <= @ToDate and b.U_IssPSCName = '[%2]'GROUP BY T2.U_STKNO, T2.PostDate, T2.DocNum, b.DocNum, d.DocNum, b.U_IssPSCName,T2.ItemCode,T3.ItemName,T2.PlannedQty,T2.U_OD,T2.U_ID, T2.U_OD/25.4,(T2.U_OD-T2.U_ID)/2UNION ALL SELECT T2.U_STKNO as 'PRN No', T2.PostDate as Date,T2.DocNum AS 'WorkOrderNo',

      1. b.DocNum as 'Issue Doc No',

      ISNULL(d.DocNum,'') as 'Receipt Doc No',

      1. b.U_IssPSCName as 'SubContractor Name',

      T2.ItemCode as 'Item Code',T3.ItemName as 'Item Name',T2.PlannedQty as 'Planned Qty',T2.U_OD as 'OD',T2.U_ID as 'ID',T2.U_OD/25.4 as 'Inches',(T2.U_OD-T2.U_ID)/2 as 'Width',case when ((T2.U_OD-T2.U_ID)/2) >=30 then ((T2.U_OD-T2.U_ID)/2) *0.37 end, (Select (T2.PlannedQty - (Select ISNULL(sum(a1.Quantity),0) from IGN1 a1 inner join OWOR b1 on a1.BaseRef = b1.DocNum and a1.ItemCode in (b1.itemcode) where b1.DocNum = t2.DocNum))) as 'Pending Qty',(Select ISNULL(sum(a1.Quantity),0) from IGN1 a1 inner join OWOR b1 on a1.BaseRef = b1.DocNum and a1.ItemCode in (b1.itemcode) where b1.DocNum = t2.DocNum) as 'Receipt Qty',

      1. a.ItemCode as 'Issued Item Code',
      2. a.Dscription as 'Issued Item Name',

      Sum(ISNULL(a.Quantity,0)) as 'Total Issue Quantity',

      (Select (Select ISNULL(sum(a1.Quantity),0) from IGN1 a1 inner join OWOR b1 on a1.BaseRef = b1.DocNum inner join WOR1 b2 on b1.DocEntry = b2.DocEntry

      where b1.DocNum = t2.DocNum and a1.ItemCode in (b2.itemcode))) as 'Issue Item - Return Quantity',

      (ISNULL((Select (Select a2.DocNum from OIGN a2 where a2.DocEntry = a1.DocEntry) from IGN1 a1 inner join OWOR b1 on a1.BaseRef = b1.DocNum inner join WOR1 b2 on b1.DocEntry = b2.DocEntry where b1.DocNum = t2.DocNum and a1.ItemCode in (b2.itemcode)),'')) as 'Return Doc No',

      SUM(ISNULL(a.U_IssPTotWeight,0)) as 'Total Issue Weight',

      SUM(ISNULL(c.U_Quantity,0)) as 'Total Receipt Weight'

      from OWOR T2 inner join WOR1 T4 on T2.DocEntry = T4.DocEntry

      INNER JOIN OITM T1 ON T1.ItemCode = T4.ItemCode inner join OITM T3 on T3.ItemCode = T2.ItemCode

      LEFT join IGE1 a on T2.DocNum = a.BaseRef Inner JOIN OIGE b on a.DocEntry = b.DocEntry and T4.ItemCode in (a.ItemCode)

      LEFT JOIN IGN1 c ON c.BaseRef = T2.DocNum and T2.ItemCode = c.ItemCode LEFT JOIN OIGN d on c.DocEntry = d.DocEntry

      WHERE b.Series in('101','20') and T2.PostDate >= @FromDate and T2.PostDate <= @ToDate and b.U_IssPSCName = '[%2]'

      GROUP BY T2.U_STKNO, T2.PostDate, T2.DocNum, b.DocNum, d.DocNum, b.U_IssPSCName,T2.ItemCode,T3.ItemName,

      T2.PlannedQty,T2.U_OD,

      T2.U_ID,T2.U_OD/25.4,(T2.U_OD-T2.U_ID)/2,a.ItemCode,a.Dscription

      order by T2.DocNum desc

      Thanks & Regards,

      Nagarajan

  • Posted on Jun 03, 2014 at 06:14 AM

    Hi,

    Any update?

    Thanks & Regards,

    Nagarajan

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jun 03, 2014 at 02:58 PM

    Hi,

    Use the elsestatement in the 'case when' expression!

    case when (T2.U_OD-T2.U_ID)/2 < 30 then ((T2.U_OD-T2.U_ID)/2) * 0.3 else ((T2.U_OD-T2.U_ID)/2) * 0.37 end


    Regards,

    Csaba

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.