Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2014
    Posts
    5

    Unanswered: Issue with Group by Mysql query

    Hello
    I have been stumped by this query for the past day,and seeking some expert help before i throw my PC out of the window, lol.

    I have a table that consists of invoices; invoice number column name is Trans_ref$$ (primary key).

    each invoice is linked to a job , column name is OPSREF$$_ORIGINAL (foreign key).

    Both columns are in the same table.

    a job can have multiple invoice.

    Now the issue is i'm trying to create a query that will identify the FIRST invoice (Trans_ref$$) in a job group and if that FIRST invoice is with a date range then output the job (OPSREF$$_ORIGINAL).
    The invoice number in a sequential / unique number.

    I have tried using a case / min group by statement to identify the first invoice in a job group within a data range, however this query is not isolating the first invoice within a date range, instead it is just outputting 1st or 2nd or 3rd positioned invoice in job group that matches the date range….I hope this makes sense .

    I have included a screen shot of the table and the desired output, and would appreciate if someone can assist with the query.


    I have attached a screen shot to help understand.

    Code:
    SET @START_DATE := "2014-08-27";
    SET @END_DATE:= "2014-09-02";
    
    select 
    count(TRANS_REF$$),
    Case 
    when min(TRANS_REF$$) and INVOICE_DATE_D8>=@START_DATE and INVOICE_DATE_D8<=@END_DATE
    then OPSREF$$_ORIGINAL
    end as OPSREF$$_ORIGINAL
    from INVOICE_HEADER_UNS
    where 
    TRANSACTION_STATUS = 9
    and Tmode$$="06"
    group by OPSREF$$_ORIGINAL
    Attached Thumbnails Attached Thumbnails query.JPG  

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that "group by" might be unnecessary.

    My idea was
    (1) find rows which are first in each OPSREF$$_ORIGINAL.
    (2) output from (1) which INVOICE_DATE_D8 was in the the date range.

    Resultant query might be like...
    Code:
    select OPSREF$$_ORIGINAL
     from  INVOICE_HEADER_UNS as t
     where TRANSACTION_STATUS = 9
      and  Tmode$$ = "06"
      and  not exists
           (select 0
             from  INVOICE_HEADER_UNS as s
             where ...
              and  ...
              ...
              and  s.INVOICE_DATE_D8 < t.INVOICE_DATE_D8
           )
      and  INVOICE_DATE_D8 between @START_DATE and @END_DATE

  3. #3
    Join Date
    Sep 2014
    Posts
    5
    Hi
    I have tried as suggested but is returning 0 rows from the query.

    here is the code i have used.

    I do not understand select 0 ; does that collect the first invoice from each OPSREF$$_ORIGINAL?


    Code:
    SET @START_DATE := "2014-08-27";
    
    SET @END_DATE:= "2014-09-02";
    
    
    select OPSREF$$_ORIGINAL
    from  INVOICE_HEADER_UNS as t
     where TRANSACTION_STATUS = 9
      and  Tmode$$ = "06"
      and  not exists
           (select 0
             from  INVOICE_HEADER_UNS as s
             where s.INVOICE_DATE_D8 <> t.INVOICE_DATE_D8
           )
      and  INVOICE_DATE_D8 between @START_DATE and @END_DATE

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by uklogistics View Post
    ...
    ...

    Code:
    SET @START_DATE := "2014-08-27";
    
    SET @END_DATE:= "2014-09-02";
    
    
    select OPSREF$$_ORIGINAL
    from  INVOICE_HEADER_UNS as t
     where TRANSACTION_STATUS = 9
      and  Tmode$$ = "06"
      and  not exists
           (select 0
             from  INVOICE_HEADER_UNS as s
             where s.INVOICE_DATE_D8 <> t.INVOICE_DATE_D8
           )
      and  INVOICE_DATE_D8 between @START_DATE and @END_DATE
    Why did you used the condition?
    Code:
             where s.INVOICE_DATE_D8 <> t.INVOICE_DATE_D8
    I suggested like...
    Code:
             where ...
              and  ...
              ...
              and  s.INVOICE_DATE_D8 < t.INVOICE_DATE_D8
    That means
    (1) You shoud use "<" rather than "<>".
    (2) More predicates(connected by "and") must be necessary.

  5. #5
    Join Date
    Sep 2014
    Posts
    5
    please clarify why I get the first record for each OPSREF$$_ORIGINAL ?

    This is the bit I do not understand in the code.

    for example , when I query OPSREF$$_ORIGINAL "466" , there are 30 invoices in this group of OPSREF$$_ORIGINAL. I'm only internet the first Trans_ref in the sequence of Trans_ref's ,i.e. 168519.

    Once I can obtain this first instance then I think I can then check whether this instance is with date range. Hope this makes sense.

    http://i59.tinypic.com/11jvm2g.jpg
    Attached Thumbnails Attached Thumbnails query2.png  

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You provided the sample data by images(.jpg or png).
    I's very ineffective to understand, and/or investigate your requirement.
    (at least I think so.)

    I made them in text formats.

    .jpg in first post.
    Code:
    TABLE-INVOICE_HEADER_UNS                      Criteria
    TRANS_REF$$ INVOICE_DATE_D8 OPSREF$$_ORIGINAL First inv in group In date range
    1           26/08/2014      ABC               Yes                No
    2           02/08/2014      ABC               No                 Yes
    /* I guessed this row might be
    2           02/09/2014      ABC
    */
    3           28/08/2014      DDD               Yes                Yes
    4           09/09/2014      DDD               No                 No
    5           01/01/2013      JJJ               Yes                No
    6           21/12/2013      JJJ               No                 No
    7           01/09/2014      LLL               Yes                Yes
    .png in last post.
    Note: I added "First inv in group" and "In date range".
    Code:
    Invoice position Trans.Ref Our.Ref Inv.Date  First inv in group In date range
                   1    168519     466 17-Sep-13 Yes                No
    ...
    ...
    ...
                  26    195891     466 07-Aug-14 No                 No
                  27    198477     466 02-Sep-14 No                 Yes
                  28    198478     466 02-Sep-14 No                 Yes
                  29    198484     466 02-Sep-14 No                 Yes
                  30    198586     466 03-Sep-14 No                 No
    Although column names were completely different,
    by using my full imaginations and comparing those two data,
    I thought that no result should be from ".png in last post".
    Because, there was no row which showed 'Yes' for both of "First inv in group" and "In date range".

    Please clarify your requirements which resolved the contradiction(in my thought) between ".jpg in first post" and ".png in last post".

  7. #7
    Join Date
    Sep 2014
    Posts
    5
    hi tonkuma
    thanks for taking the time to recreate the images into text to better represent the requirement. FYG, I haven't been able to find an an elegant way to present tabular data into forum posts which was the reason for using images. P

    In answer to your question...

    I thought that no result should be from ".png in last post".
    Because, there was no row which showed 'Yes' for both of "First inv in group" and "In date range".
    The answer to your question is YES - this OPSREF$$_ORIGINAL number 466 in the second image should not appear in results, even though trans_ref 168519 is the first invoice, it is not within the date range.

    So the requirement is;

    1. grab each OPSREF$$_ORIGINAL group within the INVOICE_HEADER_UNS table

    Code:
    Date Range - Start 27/08/2014 - End 02/09/2014					
    					
    TABLE - INVOICE_HEADER_UNS					
    					
    TRANS_REF$$	INVOICE_DATE_D8	   OPSREF$$_ORIGINAL 	  			
    --------------------------------------------------------
    |   1	    |    26/08/2014	|  ABC			| 
    |   2	    |    02/08/2014	|  ABC			|  
    --------------------------------------------------------
    |   3	    |    28/08/2014	|  DDD			|  
    |   4	    |    09/09/2014	|  DDD			|  
    ---------------------------------------------------------
    |   5       |	01/01/2013	|  JJJ		        |
    |   6       |	21/12/2013	|  JJJ			|
    ---------------------------------------------------------
    |   7	    |	01/09/2014	|  LLL			|
    ---------------------------------------------------------


    2. within each OPSREF$$_ORIGINAL group, look at TRANS_REF$$ sequence and find the first TRANS_REF$$ in that OPSREF$$_ORIGINAL group.

    Code:
    Date Range - Start 27/08/2014 - End 02/09/2014					
    					
    TABLE - INVOICE_HEADER_UNS					
    					
    TRANS_REF$$	INVOICE_DATE_D8	   OPSREF$$_ORIGINAL 	  Is First inv?    			
    --------------------------------------------------------
    |   1	    |    26/08/2014	|  ABC			|  yes		     
    |   2	    |    02/08/2014	|  ABC			|  no		    
    --------------------------------------------------------
    |   3	    |    28/08/2014	|  DDD			|  yes		     
    |   4	    |    09/09/2014	|  DDD			|  no		     
    ---------------------------------------------------------
    |   5       |	01/01/2013	|  JJJ		        |  yes	             
    |   6       |	21/12/2013	|  JJJ			|  no		     
    ---------------------------------------------------------
    |   7	    |	01/09/2014	|  LLL			|  yes	             
    ---------------------------------------------------------

    3. Look at the the FIRST INVOICE in the ordered sequence for the group and check the INVOICE_DATE_D8 for that row and see if it is within date range ( 27/08/2014 - 02/09/2014 )

    Code:
    Date Range - Start 27/08/2014 - End 02/09/2014					
    					
    TABLE - INVOICE_HEADER_UNS					
    					
    TRANS_REF$$	INVOICE_DATE_D8	   OPSREF$$_ORIGINAL 	  Is First inv?    In date range?			
    --------------------------------------------------------
    |   1	    |    26/08/2014	|  ABC			|  yes		     no
    
    --------------------------------------------------------
    |   3	    |    28/08/2014	|  DDD			|  yes		     yes
    
    ---------------------------------------------------------
    |   5       |	01/01/2013	|  JJJ		        |  yes	             no
    
    ---------------------------------------------------------
    |   7	    |	01/09/2014	|  LLL			|  yes	             yes
    ---------------------------------------------------------
    4. Does each row meet the criteria (i.e. first invoice and in date - YES + YES) ? if correct then output the distinct OPSREF$$_ORIGINAL for that row

    Here is the desired output from the example above


    Code:
    Date Range - Start 27/08/2014 - End 02/09/2014					
    					
    TABLE - INVOICE_HEADER_UNS					
    					
     OPSREF$$_ORIGINAL 	  Is First inv?    In date range?			
    ------------------------
    |  DDD			|  yes		     yes
    ------------------------
    |  LLL			|  yes	             yes
    ------------------------

    Based on this, can you suggest code that will achieve desired output?

    thanks

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    ...

    I suggested like...
    Code:
             where ...
              and  ...
              ...
              and  s.INVOICE_DATE_D8 < t.INVOICE_DATE_D8
    That means
    (1) You shoud use "<" rather than "<>".
    (2) More predicates(connected by "and") must be necessary.
    I thought that 1 more predicate(or 3 more predicates, depending on the data characteristics/constraints)
    connected by "and" might be necessary additional to "and s.INVOICE_DATE_D8 < t.INVOICE_DATE_D8".


    Sorry,
    I don't want to spoonfeeding(provide complete solution which doesn't leave room for you to invent).

  9. #9
    Join Date
    Sep 2014
    Posts
    5
    based on my previous comment, could you guide me on how to retrieve the first invoice per group. this is where i'm really struggling to understand

Posting Permissions

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