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 ?
---- Mark It As A Answer If It Helps You --- Thanks ... asp.net1
Interested in Azure, ASP.NET (MVC), jQuery, WCF, Silverlight, EF, MS SQL, ...
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
Member
67 Points
205 Posts
Subtraction colum value
Sep 18, 2012 04:41 AM|asp.net1|LINK
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 ?
Interested in Azure, ASP.NET (MVC), jQuery, WCF, Silverlight, EF, MS SQL, ...
Participant
1822 Points
562 Posts
Re: Subtraction colum value
Sep 18, 2012 04:58 AM|Prince Srivastava|LINK
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 .,
My Blog
PRINCE (INDIA)
Member
67 Points
205 Posts
Re: Subtraction colum value
Sep 18, 2012 06:04 AM|asp.net1|LINK
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
Interested in Azure, ASP.NET (MVC), jQuery, WCF, Silverlight, EF, MS SQL, ...
All-Star
94120 Points
18111 Posts
Re: Subtraction colum value
Sep 19, 2012 10:35 PM|Decker Dong - MSFT|LINK
If you wanna insert a blank space into the CurrentDue，please do this：
convert(varchar(2),' ') as NewColumnName
Member
351 Points
209 Posts
Re: Subtraction colum value
Sep 19, 2012 10:53 PM|graciax8|LINK
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
My Codepad@Blogspot.com ||