Compare dates

[farhanm]

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_lin e ol
where datediff(dd,ol. date_created,ge tdate()) = 7 and
ol.source_loc_i d=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.ord er_no,ad.name as 'Customer name',m.item_id ,
oh.order_date,
case when oel.extended_pr ice = 0 then 0
else (extended_price - commission_cost ) / oel.extended_pr ice end as 'Margin%'
--case when oh.order_date = 0 then 0
--else datediff(dd,oel .date_created,g etdate())< (oh.order_date end as 'Previous Order Date'
--(select date_created
--from #temp as [Previous Order Date]
--where datediff(dd,oel .date_created,g etdate()) =1
--)
from p21_view_oe_hdr oh
left join p21_view_oe_lin e oel on oel.order_no=oh .order_no
left join p21_view_addres s ad on ad.id=oh.custom er_id
left join p21_view_inv_ma st m on m.inv_mast_uid= oel.inv_mast_ui d
left join #temp t on t.date_created= oel.date_create d
and m.inv_mast_uid= t.inv_mast_uid
and t.item_id=oel.i tem_id
and t.date_created < oel.date_create d and t.oe_hdr_uid=oh .oe_hdr_uid
where oh.location_id= 108843
--datediff(dd,oel .date_created,g etdate())< oh.order_date as
--and oel.date_create d < 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 ?

[ck9663]

Could you post some sample data and how you want your result?

-- CK

[farhanm]

sample data

Taker Order_no Customer_Name Item_Id Order_date Margin% Previous Order date
ADEL 1829854 R & T REPAIR SERVICE SUP B28 38:30.0 92% 44:42.8
ADEL 1829854 R & T REPAIR SERVICE VICTO 1407-0013 38:30.0 84% 44:42.8
ADEL 1838392 CO WELDING SUPPLY INC PACKAGING/HANDLING/DELIVERY, OUT 10:28.0 101% 44:42.8
ADEL 1847612 SP INDUSTRIES INC ARMSTR 069-78-110 10:58.0 112% 44:42.8
ADEL 1847612 SP INDUSTRIES INC ARMSTR 069-78-112 10:58.0 129% 44:42.8
ADEL 1847639 SP INDUSTRIES INC ARMSTR 069-78-110 30:51.0 112% 44:42.8

please check the attachment for the excel doc