By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,324 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

Compare dates

P: 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 ?
Dec 3 '08 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
Could you post some sample data and how you want your result?

-- CK
Dec 3 '08 #2

P: 2
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
Dec 3 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.