how to show sales summary in rdlc from four sql table using sql

Question

hello guys, I am having bad times to generate a rdlc report to show report in predefined format given by my client.

Client given format

As you can notice RED background is my problem area, Item "A" price has been changed thrice. And accordingly their sales, breakages and refund quantity is shown. I have 4 tables Products, SalesLog, Breakages, SalesReturn. Product table consists latest(current) price of an item but SalesLog, Breakages and SalesReturn tables consists price at the time of Sales, Breakages and Sales Return. I have written queries for SalesLog, Breakges and SalesReturn which shows rows according to ProductCode, Price and Quantity. But i don't understand how I can merge their (4 tables output) output to show below result.

Query 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]

Query for Breakages

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]

Query for SalesReturn

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 to accomplish this.

thanks in advance.....

Answer 1

This is just off the top of my head from looking at your data. I think I'd just pull out a unique list of "Code", "Item", "Price" and then use this unique list to LEFT JOIN against each one of your queries (Sales, Breakage, Return). You can use a coalesce to default the values to 0 if they are null, and you can use a final where clause to drop-out any records that have zero for all three results (unless you want to show there was nothing done at a given price).

Answer 2

Would something along the following lines be of any use? (Give or take a square bracket or two as I'm no great whizz on 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 a question which I asked on ServerFault.co,m which moved to StackOverFlow.com by their Admin has got an answer which I think is easy to get (for me at least). Please don't mind those guys who helped me. this query is doing what i wanted.

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

Click here to view answer