Joining of tables

Question

This is my code:

SELECT
    A.AREA_CODE, 
    A.OUTLET_NAME, 
    SUM (C.amount) as E_SALES,
    SUM (B.amount) as NETSALES 
FROM
    M_OUTLET A,
    E_SALES_DETAIL C, 
    T_SALES_DETAIL B
WHERE
    A.OUTLET_no = B.OUTLET_NO
AND A.OUTLET_NO = C.OUTLET_NO
AND A.OUTLET_TYPE_DESC not like '%head%office%'
AND A.OUTLET_TYPE_DESC not like '%prod%'
AND A.OUTLET_TYPE_DESC not like '%stor%'
AND MONTH (B.SYSTEM_DATE) = MONTH ( '" & dtpMonthNetsales.Text & "')
AND YEAR (B.SYSTEM_DATE ) = YEAR ('" & dtpMonthNetsales.Text & "')
AND B.VOID = 'N' AND C.VOID = 'N'
GROUP BY A.AREA_CODE , A.OUTLET_NAME
ORDER BY A.AREA_CODE

But when I run my program it takes so long to show the output and it says;

Timeout expired. The time period elapsed prior to completion of the operation or the server is not responding.

I also tried to run my code in MS SQL Server 2008 and it takes 1 minute and 42 seconds to show the output.

My classmate says that I must try to use derived tables but I don't know how to do that. I searched at the internet but I can't see the right explanation for me.

The output must be like this:

AREA_CODE      OUTLET_NAME          E_SALES         NETSALES
1          MAKATI BU CAFE 2      582458.46       582458.46
2          MAKATI BU CART        365825.45        365825.45

What indexes have you got on the tables? At the very least each table should have an index on OUTLET_NO. Also you seem to implying that youare creating SQL inline in code rather than calling a stored procedure. There are all sorts of performance issues and security issues by creating dynamic SQL in code. — Dijkgraaf, May 22 '14 at 02:35
You have in your where statement. A.OUTLET_no = B.OUTLET_NO and A.OUTLET_NO = C.OUTLET_NO. If there is no index on these fields then the join will be very slow. — Dijkgraaf, May 22 '14 at 02:55

Nithin Gangadharan · Answer 1 · 2014-05-22T09:04:53.600

1

Try this:

 SELECT A.AREA_CODE, 
        A.OUTLET_NAME, 
        SUM (C.amount) as E_SALES,
        SUM (B.amount) as NETSALES 
   FROM M_OUTLET A
        INNER JOIN E_SALES_DETAIL C ON A.OUTLET_NO = C.OUTLET_NO
        INNER JOIN T_SALES_DETAIL B ON A.OUTLET_no = B.OUTLET_NO 
  WHERE B.VOID = 'N' 
    AND C.VOID = 'N'
    AND MONTH(B.SYSTEM_DATE) = MONTH('" & dtpMonthNetsales.Text & "')
    AND YEAR(B.SYSTEM_DATE) = YEAR('" & dtpMonthNetsales.Text & "')
    AND A.OUTLET_TYPE_DESC not like '%head%office%'
    AND A.OUTLET_TYPE_DESC not like '%prod%'
    AND A.OUTLET_TYPE_DESC not like '%stor%'
  GROUP BY A.AREA_CODE, A.OUTLET_NAME
  ORDER BY A.AREA_CODE

If possible try not to use LIKE operator

edited May 22 '14 at 09:04

answered May 22 '14 at 07:13

Nithin Gangadharan

527
4
9

Thank you for your help. I try your code but it gives me message like this: The multi-part identifier “A.OUTLET_NO” could not be bound. The multi-part identifier “A.OUTLET_TYPE_DESC” could not be bound. The multi-part identifier “A.AREA_CODE” could not be bound. The multi-part identifier “A.OUTLET_NAME” could not be bound. – user3646942 May 22 '14 at 09:00
Sorry if I have so many questions. I am just new to visual studio 2010 your new code shows the message: "object reference not set to an instance of an object". Thank you so much for helping me. – user3646942 May 22 '14 at 09:30
This seems to be a .net issue. Check the following link: http://stackoverflow.com/questions/779091/what-does-object-reference-not-set-to-an-instance-of-an-object-mean – Nithin Gangadharan May 22 '14 at 09:41

Joining of tables

1 Answers1