Inventory Balance stock display

Question

I have come across a small problem where I have to show the Display of Items in Inventory through a query. I am retrieving data from 4 tables and want the output similar to below displayed. the table structures and sample values have been given in SQL fiddle. Here is a SQL Fiddle for the table structures an values.

The output what I want is like this.

Date                    ItemName        Rate     InQty  OutQty  Balance ItemUnit
2013-01-04 00:00:00.000 Sand            0.000    10    5        5       NONE
2013-01-04 00:00:00.000 Backhoe Loader  650.000  20    2        18      Hr
2013-01-04 00:00:00.000 Tractor Troley  150.000  10    0        10      Hr

2013-02-04 00:00:00.000 Sand            0.000    0     5        0       NONE
2013-02-04 00:00:00.000 Backhoe Loader  650.000  5     2        21      Hr
2013-02-04 00:00:00.000 Tractor Troley  150.000  10    30       -10     Hr
.
.
.

I have spend more then a month doing this. Can any body please help me?

Answer 1

Maybe something like this: http://sqlfiddle.com/#!3/f977e/50

Removed the MIN() from the dates as it restricted the data. Added dateadd-datediff to make sure all times on dates are removed. I commented the where clause as you see. But you can add it again of course.

Hope this helps.

EDIT :

Added grand total.

http://sqlfiddle.com/#!3/f977e/75

Answer 2

Try this Query:

SELECT 
DISTINCT
CONVERT(DATETIME,CONVERT(DATE,I.[Date]),101) AS [Date],
I.Nameofitem AS ItemName,
I.Rate AS RATE,
I.Qty AS InQty,
ISNULL(O.Qty,0) AS OutQty,
ISNULL(I.Qty,0)-ISNULL(O.Qty,0) AS Balance,
O.unit AS ItemUnit
FROM Outwards_Master O
RIGHT JOIN Inwards_Master I ON O.Nameofitem=I.Nameofitem
                                AND CONVERT(DATE,O.[Date])=CONVERT(DATE,I.[Date])

SQL Fiddle

Note: This will not produce the exact output as the data defer.

Edit:

Assumptions (which i think is there in your Structure)

1.All the Date in Inward Master and in Outward Master are same and vice-versa AND Date IS PRIMARY KEY

2.Each and every Item has Inward and Outward Quantity Column for every Date

With above assumption here is the SQL Query

1.The TEMP Table

SELECT 
DENSE_RANK() over(order by I.Date) AS [RANK],
ROW_NUMBER()over(partition by I.Date order by I.Date) AS [VersionId],
I.Date,
I.Nameofitem,
I.Rate,
ISNULL(I.Qty,0) AS INQTY,
ISNULL(O.Qty,0) AS OUTQTY,
I.unit
INTO #TEMP1
FROM 
#Inwards_Master I
INNER JOIN #Outwards_Master O ON CONVERT(DATE,O.[Date])=CONVERT(DATE,I.[Date])
            AND I.Nameofitem=O.Nameofitem

SELECT * FROM #TEMP1 ORDER BY Date,Nameofitem

2.The Balance:

;WITH x AS
(
    SELECT 
        [Rank],
        [VersionId],
        Nameofitem,
        [Date],
        INQTY,
        OUTQTY,
        bal=(INQTY-OUTQTY)
    FROM #temp1
    WHERE [Rank] = 1
    UNION ALL
    SELECT 
        y.[Rank],
        y.[VersionId],
        y.Nameofitem,
        y.[Date],
        y.INQTY,
        y.OUTQTY,
        x.bal+(y.INQTY-y.OUTQTY)
    FROM x INNER JOIN #temp1 AS y
    ON y.[Rank] = x.[Rank] + 1
    AND y.VersionId=x.VersionId
    and y.Nameofitem=x.Nameofitem
)
SELECT 
    [Date],
    Nameofitem,
    INQTY,
    OUTQTY,
    Balance = bal
FROM x
ORDER BY Date,Nameofitem
OPTION (MAXRECURSION 10000);

Here is the SQL Fiddle to verify