sql query for sales summary again

Question

this question is based on answer got from another SO question, can be found here.

I have managed to write a query myself based answer provided there

   Select s.pName, 
       s.ProductCode, 
       min(s.Price)                         as MinPrice, 
       sum(s.Quantity)                      as SalesQty, 
       sum(s.Price * s.Quantity)            as SalesValue, 
       isnull((select sum(Quantity) 
               from   Breakages 
               where  pGroup = 16 
                      and quantity > 0), 0) as BreakQty, 
       isnull((select sum(Price * Quantity) 
               from   Breakages 
               where  pGroup = 16), 0)      as BreakValue, 
       isnull((select CASE 
                        WHEN min(r.Quantity) != 0 THEN Sum(r.Quantity) 
                      END), 0)              as ReturnQty, 
       isnull((select sum(Price * Quantity) 
               from   SalesReturn 
               where  pGroup = 16), 0)      as ReturnValue 
from   SalesLog as s 
       INNER JOIN SalesReturn as r 
         ON r.BillDate = s.BillDate 
       INNER JOIN Breakages as b 
         ON r.BillDate = b.BillDate 
where  s.BillDate = '12-10-2010' 
       and r.BillDate = '12-10-2010' 
       and b.BillDate = '12-10-2010' 
       and s.pGroup = 16 
       and b.pGroup = 16 
       and r.pGroup = 16 
group  by s.pName, 
          s.ProductCode; 

Here is output of above query

Name               Code Price SalesQty SValue  BreakQty BValue  RefundQty  RQty
CDM 42GRMS.        854    15      3       45        2       0         3     30
APPLE JUICE 750ML  860    59      5      295        2       0         3     30
BISLERI WATER      865     3      5       15        2       0         3     30
PERK 35 GRMS       870    10     20      200        2       0         3     30

there is a problem with output as you may not get, Breakages is 2 for Code=865 and Refund is 3 for 870 but all row is having breakages and Refund.

you can find experiments in my query. thanks........waiting fro reply

SalesRetrun Table

