Avatar of Zolf
Zolf
Flag for United Arab Emirates asked on

SQL Query Help

Hello there,

Can somebody please help me to rewrite this query to give me the sum of the total sales of product and discount in each branch office. Now the records returned are giving me for each day of the month. I want to sum it to 1 month.

SELECT
    dbo.Receipt.transactionNumber,
    dbo.Receipt.receiptDate,
    dbo.Tafsil.description,
    dbo.Branch.name,
    SUM(dbo.SaleDetail.quantity)                     AS SalesQtyinclDis,
    dbo.DeliveryOrderDetail.quantity                 AS SalesQty,
    dbo.DeliveryOrderDetail.quantityDiscount         AS Dis1,
    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,
    dbo.DeliveryOrderDetail.quantity,
    dbo.DeliveryOrderDetail.quantityDiscount,
    dbo.DeliveryOrderDetail.supplierQuantityDiscount ;

Open in new window

1
Microsoft SQL ServerSSRSSQL

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
Ryan Chong

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
 

Open in new window

?
PortletPaul

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.
Zolf

ASKER
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 ;

Open in new window

1
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
PortletPaul

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?
Zolf

ASKER
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.

1
2
Zolf

ASKER
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 ;

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PortletPaul

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       ????
Zolf

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

11
PortletPaul

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Zolf

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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Zolf

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

Very pleased to hear that. Well done.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.