Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2014
    Posts
    6

    Question Unanswered: Multiple joins and criteria issue

    Hi all,

    I'm fairly new to SQl and have what i can only describe as a novice problem.

    I have two tables. StoreRNG and StockAudit.

    StoreRNG contains a list of every product against a store and the current Stock on hand value

    StockAudit contains stock movements. So if a store sold 12 units of an item that would be in this table, together with Purchases. However particular units may not sell within a given period

    Im trying to join these two tables with the result showing all records from Storerng and only those records in Stockaudit that match a date criteria. In other words im expecting to see 8910 records with most saying Null or Zero in the adjust column.

    When i join without my date criteria, its fine and i get 8910 rows. When i add in the date criteria (DTMwhen) my results are reduced to 1500

    I would appreciate any pointers.

    Thank you

    Mike


    SELECT
    STORERNG.LINTITEMNUMBER AS IPN,
    STORERNG.STRSTORECODE as Location,
    STORERNG.DBLSTOCKONHAND AS Current_SOH,
    SUM(STOCKAUDIT.DBLSTOCKQTYADJUST) AS To_Adjust

    FROM
    STORERNG LEFT outer JOIN
    STOCKAUDIT ON STORERNG.STRSTORECODE = STOCKAUDIT.STRSTORECODE AND STORERNG.LINTITEMNUMBER = STOCKAUDIT.LINTITEMNUMBER

    WHERE (STORERNG.STRSTORECODE = 'huntingdon') and (STOCKAUDIT.DTMWHEN BETWEEN '2014-07-01' AND '2014-07-16 23:59')

    GROUP BY STORERNG.STRSTORECODE, STORERNG.LINTITEMNUMBER, STORERNG.DBLSTOCKONHAND

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    how many from this?

    SELECT
    STORERNG.LINTITEMNUMBER AS IPN,
    STORERNG.STRSTORECODE as Location,
    STORERNG.DBLSTOCKONHAND AS Current_SOH,
    SUM(STOCKAUDIT.DBLSTOCKQTYADJUST) AS To_Adjust

    FROM
    STORERNG LEFT outer JOIN
    STOCKAUDIT ON STORERNG.STRSTORECODE = STOCKAUDIT.STRSTORECODE AND STORERNG.LINTITEMNUMBER = STOCKAUDIT.LINTITEMNUMBER
    AND (STOCKAUDIT.DTMWHEN BETWEEN '2014-07-01' AND '2014-07-16 23:59')

    WHERE (STORERNG.STRSTORECODE = 'huntingdon')

    GROUP BY STORERNG.STRSTORECODE, STORERNG.LINTITEMNUMBER, STORERNG.DBLSTOCKONHAND
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Because you want to filter the rows before the JOIN operation, I'd use:
    Code:
    WITH cte AS (
       SELECT STOCKAUDIT.STRSTORECODE, STOCKAUDIT.LINTITEMNUMBER
    ,     STOCKAUDIT.DTMWHEN, SUM(STOCKAUDIT.DBLSTOCKQTYADJUST) AS To_Adjust
          FROM STOCKAUDIT
          WHERE  STOCKAUDIT.DTMWHEN BETWEEN '2014-07-01' AND '2014-07-16 23:59'
    )
    SELECT 
       STORERNG.LINTITEMNUMBER AS IPN, STORERNG.STRSTORECODE as Location
    ,  STORERNG.DBLSTOCKONHAND AS Current_SOH, cte.To_Adjust
       FROM STORERNG 
       LEFT OUTER JOIN cte
          ON (STORERNG.STRSTORECODE = cte.STRSTORECODE
          AND STORERNG.LINTITEMNUMBER = cte.LINTITEMNUMBER)
       WHERE  STORERNG.STRSTORECODE = 'huntingdon'
       GROUP BY STORERNG.STRSTORECODE, STORERNG.LINTITEMNUMBER
    ,     STORERNG.DBLSTOCKONHAND
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jul 2014
    Posts
    6
    Quote Originally Posted by Thrasymachus View Post
    how many from this?
    HI Thrasymachus,

    This appeared to give the correct Results. Brilliant! Thank you

    However to confuse things further I now want to add in another table, well the same table to be precise Stockaudit again but with different date criteria. so i would have Storerng, Stockaudit and Stockaudit_1

  5. #5
    Join Date
    Jul 2014
    Posts
    6
    Quote Originally Posted by Pat Phelan View Post
    Because you want to filter the rows before the JOIN operation, I'd use:
    Code:
    WITH cte AS (
       SELECT STOCKAUDIT.STRSTORECODE, STOCKAUDIT.LINTITEMNUMBER
    ,     STOCKAUDIT.DTMWHEN, SUM(STOCKAUDIT.DBLSTOCKQTYADJUST) AS To_Adjust
          FROM STOCKAUDIT
          WHERE  STOCKAUDIT.DTMWHEN BETWEEN '2014-07-01' AND '2014-07-16 23:59'
    )
    SELECT 
       STORERNG.LINTITEMNUMBER AS IPN, STORERNG.STRSTORECODE as Location
    ,  STORERNG.DBLSTOCKONHAND AS Current_SOH, cte.To_Adjust
       FROM STORERNG 
       LEFT OUTER JOIN cte
          ON (STORERNG.STRSTORECODE = cte.STRSTORECODE
          AND STORERNG.LINTITEMNUMBER = cte.LINTITEMNUMBER)
       WHERE  STORERNG.STRSTORECODE = 'huntingdon'
       GROUP BY STORERNG.STRSTORECODE, STORERNG.LINTITEMNUMBER
    ,     STORERNG.DBLSTOCKONHAND
    -PatP
    Hi Pat,

    I got the following error with this

    Msg 8120, Level 16, State 1, Line 2
    Column 'STOCKAUDIT.STRSTORECODE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Mike

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    oops, my bad! Try:
    Code:
    WITH cte AS (
       SELECT STOCKAUDIT.STRSTORECODE, STOCKAUDIT.LINTITEMNUMBER
    ,     STOCKAUDIT.DTMWHEN, SUM(STOCKAUDIT.DBLSTOCKQTYADJUST) AS To_Adjust
          FROM STOCKAUDIT
          WHERE  STOCKAUDIT.DTMWHEN BETWEEN '2014-07-01' AND '2014-07-16 23:59'
          GROUP BY STOCKAUDIT.STRSTORECODE, STOCKAUDIT.LINTITEMNUMBER
    ,        STOCKAUDIT.DTMWHEN
    )
    SELECT 
       STORERNG.LINTITEMNUMBER AS IPN, STORERNG.STRSTORECODE as Location
    ,  STORERNG.DBLSTOCKONHAND AS Current_SOH, cte.To_Adjust
       FROM STORERNG 
       LEFT OUTER JOIN cte
          ON (STORERNG.STRSTORECODE = cte.STRSTORECODE
          AND STORERNG.LINTITEMNUMBER = cte.LINTITEMNUMBER)
       WHERE  STORERNG.STRSTORECODE = 'huntingdon'
       GROUP BY STORERNG.STRSTORECODE, STORERNG.LINTITEMNUMBER
    ,     STORERNG.DBLSTOCKONHAND
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jul 2014
    Posts
    6
    Hi Pat P,

    Thank you for the effort. unfortunately this didn't work either.

    I now get Column 'cte.To_Adjust' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    Thrasymachus method seems to work but I now need to add the additional table

    Mike

  8. #8
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    Wait, if you are just adding another set of date criteria, couldn't you just include an OR statement for the second date range? I do not see the need to add in another instance of the table...

    AND (STOCKAUDIT.DTMWHEN BETWEEN '2014-07-01' AND '2014-07-16 23:59' OR STOCKAUDIT.DTMWHEN BETWEEN 'RangeStart2' AND 'RangeEnd2')

  9. #9
    Join Date
    Jul 2014
    Posts
    6
    Quote Originally Posted by Brian Cermak View Post
    Wait, if you are just adding another set of date criteria, couldn't you just include an OR statement for the second date range? I do not see the need to add in another instance of the table...

    AND (STOCKAUDIT.DTMWHEN BETWEEN '2014-07-01' AND '2014-07-16 23:59' OR STOCKAUDIT.DTMWHEN BETWEEN 'RangeStart2' AND 'RangeEnd2')
    Hi Brian,

    thanks for the suggestion and your time

    I need to show the two columns, Sum of one date range and sum of the other date range.

    The intention is to use this to calculate stock on hand at a given point, by taking today - subtracting the values to the start of the month and then adding to the point I need the figure for

    Mike

  10. #10
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    You can separate the two ranges with another column containing a case statement.

    WITH cte AS (
    SELECT STOCKAUDIT.STRSTORECODE, STOCKAUDIT.LINTITEMNUMBER
    , STOCKAUDIT.DTMWHEN, SUM(STOCKAUDIT.DBLSTOCKQTYADJUST) AS To_Adjust
    FROM STOCKAUDIT
    WHERE (STOCKAUDIT.DTMWHEN BETWEEN '2014-07-01' AND '2014-07-16 23:59' OR STOCKAUDIT.DTMWHEN BETWEEN 'RangeStart2' AND 'RangeEnd2')
    )
    SELECT
    STORERNG.LINTITEMNUMBER AS IPN, STORERNG.STRSTORECODE as Location
    , STORERNG.DBLSTOCKONHAND AS Current_SOH, cte.To_Adjust
    , CASE STOCKAUDIT.DTMWHEN WHEN BETWEEN '2014-07-01' AND '2014-07-16 23:59' THEN 'Range1' ELSE 'Range1' END as DTMWHENRange
    FROM STORERNG
    LEFT OUTER JOIN cte
    ON (STORERNG.STRSTORECODE = cte.STRSTORECODE
    AND STORERNG.LINTITEMNUMBER = cte.LINTITEMNUMBER)
    WHERE STORERNG.STRSTORECODE = 'huntingdon'
    GROUP BY STORERNG.STRSTORECODE, STORERNG.LINTITEMNUMBER
    , STORERNG.DBLSTOCKONHAND

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Mike,
    I like to point folks to a blog by Robert Catterall from years ago and his links to Terry Purcell's white papers, when they are having issues with outer joins as the two of them put out some of the best descriptions of each. Catterall Consulting: Outer Join: Get the Predicates Right
    Dave

  12. #12
    Join Date
    Jul 2014
    Posts
    6
    Quote Originally Posted by Brian Cermak View Post
    You can separate the two ranges with another column containing a case statement.
    Hi Brian,

    Thanks again. I am not familiar with the case statement. I gave it a go but im getting:
    Msg 156, Level 15, State 1, Line 10
    Incorrect syntax near the keyword 'BETWEEN'.


    Mike

  13. #13
    Join Date
    Jan 2003
    Location
    Minneapolis
    Posts
    58
    Sorry... a mild syntax issue. Try this.

    WITH cte AS (
    SELECT STOCKAUDIT.STRSTORECODE, STOCKAUDIT.LINTITEMNUMBER
    , STOCKAUDIT.DTMWHEN, SUM(STOCKAUDIT.DBLSTOCKQTYADJUST) AS To_Adjust
    FROM STOCKAUDIT
    WHERE (STOCKAUDIT.DTM BETWEEN '2014-07-01' AND '2014-07-16 23:59' OR STOCKAUDIT.DTM BETWEEN 'RangeStart2' AND 'RangeEnd2')
    )
    SELECT
    STORERNG.LINTITEMNUMBER AS IPN, STORERNG.STRSTORECODE as Location
    , STORERNG.DBLSTOCKONHAND AS Current_SOH, cte.To_Adjust
    , CASE STOCKAUDIT.DTM WHEN BETWEEN '2014-07-01' AND '2014-07-16 23:59' THEN 'Range1' ELSE 'Range2' END as DTM WHENRange
    FROM STORERNG
    LEFT OUTER JOIN cte
    ON (STORERNG.STRSTORECODE = cte.STRSTORECODE
    AND STORERNG.LINTITEMNUMBER = cte.LINTITEMNUMBER)
    WHERE STORERNG.STRSTORECODE = 'huntingdon'
    GROUP BY STORERNG.STRSTORECODE, STORERNG.LINTITEMNUMBER
    , STORERNG.DBLSTOCKONHAND

Tags for this Thread

Posting Permissions

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