Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5

Thread: SQL Help

  1. #1
    New to the CF scene
    Join Date
    Sep 2010
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Help

    Hi
    I have a requirement to put together an Inv Ageing report. The data is being written to SQL via an ERP.
    There is an InvMovement table which holds ever transaction ever done for each stockcode/warehouse combination and I would like to have this table rebuild in a report to allow me to age the stock at multiple date points so if I wanted to age the stock as at the end of January this year I would need to first exclude everything after that date then get all of the stock receipts aged then start to consume the oldest stock until all of the consuming transactions are done whcih will leave me with an OnHand value at that point.
    I am having a problem obviously due to my lacking T-SQL knowledge, to put something like this together and I have tried a few approaches but have not managed to get this working.
    Any help would be appreciated.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,602
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    Show the relevant part of the table schema(s).

    Table names, field names, field types. Don't need to show everything, just tables/fields that the report will depend on.

    It doesn't really sound too hard, if the table(s) are well organized.

    Likely something as simple as
    Code:
    SELECT productid, SUM(quantityReceived) - SUM(quantityShipped) AS onHand
    FROM products
    WHERE eventDate < '1/1/2010'
    GROUP BY productid
    ORDER BY productid
    That would presumably get you stock on hand as of the end of the day, December 31, 2009.

    But without seeing the schema, that's just a guess.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Sep 2010
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I can provide tables with a few lines of data but before I do that let me comment.....
    The data is fairly well structured and you quite right about your logic of providing the on hand as at the end of December. The only problem is that actually need to age the stock receipts then subtract the comsumption to give me an ageing of the remaining stock if any at that specific point.

    So example:

    Stock Code A123 has 4 receipts of stock either from purchases or manufacture, then there is consumption of the stock from 2 issues. The is also a positive adjustment of the stock item.
    StockCode TrnType TrnDate TrnQty
    A123 | Receipt |15/10/2009 | 100
    A123 | Receipt |15/11/2009 | 100
    A123 | Receipt |07/12/2009 | 100
    A123 | Issue |12/12/2009 | 100
    A123 | Receipt |10/01/2010 | 100
    A123 | Issue |15/01/2010 | 100
    A123 | Adjustment |20/01/2010 | 100

    Basically if I say I want an ageing as at the end of January 2010 then you have to say that each months receipts are ageing and then consumed oldest to newest so the ageing should look like the following based on the transactions provided above.

    StockCode | Current | 30Days | 60Days | 90Days
    A123 | 200 | 100 | 0.00 | 0.00

    Does this make sense?
    Last edited by Raft928curb18; 09-17-2010 at 01:01 PM.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,602
    Thanks
    78
    Thanked 4,387 Times in 4,352 Posts
    Well, I'd be sorely tempted to simply get the 4 agings each as a separate query and then use one composite query to clump them together.

    That is:
    Code:
    SELECT stockcode, age0.current, age30.[30Days], age60.[60Days], age90.[90Days]
    FROM ( SELECT stockcode, ...your expression... AS current 
              FROM table WHERE theDate < @asof ) AS age0,
         ( SELECT stockcode, ...your expression... AS [30Days]
              FROM table WHERE theDate < DATEADD(d, -30, @asof) ) AS age30,
         ( SELECT stockcode, ...your expression... AS [60Days]
              FROM table WHERE theDate < DATEADD(d, -60, @asof) ) AS age60,
         ( SELECT stockcode, ...your expression... AS [90Days]
              FROM table WHERE theDate < DATEADD(d, -90, @asof) ) AS age90
    WHERE age0.stockcode = age30.stockcode
    AND age0.stockcode = age60.stockcode
    AND age0.stockcode = age90.stockcode
    ORDER BY age0.stockcode
    *SOMETHING* along those lines.

    Obviously untested. And I'm not sure what you need to report for those aging values. But assuming you can get the aging value right for one date, presumably you can get it right for each of the 4 needed dates.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New to the CF scene
    Join Date
    Sep 2010
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Let me have a look and see what the results look like, it might just be that easy.....


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •