How to show sales summary in rdlc of four sql tables using sql

Question

I have bad times for generating an rdlc report to display a report in a predefined format specified by my client.

Client format Output format http://i29.tinypic.com/4t9g7b.jpg

As you can see, the RED background is my problem area, the price “A” has been changed three times. And accordingly, their sales, breakdowns and the number of returns are shown. I have 4 product tables, SalesLog, Breakages, SalesReturn. The product table consists of the last (current) product price, but the SalesLog, Breakages, and SalesReturn tables consist of the price during sales, breaks, and sales returns. I wrote queries for SalesLog, Breakges, and SalesReturn that show rows according to ProductCode, Price, and Quantity. But I do not understand how I can combine their output (4 tables) to show the result below.

Request for SalesLog

SELECT  [Products].pCode AS Code,
    [Products].pName AS Item,
    SalesLog.[Price] AS Price ,
    COUNT(*)AS Quantity ,
    SUM(SalesLog.[Price]) AS Total
FROM    SalesLog
    INNER JOIN [Products] ON [Products].pCode = SalesLog.ProductCode
WHERE   BillDate = '07/01/2010'
GROUP BY [Products].pCode,[Products].pName ,
    SalesLog.[Price]

Error Request

SELECT  [Products].pCode AS Code,
    [Products].pName AS Item,
    Breakages.[Price] AS Price ,
    COUNT(*)AS Quantity ,
    SUM(Breakages.[Price]) AS Total
FROM    Breakages
    INNER JOIN [Products] ON [Products].pCode = Breakages.ProductCode
WHERE   BillDate = '07/01/2010'
GROUP BY [Products].pCode,[Products].pName ,
    Breakages.[Price]

Request for return to sales

SELECT  [Products].pCode AS Code,
    [Products].pName AS Item,
    Breakages.[Price] AS Price ,
    COUNT(*)AS Quantity ,
    SUM(Breakages.[Price]) AS Total
FROM    Breakages
    INNER JOIN [Products] ON [Products].pCode = Breakages.ProductCode
WHERE   BillDate = '07/01/2010'
GROUP BY [Products].pCode,[Products].pName ,
    Breakages.[Price]

Product table

CREATE TABLE [dbo].[Products](
    [ProductId] [int] IDENTITY(1,1) NOT NULL,
    [pName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [pSize] [int] NULL,
    [pPrice] [decimal](10, 2) NULL,
    [pPackQty] [int] NULL,
    [pGroup] [int] NULL,
    [pCode] [int] NULL,
    [OpenStock] [int] NULL,
    [CloseStock] [int] NULL,
    [YrlyOpenStock] [int] NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(    
    [ProductId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Can anyone help me or suggest any trick for this.

thanks in advance.....

Answer 1

, . , "Code", "Item", "Price", LEFT JOIN (Sales, Breakage, Return). coalesce 0, , final where, , ( , ).

Answer 2

- ? ( , whiz SQL Server).

SELECT [Products].pCode AS Code,
       [Products].pName AS Item,
       SLO.[Price]      AS Price,
       (SELECT COUNT(*)
           FROM SalesLog SL
           WHERE SL.ProductCode = SLO.ProductCode AND
                 SL.Billdate    = SLO.BillDate) AS SalesQty,
       (SELECT SUM(SL.Price)
           FROM SalesLog SL
           WHERE SL.ProductCode = SLO.ProductCode AND
                 SL.Billdate    = SLO.BillDate) AS SalesValue,
       (SELECT COUNT(*)
            FROM Breakages
            WHERE Breakages.ProductCode = SLO.ProductCode AND
                  Breakages.BillDate    = SLO.BillDate) AS BreakageQty,
       ...etc...

FROM SalesLog SLO INNER JOIN [Products] ON [Products].pCode = SLO.ProductCode
    WHERE BillDate = '07/01/2010'
    GROUP BY [Products].pCode,
             [Products].pName ,
             SLO.[Price]

Answer 3

Finally, the question I asked on ServerFault.co, m, which went to StackOverFlow.com as its Administrator, received an answer which, it seems to me, is easy to get (at least for me). Please ignore those guys who helped me. This request does what I wanted.

Thanks to everyone for their support (especially STACKOVERFLOW.COM )

Click here to view the answer.