July 3, 2011 at 1:43 pm
Hi All,
I need to take inventory stock report. I have 3 tables and provide all the scripts with data. Please refer my report sample here
Since i don't have any experience in Stored procedure, Can anyone write a stored procedure, which make the output shown in the above image? Your Help regarding this highly appreciated.
1) tblCity - Contains City names
2) tblProduct - Contains All Product names.
3) tblInventoryStock - Contains the Inventory Entry
CREATE TABLE [dbo].[tblCity](
[i_id] [int] IDENTITY(1,1) NOT NULL,
[vc_cityname] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
insert into tblCity values('Chennai')
insert into tblcity values('Mumbai')
insert into tblCity values('Kolkatta')
CREATE TABLE [dbo].[tblProduct](
[i_id] [int] IDENTITY(1,1) NOT NULL,
[vc_bagname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
insert into tblProduct values('BG-1')
insert into tblProduct values('BG-2')
insert into tblProduct values('BG-3')
insert into tblProduct values('BG-4')
insert into tblProduct values('BG-5')
CREATE TABLE [dbo].[tblInventoryStock](
[i_inv_id] [int] IDENTITY(1,1) NOT NULL,
[dt_buyingdate] [datetime] NOT NULL,
[i_bagid] [int] NOT NULL,
[i_fromcityid] [int] NOT NULL,
[dt_solddate] [datetime] NULL,
[dt_awaitdate] [datetime] NULL
) ON [PRIMARY]
insert into tblInventoryStock values('1-Jan-2011',1,1,NULL,null)
insert into tblInventoryStock values('1-Jan-2011',1,2,'10-Jan-2011','15-Jan-2011')
insert into tblInventoryStock values('10-Mar-2011',1,1,NULL,null)
insert into tblInventoryStock values('12-Mar-2011',1,2,NULL,null)
insert into tblInventoryStock values('1-Jan-2011',2,3,NULL,null)
insert into tblInventoryStock values('15-Mar-2011',2,3,NULL,null)
insert into tblInventoryStock values('1-Jan-2011',3,1,'17-Mar-2011',null)
insert into tblInventoryStock values('1-Jan-2011',3,1,'18-Jan-2011',null)
insert into tblInventoryStock values('1-Jan-2011',3,2,'18-Jan-2011',null)
insert into tblInventoryStock values('1-Jan-2011',3,2,'15-Jan-2011','20-Jan-2011')
insert into tblInventoryStock values('1-Mar-2011',3,1,NULL,null)
insert into tblInventoryStock values('1-Mar-2011',3,1,NULL,null)
insert into tblInventoryStock values('1-Mar-2011',3,2,'2-Mar-2011','5-Mar-2011')
insert into tblInventoryStock values('1-Mar-2011',3,3,'2-Mar-2011','5-Mar-2011')
insert into tblInventoryStock values('1-Jan-2011',4,2,'3-Mar-2011',null)
insert into tblInventoryStock values('1-Jan-2011',4,3,'2-Mar-2011','5-Mar-2011')
PS:
In inventorystock table, If a product sold than it can be await to be taken, in other words, await date greater than sold date.
If both are null that means product is still avail in inventory, not sold.
thanks in advance.
July 3, 2011 at 2:56 pm
Since i don't have any experience in Stored procedure, Can anyone write a stored procedure, which make the output shown in the above image?
Sorry do not see an image in your post or as an attachment. May I suggest rather that attaching an image, to provide a short list of the desired output. Many who would like to assist you, do not often open attached images, since an image could contain malicious code.
Now guessing what you require does the following produce what you need?
SELECT tis.i_inv_id,tis.i_bagid,tc.vc_cityname FROM tblInventoryStock tis
RIGHT JOIN tblCity tc
ON tc.i_id = tis.i_fromcityid AND tis.dt_solddate IS NULL AND tis.dt_awaitdate IS NULL
Results:
i_inv_id i_bagid cityname
1 1 Chennai
3 1 Chennai
11 3 Chennai
12 3 Chennai
4 1 Mumbai
5 2 Kolkatta
6 2 Kolkatta
July 3, 2011 at 3:28 pm
What have you tried so far and where did you get stuck?
Your request that we should write the stored procedure for you is quite demanding.... We're here to assist you, not to replace you.
Most probably, the issue can be resolved without a stored procedure (however, a quirky update most probably would be one of the fastest solutions).
I recommend you give it a try and see how far you can resolve it by yourself. A google search would also help to get some ideas.
Edit: @Ron: the picture shows the requested result of an SSRS report. Not only the result set of a query or stored procedure...
July 6, 2011 at 7:56 am
First i thank your reply lutzM and bitbucket.
First i want to explain my requirement clear.
The inventory table (tblInventoryStock) contains the inventory entries.
The Problem is here is, the product may not come in a regular basis.
That means, there may be a chance the inventory table leaves a month between.
Pls refer my table queries.
So i tried the following way:
First, I create a temp table, with all the month between "FROM DATE" and "END DATE".(#tmpyear)
Second, I build another temp table with necessary columnns. (#tmprptdat). Columns
like product_count,openingstock,takenproductcount,awitprdcount etc.
Third, I "CROSS" join the (#tmpyear) table with "tblProduct" and "LEFT" join
with "#tmprptdat" table.
So I get the all the products with each month. (For example, i have 3 products,
and "FROM DATE" is 1-Jun-2011 and the end date is "15-Aug-2011" then
i got:
startdate enddate product
1-june-2011 31-jun-2011 Product 1
1-june-2011 31-jun-2011 Product 2
1-june-2011 31-jun-2011 Product 3
1-jul-2011 31-jul-2011 Product 1
1-jul-2011 31-jul-2011 Product 2
1-jul-2011 31-jul-2011 Product 3
1-Aug-2011 30-Aug-2011 Product 1
1-Aug-2011 30-Aug-2011 Product 2
1-Aug-2011 30-Aug-2011 Product 3
With the above table, i didn't miss any month.
I dont know how to proceed further.
What i want to do is, i just loop thru the above (which obtained in Third step) and update
the columns (product_count,openingstock,takenproductcount,awitprdcount) for
each and every month by the following conditions:
1) If i buy one item in june month, and it will sold on june but await date
date is july then it considered as "Sold" in june.
2) if there is NO "sold date" and NO "awaitdate" the product still in inventory.
3) One month's "closing stock" be a next month's "opeing stock"
4) Closing stock can be calculated as:
= Opening stock - (Incoming products of the month + (sold count +await count))
Please guide be on this.
Thanks.
I tried the following way.
July 6, 2011 at 9:30 am
Just so we are clear about what is required.
You want to be able to pass in a date and have the query tell you all items/cities that had stock at that date and the amount of stock that is on hand and available for sale.
The easiest way to do this is to break it down into its constituent parts.
select all of the stock transaction records where the in stock date is before the reporting date. sum the records grouping by stock and location.
select all of the stock transaction records where the sale date is before the reporting date. sum the records grouping by stock and location.
select all of the stock transaction records where the collection date is before the reporting date. sum the records grouping by stock and location.
You can create these as Common table expressions.
Take the stock in less the stock sold as available stock.
Take the stock in less the stock collected as on hand stock.
do some research about common table expressions (CTE)
try to write the code yourself as this is the best way to learn and come back to us if you are struggling
Obiron
July 7, 2011 at 12:08 pm
May I inquire as to whether or not this is a homework or other non-work related post?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply