Parent Child Relationship

• Vijay Vijayaratnam

  SSC-Addicted

  Points: 471

  More actions

  November 2, 2005 at 5:43 pm

  #89881

    

  I have invoice  Master File  and I need to get Minimum Date from my stock movement history  Table   

  How can I write the sql scripts please

    

  Invoice Master

  Invoice No    Ref No  

  OP/I2002     T13711  

  OP/I2003     T40743

  Stock Movement History  Table

  Warehouse Date  Ref_No  Batch_no

  D2  21/05/2003 P08183     P08183   

  D2  11/08/2003 T13711     P08183   

  D2  11/08/2003 T13711     P10709   

  AB  11/08/2003 T13711     T13711   

  AB  11/08/2003 T13711     T13711   

  Warehouse    Date  Ref_No  Batch_no

  DT  26/05/2004 Q05153     Q05153     

  DT  15/09/2004 223590  Q05153     

  MK  15/09/2004 G30417     G30417     

  TR  22/12/2004 T33859     T33859     

  MK  22/12/2004 T33859     G30417     

  TR  11/05/2005 261732  T33859     

  Bs  11/05/2005 G30934     G30934     

  TR  12/05/2005 T36959     T36959     

  Bs  12/05/2005 T36959     G30934     

  TR  18/05/2005 262291  T36959     

  Bs  18/05/2005 G30969     G30969     

  DT  23/05/2005 T37192     T37192     

  Bs  23/05/2005 T37192     G30969     

  TR  23/05/2005 T37194     T37194     

  DT  23/05/2005 T37194     T37192     

  Bs  27/09/2005 T40743     T40743     

  TR  27/09/2005 T40743     T37194     

  Thanks in advance

  Kind Regards

  Vijay

• [KH]

  SSCommitted

  Points: 1908

  More actions

  November 3, 2005 at 12:44 am

  #602043

  select [Invoice No], [Ref No], min([Date])

  from [Invoice Master] i, [Stock Movement] m

  where i.[Ref No] = m.[Ref No]

  group by [Invoice No], [Ref No]