Try This... ---------------------------------------------------------------------------------------------------------Table Creation -------------------------------------------------------------------------------------------------------CREATE TABLE TableA(ItemID INT, ItemName VARCHAR(100),ItemDescription VARCHAR(100),CurrentQuantity INT)CREATE TABLE TableB(LogID INT,ItemID INT,Beginning INT,[Add] INT,Less INT,Ending INT,[Date] DATE)INSERT INTO TableA VALUES (1,'Bolts','35"',20) ,(2,'Paint','GOLD Paint',30) ,(3,'Screws','30"',40)INSERT INTO TableB VALUES (1,1,30,0,10,20,'04-04-2014') ,(2,2,40,10,10,40,'04-04-2014') ,(3,3,50,5,10,45,'04-04-2014') ,(4,1,20,0,0,20,'04-05-2014') ,(5,2,40,0,10,30,'04-04-2014') ,(6,3,45,0,5,40,'04-04-2014')---------------------------------------------------------------------------------------------------------Front End InputVariable Declartion-------------------------------------------------------------------------------------------------------DECLARE @FromDate VARCHAR(1024) = '04-04-2014'DECLARE @ToDate VARCHAR(1024) = '04-05-2014'---------------------------------------------------------------------------------------------------------Query-------------------------------------------------------------------------------------------------------SELECT A.ItemName ,A.ItemDescription ,C.Begining ,SUM(B.[Add]) AS [Add] ,SUM(B.Less) As [Less] ,D.EndingFROM TableA AINNER JOIN TableB BON A.ItemID = B.ItemIDINNER JOIN (SELECT (CASE WHEN MAX(B.[Date]) OVER(PARTITION BY B.[Date]) = @FromDate THEN B.Beginning END )AS Begining,ROW_NUMBER () OVER (ORDER BY (SELECT 1)) AS Rn FROM TableB B ) CON A.ItemID =C.RnINNER JOIN (SELECT (CASE WHEN MAX(B.[Date]) OVER(PARTITION BY B.[Date],B.Ending) = @FromDate THEN B.Ending END )AS Ending,ROW_NUMBER () OVER (ORDER BY (SELECT 1)) AS Rn FROM TableB B ) DON A.ItemID =D.RnGROUP BY A.ItemName,A.ItemDescription,C.Begining,D.Ending---------------------------------------------------------------------------------------------------------DROP TableDROP TABLE TableADROP TABLE TableB-------------------------------------------------------------------------------------------------------
---------------Murali KrishnaYou live only once ..If you do it right once is enough.......