Post reply

How to show Opening Stock of previous day

  • October 4, 2011 at 9:22 am

    #244145

    I have separate table of transaction like purchase, sale, receive, transfer and master table where is current stock. for ex.

    on 01-Oct-2011 i have following records in different tables.

    Opening Stock(Master Table) 200 Qty

    Purchase (Purchase Table) 35 Qty

    Receive (Receive Table ) 15 Qty

    Sale (Sale Table) 85 Qty

    Transfer (Transfer Table) 10 Qty

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

    Closing Stock 155 Qty

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

    Now i'll get 155 at the evening of 01-Oct-2011 and after 2 days at the evening (03-Oct-2011) Current stock in Master Table Qty is 215.

    On 04 Oct i can get Opening stock that will be 215.

    How can i get the report of all transaction from 01-Oct-2011 to 03-Oct-2011 in below format.

    Opening Stock (01-Oct-2011)

    Purchase Qty (From 01-Oct to 03-Oct)

    Receive Qty (From 01-Oct to 03-Oct)

    Sale Qty (From 01-Oct to 03-Oct)

    Transfer (From 01-Oct to 03-Oct)

    Closing Stock (03-Oct-2011)

    Any help will be appreciated.

    Thanks

    Yusuf

  • October 4, 2011 at 10:10 am

    #1390235

    Hi. It seems you are somewhat new around here. Welcome. Before we have a chance at helping you with your issue you need to provide some details. If you want tested and accurate code you need to post ddl, sample data (insert statements), desired output based on your sample data. Without that the best we can do is take a stab in the dark. Take a look at the link in my signature for best practices on how to collect this information and post it in a consumable format.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 2 posts - 1 through 1 (of 1 total)

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