CREATE TABLE [dbo].[SalesReturn](
    [srID] [int] IDENTITY(1,1) NOT NULL,
    [ProductCode] [int] NULL,
    [Quantity] [int] NULL,
    [pGroup] [int] NULL,
    [MemoNo] [int] NULL,
    [SalesmanID] [int] NULL,
    [Price] [int] NULL,
    [BillDate] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [AddedOn] [datetime] NULL,
 CONSTRAINT [PK_SalesReturn] PRIMARY KEY CLUSTERED 
([srID] ASC) WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

SalesLog Table

CREATE TABLE [dbo].[SalesLog](
   [SalesID] [int] IDENTITY(1,1) NOT NULL,
   [MemoNo] [int] NULL,
   [ProductCode] [int] NULL,
   [Quantity] [int] NULL,
   [Price] [int] NULL,
   [pGroup] [int] NULL,
   [pName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [pSize] [int] NULL,
   [BillDate] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_SalesLog] PRIMARY KEY CLUSTERED 
 (  [SalesID] ASC )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
 ) ON [PRIMARY]

Breakages Table

CREATE TABLE [dbo].[Breakages](
    [breakId] [int] IDENTITY(1,1) NOT NULL,
    [MemoNo] [int] NULL,
    [SalesmanID] [int] NULL,
    [ProductCode] [int] NULL,
    [pName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Quantity] [int] NULL,
    [Price] [int] NULL,
    [pGroup] [int] NULL,
    [BillDate] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [AddedOn] [datetime] NULL,
 CONSTRAINT [PK_Breakages_1] PRIMARY KEY CLUSTERED (
[breakId] ASC )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

UPDATED QUERY
I have added Products table reference and is showing desired output but its showing all products whether any sale or break or refund occurred or not that date.

I don't want to display rows where there is no sale or no breakages or no refund. this will reduce my report size. Current showing 319 rows but after removing rows (manually calculation) according to my logic it reduced to 16 rows (on fake data)

SELECT  p.pName, p.pCode, MIN(p.pPrice) AS MinPrice
   , SUM(s.Quantity) AS SalesQty, SUM(s.Quantity) * MIN(p.pPrice) AS SalesValue
   , MIN(b.Quantity) AS BreakQty, MIN(b.Quantity) * MIN(p.pPrice) AS BreakValue
   , MIN(r.Quantity) AS ReturnQty, MIN(r.Quantity) * MIN(p.pPrice) AS ReturnValue
FROM    Products AS p

OUTER APPLY (SELECT SUM(s.Quantity) AS Quantity
         FROM   SalesLog AS s
         WHERE  s.BillDate = '12-10-2010'
                AND s.ProductCode = p.pCode
        ) AS s
OUTER APPLY (SELECT SUM(r.Quantity) AS Quantity
         FROM   SalesReturn AS r
         WHERE  r.BillDate = '12-10-2010'
                AND r.ProductCode = p.pCode
        ) AS r
OUTER APPLY (SELECT SUM(b.Quantity) AS Quantity
         FROM   Breakages AS b
         WHERE  b.BillDate = '12-10-2010'
                AND b.ProductCode = p.pCode
        ) AS b
WHERE  p.pGroup!=15 and p.pGroup!=16 
GROUP BY p.pName, p.pCode;

Answer 1

Your subquery, such as this, will always get the same data for each row because the where clause doesn't reference anything from the outer query.

   isnull((select sum(Quantity) 
           from   Breakages 
           where  pGroup = 16 
                  and quantity > 0), 0) as BreakQty

I don't have time to figure out what it should be.

Answer 2

The subqueries should reference the main query, with a where clause. associating te sum's with SalesLog.ProductCode, I think. To a more accurate answer, you should post the tables structure.

Answer 3

It is difficult to give an accurate answer as I don't know your data. However, I think you are after something along the lines of:

SELECT  s.pName
      , s.ProductCode
      , MIN(s.Price) AS MinPrice
      , SUM(s.Quantity) AS SalesQty
      , SUM(s.Quantity) * MIN(s.Price) AS SalesValue
      , MIN(b.Quantity) AS BreakQty
      , MIN(b.Quantity) * MIN(s.Price) AS BreakValue
      , MIN(r.Quantity) AS ReturnQty
      , MIN(r.Quantity) * MIN(s.Price) AS ReturnValue
FROM    SalesLog AS s
OUTER APPLY (SELECT SUM(r.Quantity) AS Quantity
             FROM   @SalesReturn AS r
             WHERE  r.BillDate = s.BillDate
                    AND r.ProductCode = s.ProductCode
            ) AS r
OUTER APPLY (SELECT SUM(b.Quantity) AS Quantity
             FROM   @Breakages AS b
             WHERE  b.BillDate = s.BillDate
                    AND b.ProductCode = s.ProductCode
            ) AS b
WHERE   s.BillDate = '12-10 2010'
GROUP BY s.pName
      , s.ProductCode ; 

Answer 4

You are moving the goal posts! However, below is a possible solution for your updated problem.

Note:

1. It is much more efficient to use > or < instead of != if you can. This is why I have changed the predicate on the p.pGroup. (I have assumed there are no groups between 15 and 16).
2. All the agrigation is done in the APPLY sub queries, so you don't need a GROUP BY clause any more.

Here is the updated query:

SELECT  p.pName
      , p.ProductCode
      , p.Price AS MinPrice
      , s.Quantity AS SalesQty
      , s.Quantity * p.Price AS SalesValue
      , b.Quantity AS BreakQty
      , b.Quantity * p.Price AS BreakValue
      , r.Quantity AS ReturnQty
      , r.Quantity * p.Price AS ReturnValue
FROM    Products AS p
OUTER APPLY (SELECT SUM(s.Quantity) AS Quantity
             FROM   SalesLog AS s
             WHERE  s.BillDate = '12-10 2010' 
                    AND s.ProductCode = p.ProductCode
            ) AS s
OUTER APPLY (SELECT SUM(r.Quantity) AS Quantity
             FROM   SalesReturn AS r
             WHERE  r.BillDate = '12-10 2010' 
                    AND r.ProductCode = p.ProductCode
            ) AS r
OUTER APPLY (SELECT SUM(b.Quantity) AS Quantity
             FROM   Breakages AS b
             WHERE  b.BillDate = '12-10 2010' 
                    AND b.ProductCode = p.ProductCode
            ) AS b
WHERE   p.pGroup < 15
        AND p.pGroup > 16
        AND (
             s.Quantity IS NOT NULL
             OR r.Quantity IS NOT NULL
             OR b.Quantity IS NOT NULL
            )