SQL Query Help

try change to:

SELECT
    dbo.Receipt.transactionNumber,
    dbo.Receipt.receiptDate,
    dbo.Tafsil.description,
    dbo.Branch.name,
    SUM(dbo.SaleDetail.quantity)                     AS SalesQtyinclDis,
    SUM(dbo.DeliveryOrderDetail.quantity )                AS SalesQty,
    SUM(dbo.DeliveryOrderDetail.quantityDiscount )        AS Dis1,
    SUM(dbo.DeliveryOrderDetail.supplierQuantityDiscount)  AS Dis2
FROM
    dbo.SaleDetail
INNER JOIN
    dbo.Sale
ON
    (
        dbo.SaleDetail.saleId = dbo.Sale.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.Batch Batch_alias1
ON
    (
        dbo.SaleDetail.batchId = Batch_alias1.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        Batch_alias1.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
INNER JOIN
    dbo.DeliveryOrderDetail
ON
    (
        dbo.SaleDetail.orderDetailId = dbo.DeliveryOrderDetail.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.Receipt.receiptDate >= '2015-06-22'
AND dbo.Receipt.receiptDate < '2015-07-23'
GROUP BY
    dbo.Receipt.transactionNumber,
    dbo.Receipt.receiptDate,
    dbo.Tafsil.description,
    dbo.Branch.name
 

Select allOpen in new window

?

SELECT
    ... ,
    dbo.Receipt.receiptDate,
   ...
 WHERE
    dbo.Receipt.receiptDate >= '2015-06-22'
AND dbo.Receipt.receiptDate < '2015-07-23'
GROUP BY
    ...,
    dbo.Receipt.receiptDate, ...
 
Remove the date from the select and group by clauses (in bold)
You are currently asking for rows for EACH DATE, so stop doing that and the number of rows will reduce.

-----
If you must have a date column you could use a constant for the starting date or ending date or both e.g.

SELECT
    ... ,
    dateadd(day,0,'20150622') as PeriodStartDate,
    ...

 WHERE
    dbo.Receipt.receiptDate >= '2015-06-22'
AND dbo.Receipt.receiptDate < '2015-07-23'
GROUP BY
    ...

This will still reduce the number of rows from your current query.

Thanks guys for your feedbacks!!

Ryan Chong:
your query was not helpful.

Paul Maxwell:
your explanation was helpful for me to understand the core principle and then I managed to make some changes to my query and it brought me closer to my requirement but I have issue with one column. That is the Discount columns. please see the screenshot and my query changes.

SELECT
    --dbo.Receipt.transactionNumber,
    --dbo.Receipt.receiptDate,
    dbo.Tafsil.description as ProductName,
    dbo.Branch.name as BranchOffice,
    SUM(dbo.SaleDetail.quantity)                     AS SalesQtyInclDis,
    --dbo.DeliveryOrderDetail.quantity                 AS SalesQty,
    --dbo.DeliveryOrderDetail.quantityDiscount         AS Dis1,
    --dbo.DeliveryOrderDetail.supplierQuantityDiscount AS Dis2,
    SUM(DeliveryOrderDetail.quantityDiscount+DeliveryOrderDetail.supplierQuantityDiscount) as TotalDiscount
FROM
    dbo.SaleDetail
INNER JOIN
    dbo.Sale
ON
    (
        dbo.SaleDetail.saleId = dbo.Sale.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.Batch Batch_alias1
ON
    (
        dbo.SaleDetail.batchId = Batch_alias1.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        Batch_alias1.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
INNER JOIN
    dbo.DeliveryOrderDetail
ON
    (
        dbo.SaleDetail.orderDetailId = dbo.DeliveryOrderDetail.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.Receipt.receiptDate >= '2015-06-22'
AND dbo.Receipt.receiptDate < '2015-07-23'
GROUP BY
    --dbo.Receipt.transactionNumber,
    --dbo.Receipt.receiptDate,
    dbo.Tafsil.description,
    dbo.Branch.name
    --dbo.DeliveryOrderDetail.quantity,
    --dbo.DeliveryOrderDetail.quantityDiscount,
    --dbo.DeliveryOrderDetail.supplierQuantityDiscount ;

Select allOpen in new window

Zolf,
You are expecting us to know/understand your tables & fields.
That is very hard for us (probably impossible without your help).

You are using this:
SUM( DeliveryOrderDetail.quantityDiscount
       +DeliveryOrderDetail.supplierQuantityDiscount  ) as TotalDiscount

That is giving incorrect answers, but what IS the correct result for that column?
(we don't know this only you do)

Using a very small data example,how would you (manually) calculate the correct result?

Thanks Paul for your feedbacks, I have tried to product screenshot of the tables to help you understand my requirement and hence help me to achieve my goal.

and this is the query which gives me the records for the above screenshot i.e I have not added them all up.

SELECT
    dbo.Receipt.transactionNumber,
    dbo.Receipt.receiptDate,
    dbo.Tafsil.description as productName,
    dbo.Branch.name as branchoffice,
    SUM(dbo.SaleDetail.quantity)                     AS SalesQtyinclDis,
    dbo.DeliveryOrderDetail.quantity                 AS SalesQty,
    dbo.DeliveryOrderDetail.quantityDiscount         AS Dis1,
    dbo.DeliveryOrderDetail.supplierQuantityDiscount AS Dis2,
   (dbo.DeliveryOrderDetail.quantityDiscount+dbo.DeliveryOrderDetail.supplierQuantityDiscount) as DiscountSum
FROM
    dbo.SaleDetail
INNER JOIN
    dbo.Sale
ON
    (
        dbo.SaleDetail.saleId = dbo.Sale.id)
INNER JOIN
    dbo.Receipt
ON
    (
        dbo.Sale.invoiceReceiptId = dbo.Receipt.id)
INNER JOIN
    dbo.Batch Batch_alias1
ON
    (
        dbo.SaleDetail.batchId = Batch_alias1.id)
INNER JOIN
    dbo.Tafsil
ON
    (
        Batch_alias1.productId = dbo.Tafsil.id)
INNER JOIN
    dbo.Branch
ON
    (
        dbo.Receipt.branchId = dbo.Branch.id)
INNER JOIN
    dbo.DeliveryOrderDetail
ON
    (
        dbo.SaleDetail.orderDetailId = dbo.DeliveryOrderDetail.id)
INNER JOIN
    dbo.Mohin
ON
    (
        dbo.Tafsil.mohinId = dbo.Mohin.id)
INNER JOIN
    dbo.Kol
ON
    (
        dbo.Mohin.kolId = dbo.Kol.id)
WHERE
    dbo.Receipt.receiptDate >= '2015-06-22'
AND dbo.Receipt.receiptDate < '2015-07-23'
GROUP BY
    dbo.Receipt.transactionNumber,
    dbo.Receipt.receiptDate,
    dbo.Tafsil.description,
    dbo.Branch.name,
    dbo.DeliveryOrderDetail.quantity,
    dbo.DeliveryOrderDetail.quantityDiscount,
    dbo.DeliveryOrderDetail.supplierQuantityDiscount ;

Select allOpen in new window

Excellent information, thank you, but a db diagram does not tell us what YOU consider to be CORRECT.

Take some values from those quantity columns and manually show us what the correct arithmetic is.

E.g.
Qtya. qtyb. expected calculation
10        5       ????

I have tried to provide as much as info,hope it is clear

well if you only wanted the SUM( "this column" )

why do you have SUM( "this column" + "that column" ) ?

Change:
    SUM(DeliveryOrderDetail.quantityDiscount+DeliveryOrderDetail.supplierQuantityDiscount) as TotalDiscount

to
 , SUM(DeliveryOrderDetail.quantityDiscount)
, SUM(DeliveryOrderDetail.supplierQuantityDiscount)

now see if one of these 2 columns produce the correct results

Thanks for your feedbacks. It is not giving me the correct sum,still giving me 80. The correct sum is 38

Thanks for your feedback. I managed to use subquery as you mentioned to achieve my requirement.

Very pleased to hear that. Well done.