Please help with this query.

[lucia]

I am using Sql server 2000.

I have a table called s. I need left join t with this s. and left join m with t.
Some fields in table t:Tran_type, Commodity, Qty ,Amount...
Some fields in table m:Code...

What I need is: When t.Tran_type ="p" or "D", if t.Commodity ="C" then sum the Qty and Amount. If m.Code ="BA"or "BO", then sum the Qty and Amount.

I have a query like this:

  strSQL = strSQL & "SELECT s.Route_Num , s.StopDate, s.START_DATETIME, s.END_DATETIME, "
  strSQL = strSQL & " t.Tran_Type, t.Commodity, m.Code, "
  strSQL = strSQL & "SUM(t.Qty)as TotQty, SUM(t.Amount) as TotAmount "
  strSQL = strSQL & "FROM  s "
  strSQL = strSQL & "LEFT JOIN t ON (s.Stop_Num = t.stop_num "
  strSQL = strSQL & "AND s.StopDate = t.TranDate "
  strSQL = strSQL & "AND s.Route_Num = t.Route_Num) "
  strSQL = strSQL & "LEFT JOIN m ON t.Commodity = m.CommId "
  strSQL = strSQL & "WHERE t.ItemId <>'NOPU' "
  strSQL = strSQL & "AND s.START_DATETIME >='" & DTPFrom.value & "' "
  strSQL = strSQL & "AND s.END_DATETIME <='" & DTPTo.value & "' "
  strSQL = strSQL & "AND (t.Tran_Type='D' or t.Tran_Type='P') "
  strSQL = strSQL & "AND (m.CODE = 'BO' or m.CODE = 'BA') "
  strSQL = strSQL & "AND t.Commodity ='C' "
  strSQL = strSQL & "GROUP BY  "
  strSQL = strSQL & "s.Route_Num , s.StopDate, s.START_DATETIME, s.END_DATETIME, "
  strSQL = strSQL & " t.Tran_Type, t.Commodity, m.Code "
  strSQL = strSQL & "ORDER BY  s.StopDate, s.Start_DateTime"

It returns 0 record. I know there is a problem around following part. But I don't know how to correct it according to the needs I mentioned above.

strSQL = strSQL & "AND (t.Tran_Type='D' or t.Tran_Type='P') "
strSQL = strSQL & "AND (m.CODE = 'BO' or m.CODE = 'BA') "
strSQL = strSQL & "AND t.Commodity ='C' "

Thanks a lot.

Since there is no one in the database forum, I have to post it here.

[si_the_geek]

This is not related to VB code at all - thread moved to Database Development forum

[si_the_geek]

One thing that is likely to cause a problem at some point (if it isn't already) is the way you have appended Date values to the SQL statement - it is not guaranteed to work correctly, as it depends on the regional settings of the computer your program runs on.

To do it safely, you need to convert the Date into a String of a particular format, as explained in the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of this forum)


Your question isn't entirely clear, but if I'm understanding you correctly, change this:

AND (t.Tran_Type='D' or t.Tran_Type='P') 
AND (m.CODE = 'BO' or m.CODE = 'BA') 
AND t.Commodity ='C'

..to this:

AND (
      (  (t.Tran_Type='D' or t.Tran_Type='P') AND t.Commodity ='C'  )
     OR 
      (  m.CODE = 'BO' or m.CODE = 'BA'  ) 
    )

(you don't need to put the extra spacing into your code, I just do it like this for clarity)

If you want, you could make that shorter by using IN rather than or's, eg:

AND (
      (  t.Tran_Type IN('D','P') AND t.Commodity ='C'  )
     OR 
      m.CODE IN('BO','BA') 
    )