SQL Query help in Subtracting two columns

Hello there,

I have this query and I want to subtract two result from another colum result,like so
soldQtyincldisc - (dbo.deliveryorderdetail.quantityDiscount - dbo.deliveryorderdetail.supplierquantitydiscount).
can somebody help me please

cheers
Zolf

SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + cast (dbo.Tafsil.code as  varchar(50)) AS code,
      dbo.Branch.NAME
    , dbo.Tafsil.description
    , CONVERT(VARCHAR(10), dateadd(day, datediff(day,0, dbo.Receipt.receiptDate ), 0), 105) AS saledate
    , sum(dbo.SaleDetail.quantity) AS soldQtyincldisc,
    dbo.deliveryorderdetail.quantityDiscount,
   dbo.deliveryorderdetail.supplierquantitydiscount
FROM dbo.Batch
LEFT OUTER JOIN dbo.Tafsil ON (dbo.Batch.productId = dbo.Tafsil.id)
LEFT OUTER JOIN dbo.SaleDetail ON (dbo.Batch.id = dbo.SaleDetail.batchId)
LEFT OUTER JOIN dbo.Sale ON (dbo.SaleDetail.saleId = dbo.Sale.id)
LEFT OUTER JOIN dbo.Receipt ON (dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
LEFT OUTER JOIN dbo.Warehouse ON (dbo.Batch.warehouseId = dbo.Warehouse.id)
LEFT OUTER JOIN dbo.Branch ON (dbo.Warehouse.branchId = dbo.Branch.id)
LEFT OUTER JOIN dbo.Mohin ON  (dbo.Tafsil.mohinId = dbo.Mohin.id)
LEFT OUTER JOIN dbo.Kol ON (dbo.Mohin.kolId = dbo.Kol.id)
LEFT OUTER JOIN dbo.DeliveryOrderdetail ON (dbo.Saledetail.orderdetailId = dbo.DeliveryOrderdetail.id)
WHERE dbo.Receipt.receiptDate >= '2015-06-22'
    AND dbo.Receipt.receiptDate <  '2015-07-23'
GROUP BY
      dbo.Branch.NAME
    , dbo.Tafsil.description
    , dateadd(day, datediff(day,0, dbo.Receipt.receiptDate ), 0),
    kol.code,
    Mohin.code,
    Tafsil.code,
    dbo.deliveryorderdetail.quantityDiscount,
    dbo.deliveryorderdetail.supplierquantitydiscount
ORDER BY 
      dateadd(day, datediff(day,0, dbo.Receipt.receiptDate ), 0)

Open in new window

zolfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
It depends on the data model. E.g.

 
SELECT  RIGHT('000' + CAST(K.code AS VARCHAR), 3) + RIGHT('00' + CONVERT(VARCHAR, M.code), 3) + '' + CAST (T.code AS VARCHAR(50)) AS code ,
        Br.NAME ,
        T.[DESCRIPTION] ,
        CONVERT(VARCHAR(10), DATEADD(DAY, DATEDIFF(DAY, 0, R.receiptDate), 0), 105) AS saledate ,
        SUM(SD.quantity) AS soldQtyincldisc ,
        SUM(SD.quantity - DOD.quantityDiscount) AS soldQtyincldisc2 ,
        SUM(SD.quantity) - DOD.quantityDiscount AS soldQtyincldisc3 ,
        DOD.quantityDiscount ,
        DOD.supplierquantitydiscount
FROM    dbo.Batch Bt
        LEFT JOIN dbo.Tafsil T ON Bt.productId = T.id
        LEFT JOIN dbo.SaleDetail SD ON Bt.id = SD.batchId
        LEFT JOIN dbo.Sale S ON SD.saleId = S.id
        LEFT JOIN dbo.Receipt R ON S.invoiceReceiptId = R.id
        LEFT JOIN dbo.Warehouse W ON Bt.warehouseId = W.id
        LEFT JOIN dbo.Branch Br ON W.branchId = Br.id
        LEFT JOIN dbo.Mohin M ON T.mohinId = M.id
        LEFT JOIN dbo.Kol K ON M.kolId = K.id
        LEFT JOIN dbo.DeliveryOrderdetail DOD ON SD.orderdetailId = DOD.id
WHERE   R.receiptDate >= '2015-06-22'
        AND R.receiptDate < '2015-07-23'
GROUP BY Br.NAME ,
        T.[DESCRIPTION] ,
        DATEADD(DAY, DATEDIFF(DAY, 0, R.receiptDate), 0) ,
        K.code ,
        M.code ,
        T.code ,
        DOD.quantityDiscount ,
        DOD.supplierquantitydiscount
ORDER BY DATEADD(DAY, DATEDIFF(DAY, 0, R.receiptDate), 0);

Open in new window

0
Vikas GargBusiness Intelligence DeveloperCommented:
Hello,



SELECT
RIGHT('000' + CAST(kol.code AS VARCHAR), 3) + RIGHT('00'+ CONVERT(VARCHAR,dbo.Mohin.code),3) + '' + cast (dbo.Tafsil.code as  varchar(50)) AS code,
      dbo.Branch.NAME
    , dbo.Tafsil.description
    , CONVERT(VARCHAR(10), dateadd(day, datediff(day,0, dbo.Receipt.receiptDate ), 0), 105) AS saledate
    , sum(dbo.SaleDetail.quantity) AS soldQtyincldisc,
    dbo.deliveryorderdetail.quantityDiscount,
   dbo.deliveryorderdetail.supplierquantitydiscount
   ,
   sum(dbo.SaleDetail.quantity) - (dbo.deliveryorderdetail.quantityDiscount - dbo.deliveryorderdetail.supplierquantitydiscount) resultcolum

FROM dbo.Batch
LEFT OUTER JOIN dbo.Tafsil ON (dbo.Batch.productId = dbo.Tafsil.id)
LEFT OUTER JOIN dbo.SaleDetail ON (dbo.Batch.id = dbo.SaleDetail.batchId)
LEFT OUTER JOIN dbo.Sale ON (dbo.SaleDetail.saleId = dbo.Sale.id)
LEFT OUTER JOIN dbo.Receipt ON (dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
LEFT OUTER JOIN dbo.Warehouse ON (dbo.Batch.warehouseId = dbo.Warehouse.id)
LEFT OUTER JOIN dbo.Branch ON (dbo.Warehouse.branchId = dbo.Branch.id)
LEFT OUTER JOIN dbo.Mohin ON  (dbo.Tafsil.mohinId = dbo.Mohin.id)
LEFT OUTER JOIN dbo.Kol ON (dbo.Mohin.kolId = dbo.Kol.id)
LEFT OUTER JOIN dbo.DeliveryOrderdetail ON (dbo.Saledetail.orderdetailId = dbo.DeliveryOrderdetail.id)
WHERE dbo.Receipt.receiptDate >= '2015-06-22'
    AND dbo.Receipt.receiptDate <  '2015-07-23'
GROUP BY
      dbo.Branch.NAME
    , dbo.Tafsil.description
    , dateadd(day, datediff(day,0, dbo.Receipt.receiptDate ), 0),
    kol.code,
    Mohin.code,
    Tafsil.code,
    dbo.deliveryorderdetail.quantityDiscount,
    dbo.deliveryorderdetail.supplierquantitydiscount
ORDER BY
      dateadd(day, datediff(day,0, dbo.Receipt.receiptDate ), 0)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
Please just use the embed code buttons..

use-code-tags-please.png
0
zolfAuthor Commented:
Thanks for your comments!! It is not working as I am expecting.Please see the screenshot.


1
0
Anoo S PillaiCommented:
Double check whether your calculation should be -  

soldQtyincldisc -
(dbo.deliveryorderdetail.quantityDiscount - dbo.deliveryorderdetail.supplierquantitydiscount)

OR

soldQtyincldisc -
(dbo.deliveryorderdetail.quantityDiscount + dbo.deliveryorderdetail.supplierquantitydiscount)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.