how to show price changes in sales report using t-sql

Question

I have two tables Products and SalesLog,

SalesLog Table

CREATE TABLE [dbo].[SalesLog](
[SalesID] [int] IDENTITY(1,1) NOT NULL,
[MemoNo] [int] NULL,
[ProductCode] [int] NULL,
[Quantity] [int] NULL,
[Price] [decimal](10, 2) 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]

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

In my RDLC report I have to show Sales report for different items. Now the problem is that I have show report according Date Range (for example 01/01/2010 to 15/03/2010 dd/mm/yyyy) and in this period Item Price may change for several times. So i have to show those price changes also. For example

Sales Report for the date 01/01/2010 to 15/03/2010

ItemName    Price   Quantity   Total
  A          $1       2          $2
  A          $1.20    5          $6
  A          $1.10    5          $5.50
  B          $5       1          $5

As you can see in above example Item "A" price has changed twice, so i have to show how many quantities sold at particular price in that period. My Products table contains latest price of an Item (pPrice column) and SalesLog table contains Item price at the time of billing (Price column).

I tried a lot but no where near it, Can anyone help me with T-SQL query of any other technique to achieve my goal.

thank a lot..............

Answer 1

Are you looking to get the data in Example A? If so, this should work;

DECLARE @StartDate DATETIME ,
    @EndDate DATETIME

SELECT  @StartDate = '01 Jan 2010' ,
        @EndDate = '15 Mar 2010'

SELECT  [Products].pName AS ItemName,
        SalesLog.[Price] AS Price ,
        COUNT(*)AS Quantity ,
        SUM(SalesLog.[Price]) AS Total
FROM    SalesLog
        JOIN [Products] ON [Products].pCode = SalesLog.ProductCode /*Check this join - I'm not sure what your relationship is*/
WHERE   BillDate >= @StartDate
        AND BillDate < @EndDate + 1
GROUP BY [Products].pName ,
        SalesLog.[Price]