If three things are true, return them. If not, return other

1

What I’m doing with this code below is pulling all the parts I’ve ordered in the last 5 years, who I purchased it from and what the last price I paid for it. My limitations with this report is sometimes we purchase an item at a higher price from a different vendor because our normal vendor is out. And when I load new pricing from the vendors, my report pulls incorrect prices until after I make a new purchase at the new price. I want to fix this.

I have added three lines below that I commented out because I currently don’t use that information but I want to incorporate it into my code.

I want the code to first pull in all the parts we’ve ordered in the last 5 year….it currently does this
Then I want it to look to see if we have an Auto_Vendor loaded, Auto_Price loaded and that the SELLPRICE1DATE is less than one year old

If we meet all three criteria’s above, I want it to return the:
“Auto_Vendor” in the “cv.name as [Vendor]” line
“Auto_Price” in the “cast(a.UNITP as money) as [Last Cost Paid]” line

If we don’t meet all three criteria’s, I just want the code to return what it currently is returning…..the vendor we last purchased it from and the last price we paid for it. Thanks for your help

Select CV.NAME AS [Vendor]
, a.PARTNUMBER
, a.DESCRIPTN
, CAST(a.UNITP AS money) AS [Last Cost Paid]
--, cast(st.AUTO_PRICE as money) as [New Cost]
--, st.AUTO_VENDOR as [New Vendor]
--, st.SELLPRICE1DATE AS [New Cost Updated]

from PO_LINE a
JOIN CUSTVEND CV ON (CV.ACCTNO = A.ACCTNO)
join STOCK st on (a.PARTNUMBER = st.PARTNUMBER)

where A.LINE_TYPE = '01'
and not a.ADDED_USR in ('KB','CM','KGM')
and a.line = (select MAX(line) from PO_LINE b where a.PARTNUMBER = b.PARTNUMBER and not a.ADDED_USR in ('KB','CM','KGM'))
and A.DUE_DATE > DATEADD(YEAR, -5, GETDATE())
and not a.PARTNUMBER in ('381550KIT','218270')
and not a.acctno = '51409'

order by cv.NAME, a.partnumber, a.line

2

Please provide:

  • database engine and version
  • table descriptions as create statements
  • sample data as insert statements
  • expected output from the sample data you provide
3

Try a sub query or cte to find the information matching the three criteria then left join it to the current query.

4

I think I got it to work with a sub query. I'm new at this but I think it is working now. Thanks

5

Maybe as below, depending on the specifics of your data:

Select 
case when has_auto_vendor = 1 then 'Auto_Vendor' else CV.NAME end AS [Vendor]
, a.PARTNUMBER
, a.DESCRIPTN
, case when has_auto_vendor = 1 then 'Auto_Price' else CAST(CAST(a.UNITP AS money) AS varchar(20)) end AS [Last Cost Paid]
--, cast(st.AUTO_PRICE as money) as [New Cost]
--, st.AUTO_VENDOR as [New Vendor]
--, st.SELLPRICE1DATE AS [New Cost Updated]

from PO_LINE a
JOIN CUSTVEND CV ON (CV.ACCTNO = A.ACCTNO)
join STOCK st on (a.PARTNUMBER = st.PARTNUMBER)
cross apply (
    select case 
        when st.AUTO_VENDOR > '' and st.AUTO_PRICE > 0.0 and st.SELLPRICE1DATE >= DATEADD(YEAR, -1, GETDATE())
        then 1
        else 0 end as has_auto_vendor        
) as assign_alias_names
where A.LINE_TYPE = '01' 
and not a.ADDED_USR in ('KB','CM','KGM')
and a.line = (select MAX(line) from PO_LINE b where a.PARTNUMBER = b.PARTNUMBER and not a.ADDED_USR in ('KB','CM','KGM'))
and A.DUE_DATE > DATEADD(YEAR, -5, GETDATE())
and not a.PARTNUMBER in ('381550KIT','218270')
and not a.acctno = '51409'

order by cv.NAME, a.partnumber, a.line