First of all thank you very much for a prompt reply to my query and here is the requested information:
I am using Power Builder as a Front End:
Process Select Statement is:
select
d.gst_type
, d.gst_type gst_typeo
, sum(sale_qty) qty
, sum(sale_discount) sale_discount
, sum(cexcl_stax) cgross
, sum(cstax) cstax
, sum(cincl_stax) inclusive_gross
, sum(cadstax) pur_tax
, sum(000000000.00) adjust_amount
, sum(000000000.00) margin_per
from stax_sale_det d
join stax_sale_mst pm on pm.trans_no=d.trans_no
where pm.mfgcom_code = :ra_mfgcomcode
and trans_date between :ra_trans_datef and :ra_trans_datet
and d.sale_type='UNR'
group by
d.gst_type
order by 1
Report 1 Select Statement is:
select
ntn_no = c.ntn_no
, cnic_no = c.cnic_no
, mfg_name = g.mfg_name
, cust_name = c.cust_name
, cust_type = (case when d.sale_type='LOC' then 'Registered' else 'Un-Registered' end)
, doc_type = 'SI'
, doc_no = pm.trans_no
, doc_date = right('0'+convert(varchar,day(pm.trans_date)),2) +'/'
+ right('0'+convert(varchar,month(pm.trans_date)),2) + '/'
+ right('0'+convert(varchar,year(pm.trans_date)),4)
, Hs_code = NULL
, sale_type = (case when (select count(*) from stp_cust_examption where cust_code=c.cust_code and pm.trans_date between start_date and end_date)>0 then 'E' else d.gst_type end)
, gst_rate = (d.gst_rate / 100)
, sale_qty = 0
, uom = NULL
, d.gst_type
, excl_stax = case when sum(isnull(free_qty,0))>0 then sum(cstax/case when d.gst_rate=0 then 1 else d.gst_rate/100 end) else sum(d.cexcl_stax) end
, stax = sum(cstax)
, sdis = sum(sale_discount)
, extra_tax = sum(cadstax)
, stax_held = NULL
from stax_sale_det d
join stax_sale_mst pm on pm.trans_no=d.trans_no
join stp_mfg g on (pm.mfg_code=g.mfg_code)
join stp_cust c on ( pm.cust_code = c.cust_code)
join stp_product p on ( d.prod_code = p.prod_code)
where pm.mfgcom_code = :ra_mfgcom_code
and ( isnull(:ra_cust_code,'')='' or pm.cust_code = :ra_cust_code)
and (pm.sale_type = :ra_sale_type or isnull(:ra_sale_type,'')='')
and trans_date between :ra_date_from and :ra_date_to
and ( p.gst_type = :ra_gst_type or isnull(:ra_gst_type,'')='')
and d.sale_qty>0
and g.mfgcom_code = :ra_mfgcom_code
group by c.ntn_no
, c.cnic_no
, c.cust_code
, c.cust_name
, c.gst_no
, d.gst_type
, pm.trans_no
, pm.trans_date
, d.gst_rate
, pm.mfg_code
, g.mfg_name
, d.sale_type
order by 4, 9
Report 2 Select Statement is:
select
inv_no = m.trans_no
, inv_date = m.trans_date
, cust_descr = c.cust_name + ' ' + c.addr1 + char(13) + isnull(c.gst_no,'')
, goods_descr = (case when (select count(*) from stp_cust_examption where cust_code=m.cust_code and m.trans_date between start_date and end_date)>0 then 'E' else d.gst_type end)
, qty = sum(d.sale_qty)
, gross_amt = sum(d.sale_qty * d.rate)
, discount = sum(sale_discount)
, gst_rate= d.gst_rate
, tot_stax = sum(cstax)
, incl_value = sum(cincl_stax - sale_discount)
, tot_ex_value = case when sum(isnull(free_qty,0))>0 then sum(cstax/case when d.gst_rate=0 then 1 else d.gst_rate/100 end) else sum(d.cexcl_stax) end
, sale_tax = sum(cstax)
, tot_adv_inc_tax_per = adv_inc_tax_per
, tot_adv_inc_tax_per_amt = sum(cadstax)
, company_name = dbo.f_decrypt(k.company_name)
, addr1 = k.addr1
, gst_no = convert(bigint,k.gst_no)
, ntn_no = k.ntn_no
, period_name = 'From ' + convert(varchar,:ra_date_from,103) +' To' + convert(varchar,:ra_date_to,103)
,d.gst_type
from stax_sale_mst m
join stax_sale_det d on ( m.trans_no = d.trans_no and m.company_code = d.company_code)
join stp_cust c on (m.cust_code = c.cust_code)
join company k on ( 1=1)
join stp_product p on (p.prod_code=d.prod_code)
where (1=1)
and (m.mfgcom_code=:ra_mfgcom_code or :ra_mfgcom_code = '')
and (m.mfg_code = :ra_mfg_code or isnull(:ra_mfg_code,'')='')
and (m.sale_type = :ra_sale_type or isnull(:ra_sale_type,'')='')
and (m.cust_code = :ra_cust_code or isnull(:ra_cust_code,'')='')
and trans_date between :ra_date_from and :ra_date_to
and sale_qty<>0
and (m.company_code = :gs_company_code)
group by m.trans_no
, m.trans_date
, m.cust_code
, c.cust_name
, c.addr1
, c.gst_no
, k.company_name
, k.addr1
, k.gst_no
, k.ntn_no
, d.gst_rate
, d.gst_type
, d.adv_inc_tax_per
order by 4
However, the issue is that on one PC this gives same results and other PC it give different results, then suddenly after 2 - 3 hours all start giving matching results. Secondly, as an other guy here on the forum commented that when sales change it is obvious that results will changed but in this case the data is static, we run a process which bulk insert data from production tables to these reporting tables. And the insert statement is as under:
delete from stax_sale_mst where mfgcom_code = :p_mfgcomcode and trans_date between :p_transdate and :p_transdate2 and sale_type='UNR';
insert into stax_sale_mst
(
trans_no
, company_code
, trans_date
, mfg_code
, cust_code
, sale_type
, status
, remarks
, mfgcom_code
)
select
(select right(convert(varchar,year(dt1)),2)
from am_next_key k2
where trans_date between k2.dt1 and k2.dt2
)
+
right('000000'+convert(varchar, isnull((select max(convert(bigint,right(trans_no,6))) from stax_sale_mst),0)+(row_number() over(order by trans_date))),6)
, company_code
, trans_date
, mfg_code
, cust_code
, sale_type
, status
, remarks
, :p_mfgcomcode
from
(
select distinct
company_code
, trans_date
, mfg_code = '101061'
, cust_code = '024577'
, sale_type = 'UNR'
, status
, remarks = 'Consolidated Invoice - Unregistered Sale Daily'
from sale_loc_mst
where mfg_code in(select mfg_code from stp_mfg where isnull(invoicing_per,'N')='Y' and mfgcom_code = :p_mfgcomcode)
and trans_date between :p_transdate and :p_transdate2
group by company_code
, trans_date
, mfg_code
, status
, remarks
) r;
if sqlca.sqlcode<>0 then
messagebox('Error', sqlca.sqlerrtext)
end if
insert into stax_sale_det
( trans_no
, company_code
, sale_type
, line_item
, gst_type
, prod_code
, sale_qty
, rate
, gst_rate
, ced_rate
, sale_discount
, ad_gst_rate
, is_rp
, rprate
, mfgcom_code
)
select (select top 1 trans_no from stax_sale_mst where trans_date=d.trans_date and sale_type = 'UNR' and cust_code='024577' and mfg_code = '101061' and mfgcom_code=:p_mfgcomcode)
, :gs_company_code
, sale_type = 'UNR'
, line_item= row_number() over(order by trans_date)
, gst_type = gst_type
, prod_code
, sale_qty = sale_qty
, rate = rate
, gst_rate = gst_rate
, ced_rate = 0
, isnull(sale_discount,0) + isnull(trade_amt,0)
, ad_gst_rate
, is_rp
, rprate
, :p_mfgcomcode
from
(
select
trans_date,
prod_code,
gst_type,
sum(sale_qty) sale_qty,
rate = dbo.f_prod_ratenewgst(m.trans_date, m.mfg_code, d.prod_code, 'S'),
gst_rate = dbo.f_prod_ratenewgst(m.trans_date, m.mfg_code, d.prod_code, 'G'),
ad_gst_rate = dbo.f_prod_ratenewgst(m.trans_date, m.mfg_code, d.prod_code, 'A'),
rprate = dbo.f_prod_ratenewgst(m.trans_date, m.mfg_code, d.prod_code, 'R'),
ced_rate,
sum(sale_discount) sale_discount,
sum(trade_amt) trade_amt,
d.is_rp
from sale_loc_mst m join sale_loc_det d on m.trans_no = d.trans_no
where m.trans_date between :p_transdate and :p_transdate2
and m.mfg_code in(select mfg_code from stp_mfg where isnull(invoicing_per,'N')='Y' and mfgcom_code = :p_mfgcomcode)
and ((gst_type='E' and gst_rate=0) or (gst_type<>'E' and gst_rate>0))
and isnull(gst_type,'')<>''
// New Change
and prod_code not in (select distinct prod_code from stax_sale_mst m join stax_sale_det d on m.trans_no=d.trans_no where m.trans_date between :p_transdate and :p_transdate2)
// New Change
group by trans_date, gst_type, prod_code, rate, gst_rate, ced_rate, d.ad_gst_rate, d.is_rp, d.rprate, m.mfg_code
) d
order by 1,prod_code ;
if sqlca.sqlcode=0 then
commit;
else
messagebox('Error', sqlca.sqlerrtext)
rollback;
end if
return sqlca.sqlcode
I hope this will explain the situation. Again in my opinion these queries are irrelevant because they are proven correct and the same query shows different result on different PC(s) shows different results if we run from source code mean from Power Builder IDE and different from complied EXE File. To me its a bug in SQL Server 2008 R2 may be we need some patch / fix to install and yes I thing is that it is installed on Windows 7 Ultimate 64-Bit Operating System and MS SQL Server 2008 R2 is also a 64-Bit version recently I also installed Service Pack 3 for SQL Server and Service Pack 1 for Windows 7.
Thanks Again.
Zafar