468,242 Members | 1,575 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

Compare dates

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

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,
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
2 1717
2,878 Expert 2GB
Could you post some sample data and how you want your result?

-- CK
Dec 3 '08 #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 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.

Similar topics

3 posts views Thread by jrc4728 | last post: by
4 posts views Thread by alexis | last post: by
4 posts views Thread by Richard Hollenbeck | last post: by
9 posts views Thread by Rich | last post: by
9 posts views Thread by Rimuen | last post: by
1 post views Thread by godsella | last post: by
12 posts views Thread by Assimalyst | last post: by
6 posts views Thread by cd123 | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.