Hi Rick-
Here is the code........
select th.av_transaction_id as transaction_id,
th.store_no as hidden_store_no,
th.store_no, th.register_no,
'00000000000000000000' as customer_no,
th.transaction_date,
th.transaction_no,
sum((tl.gross_line_amount - tl.pos_discount_amount) * db_cr_none *
-1) as gross_amount,
sum((tl.gross_line_amount - tl.pos_discount_amount) * db_cr_none *
-1) as discount_amount,
'z1000000000000000000' as line_note, th.cashier_no
into #dd_dc
from mm.dbo.av_transaction_header th,
mm.dbo.av_transaction_line tl
where th.av_transaction_id = tl.av_transaction_id
and th.store_no between {{FromStore%}} and {{ToStore%}}
and th.transaction_date between {{FromDate*}} and {{ToDate*}}
and th.transaction_void_flag = 0
and tl.line_void_flag = 0
and tl.line_object_type = 1
and th.av_transaction_id in (select distinct av_transaction_id
from mm.dbo.av_transaction_line
where line_object in (10,126,300,304))
group by th.av_transaction_id, th.store_no,th.store_no, th.register_no,
th.transaction_date, th.transaction_no, th.cashier_no
update #dd_dc
set #dd_dc.discount_amount = tl.gross_line_amount
from mm.dbo.av_transaction_line tl, #dd_dc
where tl.av_transaction_id = #dd_dc.transaction_id
and tl.line_object in (10,126,300,304)
update #dd_dc
set #dd_dc.line_note = SUBSTRING(ln.line_note, 1, 20)
from mm.dbo.av_line_note ln, #dd_dc, av_transaction_line tl where
ln.av_transaction_id = #dd_dc.transaction_id
and ln.av_transaction_id = tl.av_transaction_id
and tl.line_object in (10,126,300,304)
and tl.line_id = ln.line_id
update #dd_dc
set #dd_dc.customer_no = isnull(c.customer_no,0)
from mm.dbo.av_customer c, #dd_dc
where c.av_transaction_id = #dd_dc.transaction_id
insert into #dd_dc
select th.transaction_id as transaction_id,
th.store_no as hidden_store_no,
th.store_no, th.register_no,
'00000000000000000000' as customer_no,
th.transaction_date,
th.transaction_no,
sum((tl.gross_line_amount - tl.pos_discount_amount) * db_cr_none *
-1) as gross_amount,
sum((tl.gross_line_amount - tl.pos_discount_amount) * db_cr_none *
-1) as discount_amount,
'z1000000000000000000' as line_note, th.cashier_no
from mm.dbo.transaction_header th, mm.dbo.transaction_line tl
where tl.transaction_id = th.transaction_id
and th.store_no between {{FromStore%}} and {{ToStore%}}
and th.transaction_date between {{FromDate*}} and {{ToDate*}}
and th.transaction_void_flag = 0
and tl.line_void_flag = 0
and tl.line_object_type = 1
and th.transaction_id in (select distinct transaction_id
from mm.dbo.transaction_line
where line_object in (10,126,300,304))
group by th.transaction_id, th.store_no, th.store_no, th.register_no,
th.transaction_date, th.transaction_no, th.cashier_no
update #dd_dc
set #dd_dc.discount_amount = tl.gross_line_amount
from mm.dbo.transaction_line tl, #dd_dc
where tl.transaction_id = #dd_dc.transaction_id
and tl.line_object in (10,126,300,304)
update #dd_dc
set #dd_dc.line_note = SUBSTRING(ln.line_note, 1, 20)
from mm.dbo.line_note ln, #dd_dc, transaction_line tl where
ln.transaction_id = #dd_dc.transaction_id
and ln.transaction_id = tl.transaction_id
and tl.line_object in (10,126,300,304)
and tl.line_id = ln.line_id
update #dd_dc
set #dd_dc.customer_no = isnull(c.customer_no,0)
from mm.dbo.customer c, #dd_dc
where c.transaction_id = #dd_dc.transaction_id
select * from #dd_dc
drop table #dd_dc
-------------------------------------
i want to save the data to a table because i query it before i append to the
master. and i want to code the date & store#'s.
so should i push to get the sql server side code it & schedule it or do i go
w/ access pass-through query & windows task scheduler?
your opinion please?
thanks,
patti
