Search code examples
sql-server-2008t-sqlinventory

Inventory Balance stock display

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?

Solution

  • 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