2
I created a temp table
select ol.order_no,ol.date_created,ol.item_id,ol.inv_mast _uid,ol.oe_hdr_uid
INTO #temp
from p21_view_oe_line ol
where datediff(dd,ol.date_created,getdate()) = 7 and
ol.source_loc_id=108843
I have info for last 7 days..
I am trying to generate a report that will be results which will give me the order no for a sale if the sales margin was lower then previously sold.
select distinct oh.taker,oh.order_no,ad.name as 'Customer name',m.item_id,
oh.order_date,
case when oel.extended_price = 0 then 0
else (extended_price - commission_cost) / oel.extended_price end as 'Margin%'
--case when oh.order_date = 0 then 0
--else datediff(dd,oel.date_created,getdate())< (oh.order_date end as 'Previous Order Date'
--(select date_created
--from #temp as [Previous Order Date]
--where datediff(dd,oel.date_created,getdate()) =1
--)
from p21_view_oe_hdr oh
left join p21_view_oe_line oel on oel.order_no=oh.order_no
left join p21_view_address ad on ad.id=oh.customer_id
left join p21_view_inv_mast m on m.inv_mast_uid=oel.inv_mast_uid
left join #temp t on t.date_created=oel.date_created
and m.inv_mast_uid=t.inv_mast_uid
and t.item_id=oel.item_id
and t.date_created < oel.date_created and t.oe_hdr_uid=oh.oe_hdr_uid
where oh.location_id=108843
--datediff(dd,oel.date_created,getdate())< oh.order_date as
--and oel.date_created < getdate ()
order by 1
the abover query gives me the new orders but i need to add code where i can find if there was a previous order ?
