locked
Subtraction colum value RRS feed

  • Question

  • User35050261 posted

    Query

    select s.InvoiceID , s.Amount as InvAmount, md.PaidAmt as PrePaid , (select '' as CurrentDue) as CurrentDue , md.ReceiptID, md.Location,
    md.MachineNameIP, md.Transfered, md.HoTransfered, md.Addedby, md.DateAdded, md.Updatedby, md.DateUpdated ,
    (select '' as PaidAmt) as PaidAmt, md.VoucherNo, md.AdjustmentCause, md.AdjustedAmount
    from SalesInvoice as s join MRDetails as md on s.InvoiceID = md.InvoiceID
    where ChemistID=@ChemistID and InvoiceType=@InvoiceType

    Data

    INV000001 3040.00 1.00        __   MR00001 HO ROV-PC-DB ## Admin ## 10.10.1.65 0 0 Sourov 2012-09-09 15:47:10.373 NULL NULL ok ok 2.00

    INV000002 100.00 2.00          __   MR00001 HO ROV-PC-DB ## Admin ## 10.10.1.65 0 0 Sourov 2012-09-09 15:47:10.373 NULL NULL ko ko 3.00
    INV000002 100.00 100.00      __    MR00002 HO ROV-PC-DB ## Admin ## fe80::344d:309a:f5f5:febe%13 0 0 Sourov 2012-09-09 16:20:32.120 NULL NULL ok ok 1000.00
    INV000002 100.00 3.00          __   MR00003 HO ROV-PC-DB ## Admin ## fe80::2c25:12cd:f5f5:febe%13 0 0 Sourov 2012-09-09 15:58:36.000 Sourov 2012-09-09 16:04:30.457 usa usa 3.00
    INV000003 203.00 2.00          __   MR00003 HO ROV-PC-DB ## Admin ## fe80::2c25:12cd:f5f5:febe%13 0 0 Sourov 2012-09-09 15:58:36.000 Sourov 2012-09-09 16:04:30.457 us us 2.00
    INV000004 285.00 1.00          __   MR00003 HO ROV-PC-DB ## Admin ## fe80::2c25:12cd:f5f5:febe%13 0 0 Sourov 2012-09-09 15:58:36.000 Sourov 2012-09-09 16:04:30.463 ok ok 1.00

    I need blank space value by subtraction

     s.Amount as InvAmount  - md.PaidAmt as PrePaid, = (select '' as CurrentDue) as CurrentDue 

    3040.00                          - 1.00  = 3039

    Any Doubt to understand the problem ? 

    Tuesday, September 18, 2012 4:41 AM

Answers

  • User1413134711 posted

    hey asp.net1

    firstly remove the as

    s.Amount as InvAmount  - md.PaidAmt as PrePaid,

    becuase in subtraction it gives error if you are alias them


    and check its datatype or int ,bigint or decimal etc  or not 

    and you are not compare with '' space .becuse it return 0 or 0..00 accordingly . SO best way if you want ot compare this with 0

    Hope this will help you .,

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 18, 2012 4:58 AM
  • User1137674169 posted

    just simply do this to get the difference

    select s.InvoiceID , s.Amount as InvAmount, md.PaidAmt as PrePaid, (s.Amount-md.PaidAmt) as CurrentDue

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 19, 2012 10:53 PM

All replies

  • User1413134711 posted

    hey asp.net1

    firstly remove the as

    s.Amount as InvAmount  - md.PaidAmt as PrePaid,

    becuase in subtraction it gives error if you are alias them


    and check its datatype or int ,bigint or decimal etc  or not 

    and you are not compare with '' space .becuse it return 0 or 0..00 accordingly . SO best way if you want ot compare this with 0

    Hope this will help you .,

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 18, 2012 4:58 AM
  • User35050261 posted

    select s.InvoiceID , s.Amount as InvAmount, md.PaidAmt as PrePaid, (select s.Amount-md.PaidAmt as CurrentDue) as CurrentDue , md.ReceiptID, md.Location,md.MachineNameIP, md.Transfered, md.HoTransfered, md.Addedby, md.DateAdded, md.Updatedby, md.DateUpdated ,
    (select '' as PaidAmt) as PaidAmt, md.VoucherNo, md.AdjustmentCause, md.AdjustedAmount from SalesInvoice as s join MRDetails as md on s.InvoiceID = md.InvoiceID
    where ChemistID=@ChemistID and InvoiceType=@InvoiceType

    INV000001 3040.00 1.00 3039.00 MR00001 HO ROV-PC-DB ## Admin ## 10.10.1.65 0 0 Sourov 2012-09-09 15:47:10.373 NULL NULL ok ok 2.00

    Tuesday, September 18, 2012 6:04 AM
  • User3866881 posted

    (select '' as CurrentDue) as CurrentDue

    If you wanna insert a blank space into the CurrentDue,please do this:

    convert(varchar(2),' ') as NewColumnName

    Wednesday, September 19, 2012 10:35 PM
  • User1137674169 posted

    just simply do this to get the difference

    select s.InvoiceID , s.Amount as InvAmount, md.PaidAmt as PrePaid, (s.Amount-md.PaidAmt) as CurrentDue

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 19, 2012 10:53 PM