(ask) query SQL about my stock list of items

Question

(ask) query SQL about my stock list of items

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

kurniawan.kumala SSC Enthusiast Points: 113 More actions · Answer 1

Dave Ballantyne (1/3/2011)
Happy new year to you too,
The key is now to find the first/earliest 'IN' stock movement , that still has stock remaining in the warehouse. Since you are operating in a FIFO fashion then when you know this point , you know that 100% of each subsequent stock movement in is still remaining in the warehouse.
To get back to the original links that i gave you, specifically ..
http://www.simple-talk.com/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/ , does a real good job of breaking this down and how my query works in a performant fashion.
I would urge you to re-read that article , as this would seem to me be exactly what you would require.

hi, dave

may i question to you, base on your link.....

1. indexes IX_Dave_Items and IX_Dave_Price can be run at sql 2005?

2. at index IX_Dave_General, if i don't have trancode, so what can i do?

thank's for you answer

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

kurniawan.kumala (1/3/2011)
hi, dave
may i question to you, base on your link.....
1. indexes IX_Dave_Items and IX_Dave_Price can be run at sql 2005?
2. at index IX_Dave_General, if i don't have trancode, so what can i do?
thank's for you answer

Hi Kurniawan ,

I think that you need a bit more training before attempting this.

There seem to be some base concepts you are not understanding , short of writing the query for you , which i am not prepared to do , i not sure how to best help you now.

Is there someone more experienced on-site you can talk to about this ?

Clear Sky SQL
My Blog[/url]

kurniawan.kumala SSC Enthusiast Points: 113 More actions · Answer 3

hi dave,

i'm lonely in my office. may you guide me, step by step to solve my case ,dave?

thank's

kurniawan.kumala SSC Enthusiast Points: 113 More actions · Answer 4

hi dave,

would you answer this question , please......

when i try to create Index [kur_items] at temp_stok (i use sql server 2005)

-------------------------------------------

CREATE NONCLUSTERED INDEX [kur_items]

ON [dbo].[temp_Stok]

(

[itemlookupcode] ASC,

[datetransferred] ASC

)

INCLUDE ( [quantity])

WHERE ([TranCode] IN ('IN')

WITH (

PAD_INDEX = OFF,

STATISTICS_NORECOMPUTE = OFF,

SORT_IN_TEMPDB = OFF,

IGNORE_DUP_KEY = OFF,

DROP_EXISTING = OFF,

ONLINE = OFF,

ALLOW_ROW_LOCKS = ON,

ALLOW_PAGE_LOCKS = ON

)

ON [PRIMARY]

GO

-------------------------------------------

i have get an error : Msg 156, Level 15, State 1, Line 8

Incorrect syntax near the keyword 'WHERE'.

please, show me the solution to solve this problem? thank's

LutzM SSC Guru Points: 107049 More actions · Answer 5

kurniawan.kumala (1/3/2011)
hi dave,
would you answer this question , please......
when i try to create Index [kur_items] at temp_stok (i use sql server 2005)
-------------------------------------------
CREATE NONCLUSTERED INDEX [kur_items]
ON [dbo].[temp_Stok]
(
[itemlookupcode] ASC,
[datetransferred] ASC
)
INCLUDE ( [quantity])
WHERE ([TranCode] IN ('IN')
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
ON [PRIMARY]
GO
-------------------------------------------
i have get an error : Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'WHERE'.
please, show me the solution to solve this problem? thank's

You're trying to create a filtered index. This is a feature introduced in version 2008. Since you posted in a 2K5 forum, I'd expect your version simply doesn't support it.

Solution: either upgrade to 2K8 or remove the WHERE condition.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

kurniawan.kumala SSC Enthusiast Points: 113 More actions · Answer 6

hi lutzm,

hi dave,

base on links:

http://www.simple-talk.com/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/

finaly i found the script to solve my case. Thank's

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 7

kurniawan.kumala (1/4/2011)
finaly i found the script to solve my case. Thank's

Glad to hear that.

Just make sure you understand the code fully, before you implement it.

After all , you will be the one to have to support it.

Clear Sky SQL
My Blog[/url]