Mr xxq94813 one more wish.

Mateen
Mateen used Ask the Experts™
on
Refer
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20401845.html

Mr xxq94813 I have successfully applied your
logic and has got the desired result.

The only concern is it is taking a lot of time.

Tables:
sales_invoice_mst(inv_no varchar(8),
                  inv_date date,cust_code varchar(4))
sales_invoice_det(inv_no varhcar(8),quantity numeric(12,2),grade varchar(1)

sales_return_mst(trans_no varchar(8),sales_return_date date,cust_code varchar(4)
sales_return_det(trans_no varchar(8),quantity numeric(12,2),grade varchar(1)

sales_invoice_mst has got 25000 rows.
cancel_status<>'C' means this is not cancelled invoice.
Grade='K' means cut piece
grade='S' means short piece


My function
alter procedure dba.udf_sales(@cust_code varchar(4),@grade varchar(1),@date1 date,@date2 date)
as
begin
  declare @sales numeric(12,2),@sales_ret numeric(12,2)
  select @sales=sum(sales_invoice_det.quantity) from
    sales_invoice_det,sales_invoice_mst where
    sales_invoice_mst.inv_no=sales_invoice_det.inv_no
    and sales_invoice_mst.cust_code=@cust_code
    and sales_invoice_det.grade=@grade
    and sales_invoice_mst.cancel_status<>'C'
    and sales_invoice_mst.inv_date between @date1 and @date2
  select @sales_ret=sum(sales_return_det.quantity) from
    sales_return_det,sales_return_mst where
    sales_return_mst.trans_no=sales_return_det.trans_no
    and sales_return_mst.cust_code=@cust_code
    and sales_return_det.grade=@grade
    and sales_return_mst.sales_return_date between @date1 and @date2
  return(isnull(@sales,0)-isnull(@sales_ret,0))
end

My sql select
select distinct(cust_code),
       udf_sales(cust_code,'A','2002-10-01','2002-10-31') +
       udf_sales(cust_code,'B','2002-10-01','2002-10-31') +
       udf_sales(cust_code,'C','2002-10-01','2002-10-31') +
       udf_sales(cust_code,'K','2002-10-01','2002-10-31') +
       udf_sales(cust_code,'S','2002-10-01','2002-10-31') total_qty,
       udf_sales(cust_code,'A','2002-10-01','2002-10-31') A,
       udf_sales(cust_code,'B','2002-10-01','2002-10-31') B,
       udf_sales(cust_code,'C','2002-10-01','2002-10-31') C,
       udf_sales(cust_code,'K','2002-10-01','2002-10-31') K,
       udf_sales(cust_code,'S','2002-10-01','2002-10-31') S
from sales_invoice_mst
order by total_qty desc;

The report is taking more than 10 minutes.
My wish is shorten the time to retrieve the report.



Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi,

U did a good job.

It turns out that most of time is spent on the "join". I am afraid to say that table design got some problem.

sales_invoice_mst(inv_no varchar(8),
                 inv_date date,cust_code varchar(4))
sales_invoice_det(inv_no varhcar(8),quantity numeric(12,2),grade varchar(1)

I believe that these 2 tables both have inv_no as primary key, it doesn't make any sense to separate them into 2 tables, then do a join of inv_no on 2 tables (creating a view in this situation doesn't help much).

Strictly speaking, your report is a common data warehouse report, which is already beyond what database can handle.

In Data Warehouse, we set up 3 dimension, cust_code, grade and time dimension, which will easily make your report.

Let me know how u think of this, or anything i can do for u.

Thanks.


Author

Commented:
Hi, xxq4813

My tables are normal master detail tables.One to may
relation.
I have given you the table in brief from, otherwise
table has got more columns.
You know one customer may purchase product of
different grade on the same date.Therefore customer
and date should be in the master table and product,grade etc should come in the detail table.

So far , with your help, I am able to produce my report
although taking time. I have ginve the report to the
management. So please take time and give me some idea other
than view.
I like sql may be nested may have union all.
I have thought an idea and will try some time.

My idea is
1(step). select cust_code,sum(quantity) alias tqty sales tables
uion all cust_code,sum(qyantity*-1) sales return tables
      Alias  table1
2(step). select cust_code,grade,
    case grade='A' then  sum(quantity) alias qty_a
    case grade='B' and 'C' and 'K' and 'S' (four cases)
    from sales table
    and similarly for sales return table with -1 as
    multiplying factor.
    Alias table2

the above tow tables come in the from clause


3.(Actually first)
select table1.cust_code,sum(table1.tqty),sum(tabl2.qty_A),sum(tabl2.qty_b....
from step1,step2
where step1.cust_code=step2.cust_code

Presently I have a lot of assignments so I will try
the aobve later.

Thanks for your comment.

 

Author

Commented:
Thanks to Mr David/dtodd from which I learnt
from level select. The following sql is working
and retrieving data within seconds.


select #table1.cust_code,
       sum(#table1.quantity) t_qty,
       sum(#table2.a_qty) a_qty,
       sum(#table2.b_qty) b_qty,
       sum(#table2.c_qty) c_qty,
       sum(#table2.k_qty) d_qty,
       sum(#table2.s_qty) s_qty
from
(select sales_invoice_mst.cust_code as cust_code,
       sum(sales_invoice_det.quantity) as quantity
from sales_invoice_mst,sales_invoice_det
where sales_invoice_mst.inv_no = sales_invoice_det.inv_no
group by sales_invoice_mst.cust_code
union all
select sales_return_mst.cust_code,sum(sales_return_det.quantity*-1)
from sales_return_mst,sales_return_det
where sales_return_mst.trans_no = sales_return_det.trans_no
group by sales_return_mst.cust_code) as #table1,
(select sales_invoice_mst.cust_code as cust_code,
       case when sales_invoice_det.grade = 'A' then
            sum(sales_invoice_det.quantity) else 0 end  a_qty,
       case when sales_invoice_det.grade = 'B' then
            sum(sales_invoice_det.quantity) else 0 end  b_qty,
       case when sales_invoice_det.grade = 'C' then
            sum(sales_invoice_det.quantity) else 0 end  c_qty,
       case when sales_invoice_det.grade = 'K' then
            sum(sales_invoice_det.quantity) else 0 end  k_qty,
       case when sales_invoice_det.grade = 'S' then
            sum(sales_invoice_det.quantity) else 0 end  s_qty
from sales_invoice_mst,sales_invoice_det
where sales_invoice_mst.inv_no = sales_invoice_det.inv_no
group by sales_invoice_mst.cust_code,
         sales_invoice_det.grade
union all
select sales_return_mst.cust_code,
       case when sales_return_det.grade = 'A' then
            sum(sales_return_det.quantity*-1) else 0 end ,
       case when sales_return_det.grade = 'B' then
            sum(sales_return_det.quantity*-1) else 0 end ,
       case when sales_return_det.grade = 'C' then
            sum(sales_return_det.quantity*-1) else 0 end ,
       case when sales_return_det.grade = 'K' then
            sum(sales_return_det.quantity*-1) else 0 end ,
       case when sales_return_det.grade = 'S' then
            sum(sales_return_det.quantity*-1) else 0 end
from sales_return_mst,sales_return_det
where sales_return_mst.trans_no = sales_return_det.trans_no
group by sales_return_mst.cust_code, sales_return_det.grade) as #table2      
where #table1.cust_code = #table2.cust_code
group by #table1.cust_code
order by t_qty desc

Any other idea will be appreciated.

Author

Commented:
Thanks to Mr David/dtodd from which I learnt
from level select. The following sql is working
and retrieving data within seconds.


select #table1.cust_code,
       sum(#table1.quantity) t_qty,
       sum(#table2.a_qty) a_qty,
       sum(#table2.b_qty) b_qty,
       sum(#table2.c_qty) c_qty,
       sum(#table2.k_qty) d_qty,
       sum(#table2.s_qty) s_qty
from
(select sales_invoice_mst.cust_code as cust_code,
       sum(sales_invoice_det.quantity) as quantity
from sales_invoice_mst,sales_invoice_det
where sales_invoice_mst.inv_no = sales_invoice_det.inv_no
group by sales_invoice_mst.cust_code
union all
select sales_return_mst.cust_code,sum(sales_return_det.quantity*-1)
from sales_return_mst,sales_return_det
where sales_return_mst.trans_no = sales_return_det.trans_no
group by sales_return_mst.cust_code) as #table1,
(select sales_invoice_mst.cust_code as cust_code,
       case when sales_invoice_det.grade = 'A' then
            sum(sales_invoice_det.quantity) else 0 end  a_qty,
       case when sales_invoice_det.grade = 'B' then
            sum(sales_invoice_det.quantity) else 0 end  b_qty,
       case when sales_invoice_det.grade = 'C' then
            sum(sales_invoice_det.quantity) else 0 end  c_qty,
       case when sales_invoice_det.grade = 'K' then
            sum(sales_invoice_det.quantity) else 0 end  k_qty,
       case when sales_invoice_det.grade = 'S' then
            sum(sales_invoice_det.quantity) else 0 end  s_qty
from sales_invoice_mst,sales_invoice_det
where sales_invoice_mst.inv_no = sales_invoice_det.inv_no
group by sales_invoice_mst.cust_code,
         sales_invoice_det.grade
union all
select sales_return_mst.cust_code,
       case when sales_return_det.grade = 'A' then
            sum(sales_return_det.quantity*-1) else 0 end ,
       case when sales_return_det.grade = 'B' then
            sum(sales_return_det.quantity*-1) else 0 end ,
       case when sales_return_det.grade = 'C' then
            sum(sales_return_det.quantity*-1) else 0 end ,
       case when sales_return_det.grade = 'K' then
            sum(sales_return_det.quantity*-1) else 0 end ,
       case when sales_return_det.grade = 'S' then
            sum(sales_return_det.quantity*-1) else 0 end
from sales_return_mst,sales_return_det
where sales_return_mst.trans_no = sales_return_det.trans_no
group by sales_return_mst.cust_code, sales_return_det.grade) as #table2      
where #table1.cust_code = #table2.cust_code
group by #table1.cust_code
order by t_qty desc

Any other idea will be appreciated.
Points refunded and moved to PAQ

** Mindphaser - Community Support Moderator **

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial