8654

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 <strong>SQL fiddle</strong>. Here is a <strong><a href="http://sqlfiddle.com/#!3/f977e/1/0" rel="nofollow">SQL Fiddle for the table structures an values.</a></strong>

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?

Answer1:

Maybe something like this:<s> <a href="http://sqlfiddle.com/#!3/f977e/50" rel="nofollow">http://sqlfiddle.com/#!3/f977e/50</a> </s>

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.

<strong>EDIT :</strong>

Added grand total.

<a href="http://sqlfiddle.com/#!3/f977e/75" rel="nofollow">http://sqlfiddle.com/#!3/f977e/75</a>

Answer2:

<s>Try this Query:</s>

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

<strong><a href="http://sqlfiddle.com/#!3/f977e/7" rel="nofollow">SQL Fiddle</a></strong>

<strong>Note:</strong> This will not produce the exact <strong>output</strong> as the data defer.

<strong>Edit:</strong>

<strong>Assumptions (which i think is there in your <em>Structure</em>)</strong>

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

2.Each and every Item has <strong>Inward</strong> and <strong>Outward Quantity Column</strong> for every <strong>Date</strong>

With above assumption here is the SQL Query

1.The <strong>TEMP Table</strong>

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);

<strong><a href="http://sqlfiddle.com/#!3/88bb1/1" rel="nofollow">Here is the SQL Fiddle to verify</a></strong>

Recommend

  • iPhone - Escape charecter issue in JSON parsing
  • Update All Rows in DataBase with a hash value [closed]
  • Autoloading classes when not in same directory as zend library
  • Tomcat JDBC MySQL ClassNotFoundException
  • How to use an anonymous class instance in another generate bytecode class
  • Why does adding a reference to jasmine.js break my ReSharper test?
  • pass a base path to a swf loaded at runtime
  • Get count of row and sum group by quarter of date, if another column doesnt exits a value in SQL Ser
  • Twig_Error_Syntax for “Unknown filter” with a Twig filter in Timber
  • Spring AOP Logging Interceptor and JAXB issue
  • Replacing NULL value in current row based on 'closest' matching 'earlier' row. (
  • ClassNotFoundException: Attempted to load class “FOSUserBundle” from namespace “FOS\\UserBundle”
  • How to prevent JBoss inter web application classpath?
  • Angular2-mdl and webpack
  • How can I change config file path in Codeigniter?
  • Unable to import a React Component in my Mocha test
  • Android - What should I use to get data from remote db?
  • What is the best SIMPLE replacement for VS Setup-project Installer for WinXP + WPF + .NET 4.0? [clos
  • AJAX problem in Internet Explorer
  • doPostBack from C# with JavaScript
  • How to get list of users who's birthday is today in MongoDB
  • Pandas: merge_asof() sum multiple rows / don't duplicate
  • How to use arithmetic operators with SAS macro variables [duplicate]
  • passing a javascript variable to PHP with xmlhttprequest
  • jhipster run embedded jar with prod profile - issue with liquibase
  • Sybase Error Implicit Conversion from datatype 'VARCHAR' to 'INT' not allowed
  • uml Composition relationships to RDF and OWL
  • Find Previous month name using Calender or any classes that in java
  • Javascript, Regex - I need to grab each section of a string contained in brackets
  • Invalid Date on validation Date of js
  • Install PHP intl extension on MacOS
  • Alternative To body {overflow:scroll;} That Will Prevent Page Jostling/Wriggling?
  • Play WS (2.2.1): post/put large request
  • Updated Ionic CLI but shows previous version (Windows)
  • Warning: Can't call setState (or forceUpdate) on an unmounted component
  • bootstrap to use multiple ng-app
  • How to get icons for entities from eclipse?
  • Turn off referential integrity in Derby? is it possible?
  • JaxB to read class hierarchy
  • How to Embed XSL into XML