January 28, 2011 at 4:15 am
Hi SQL Gurus
I am trying to tweak a code from inventory movement table to a new format but stuck with the logic. I hope you will help me in getting this task done.
for simplicity & easy to understand I am keeping aside all kinds of complexities for the time being & just focusing on simple example
I have inventory movement table which has following fields
(very simple assuming 1 store 1 product logic, no returns, no movement apart from Purchase and Sales)
TransactionDate -- ProductCode -- TransactionType -- Qty
2011-01-01 -- A -- Purchase -- 5
2011-01-02 -- A -- Purchase -- 8
2011-01-05 -- A -- Sales -- 12
2011-01-06 -- A -- Purchase -- 3
Now I want the output in this format
ProductCode -- PurchaseDate -- SalesDate -- Units
A -- 2011-01-01-- 2011-01-05 -- 1
A -- 2011-01-01-- 2011-01-05 -- 1
A -- 2011-01-01-- 2011-01-05 -- 1
A -- 2011-01-01-- 2011-01-05 -- 1
A -- 2011-01-01-- 2011-01-05 -- 1
-- 5 Pcs were sold from first purchase
A -- 2011-01-02-- 2011-01-05 -- 1
A -- 2011-01-02-- 2011-01-05 -- 1
A -- 2011-01-02-- 2011-01-05 -- 1
A -- 2011-01-02-- 2011-01-05 -- 1
A -- 2011-01-02-- 2011-01-05 -- 1
A -- 2011-01-02-- 2011-01-05 -- 1
A -- 2011-01-02-- 2011-01-05 -- 1
-- Next 7 Pcs were sold from next purchase
A -- 2011-01-02-- NULL -- 1
A -- 2011-01-06-- NULL -- 1
A -- 2011-01-06-- NULL -- 1
A -- 2011-01-06-- NULL -- 1
so basically I will get my ageing per piecewise which tells me that if SalesDate is null it means those items are in stock (4 piece in my example)
out of which 1 item is purchased on 2nd Jan 2011 (Age 26 days) & next 3 item purchased on 6th Jan (Age 22 days assuming I am calculating age on 28th Jan 2011)
Since I am going to do this exercise once in a week so I am ready to compromise on speed and time as this will give me age for single item per row.
Kindly help me in this as after lots of R&D I can say that this is possible using cursor as I have to do a row scan first split purchase in single single items & then match Sales.
Thanks you everyone in advance to your time and support.
Regards,
Anand M. Bohra
January 28, 2011 at 4:25 am
Anand
No need to use a cursor. Create a Numbers (or Tally) table - search this site if you don't know what that is. Then join your table to the Numbers table on an inequality (ON Qty >= Number). If you're not allowed to create new objects in the database, you can derive a Numbers table using a CTE. I think that's also explained in the articles you'll find when you do your search.
Please give it a go and post back if you struggle.
John
January 28, 2011 at 5:00 am
You will need the techniques described here
January 28, 2011 at 5:08 am
my R&D reached till this stage
Create Table #InventoryAudit
(
TransDate smalldatetime not null,
ProdCode nvarchar(1) not null,
TransType nvarchar(3) not null,
Qty float
)
insert into #InventoryAudit
select '2011-01-01','A','Pur',5 Union all
select '2011-01-02','A','Pur',8 Union all
select '2011-01-05','A','Sls',12 Union all
select '2011-01-06','A','Pur',3
Declare @TransDate as smalldatetime
Declare @Qty as float
Declare @j-2 as integer
DECLARE @TranName VARCHAR(20)
SELECT @TranName = 'MyTransaction'
BEGIN TRANSACTION @TranName
CREATE TABLE #InventoryAuditDetailed(
ProdCode nvarchar(1) not null,
PurchaseDate smalldatetime not null,
SalesDate smalldatetime null,
Qty float
) ON [PRIMARY]
Declare PurSplitter Cursor
for
Select TransDate, Qty from #InventoryAudit where transtype='Pur'
begin transaction
Open PurSplitter
fetch next from PurSplitter into @TransDate, @Qty
while @@fetch_status=0
begin
Set @j-2=1
While (@J <= @Qty)
begin
Insert Into #InventoryAuditDetailed
values ('A',@TransDate,'',1)
end
fetch next from PurSplitter into @TransDate, @Qty
end
Declare SalesFeeder Cursor
for
Select TransDate, Qty from #InventoryAudit where transtype='Sls' order by transdate desc
commit transaction
select * from #InventoryAuditDetailed
drop table #InventoryAuditDetailed
drop table #InventoryAudit
close PurSplitter
deallocate PurSplitter
set nocount off
if @@error <> 0
begin
ROLLBACK TRANSACTION @TranName
end
ELSE
BEGIN
COMMIT TRANSACTION @TranName
END
Now I had splited my Purchase data per single qty now trying to feed sales dates against each purchase records
January 28, 2011 at 5:17 am
Anand
Is there a question there?
Like I said, no cursor is necessary. Try it the way I suggested, or use the link that Dave posted, and get back to us if anything doesn't make sense.
John
January 28, 2011 at 5:47 am
Hi John / Dave
Thanks for the link Dave, I had started coping the SQL codes & checking the flow, will let you know using this technique I can get my required output or not.
This code seems great as it has provision for cost also.
but since I don't want any aggregation as my end requirement is one row (15 units purchase means 15 rows so that I can have data for calculating Aged inventory, Fast movers, turnaround time etc etc.)
just give me some time to check and get back to you.
Thanks for your time and support.
Regards,
Anand
January 28, 2011 at 7:03 am
Hi John
I had gone through this great article but my requirement is slightly different.
I don't want to know what is left units & price but my main concern is for every sale units I want to get its purchase date.
so that the difference in dates will give me idea that in how many days I sold a single unit (to know fast movers), if sale date is null it means I am holding stock (inventory) then using current date I wanted to know my stock with ageing with predefined or custom grouping like
0-15 days, 16-45 days, 46-90 days, 91-180 days, 180days and above.
so basically I want to first make my database splitted into individual rows based on total units purchased then based on total sold I want to put sales dates against each purchase.
Kindly help me out the stored procedure & cursor which I posted above does the work of spliting purchase quantity per row, now struggling hard to feed sales dates against each purchase.
Hope everyone is clear with my unique requirement.
Regards,
Anand
January 28, 2011 at 7:16 am
I think you need to first answer the question:
How do you know what the first sellable item of stock to be sold is ?
If you start from an empty warehouse this is easy ,
but you wont be on week 2.
so ...
Week 1
50 units of stock come in.
45 units of stock go out.
Week 2
35 units of stock come in
10 units of stock go out.
for those 10 units of stock 5 will be from week 1 ,
i dont see how you have even started to answer this question.
(unless of course you are emptying your warehouse and throwing the unsold stock away , though i cant see that happening 🙂 )
I think that by adapting my FIFO code you should be able to find your (speedy) answer
January 28, 2011 at 7:44 am
I am working on Empty warehouse table. [means data wont start from mid of week its always historical say last 3 yrs (since inception)]
I have 2 similar cases of this kind
1. where inventory movements are recorded row by row (given example)
2. Where user maintains 2 tables 1. Purchase and 2. Sales (almost all organizations have data in this way)
so I want a program/loop/cursor/best method which gives me output in this way that whenever users runs so that I have a complete history of a single pc item in one row
i.e. item A purchase on XYZ Date, Sold on XYZ date, Purchase cost, Sales Price, Units (1 always) 7 so on
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply