Opening & Closing Stock Problem

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » Opening & Closing Stock Problem

14 posts, Page 1 of 2

1 2 »»

Opening & Closing Stock Problem

Rate Topic

Display Mode

Topic Options

Author

Message

shakir-769418

Posted Sunday, January 18, 2009 12:58 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 03, 2011 12:05 AM
Points: 3, Visits: 31

I am shakir hossain from Bangladesh; I am a beginner of MS SQL 2000 programmer. I need to your help. I try to make a program for store department. I need to make a report daily all Item wise stock report. For example, Opening stock, received qty, total stock qty, sale/issue qty, and closing stock. For more detail, when we want to see first time opening stock comes from initial opening stock, and when we want to see next days stock report then opening stock comes from previous date & previous closing stock.

Date Item Name Opening stock Received qty. Total Qty. Issue/Sale qty. Closing Stock.

14/12/2006 PH meter 10 1 11 5 6

When we want to see 15/12/2006 report opening stock comes from 14/12/2006 closing stock

Date Item Name Opening stock Received qty. Total Qty. Issue/Sale qty. Closing Stock.

15/12/2006 PH meter 6 0 6 1 5

Date Item Name Opening stock Received qty. Total Qty. Issue/Sale qty. Closing Stock.

16/12/2006 PH meter 5 0 5 0 5

I am waiting for your reply.

Thanking You
Shakir Hossain
shakirbd@yahoo.com

Post #638875

Steve Jones - SSC Editor

Posted Sunday, January 18, 2009 9:14 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 4:22 PM
Points: 30,079, Visits: 12,279

It's not quite clear what you are doing here, or how you want to use this. We also like to see you do some work, show some effort in T-sQL here as we don't do homework questions.

Think MIN(), MAX() with the date and returning the other data. You'll need a GROUP BY clause as well.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #638934

Garadin

Posted Sunday, January 18, 2009 11:57 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:42 AM
Points: 1,524, Visits: 4,012

In addition to what Steve has requested, please see the article in my signature on how to provide sample data.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. Wink

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation

Post #638999

Jeswanth Jaishanker

Posted Sunday, January 18, 2009 11:12 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 08, 2012 4:05 AM
Points: 229, Visits: 155

Hi,

if you are creating a stock report like Opening Balance in the first row and daily transactions below and at the end Closing Stock, you can create a functioon which returns a table and call it top as opening balance and last and closing balance and union it with the daily transactions like

select 'By Opening Bal' as header ,f1,f2,f3,dt,ob from dbo.Opening_stockbalance(@dt)
union all
select 'trans',f1,f2,f3,dt,qty from stocktrans where dt = @dt
union all
select 'Clsong,'f1,f2,f3,dt,ob from dbo.Closing_stockbalance(@dt)

hope this will help u ,
Jeswanth

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

Post #639092

shakir-769418

Posted Monday, January 19, 2009 4:38 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 03, 2011 12:05 AM
Points: 3, Visits: 31

Please give me detail solutions.....

Post #639181

GilaMonster

Posted Monday, January 19, 2009 9:39 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:20 PM
Points: 32,249, Visits: 24,735

shakir (1/19/2009)

Please give me detail solutions.....

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #639402

Jeff Moden

Posted Monday, January 19, 2009 2:17 PM

One Orange Chip

Group: General Forum Members
Last Login: Today @ 11:02 PM
Points: 29,772, Visits: 23,421

Better than all of that, let's see what YOU have tried. We're not consultants that work for free to write other people's code... we're here to help you with the code you've written. ;)

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #639519

Steve Jones - SSC Editor

Posted Monday, January 19, 2009 9:47 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 4:22 PM
Points: 30,079, Visits: 12,279

shakir (1/19/2009)

Please give me detail solutions.....

This isn't trying to solve the problem. If you want that, hire someone. We are happy to help, and I gave hints, but you have to do some work as well.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #639639

tevers

Posted Monday, January 19, 2009 10:26 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 04, 2012 1:28 PM
Points: 1, Visits: 34

So that's how outsourcing works!! The project goes to XXXX and someone posts it as a question and then sends the answer back. Although I think they have to add a few logic errors and misspellings so the company thinks it was actually done offshore.

Editor's Note: Removed an offensive note in the post. I realize this was in humor, but it was insulting to a group of people.

Post #639654

Jeff Moden

Posted Tuesday, January 20, 2009 5:42 AM

One Orange Chip

Group: General Forum Members
Last Login: Today @ 11:02 PM
Points: 29,772, Visits: 23,421

tevers (1/19/2009)

So that's how outsourcing works!! The project goes to India and someone posts it as a question and then sends the answer back. Although I think they have to add a few logic errors and misspellings so the company thinks it was actually done offshore.

Heh... pretty much what I was thinking. Welcome aboard, Tevers.

--Jeff Moden

Post #639844

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions