CODEWITHSUNDEEP
sql-server-2008
user3646942
user3646942

Reputation: 5

Joining of tables

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

Upvotes: 0

Views: 47

Answers (1)

Nithin Gangadharan
Nithin Gangadharan

Reputation: 527

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

Upvotes: 1

Related Questions